Hi Chris,
I have just gone through your post(
https://blogs.oracle.com/sql/entry/how_to_create_an_execution ) on methods to generate execution plan (NOT explain plan), Thanks for quick walk-through on that.
I have query on TKProf method. I always felt that TKProf gives explain plan of the queries traced in a session(s) rather than execution plan because the plans are actually created when trace file is converted to readable format using TKProf utility with parameter EXPLAIN usernm/passwd@db.
Can you please clarify the same and confirm. Does non-readable sqltrace files(.trc) already have sql plans in them?
Thanks,
Imran.
If you do "tkprof explain=", then we are actually *running* an explain plan command on the SQL's we encounter in the trace file. So its quite possible that those plans do not match the ones that were actually used during execution.
However, if statistics_level = all, or any other combination of settings has been used to capture rowsource details in the raw trace file, then the formatted tkprof file will contain the *actual* plan used.
You'll see things like this in the output:
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=8 pr=0 pw=0 time=317 us cost=6 size=378 card=2)
1 1 1 HASH JOIN OUTER (cr=8 pr=0 pw=0 time=300 us cost=5 size=378 card=2)
1 1 1 NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=36 us cost=2 size=292 card=2)
1 1 1 TABLE ACCESS CLUSTER IND$ (cr=4 pr=0 pw=0 time=28 us cost=2 size=188 card=2)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=13 us cost=1 size=0 card=1)(object id 3)
0 0 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=5 us cost=0 size=52 card=1)
0 0 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 76)
0 0 0 VIEW (cr=3 pr=0 pw=0 time=32 us cost=3 size=43 card=1)
0 0 0 SORT GROUP BY (cr=3 pr=0 pw=0 time=32 us cost=3 size=15 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED CDEF$ (cr=3 pr=0 pw=0 time=17 us cost=2 size=15 card=1)
6 6 6 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=8 us cost=1 size=0 card=5)(object id 54)
Hope this helps.