Archive for December, 2009

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 plan 2. Pass this value to V$SQL_BIND_CAPTURE and print the variables associated with this... »

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 refcursor SQL> begin 2 open :b for select username from dba_users; 3... »

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 it was). The resulting trace file was huge at 200MB. I... »