RE: SQL Tuning....

  • From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
  • To: "roon987@xxxxxxxxxxx" <roon987@xxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Sep 2012 14:30:22 -0400

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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_...

 
--
//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

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


Other related posts: