I had to analyze a performance problem this morning, and it occurred to me it is a good example of troubleshooting queries impacted by the clustering_factor of an index. As such, below is the email exchange. I took a look,…
Category: Oracle
Converting lock modes to determine what the root cause is
Take name|mode print hex(1415053316) …and convert to hex… 0x54580004 …then take the first two, second two, and last four values to get the reason code. 54 in hex = T 58 in hex = X and 0004 = 4. This…
VIP migrations
We start with a two node cluster with all components up. linux2:oracle:tst10g2:/home/oracle>./crsstat.ksh HA Resource Target State ———– —— —– ora.linux1.ASM1.asm ONLINE ONLINE on linux1 ora.linux1.LISTENER_LINUX1.lsnr ONLINE ONLINE on linux1 ora.linux1.gsd ONLINE ONLINE on linux1 ora.linux1.ons ONLINE ONLINE on linux1 ora.linux1.vip…
Troubleshooting “library cache lock”
If you have several users waiting on a library cache lock, they are normally blocked by one user that is waiting on a library cache pin. You have to find out who is blocking the user waiting on the library…
cx_Oracle cursor fetching behaviour
I have noticed this over the past few months, and think I still may be missing something. Given the following code… #!/home/oracle/local/bin/python import cx_Oracle import sys con = cx_Oracle.connect(“howards”,”******”,”perfrac”) con.cursor().execute(“alter session set events ‘10046 trace name context forever, level 12′”);…
Fire! Fire! Fire!
If you find yourself in the middle of a hung database, you can do what follows (see Metalink for additional information) Can login to the database alter session set events ‘immediate trace name SYSTEMSTATE level 10’; –three time waiting five…
SQL Bind Capture – Part 3
I needed to find out which variables were producing a poor execution plan. Short of enabling 10046 trace with binds enabled, I found the following is a good approximation. Basically: 1. Find the PLAN_HASH_VALUE for the SQL_ID with the poor…
Using a cursor variable in SQL*Plus
Below is a simple example of opening a cursor variable in SQL*Plus and then printing its contents. DBA’s can use this if they are not PL/SQL proficient to do some simple test casing with developer cursor code. SQL> variable b…
Shell scripting stats from a 10046 trace
This morning, I enabled a 10046 trace at level 12 for a slow session. I did not scope the trace interval (ala Cary Milsap), so I didn’t even have the query text in the trace file (although I knew what…
Which events are set in my instance?
Quick and dirty today… For 10g… connect / as sysdba oradebug setmypid oradebug dump events 4 …and for 11g… connect / as sysdba oradebug setmypid oradebug eventdump session