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 Go to next message
shrinika
Messages: 266
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 #327811 is a reply to message #327809] Tue, 17 June 2008 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. It is not a Pre execution plan, it is an estimated execution plan. Both gives the same thing, actually autotrace does an explain plan.

2. TKPROF does not use v$sql_plan, actually tkprof existed far before v$sql_plan. tkprof use a trace file which contains the real execution plan (if you don't use explain parameter).

Regards
Michel
Re: Explain Plan Vs set autotrace Vs Tkprof [message #327812 is a reply to message #327811] Tue, 17 June 2008 14:57 Go to previous messageGo to next message
shrinika
Messages: 266
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 #327814 is a reply to message #327812] Tue, 17 June 2008 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. It is in the trace file.
2. v$sql_plan is useful when you don't have a trace file and to see what execution plans are currently in SGA.

Regards
Michel
Re: Explain Plan Vs set autotrace Vs Tkprof [message #327930 is a reply to message #327814] Wed, 18 June 2008 07:05 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Michel, Perfect answers. Thank you !!!
Re: Explain Plan Vs set autotrace Vs Tkprof [message #327994 is a reply to message #327930] Wed, 18 June 2008 09:50 Go to previous messageGo to next message
shrinika
Messages: 266
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 #327999 is a reply to message #327994] Wed, 18 June 2008 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You surely miss something as plan1 has no relation with your query.
Put your trace file. Note not tkprof, original trace file.

Regards
Michel
Re: Explain Plan Vs set autotrace Vs Tkprof [message #328003 is a reply to message #327999] Wed, 18 June 2008 10:28 Go to previous messageGo to next message
shrinika
Messages: 266
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 #328008 is a reply to message #328003] Wed, 18 June 2008 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must not trace and execute "explain plan" at the same time.
So start sql trace, execute the query, stop the trace, then tkprof the trace file, finally use "explain plan" and compare.

Regards
Michel
Re: Explain Plan Vs set autotrace Vs Tkprof [message #328055 is a reply to message #328008] Wed, 18 June 2008 13:30 Go to previous messageGo to next message
shrinika
Messages: 266
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 #328056 is a reply to message #327809] Wed, 18 June 2008 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
As you can see, if you want tkprof to produce an EXPLAIN_PLAN, you have to request it.

 tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor


Re: Explain Plan Vs set autotrace Vs Tkprof [message #328058 is a reply to message #328055] Wed, 18 June 2008 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start a new session before tracing.
Your trace now contains too much things that are not related to the query you want to execute.
Do it like I said: trace on, query, trace off (or better disconnect).
Also use tkprof with option "sys=no".

Regards
Michel
Re: Explain Plan Vs set autotrace Vs Tkprof [message #328063 is a reply to message #328056] Wed, 18 June 2008 14:12 Go to previous messageGo to next message
shrinika
Messages: 266
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 #328064 is a reply to message #328063] Wed, 18 June 2008 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the real plan used during execution:
Row Source Operation

This is the estimated execution plan returned by "explain plan":
Execution Plan

Don't use "explain" parameter of tkprof unless row source is not included which is only the case if trace is not complete.

Regards
Michel

[Updated on: Wed, 18 June 2008 14:38]

Report message to a moderator

Re: Explain Plan Vs set autotrace Vs Tkprof [message #328070 is a reply to message #328064] Wed, 18 June 2008 15:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
shrinika
Messages: 266
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 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: diff between natural and inner join
Next Topic: Cursor_sharing
Goto Forum:
  


Current Time: Tue Dec 06 11:59:46 CST 2016

Total time taken to generate the page: 0.08877 seconds