Home » SQL & PL/SQL » SQL & PL/SQL » how to get the individual execution time of operation in an execution plan
how to get the individual execution time of operation in an execution plan [message #261193] Wed, 22 August 2007 02:12 Go to next message
gurumurthy4
Messages: 17
Registered: August 2007
Junior Member
i want to get the execution time of individual operations in an execution plan ( for eg: select statement, merge..)
Re: how to get the individual execution time of operation in an execution plan [message #261194 is a reply to message #261193] Wed, 22 August 2007 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use trace.

Regards
Michel
Re: how to get the individual execution time of operation in an execution plan [message #261196 is a reply to message #261194] Wed, 22 August 2007 02:19 Go to previous messageGo to next message
gurumurthy4
Messages: 17
Registered: August 2007
Junior Member
actually i get the values by setting the autotrace.. but the cpu cost are not exact.... and another doubt is that autotrace actually fetches values from which table?
Re: how to get the individual execution time of operation in an execution plan [message #261203 is a reply to message #261196] Wed, 22 August 2007 02:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
report
Reported By: gurumurthy4
On: Wed, 22 August 2007 09:26
In: SQL & PL/SQL SQL Experts how to get the individual execution time of operation in an execution plan
Reason from which table the autotrace option fetches values? V$SQL table shows CPU time in microseconds.. then why autotrace shows the same in sec for even very small values( since it shows only for a part of query while v$sql shows for a query)


You probably clicked the wrong button Wink

MHE
Re: how to get the individual execution time of operation in an execution plan [message #261212 is a reply to message #261196] Wed, 22 August 2007 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use autotrace, use real sql trace.

Regards
Michel
Re: how to get the individual execution time of operation in an execution plan [message #261324 is a reply to message #261212] Wed, 22 August 2007 05:47 Go to previous messageGo to next message
gurumurthy4
Messages: 17
Registered: August 2007
Junior Member
Actually the sql_trace option traces values for time in parse,execute and fetch.... But I need to get the values for individual operations like merge,sort,select,table scan,etc., where can I get them?
Re: how to get the individual execution time of operation in an execution plan [message #261348 is a reply to message #261324] Wed, 22 August 2007 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$sql_plan% but they are only estimated times.
The only real times are in trace.

Regards
Michel
Re: how to get the individual execution time of operation in an execution plan [message #261662 is a reply to message #261193] Thu, 23 August 2007 05:04 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
You can use oracle trace as previously said. Here are all possibilities how to activate it.
However your requirements are a bit mixed.
So if you want to get timings for individual SQL statements (SELECT, MERGE, UPDATE etc etc)then it is OK, you can get them.
If you want to get timings for execution steps then it is
1) harder
2) not always make much sense.

For example if you have execution plan as follows:
hash join
  table scan1
  table scan2

then getting timings for each step is ok, because each of them is performed once and it is interesting what takes longest - one of table scans or hash join.

But in case you have
nested loops
  table1 access by index rowid
    index1 unique scan
  table2 access by index rowid
    index2 unique scan

then speak about individual operation time is a bit hard because there are (possibly) many index and table accesses in cycle.

Gints Plivna
http://www.gplivna.eu

[Updated on: Thu, 23 August 2007 05:12]

Report message to a moderator

Previous Topic: Ambigious Column Error
Next Topic: XMLSEQUENCE and Group By
Goto Forum:
  


Current Time: Sun Dec 04 14:43:30 CST 2016

Total time taken to generate the page: 0.14143 seconds