RE: 10g SQL Execution
- From: "Siva Valiveru" <SValiveru@xxxxxxxxxxxxx>
- To: <rama.ari@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Oct 2006 12:32:18 -0700
In the bad time the data is serviced from disk instead of buffer like
good case, looks like you need to see the buffer cache sizing during the
peak usage time.
bad time -> 2million disk reads vs good time -> 47k disk reads , other
stats remaing same, buffer reads and num rows. As there is no 'huge'
difference in the buffer gets I think it is not change in the execution
plan which you are seeing. It is the latency from the disk reads is what
you are hit with(@11 ms service time for each disk read, for 2million
reads ~ 8hrs)
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
rama.ari@xxxxxxxxxxxxx
Sent: Wednesday, October 04, 2006 1:49 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: 10g SQL Execution
Hi All,
We are having strange situation and trying to figure out what is causing
the SQL to finish with different timings.
Database: Oracle 10.1.0.4
OS: HP-UX 11i
Application: Cognos (Reporting)
As it is cognos reporting database, it has two main processes. One is
ETL job and other one is Cube process. Both the processes run once every
two hours. Cube process reads fact and dimension tables and creates flat
file on Application server.
Scenario 1 (bad): Ran during the day time and took more than 8 Hrs to
finish
-- tkprof output
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0 0
0
Execute 0 0.00 0.00 0 0 0
0
Fetch 335492 1483.97 29539.89 2588046 93871755 0
1677459
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 335492 1483.97 29539.89 2588046 93871755 0
1677459
Scenario 2 (good): Ran during mid night and finished within 30 Min.
-- tkprof output
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 0 0.00 0.00 0 0 0
0
Execute 0 0.00 0.00 0 0 0
0
Fetch 340610 670.10 1537.03 47732 82274435 0
1703049
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 340610 670.10 1537.03 47732 82274435 0
1703049
There is no database change between these two scenarios other different
usage between day and night time. We do run gather stats twice a week.
I am investigating following areas
1) 10g Resource Groups
2) Wait Events
3) SGA
3) Network Traffic
4) Health of the over all Database
Does any one have any more ideas?
Thanks in Advance
Rama Ari
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you have
received it in error, please notify the sender immediately and delete
the original. Any other use of the email by you is prohibited.
Other related posts:
