TKPROF EXPLAIN always performs a hard parsing

TKPROF with ‘explain=user/pwd’ option always do a hard parsing even though an existing execution plan is cached in shared pool. It will parse the statement with current parsing context of ‘user’ and calculate with statistics of the moment of parsing, if it finds statistics missing or stale it will performs dynamic sampling on the tables. This is also the default behavior of EXPLAIN PLAN.

With “TKPROF EXPLAIN=” we can compare “PLAN GUESSED AT PRESENT” with “PLAN REALLY EXECUTED” and if they’re different find out the reason why Oracle chosen a different plan. Here is a sample output of “TKPROF EXPLAIN=”:

********************************************************************************

select id, object_name
from
 t where id = 1

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     3371      0.19       0.24          0      17463          0       50545
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3373      0.19       0.24          0      17463          0       50545

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 78  (GUANG)

Rows     Row Source Operation
-------  ---------------------------------------------------
 50545  TABLE ACCESS BY INDEX ROWID T (cr=17463 pr=0 pw=0 time=564077 us)
 50545   INDEX RANGE SCAN T_IDX (cr=13427 pr=0 pw=0 time=109035 us)(object id 60612)

Rows     Execution Plan
-------  ---------------------------------------------------
 0  SELECT STATEMENT   MODE: ALL_ROWS
 50545   TABLE ACCESS (FULL) OF 'T' (TABLE)

********************************************************************************

Active Sql Tracing

Activate sql tracing on current session

— set maximum trace file size

alter session set max_dump_file_size='4000m';

— set a prefix to the trace file, by default it’s called <dbname>_ora_<spid>.trc

alter session set tracefile_identifier ='perf_test';

— activate basic sql_trace

alter session set events '10046 trace name context forever, level 1';

— activate basic sql_trace + bind variables

alter session set events '10046 trace name context forever, level 4';

— activate basic sql_trace + wait events

alter session set events '10046 trace name context forever, level 8';

— activate basic sql_trace + bind variables + wait events

alter session set events '10046 trace name context forever, level 12';

— diactivate sql tracing

alter session set events '10046 trace name context off';

Activate sql tracing on other sessions

— to identify other session’s sid and serial#

set lines 200 pages 1000 verify off
col sid for 99999
col serial# for 99999
col username for a10
col process for a10
col PROGRAM for a30
col MODULE  for a30
col logon_time for a20
col machine for a20
col status for a10

select sid,
       serial#,
       username,
       status,
       process,
       PROGRAM,
       machine,
       MODULE,
       to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss') logontime
  from v$session
 where username like upper('%&&username%')
   and STATUS like upper('%&&status%')
order by logontime;

Activate sql tracing on user by logon trigger

— to activate sql_tracing on user, say ‘scott’ in this example

create or replace trigger active_sql_trace_on_user
after logon on database
declare
v_user dba_users.username%TYPE:=user;
sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 trace name context forever, level 12'||chr(39);
begin
if (v_user='SCOTT') THEN
execute immediate sql_stmt1;
end if;
end;
/