 Difference in elapsed time of the query where everything in the trace seems to be identical [message #609404] |
Thu, 06 March 2014 06:46  |
 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
