Rowsource For Each Execution

When you trace SQL statement sometimes what happens is if it’s already parsed then you will not get rowsource operation for each execution in trace. This might need to re-parse that statement.  And its more problematic when application is involved where most SQL’s are parsed already.

Let’s see simple example.

  • 1st Example
  • exec dbms_monitor.session_trace_enable(NULL, NULL, TRUE, TRUE);
    select count(*) from scott.emp;
    select count(*) from scott.emp;
    select count(*) from scott.emp;
    exec dbms_monitor.session_trace_disable();
    
  • 2nd Example
  • exec dbms_monitor.session_trace_enable(plan_stat=>'ALL_EXECUTIONS');
    select count(*) from scott.emp;
    select count(*) from scott.emp;
    select count(*) from scott.emp;
    exec dbms_monitor.session_trace_disable();
    
  • From 1st Trace File
  • SQL ID: abj9tmfcs15bm
    Plan Hash: 2937609675
    select count(*) from scott.emp
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        3      0.00       0.01          0          0          0           0
    Execute      3      0.00       0.00          0          0          0           0
    Fetch        6      0.00       0.02          1          3          0           3
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      0.00       0.03          1          3          0           3
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 89 
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=1 pr=0 pw=0 time=0 us)
            14         14         14   INDEX FULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=13 us cost=1 size=0 card=14)(object id 73197)
    
  • From 2nd Trace File
  • SQL ID: abj9tmfcs15bm
    Plan Hash: 2937609675
    select count(*) from scott.emp
    
    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.03          1          3          0           3
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      0.00       0.03          1          3          0           3
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 89
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=1 pr=1 pw=0 time=0 us)
            14         14         14   INDEX FULL SCAN PK_EMP (cr=1 pr=1 pw=0 time=13 us cost=1 size=0 card=14)(object id 73197)
    
    About these ads

    About Taral
    I am Rookie to Oracle Technology so let's see where it goes

    One Response to Rowsource For Each Execution

    1. Pingback: TKPROF in 11gR2 – New Rows Columns « Started Learning Oracle

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: