RE: 10g SQL Execution

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rama.ari_at_accenture.com
Sent: Wednesday, October 04, 2006 1:49 PM To: oracle-l_at_freelists.org
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
4. Network Traffic
5. 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.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 05 2006 - 14:32:18 CDT