Saturday, April 07, 2007

An effect of sql_trace=true

Tom Kyte has a recent post about explain plan.

In that there is something I have not realized before that I want to share. When you execute a sql statement after you set sql_trace=true it is hard parsed again even if it is already in the shared pool.

A simple test is below.


SQL> select 'test' from dual;

'TES
----
test

SQL> alter session set sql_trace=true;

Session altered.

SQL> select 'test' from dual;

'TES
----
test

SQL> exit


The tkprof output shows that the same statement run after setting sql_trace=true is hard parsed again.


select 'test'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 1 0 1

Misses in library cache during parse: 1




Compare this with the same sql run two times after setting sql_trace=true.


SQL> alter session set sql_trace=true;

Session altered.

SQL> select 'test2' from dual;

'TEST
-----
test2

SQL> r
1* select 'test2' from dual

'TEST
-----
test2


SQL> exit



select 'test2'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 2 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 2 0 2

Misses in library cache during parse: 1


Sql trace has the side effect of hard parsing every statement executed after it is enabled. If you have your sql statements already in the shared pool before enabling sql trace, be careful that you will be seeing the execution plans for your particular execution in that particular time, not the ones from the shared pool.

3 comments:

  1. In the second case, running same query twice after setting sql_trace = true, parse equals 2. But this is not hard parse, it is softparse.

    ReplyDelete
  2. True, in the second case there are 2 parses. One of them is a hard parse as indicated by "Misses in library cache during parse: 1". The other is a soft parse.

    ReplyDelete
  3. after you set sql_trace=true your envirnoment changes, check V$SQL_SHARED_CURSOR and you will see two child cursor thats why there is a miss

    ReplyDelete