Re: SQL Tuning....

From: Kellyn Pot'vin <kellyn.potvin_at_ymail.com>
Date: Wed, 12 Sep 2012 09:56:07 -0700 (PDT)
Message-ID: <1347468967.90210.YahooMailNeo_at_web121006.mail.ne1.yahoo.com>

My eyes started to give out looking at the formatting on the AWR report... :) I would trace the developer's session that is saying this is taking so long for him.  You know it's running fine for you, so find out what is not running *fine* for him... :)

 

Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
RMOUG Director of Training Days 2013

~Tombez sept fois, se relever huit!

---

 From: Zabair Ahmed <roon987_at_yahoo.co.uk> To: oracle-l <oracle-l_at_freelists.org> Sent: Wednesday, September 12, 2012 10:41 AM 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

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

Received on Wed Sep 12 2012 - 11:56:07 CDT