RE: SQL Traces' Comparison
Date: Thu, 14 Aug 2008 18:10:55 +0000
Message-ID: <20080814181055.46814.qmail@optimaldba.com>
As there are multiple executions you cannot determine if 1 call caused most of the time/query increase or if it was distributed across all the executions.
You should also check each of the row source operations in the execution plans to determine which table (assuming multi-table query) is consuming the most LIO (cr value).
The main thing that would concern me is the substantial increase in Logical I/O (query). For the first run, it performed roughly 60 LIOs per execution. For the second run, this increased to 188,672 per execution. As Oracle does not provide a good tool for determining why a query performed a LIO, you have to make an educated guess. One of the most likely causes is the process of generating a read consistent view of the data. Were there updates being applied to the tables during the queries?
LIO means cpu time, so there is part of the reason the elapsed time increased. However, there is still about 6 0 seconds unaccounted for. What was the second query waiting on? Locks or latches? Since the output shows no physical I/O, it should not be db file sequential/scattered read.
Regards,
Daniel Fink
Oracle Performance, Diagnosis, Data Recovery and Training
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Lost Data? http://www.ora600.be
------- Original Message -------
On 8/14/2008 5:10 PM VIVEK_SHARMA wrote:
Folks
For the same SQL with
the same execution Path on the same Database, what is the possible cause for
High Elapsed Time ub Case 2 (below)?
NOTE - Table sizes having
grown Slightly (by a few GB) in Case 2
Will provide any
detail needed
Cheers & Thanks
P.S.
CASE 1 Earlier
call
count cpu
elapsed disk
query current rows
-------
------ -------- ---------- ---------- ---------- ----------
----------
Parse
24 0.01
0.00
0 0
0 0
Execute
36 0.05
0.03
0 0 0
0
Fetch
55 0.03
0.02
0 2099
0 374
-------
------ -------- ---------- ---------- ---------- ----------
----------
total
115 0.09 0.06
0 2099
0 374
CASE 2 Later
call
count cpu
elapsed disk
query current rows
-------
------ -------- ---------- ----
------ ---------- ----------
----------
Parse
15 0.00
0.00
0 0
0 0
Execute
17 0.00
0.00
0
0
0 0
Fetch
17 30.30
91.66 0
3207424
0 68
------- ------
-------- ---------- ---------- ---------- ---------- ----------
total
49 30.30 91.66
0 3207424
0 68
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute t
his e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***