Home » SQL & PL/SQL » SQL & PL/SQL » Explain Plan Vs set autotrace Vs Tkprof (oracle9i)
| Explain Plan Vs set autotrace Vs Tkprof [message #327809] |
Tue, 17 June 2008 14:06  |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
All,
I need clarification for Explain plan, autotrace & TKPROF.
My understanding is, Expalin plan is Pre execution plan(like asking direction to home). TKPROF is post execution plan(like ask how they got home). Explain plan takes the execution plan from plan_table.
Let me ask the questions now..
1. Are both(explain plan, autotrace) providing Pre execution plan? If the answer is YES, then can i assume that, both of them showing the execution plan from PLAN_TABLE?
2. Does TKPROF use v$sql_plan table to display execution plain in trace file?
Thanks and appreciate if any one clarify this.
|
|
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #327812 is a reply to message #327811] |
Tue, 17 June 2008 14:57  |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Michel, First i would like to thank for your immediate reply.
Regarding the first answer, it is very clear and i am good on this.
After reading the second answer, i have one thing to clarify.
1.You are saying, tkprof use a trace file which contains the real execution plan. Where exactly TKPROF gets this real execution plan?
2. Where exaclty v$sql_plan is useful? I am sure, the plan_table info is estimated execution plan. I think, v$sql_plan is post execution plan. But where exactly v$sql_plan is useful?
Thanks
|
|
|
|
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #327994 is a reply to message #327930] |
Wed, 18 June 2008 09:50 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Michel, One more last question on this thread. I enabled the trace on my database.
I ran the query
select count(ename) from employee where empno=7934;
I see the below two explain plan in my trace file..
Plan1
select count(ename)
from
employee where empno=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.42 1.29 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.18 5.64 11851 11861 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.60 6.94 11851 11861 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
10 SORT ORDER BY
10 FILTER
10 FIXED TABLE FULL X$KSUSESTA
1 FIXED TABLE FULL X$KSUSGIF
Plan2
EXPLAIN PLAN SET STATEMENT_ID='PLUS316' FOR select count(ename) from employee
where empno=7934
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.06 0.09 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 0.09 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT AGGREGATE
0 TABLE ACCESS FULL EMPLOYEE
Question : In the above, there are two plans(plan1 & plan2). My understanding here is, plan1 is real execution plan and plan2 is estimated execution plan. Am i correct? Please let me know.
|
|
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #328003 is a reply to message #327999] |
Wed, 18 June 2008 10:28 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Michel, i have attached the trace file. Please let me know where exactly the actual execution plan is stored in trace file.
Here is the query i ran for generating the attached the trace file.
SQL> select count(ename) from employee where empno=7934;
COUNT(ENAME)
------------
2097152 |
|
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #328055 is a reply to message #328008] |
Wed, 18 June 2008 13:30 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Michel, Thanks for all your support on this thread.
Here is the things i am trying to do. I wanted to compare the explain plan and tkprof plan.
Here are the steps i followd.
1. Enable the trace
2. run the query(select count(ename) from employee where empno=7934)
2. convert the trace file to tkprof format. The origial trace file name is dba1_ora_1148.trc. This trace file is attached. Alos tkprof file is also attched.
3. Ran the explain plan.
SQL> explain plan for select count(ename) from employee where empno=7934;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1141 |
| 1 | SORT AGGREGATE | | 1 | 11 | |
|* 2 | TABLE ACCESS FULL | EMPLOYEE | 2102K| 22M| 1141 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter("EMPLOYEE"."EMPNO"=7934)
Note: cpu costing is off
15 rows selected.
SQL>
Now i wanted to compare the above result with tkprof result.
My issue here is, i am not able to see the plan in tkprof output file. Can you please help me on this.
Also here is the tkprof output file copy.
TKPROF: Release 9.2.0.1.0 - Production on Wed Jun 18 14:10:30 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: dba1_ora_1148.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or
remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and
:5 is null)and(subname=:6 or subname is null and :6 is null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 15 0.00 0.00 0 0 0 0
Fetch 15 0.00 0.03 4 42 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37 0.00 0.03 4 42 0 12
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,
flags
from
seq$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 4 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select con#,obj#,rcon#,enabled,nvl(defer,0)
from
cdef$ where robj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
cdef$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 14 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.00 0.00 0 14 0 7
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select intcol#,nvl(pos#,0),col#
from
ccol$ where con#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 14 0.00 0.00 0 28 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22 0.00 0.00 0 28 0 7
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.14 0.47 0 0 0 0
Execute 1 0.00 0.00 1 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.14 0.48 1 1 3 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE
1 INDEX UNIQUE SCAN I_SEQ1 (object id 107)
********************************************************************************
select user#,type#
from
user$ where name=: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 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select privilege#,level
from
sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with
(grantee#=:1 or grantee#=1) and privilege#>0
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 29 0.00 0.00 0 35 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.00 0.00 0 35 0 28
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
28 CONNECT BY WITH FILTERING
4 NESTED LOOPS
4 CONCATENATION
0 INDEX RANGE SCAN I_SYSAUTH1 (object id 110)
4 INDEX RANGE SCAN I_SYSAUTH1 (object id 110)
4 TABLE ACCESS BY USER ROWID SYSAUTH$
24 NESTED LOOPS
23 BUFFER SORT
23 CONNECT BY PUMP
24 INDEX RANGE SCAN I_SYSAUTH1 (object id 110)
********************************************************************************
select name,password,datats#,tempts#,type#,defrole,resource$, ptime, exptime,
ltime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',
defschclass)
from
user$ where user#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 11 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.01 1 22 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.00 0.01 1 22 0 11
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER USER$
1 INDEX UNIQUE SCAN I_USER# (object id 11)
********************************************************************************
select distinct(-privilege#),nvl(option$,0)
from
sysauth$ where grantee#=:1 and privilege#<0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 0.01 0.00 0 0 0 0
Fetch 104 0.00 0.02 1 24 0 94
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 124 0.01 0.02 1 24 0 94
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
8 SORT UNIQUE
8 TABLE ACCESS BY INDEX ROWID SYSAUTH$
8 INDEX RANGE SCAN I_SYSAUTH1 (object id 110)
********************************************************************************
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA'
NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,'
NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE=
'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '-04:00' NLS_DUAL_CURRENCY = '$'
NLS_TIME_FORMAT = 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT = 'DD-MON-RR
HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT = 'HH.MI.SSXFF AM TZR'
NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM TZR'
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select u.name, o.name, trigger$.sys_evts, trigger$.type#
from
obj$ o, user$ u, trigger$ where bitand(trigger$.property,16) = 16 and
trigger$.baseobject = :1 and trigger$.obj# = o.obj# and o.owner# = u.user#
order by o.obj#
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 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID TRIGGER$
0 INDEX RANGE SCAN I_TRIGGER1 (object id 130)
0 TABLE ACCESS BY INDEX ROWID OBJ$
0 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
0 TABLE ACCESS CLUSTER USER$
0 INDEX UNIQUE SCAN I_USER# (object id 11)
********************************************************************************
select node,owner,name
from
syn$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 2 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 2 9 0 3
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 1 8 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 1 8 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SELECT USER
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.48 4 13 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.48 4 16 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
********************************************************************************
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 15 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 15 0 5
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SELECT NULL
FROM
DUAL FOR UPDATE NOWAIT
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 3 4 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 4 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
0 FOR UPDATE
1 TABLE ACCESS FULL DUAL
********************************************************************************
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece
from
idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 20 0.00 0.03 5 52 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36 0.00 0.03 5 52 0 12
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID IDL_SB4$
2 INDEX RANGE SCAN I_IDL_SB41 (object id 123)
********************************************************************************
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece
from
idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 16 0.01 0.03 7 48 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 32 0.01 0.03 7 48 0 12
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID IDL_UB1$
1 INDEX RANGE SCAN I_IDL_UB11 (object id 120)
********************************************************************************
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from
idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.03 5 28 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 0.00 0.03 5 28 0 4
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID IDL_CHAR$
1 INDEX RANGE SCAN I_IDL_CHAR1 (object id 121)
********************************************************************************
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece
from
idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.02 5 36 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 0.00 0.02 5 36 0 8
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID IDL_UB2$
2 INDEX RANGE SCAN I_IDL_UB21 (object id 122)
********************************************************************************
select audit$,options
from
procedure$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 12 0 4
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PROCEDURE$
1 INDEX UNIQUE SCAN I_PROCEDURE1 (object id 115)
********************************************************************************
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname
from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.01 1 36 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22 0.00 0.01 1 36 0 7
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 NESTED LOOPS OUTER
1 TABLE ACCESS BY INDEX ROWID DEPENDENCY$
1 INDEX RANGE SCAN I_DEPENDENCY1 (object id 127)
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
********************************************************************************
select order#,columns,types
from
access$ where d_obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 16 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 16 0 3
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID ACCESS$
0 INDEX RANGE SCAN I_ACCESS1 (object id 129)
********************************************************************************
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 4 17 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 0.00 0.01 4 17 0 5
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
BEGIN DBMS_OUTPUT.DISABLE; END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.51 3.22 0 0 0 0
Execute 1 0.35 2.06 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.87 5.28 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
********************************************************************************
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),
nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0)
,nvl(spare2,0),spare4,spare6
from
tab$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 9 0 3
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS CLUSTER TAB$
2 INDEX UNIQUE SCAN I_OBJ# (object id 3)
********************************************************************************
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0)
from
seg$ where ts#=:1 and file#=:2 and block#=:3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 1 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 1 12 0 4
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER SEG$
1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (object id 9)
********************************************************************************
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,spare2,spare6, decode(i.pctthres$,null,
null, mod(trunc(i.pctthres$/256),256))
from
ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1)))
deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=
:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.bo#=
:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.00 0.00 0 14 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 MERGE JOIN OUTER
1 SORT JOIN
1 TABLE ACCESS CLUSTER IND$
2 INDEX UNIQUE SCAN I_OBJ# (object id 3)
0 SORT JOIN
0 VIEW
0 SORT GROUP BY
0 TABLE ACCESS CLUSTER CDEF$
1 INDEX UNIQUE SCAN I_COBJ# (object id 30)
********************************************************************************
select pos#,intcol#,col#,spare1,bo#,spare2
from
icol$ where obj#=:1
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 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 8 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID ICOL$
1 INDEX RANGE SCAN I_ICOL1 (object id 40)
********************************************************************************
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 36 0.00 0.01 1 12 0 32
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 42 0.00 0.01 1 12 0 32
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
24 SORT ORDER BY
24 TABLE ACCESS CLUSTER COL$
3 INDEX UNIQUE SCAN I_OBJ# (object id 3)
********************************************************************************
select cols,audit$,textlength,intcols,property,flags,rowid
from
view$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.32 1.03 2 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.02 3 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.32 1.05 5 7 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID VIEW$
1 INDEX UNIQUE SCAN I_VIEW1 (object id 104)
********************************************************************************
select text
from
view$ where rowid=:1
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 2 0.00 0.00 0 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 4 0 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$
********************************************************************************
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#,
nvl(typidcol#, 0)
from
coltype$ where obj#=:1 order by intcol# desc
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 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select intcol#, toid, version#, intcols, intcol#s, flags, synobj#
from
subcoltype$ where obj#=:1 order by intcol# asc
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 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select col#,intcol#,ntab#
from
ntab$ where obj#=:1 order by intcol# asc
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 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk,
l.pctversion$, l.flags, l.property, l.retention, l.freepools
from
lob$ l where l.obj# = :1 order by l.intcol# asc
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 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS CLUSTER LOB$
1 INDEX UNIQUE SCAN I_OBJ# (object id 3)
********************************************************************************
select col#,intcol#,reftyp,stabid,expctoid
from
refcon$ where obj#=:1 order by intcol# asc
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 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select col#,intcol#,charsetid,charsetform
from
col$ where obj#=:1 order by intcol# asc
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 9 0.00 0.00 0 3 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 3 0 8
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum
from
opqtype$ where obj# = :1 order by intcol# asc
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 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE
FROM
SYSTEM.PRODUCT_PRIVS WHERE (UPPER(:"SYS_B_0") LIKE UPPER(PRODUCT)) AND
(UPPER(USER) LIKE USERID)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.84 3.32 1 13 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.84 3.32 1 16 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL SQLPLUS_PRODUCT_PROFILE
********************************************************************************
SELECT CHAR_VALUE
FROM
SYSTEM.PRODUCT_PRIVS WHERE (UPPER(:"SYS_B_0") LIKE UPPER(PRODUCT)) AND
((UPPER(USER) LIKE USERID) OR (USERID = :"SYS_B_1")) AND (UPPER(ATTRIBUTE)
= :"SYS_B_2")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.12 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.12 0 5 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL SQLPLUS_PRODUCT_PROFILE
********************************************************************************
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.95 4.09 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.95 4.09 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
********************************************************************************
SELECT DECODE(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3")
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
********************************************************************************
commit
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
********************************************************************************
select count(ename)
from
employee where empno=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.34 1.22 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.15 5.67 11851 11861 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.50 6.90 11851 11861 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
********************************************************************************
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 1 6 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 1 6 0 2
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select /*+ rule */ bucket, endpoint, col#, epvalue
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
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 1 0.01 0.00 2 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 2 2 0 1
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 1.76 8.37 5 28 0 0
Execute 9 1.32 6.17 0 3 5 2
Fetch 6 1.15 5.68 11851 11873 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 4.25 20.23 11856 11904 5 5
Misses in library cache during parse: 9
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 128 0.46 1.52 2 4 0 0
Execute 149 0.01 0.02 1 1 3 1
Fetch 370 0.03 0.30 44 543 0 282
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 647 0.51 1.85 47 548 3 283
Misses in library cache during parse: 7
9 user SQL statements in session.
128 internal SQL statements in session.
137 SQL statements in session.
********************************************************************************
Trace file: dba1_ora_1148.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
9 user SQL statements in trace file.
128 internal SQL statements in trace file.
137 SQL statements in trace file.
48 unique SQL statements in trace file.
1467 lines in trace file.
|
|
|
|
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #328063 is a reply to message #328056] |
Wed, 18 June 2008 14:12 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
anacedent, Thanks for your help.
Michel, As you said, i ran the query and tkprof as below.
SQL> select count(ename) from employee where empno=7934;
COUNT(ENAME)
------------
2097152
SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>
D:\oracle\admin\dba1\udump>tkprof dba1_ora_1148.trc output.txt explain=scott/tig
er@dba1 sys=no
TKPROF: Release 9.2.0.1.0 - Production on Wed Jun 18 14:56:21 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
D:\oracle\admin\dba1\udump>
Here below is the explain plan which i found from tkprof output file. Can you please confirm that, that is the one is tkprof explain plan?
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
2097152 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'EMPLOYEE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX' (NON-UNIQUE)
Here is the whole content of trace file
TKPROF: Release 9.2.0.1.0 - Production on Wed Jun 18 14:56:21 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: dba1_ora_1148.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select count(ename)
from
employee where empno=:"SYS_B_0"
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 1.09 4.31 11318 11861 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.09 4.31 11318 11861 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2097152 TABLE ACCESS FULL OBJ#(30800)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
2097152 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'EMPLOYEE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX' (NON-UNIQUE)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
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 1.09 4.31 11318 11861 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.09 4.31 11318 11861 0 1
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: dba1_ora_1148.trc
Trace file compatibility: 9.00.01
Sort options: default
0 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
12 lines in trace file.
|
|
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #328070 is a reply to message #328064] |
Wed, 18 June 2008 15:48 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
Unless things have changed in recent Oracle versions: The explain plan produced when you run tkprof is the current plan based on the current state of the table in the current database. This means that you should run the tkprof explain against the schema in the particular database where the trace was gathered. If anything that influences the optimizer (db parameters, table stats, presence of constraints & indexes etc etc) then the trace file metrics may tell a very different picture to the explain plan.
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #328076 is a reply to message #327809] |
Wed, 18 June 2008 20:51 |
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
|
Member |
|
|
And Michel is quite right to urge you to stop (mis)using the term "Explain Plan". The correct term is "Execution Plan", or just plain "Plan". Explain Plan is a tool you can use to get an estimate of an "Execution Plan" without having to actually run a query.
And of course, if all you want is the execution plan used by a query which is still in the SGA, you should consider using DBMS_XPLAN.
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #328225 is a reply to message #328076] |
Thu, 19 June 2008 07:11 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Michel, Thanks for all your valuable input on this thread. Thanks again!!!
TheSingerman, Thanks for correcting the term Execution plan. I agree that i should not say Explain plan.
Just my note. DBMS_XPLAN takes the input from PLAN_TABLE. This display the execution plan in tree format and we do not need to write the query using connect by prior.
|
|
|
|
| Re: Explain Plan Vs set autotrace Vs Tkprof [message #328230 is a reply to message #328225] |
Thu, 19 June 2008 07:29 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | DBMS_XPLAN takes the input from PLAN_TABLE. This display the execution plan in tree format and we do not need to write the query using connect by prior.
|
Yes, dbms_xplan is a package that formats the output.
Depending on the parameters you give it and Oracle version, it takes its data from plan_table or v$sql_plan or any other plan_table like table or view.
Regards
Michel
[Updated on: Thu, 19 June 2008 07:29] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Dec 03 04:38:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
