Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Performance degradation
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 - 13:53:10 CDT