Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘sql*trace

Tracing user specific SQL statements

with 4 comments

Assuming following request:

I would like to trace SQL statements of specific Oracle user, not any other users.

My first choice would be the powerful end to end tracing facility of Oracle 10g.
Next one is dbms_xplan package which is not only powerful but also the most important package in Oracle.

1. End to end tracing to trace specific user.

UKJA@ukja102>  -- 1. Create logon trigger in which I give identifier to user's session                                                   
UKJA@ukja102> connect /as sysdba                                                     
Connected.                                                                           
                                                                                     
SYS@ukja10> create or replace trigger logon_trigger                                  
  2  after logon on database                                                         
  3  begin                                                                           
  4    if ora_login_user = 'UKJA' then                                               
  5      dbms_session.set_identifier('ukja');                                       
  6    end if;                                                                       
  7  end;                                                                            
  8  /                                                                               
                                                                                     
Trigger created.                                                                     
                                                                                     
SYS@ukja10> -- 2. Enable sql*trace for given client identifier                                                                       
SYS@ukja10> exec dbms_monitor.client_id_trace_enable(client_id=>'ukja', -            
>           waits=>true, binds=>true);                                               
                                                                                     
PL/SQL procedure successfully completed.                                             
                                                                                     
SYS@ukja10>  -- 3. Logon as UKJA user and execute some queries                                                                         
SYS@ukja10> connect ukja/ukja@ukja102                                                
Connected.                                                                           
                                                                                     
UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> connect ukja/ukja@ukja102                                              
Connected.                                                                           
                                                                                     
UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> connect ukja/ukja@ukja102                                              
Connected.                                                                           

UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> -- 4. Disable sql*trace for given client identifier                                                                      
UKJA@ukja102> connect sys /as sysdba                                                 
Connected.                                                                           
                                                                                     
SYS@ukja10> exec dbms_monitor.client_id_trace_disable(client_id=>'ukja');            
                                                                                     
PL/SQL procedure successfully completed.                                             
                                                                                     
SYS@ukja10>  -- 5. Now the most difficult part. 
Our 3 trace files are scattered in the user dump directory. We should merge it.
The fantastic trcsess tool does this for me.   
                                                                      
SYS@ukja10> col value new_value dump_dir                                             
SYS@ukja10>                                                                          
SYS@ukja10> select value from v$parameter where name = 'user_dump_dest';             
                                                                                     
VALUE                                                                                
--------------------                                                                 
C:\ORACLE\ADMIN\UKJA                                                                 
10\UDUMP                                                                             
                                                                                     
                                                                                     
SYS@ukja10> -- Merge!                                                                         
SYS@ukja10> ho trcsess output=trc1.out clientid=ukja &dump_dir\*.trc                 
                                                                                     
SYS@ukja10> -- And tkprof                                                                          
SYS@ukja10> ho tkprof trc1.out tk1.out                                               

 -- Tkprof report. The result(3 executions from different sessions) was merged successfully! 
select count(*) 
from
 t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.00          0          9          0           3                                                                                     



This end to end tracing is quite flexible and powerful. Love it!

2. DBMS_XPLAN.DISPLAY_CURSOR to trace specific user.

UKJA@ukja102>  -- Following simple query would capture the queries
which start with 'select /*+ gather_plan_statistics */ count(*)%' and
executed by UKJA user
UKJA@ukja102> select                                                                 
  2      cursor(select * from table(                                                 
  3          dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last')))  
  4  from v$sql s                                                                    
  5  where s.parsing_schema_name = 'UKJA'                                            
  6        and s.sql_text like 'select /*+ gather_plan_statistics */ count(*) %'     
  7  ;                                                                               
                                                                                     
CURSOR(SELECT*FROMTA                                                                 
--------------------                                                                 
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  fuynj2z4vdjks, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t3                                
                                                                                     
Plan hash value: 463314188                                                           
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| T3   |      1 |   1000 |   1000 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  5cunshnzqa8vb, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t2                                
                                                                                     
Plan hash value: 3321871023                                                          
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |      1 |      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  cz662tawx6sbt, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t1                                
                                                                                     
Plan hash value: 3724264953                                                          
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------
                                                                                     



Again, very easy and powerful.

Written by Dion Cho

March 10, 2009 at 6:57 am

Posted in Misc.

Tagged with , ,

Follow

Get every new post delivered to your Inbox.

Join 58 other followers