Re: 10g SQL Execution

Rama,

> Scenario 1 (bad): Ran during the day time and took more than 8 Hrs to
finish
> Scenario 2 (good): Ran during mid night and finished within 30 Min.

I summarized the lines as below:

Type count cpu elapsed disk query rows ==== ====== ======= ========= ========= ========== ========= Bad 335492 1483.97 29,539.89 2,588,046 93,871,755 1,677,459 Good 340610 670.10 1,537.03 47,732 82,274,435 1,703,049

If both produced the same (or almost the same) result - and the number of rows, LIOs and count imply this - it seems that in the bad case, you are performing more than 50 times the number of I/O requests as compared to the good case. Another point to consider is this: In both cases, the query performed almost 50 LIOs for every row fetched, indicating an inefficient join (probably a large number of Nested Loop joins?).

Keeping these in mind, I would believe that this is the result of a combination of factors: The buffer cache is inadequately sized during the day as it seems that blocks of interest (probably index blocks and the data blocks they point to) are dropping off the cold end of the Buffer cache due to other contending hot blocks during the active daytime period. You might see the effect of this if you track the v$sesstat (or v$sess_io) for the bad/good sessions. During the night, the buffer cache is able to hold most of these blocks in the cache and hence you are not performing as much I/O as during the day.

The other issue is latching, which can be very CPU intensive. If the index root/first level leaf blocks are popular during the day, then it is possible that you may also be seeing a lot of CBC latching.

All of this will of course be substantiated by AWR. Since this is 10g, you should also be able to look at ASH data as well and dig into the various views. You might want to take a look at Ch 10 in my book for the views to check.

Some other details such as init.ora parameters, specific areas of AWR report will help drill down further. I would look at latching and I/O specifically.

-- 
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is always inevitable; Discouragement is invariably optional

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

--
http://www.freelists.org/webpage/oracle-l