Home » RDBMS Server » Performance Tuning » Difference in elapsed time of the query where everything in the trace seems to be identical (Oracle 11g, Windows 7)
icon5.gif  Difference in elapsed time of the query where everything in the trace seems to be identical [message #609404] Thu, 06 March 2014 06:46 Go to next message
Vivek_Garg
Messages: 10
Registered: September 2012
Junior Member
While analysing the traces of the program , one before and one which I have run after creating two new indexes, I found some queries which are now having high runtime compared to previous run(which are not using any of the new indexes i have created and have identical execution plans).

One particular query is :

SELECT GP.PERIOD_YEAR, GP.PERIOD_NAME, GP.PERIOD_YEAR || '-' || 
LPAD(GP.PERIOD_NUM, 2, '0') 
FROM
GL_PERIODS GP WHERE GP.PERIOD_TYPE = :B3 AND GP.PERIOD_SET_NAME = :B2 AND 
GP.ADJUSTMENT_PERIOD_FLAG = 'N' AND TRUNC (:B1 ) BETWEEN TRUNC 
(GP.START_DATE) AND TRUNC (GP.END_DATE)

Before Stats:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1738112     31.63      31.26          0          0          0           0
Fetch   1738112    778.18     780.36         17    6952448          0     1738112
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   3476225    809.81     811.63         17    6952448          0     1738112

After Stats:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1738112     75.80      76.87          0          0          0           0
Fetch   1738112   1490.70    1499.47         17    6952448          0     1738112
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   3476225   1566.50    1576.36         17    6952448          0     1738112

And wait events for this query are

db file sequential read,
latch: cache buffers chains,
latch: shared pool
which are not having total wait time of more than a second.

So what I want to know is why this particular query is showing such difference in runtime? What all areas should I look into to get the cause of runtime difference.

I have run the programs in the same instance. Let me know if some more information is required.

--
Mod update: added [code] tags, please do so yourself in future. jw.

[Updated on: Thu, 06 March 2014 06:54] by Moderator

Report message to a moderator

Re: Difference in elapsed time of the query where everything in the trace seems to be identical [message #609409 is a reply to message #609404] Thu, 06 March 2014 07:09 Go to previous message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide
and please read How to use [code] tags and make your code easier to read

The number of buffer reads is identical before and after, so it would seem likely that execution plan has not changed. Can you confirm that by showing the ROW SOURCE part of the traces?
Could the difference be something completely unrelated, such as other activity in the database? Do you get repeatable results?

Previous Topic: AWR - "db file parallel read" + "direct path read" wait events, wait class - "User I/O"
Next Topic: How to find throughput by query, session and database wise?
Goto Forum:
  


Current Time: Sun Dec 05 04:20:34 CST 2021