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
   

       
---------------------------------
Ahhh...imagining that irresistible "new car" smell?
 Check outnew cars at Yahoo! Autos.

Other related posts:

  • » DB Performance Issue