RE: SQL Tuning....

From: Jorgensen, Finn <Finn.Jorgensen_at_constellation.com>
Date: Wed, 12 Sep 2012 14:30:22 -0400
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF035942736D7C_at_EXM-OMF-04.Ceg.Corp.Net>

Executions 1
Elapsed Time per Exec (s) 6.78

Is this the AWR for when you ran it or when he ran it? Says it ran in under 7 seconds. That doesn't jive with what the developer is telling you. Maybe his tool is doing some kind of formatting of the data after it receives it back from Oracle? What tool is he using anyway?

Thanks,
Finn

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zabair Ahmed Sent: Wednesday, September 12, 2012 12:41 PM To: oracle-l
Subject: SQL Tuning....

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


>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee.  If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. -IP2

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

Received on Wed Sep 12 2012 - 13:30:22 CDT