| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> DB Performance Issue
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
 |
 |