RE: 10g SQL Execution

From: <rama.ari_at_accenture.com>
Date: Wed, 4 Oct 2006 15:48:53 -0500
Message-ID: <2BC5DDFF6EE1054C80EAC80E4D537B00956E80@AMRXM2113.dir.svc.accenture.com>

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.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 04 2006 - 15:48:53 CDT