DB Performance Issue
- From: Asif Momen <asif_oracle@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 7 May 2007 00:44:03 -0700 (PDT)
Hello,
A batch runs daily at night which takes around 5 hours to complete. Following
is the AWR report for 30 minutes during the batch.
Before "SQL Ordered by Reads" Section of the AWR report used to be topped by
the application related SQL's but now we see its been topped by Dictionary
related queries.
Could you please share your experience as what could be the reason for the
SQL's?
Following are the System and Database details and an excerpt from the AWR
report:
System configuration: OS=AIX (5300-03) lcpu=12 mem=8192MB
DB_BLOCK_SIZE = 4096
SQL> select TABLESPACE_NAME,block_size, extent_management, allocation_type,
SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- ---------- --------- ------
SYSTEM 4096 LOCAL SYSTEM MANUAL
UNDOTBS1 4096 LOCAL SYSTEM MANUAL
SYSAUX 4096 LOCAL SYSTEM AUTO
TEMP 4096 LOCAL UNIFORM MANUAL
USERS 4096 LOCAL UNIFORM AUTO
APPL_IDX 4096 LOCAL SYSTEM AUTO
APPL_DATA 4096 LOCAL SYSTEM AUTO
Snap Id Snap Time Sessions Cursors/Session Begin Snap: 4710
07-May-07 00:00:40 27 96.6 End Snap: 4711 07-May-07 00:30:32 27 101.7
Elapsed: 29.85 (mins) DB Time: 42.67 (mins)
SQL ordered by Reads
=================
Physical Reads Executions Reads per Exec %Total CPU Time (s)
Elapsed Time (s) SQL Id SQL Module SQL Text 251,385 1 251,385.00 43.31
20.35 152.74 a82s6kpmyzvwq SQL*Plus select /*+ RULE */ :"SYS_B_00"...
251,369 1 251,369.00 43.30 34.68 783.58 c50ryyh9d7urn SQL*Plus select
/*+ RULE */ :"SYS_B_00"...
a82s6kpmyzvwq select /*+ RULE */
:"SYS_B_00"||A.FILE#||:"SYS_B_01"||A.BLOCK#||:"SYS_B_02" ||
A.MAX_EXT_SIZE||:"SYS_B_03"||B.MAX_BLOCKS_FREE||:"SYS_B_04"||A.TS#||:"SYS_B_05"
NOHEAD from (SELECT /*+ RULE */ TS#, FILE#, BLOCK#, MAX(EXTSIZE) MAX_EXT_SIZE
FROM SYS.SEG$ GROUP BY TS#, FILE#, BLOCK#) A, (SELECT /*+ RULE */ VB.NAME
TABLESPACE_NAME, VB.TS#, MAX(nvl(VA.BLOCKS, :"SYS_B_06")) MAX_BLOCKS_FREE FROM
DBA_FREE_SPACE VA, SYS.TS$ VB WHERE VB.NAME = VA.TABLESPACE_NAME (+) AND
VB.ONLINE$ != :"SYS_B_07" GROUP BY VB.NAME, VB.TS#) B, (SELECT TS#, FILE#,
BLOCK# FROM P$OBJ_EXCLUSION WHERE TS# IS NOT NULL AND FILE# IS NULL AND BLOCK#
IS NULL) C, (SELECT TS#, FILE#, BLOCK# FROM P$OBJ_EXCLUSION WHERE TS# IS NOT
NULL AND FILE# IS NOT NULL AND BLOCK# IS NOT NULL) S, (SELECT /*+ RULE */
UA.TS# TS#, UA.FILE# FILE#, UA.BLOCK# BLOCK# FROM SYS.SEG$ UA, P$OBJ_EXCLUSION
UB WHERE UB.USER# IS NOT NULL AND UA.USER# = UB.USER#) U, (SELECT A.TS# TS#,
NULL FILE#, NULL BLOCK# FROM SYS.FILE$
A, SYS.FILEXT$ B WHERE A.FILE# = B.FILE#) D, ( SELECT /*+ RULE */
TABLESPACE_NAME FROM DBA_TABLESPACES WHERE STATUS = :"SYS_B_08" AND CONTENTS =
:"SYS_B_09" AND (EXTENT_MANAGEMENT = :"SYS_B_10" AND ALLOCATION_TYPE IN
(:"SYS_B_11", :"SYS_B_12") OR EXTENT_MANAGEMENT = :"SYS_B_13") ) E WHERE A.TS#
= B.TS# AND A.MAX_EXT_SIZE > B.MAX_BLOCKS_FREE AND B.MAX_BLOCKS_FREE >
:"SYS_B_14" AND A.TS# = C.TS#(+) AND C.TS# IS NUL L AND A.TS# = S.TS# (+) AND
A.FILE# = S.FILE# (+) AND A.BLOCK# = S.BLOCK# (+) AND S.TS# IS NULL AND S.FILE#
IS NULL AND S.BLOCK# IS NULL AND A.TS# = U.TS# (+) AND A.FILE# = U.FILE# (+)
AND A.BLOCK# = U.BLOCK# (+) AND U.TS# IS NULL AND U.FILE# IS NULL AND U.BLOCK#
IS NULL AND A.TS# = D.TS#(+) AND D.TS# IS NULL AND B.TABLESPACE_NAME =
E.TABLESPACE_NAME
c50ryyh9d7urn select /*+ RULE */ :"SYS_B_00" bpb,
decode(min((a.BYTES_FREE / b.BYTES) * :"SYS_B_01"), :"SYS_B_02", :"SYS_B_03",
min((a.BYTES_FREE / b.BYTES) * :"SYS_B_04")) from (select /*+ RULE */
tfs_b.name tablespace_name, tfs_b.ts# ts#, sum(nvl(tfs_a.bytes, :"SYS_B_05"))
bytes_free from dba_free_space tfs_a, sys.ts$ tfs_b where tfs_b.name =
tfs_a.tablespace_name (+) and tfs_b.online$ != :"SYS_B_06" group by tfs_b.name,
tfs_b.ts#) a, (select /*+ RULE */ e.tablespace_name, sum(e.bytes) bytes from
dba_data_files e, (select /*+ RULE */ tablespace_name from dba_data_files where
autoextensible = :"SYS_B_07") f where e.tablespace_name = f.tablespace_name(+)
and f.tablespace_name is null and e.autoextensible = :"SYS_B_08" group by
e.tablespace_name) b, DBA_TABLESPACES c, (SELECT /*+ RULE */ TS# FROM
P$OBJ_EXCLUSION WHERE OBJECT_TYPE = :"SYS_B_09" AND FILE# IS NULL AND BLOCK# IS
NULL) d where a.TABLESPACE_NAME = b.TABLESPACE_NAME and b.TABLESPACE_NAME =
c.TABLESPACE_NAME and c.CONTENTS != :"SYS_B_10" and c.status = :"SYS_B_11" and
a.TS# = d.TS#(+) and d.TS# is NULL and c.TABLESPACE_NAME != :"SYS_B_12"
Regards
---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.
Other related posts:
