Re: 10g SQL Execution
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: rama.ari@xxxxxxxxxxxxx
- Date: Wed, 4 Oct 2006 16:06:58 -0700
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 **
Other related posts:
