Re: SQL Tuning....
- From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
- To: "roon987@xxxxxxxxxxx" <roon987@xxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Sep 2012 09:56:07 -0700 (PDT)
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@xxxxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
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_...
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
