performance degradation ALL_TYPE

From: Leon <lrzhemov_at_shaw.ca>
Date: 8 May 2002 15:42:13 -0700
Message-ID: <dac2c664.0205081442.6dcb54b7@posting.google.com>

Hi,

I would appreciate your feedback. The issue is quite urgent.

Problem: Degradation in DB performance, slow respond to user queries

Findings: High waits for latch free event. Specifically latch contention for cache buffer chains (average 2-3%)

Enterprise manager identify
SELECT ATTR_NO, ATTR_NAME, ATTR_TYPE_NAME, ATTR_TYPE_OWNER FROM
ALL_TYPE_ATTRS WHERE OWNER = :1 AND TYPE_NAME = :2 ORDER BY ATTR_NO As waiting for latch sql statement

Tracing for user session shows:

SELECT ATTR_NO, ATTR_NAME, ATTR_TYPE_NAME, ATTR_TYPE_OWNER FROM ALL_TYPE_ATTRS WHERE OWNER = :1 AND TYPE_NAME = :2 ORDER BY ATTR_NO

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse     1548      0.52       0.38          0          0          0  
        0
Execute   1548      0.43       0.40          0          0          0  
        0
Fetch     1548    116.42     138.30          0    5586733       6192  
        0

------- ------ -------- ---------- ---------- ---------- ----------

total     4644    117.37     139.08          0    5586733       6192  
        0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 36 (ELIZABETH)

Rows Row Source Operation

-------  ---------------------------------------------------
      0  SORT ORDER BY
      0   FILTER
      1    NESTED LOOPS
      1     NESTED LOOPS
    233      NESTED LOOPS
    233       NESTED LOOPS
    233        NESTED LOOPS
    233         NESTED LOOPS
    233          TABLE ACCESS FULL ATTRIBUTE$
    464          TABLE ACCESS CLUSTER TYPE$
    464         TABLE ACCESS CLUSTER TYPE$
    464          INDEX UNIQUE SCAN (object id 255)
    464        TABLE ACCESS BY INDEX ROWID OBJ$
    464         INDEX RANGE SCAN (object id 35)
    464       TABLE ACCESS CLUSTER USER$
   464        INDEX UNIQUE SCAN (object id 11)
    232      TABLE ACCESS BY INDEX ROWID OBJ$
    464       INDEX RANGE SCAN (object id 35)
      0     TABLE ACCESS CLUSTER USER$
      0      INDEX UNIQUE SCAN (object id 11)
      0    FIXED TABLE FULL X$KZSPR
      0    NESTED LOOPS
      0     FIXED TABLE FULL X$KZSRO
      0     INDEX RANGE SCAN (object id 101)





********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse     7452      2.62       2.83          0          0          0  
        0
Execute   7452     28.98      40.86          0        674        216  
     4144
Fetch     3308    117.70     140.68          0    5618530       6264  
     1957

------- ------ -------- ---------- ---------- ---------- ----------

total    18212    149.30     184.37          0    5619204       6480  
     6101

as you can see it looks like this statement is taking all Oracle resources

Client application is Java applet (thin JDBC) on Web Logic server it is mostly calling stored procedures and gets information as arrays.

Please point me to the direction how to fix this.

Thanks
Leon Received on Wed May 08 2002 - 17:42:13 CDT

performance degradation ALL_TYPE_ATTRS select