very poor SYS view performance (8i)

From: Uwe Kuechler <gg.3.ukuechle_at_spamgourmet.com>
Date: 10 Dec 2003 02:23:54 -0800
Message-ID: <beab228e.0312100223.6200e96c@posting.google.com>

Hi folks,

I've googled around this newsgroup quite a bit and found a few similar requests but with no solution so far. Now here's my problem:

Oracle 8.1.7.0 DB running for months and performing ok, BUT: When a monitoring process (third-party software) issues selects onto SYS's tables, those queries run very long and consume a lot of CPU. The same query on another, similar database does not have this issue, that's why I wonder whether the SYS tables could have been corrupted in one way or another.

Here's on of those queries:

select 	to_char(null), ac.owner, ac.table_name, acc.column_name, 
        acc.position, ac.constraint_name 
from 	all_constraints ac, all_cons_columns acc 
where 	ac.owner=acc.owner  
and 	ac.constraint_type='P' 
and 	ac.constraint_name=acc.constraint_name  
and 	ac.owner like 'FCISZIP' escape '\'  
and 	ac.table_name like 'JOBTBL' escape '\'

;

This is what the explain plan tells me:
SELECT STATEMENT Cost =
  FILTER
    NESTED LOOPS OUTER

      NESTED LOOPS
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS OUTER
              NESTED LOOPS OUTER
                NESTED LOOPS
                  NESTED LOOPS
                    NESTED LOOPS
                      NESTED LOOPS
                        NESTED LOOPS
                          NESTED LOOPS
                            TABLE ACCESS BY INDEX ROWID USER$
                              INDEX UNIQUE SCAN I_USER1 UNIQUE
                            TABLE ACCESS BY INDEX ROWID USER$
                              INDEX UNIQUE SCAN I_USER1 UNIQUE
                          TABLE ACCESS BY INDEX ROWID CON$
                            INDEX RANGE SCAN I_CON1 UNIQUE
                        TABLE ACCESS BY INDEX ROWID CDEF$
                          INDEX UNIQUE SCAN I_CDEF1 UNIQUE
                      TABLE ACCESS BY INDEX ROWID CON$
                        INDEX UNIQUE SCAN I_CON1 UNIQUE
                    TABLE ACCESS BY INDEX ROWID CDEF$
                      INDEX UNIQUE SCAN I_CDEF1 UNIQUE
                  TABLE ACCESS BY INDEX ROWID OBJ$
                    INDEX UNIQUE SCAN I_OBJ1 UNIQUE
                TABLE ACCESS BY INDEX ROWID CON$
                  INDEX UNIQUE SCAN I_CON2 UNIQUE
              TABLE ACCESS CLUSTER USER$
                INDEX UNIQUE SCAN I_USER# NON-UNIQUE
            TABLE ACCESS BY INDEX ROWID CCOL$
              INDEX RANGE SCAN I_CCOL2 UNIQUE
          INDEX UNIQUE SCAN I_OBJ1 UNIQUE
        TABLE ACCESS BY INDEX ROWID COL$
          INDEX UNIQUE SCAN I_COL3 UNIQUE
      TABLE ACCESS CLUSTER ATTRCOL$
    NESTED LOOPS
      FIXED TABLE FULL X$KZSRO
      INDEX RANGE SCAN I_OBJAUTH2 NON-UNIQUE

    FIXED TABLE FULL X$KZSPR
    NESTED LOOPS

      FIXED TABLE FULL X$KZSRO
      INDEX RANGE SCAN I_OBJAUTH2 NON-UNIQUE

    FIXED TABLE FULL X$KZSPR optimizer_mode is set to "choose", but the SYS tables are NOT analyzed, so this can't be the cause. And yes, the data dictionary is quite "big", with about 13,000 rows in all_constraints and about 15,000 rows in all_cons_columns. But this still doesn't really explain why that statement has to consume almost all CPU for minutes and why it generates about 1.6 Billion (sic!) buffer gets.

Ideas, any1?

cya,
Uwe Received on Wed Dec 10 2003 - 04:23:54 CST