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.