Here's a treat for Oracle geeks, hackers and maniacs out there... Update: As the beginning says, this article was meant as something interesting about Oracle's internals and CTRL+C / OCICancel() handling. There's a more practical way for canceling session calls if you are running Oracle Enterprise Edition and are currently using resource manager: You can set the consumer group for a session to CANCEL_SQL to cancel its current call: DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ( session_id IN NUMBER, session_serial IN NUMBER, consumer_group IN VARCHAR2); Thanks to commenter "null" for this info. Note that I haven't tested how/whether this feature works correctly so there's homework for you ;-) I recently received a question ...

Tanel Poder
Uncategorized

If you are in Singapore and have 24th Feb afternoon available then you can register and join a free Oracle performance troubleshooting seminar I'm doing in Singapore Management University's (SMU) campus. The seminar will be about: Systematic Oracle Performance Troubleshooting Identifying performance troublemakers Understanding execution plans The date is Wednesday, 24th Feb The seminar time is from 15:30-19:00 (don't be late) Registration and more details are here: http://tech.e2sn.com/events/singapore-oracle-meetup/

Tanel Poder
Cool stuff, Oracle, Performance, Troubleshooting, Tuning

The LatchProf and LatchProfX scripts allow you to be more systematic with latch contention troubleshooting and tuning. No more guesswork is needed as these scripts give you exact session IDs and in this version also SQLIDs of the troublemaking applications. You can download the new versions here: LatchProf (reads V$ views) LatchProfX (reads X$ tables, but gives better info, run as SYS) Example output (with SQLID info) is below: [code]SQL> @latchprof name,sid,sqlid % % 100000 -- LatchProf 1.21 by Tanel Poder ( http://www.tanelpoder.com ) NAME                                       SID SQLID               Held       Gets  Held %     Held ms Avg hold ms ----------------------------------- ---------- ------------- ---------- ---------- ------- ----------- ----------- cache buffers chains                       133 3jbwa65aqmkvm ...

Tanel Poder
Administration, Cool stuff, Oracle, Performance, Troubleshooting

I have written the first article to the troubleshooting section of my new website tech.E2SN.com: It's about a very valuable Oracle troubleshooting tool -> ERRORSTACK trace. I cover 4 frequently asked questions there: Reading the current executing SQL statement text from errorstack trace Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace Reading the current bind variable values from errostack trace Identifying how much private (UGA) memory a cursor is using You can read it here: http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output By the way, if you like my new website, feel free to link to it !!! ;-) http://tech.e2sn.com

Tanel Poder
Administration, Cool stuff, Internals, Oracle, Troubleshooting, Tuning

Just to let you know where I'll be in the near future :) Seminars Advanced Oracle Troubleshooting 2.0 I have rearranged and adjusted the material so it flows smoother, has even more practical tools and scripts and describes some internals even deeper ;-) 3-5 March - New York 15-17 March - San Francisco These dates are close, last chance to register ;-) Note that after these I won't be doing an Advanced Oracle Troubleshooting class in US for a while... Conferences Hotsos Symposium, Dallas, TX, 8-11 March I will deliver 2 presentations at the Hotsos Symposium 2010 Back to Basics: Choosing the Starting Point of ...

Tanel Poder
Cool stuff, Oracle, Training

I just published a new script and article about calculating the real Oracle process memory usage on Solaris. The problem with V$PROCESS* views (and the V$SESSTAT) is that they will tell you what Oracle thinks it's using, not the real amount of memory used. There will be discrepancies due how memory is actually allocated in OS, libraries out of Oracle's control, the static memory areas inside Oracle binary and of course bugs. I was working on one of such problems and decided to make my life easier by writing the script. It's not so much about calculating the exact figures (they will ...

Tanel Poder
Uncategorized

Kyle Hailey has started putting together a much needed Oracle wait event reference. You can access it here. By the way, Oracle documentation also has a wait event reference section, it has more events, but it's less detailed... I have plans to go deep into some wait events and cover some less common ones in tech.E2SN too... in the future ;-)

Tanel Poder
Cool stuff, Internals, Oracle, Performance, Tuning

Coskan Gundogar and Karl Arao have written two interesting articles about Oracle performance analysis and visualization, check these out! Coskan's article: http://coskan.wordpress.com/2010/01/27/working-with-statspack-part-1a-diagnosis/ Karl's article: http://karlarao.wordpress.com/2010/01/31/workload-characterization-using-dba_hist-tables-and-ksar/ Note that in March I will be releasing PerfSheet v3.0, which will have lots of improvements! ;-)

Tanel Poder
Cool stuff, Oracle, Performance

In my Beyond Oracle Wait interface article I troubleshooted a test case where an execution plan somehow went "crazy" and started burning CPU, lots of logical IOs and the query never completed. I have uploaded the test case I used to my new website, to a section where I will upload some of my demo scripts which I show at my seminars (and people can download & test these themselves too): http://tech.e2sn.com/oracle-seminar-demo-scripts Basically what I do is this: I run the query with bind variable values where only a handful of rows match the filter condition. Thus Oracle picks nested loop join (and indexed ...

Tanel Poder
Oracle, Performance, SQL, Troubleshooting

I have been very busy over last months (as you see from the lack of blog entries). Part of the reason is that I've been building new seminar material and now I'm pleased to announce some first seminar dates! I have updated new seminar dates and cities in m new webpage: http://tech.e2sn.com/oracle-training-seminars From April 2010 I offer total 3 different seminars Advanced Oracle Troubleshooting v2.0 (3 days) Advanced Oracle SQL Tuning (3 days) Partitioning and Parallel Execution for Performance (1 day) I have rearranged the Advanced Oracle Troubleshooting class based on customer feedback, removed some content, added new content and I think this deserves a new version ...

Tanel Poder
Cool stuff, Oracle, Oracle 11g, Oracle 11gR2, Performance, SQL, Troubleshooting