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)

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

Advertisements
Tags: ,

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: