SQL Tuning....

From: Zabair Ahmed <roon987_at_yahoo.co.uk>
Date: Wed, 12 Sep 2012 17:41:04 +0100 (BST)
Message-ID: <1347468064.22300.YahooMailClassic_at_web171304.mail.ir2.yahoo.com>

Oracle 11.2.0.2 on AIX 6.1 �
Can anybody see anything wrong with the following query.
�

I've ran it from within SQLPLUS and it runs pretty quickly for me...(about 3secs)..
�

Can't see anything wrong with it. Am I missing something. Developer is saying that it's taking between 4mins and 1hr to run!
�

select

STG_CS2RECEIVABLE.CASE_NO,
STG_CS2RECEIVABLE.CPART_RL_NO,
STG_CS2RECEIVABLE.CREATION_DATE,
STG_CS2RECEIVABLE.CREATION_TIME,
STG_CS2RECEIVABLE.FINANCL_SCHDL_NO,
STG_CS2RECEIVABLE.GL_ACC_DT,
STG_CS2RECEIVABLE.GL_BUS_UNIT,
STG_CS2RECEIVABLE.GL_CALC_TP,
STG_CS2RECEIVABLE.GL_CUSTOMER,
STG_CS2RECEIVABLE.IBMSNAP_AUTHID,
STG_CS2RECEIVABLE.IBMSNAP_COMMITSEQ,
STG_CS2RECEIVABLE.IBMSNAP_INTENTSEQ,
STG_CS2RECEIVABLE.IBMSNAP_LOGMARKER,
STG_CS2RECEIVABLE.IBMSNAP_OPERATION,
STG_CS2RECEIVABLE.LIAB_END_DT,
STG_CS2RECEIVABLE.LIAB_STRT_DT,
STG_CS2RECEIVABLE.LST_UPDD_TSTMP,
STG_CS2RECEIVABLE.RCVBL_AACD,
STG_CS2RECEIVABLE.RCVBL_AMT,
STG_CS2RECEIVABLE.RCVBL_NO,
STG_CS2RECEIVABLE.RCVBL_OS_AMT,
STG_CS2RECEIVABLE.RCVBL_TP,
STG_CS2RECEIVABLE.RESOLUTION_STATUS,
STG_CS2RECEIVABLE.X_UID

from ods.STG_CS2RECEIVABLE;
�

AWR Snapshot...
�

DB Name
DB Id
Instance
Inst num
Startup Time
Release
RAC PTDWDB
278188392
ptdwdb1
1
18-Aug-12 10:08
11.2.0.2.0
YES Host Name
Platform
CPUs
Cores
Sockets
Memory (GB)

ukbilvsdwp579
AIX-Based Systems (64-bit)
16
4
�

24.00

Snap Id
Snap Time
Sessions
Cursors/Session

Begin Snap:
3291
23-Aug-12 14:00:40
78
4.7

End Snap:
3292
23-Aug-12 14:27:57
74
4.6

Elapsed:
�

27.29 (mins)
�
�

DB Time:
�

0.60 (mins)
�
�

Report Summary
Cache Sizes

Begin
End

Buffer Cache:
4,736M
4,736M
Std Block Size:
8K

Shared Pool Size:
1,696M
1,696M
Log Buffer:
16,536K
Load Profile

Per Second
Per Transaction
Per Exec
Per Call

DB Time(s):

0.0
0.2
0.00
0.00

DB CPU(s):

0.0
0.1
0.00
0.00

Redo size:
1,367.1
9,135.2
�
�

Logical reads:
55.0
367.7
�
�

Block changes:
4.4
29.6
�
�

Physical reads:
7.8
52.0
�
�

Physical writes:
0.6
4.0
�
�

User calls:
14.9
99.8
�
�

Parses:
1.9
12.7
�
�

Hard parses:
0.2
1.0
�
�

W/A MB processed:
0.1
0.3
�
�

Logons:
0.0
0.2
�
�

Executes:
4.6
30.6
�
�

Rollbacks:
0.0
0.0
�
�

Transactions:
0.2
�
�
�
�

SQL ordered by Elapsed Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 
%Total - Elapsed Time as a percentage of Total DB time 
%CPU - CPU Time as a percentage of Elapsed Time 
%IO - User I/O Time as a percentage of Elapsed Time 

Captured SQL account for 91.8% of Total DB Time (s): 36 Captured PL/SQL account for 28.3% of Total DB Time (s): 36

Elapsed Time (s)
Executions
Elapsed Time per Exec (s)

%Total
%CPU
%IO

SQL Id
SQL Module
SQL Text

6.78
1
6.78
18.88
3.84
58.29
ga81jwuudp563
SQL*Plus
select STG_CS2RECEIVABLE.CASE_...

5.44
0
�

15.17
44.45
6.47
8u809k64x3nzd
OEM
begin DBMS_WORKLOAD_REPOSITORY...

4.55
97
0.05
12.67
43.10
0.00
gtr8rw7p2h5xy
Realtime Connection
SELECT event#, sql_id, sql_pla...
�
�

SQL ordered by User I/O Wait Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

%Total - User I/O Time as a percentage of Total User I/O Wait time 
%CPU - CPU Time as a percentage of Elapsed Time 
%IO - User I/O Time as a percentage of Elapsed Time 

Captured SQL account for 93.6% of Total User I/O Wait Time (s): 6 Captured PL/SQL account for 36.7% of Total User I/O Wait Time (s): 6

User I/O Time (s)
Executions
UIO per Exec (s)
%Total
Elapsed Time (s)
%CPU
%IO
SQL Id
SQL Module
SQL Text

3.95
1
3.95
63.00
6.78
3.84
58.29
ga81jwuudp563
SQL*Plus
select STG_CS2RECEIVABLE.CASE_...

�

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

Received on Wed Sep 12 2012 - 11:41:04 CDT