| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance degradation
Wow, almost 6 million LIOs to find out there are no rows in the result set.
Every one of the LIOs uses CPU and has to acquire a cache buffers chains
latch (see "Why You Should Focus on LIOs instead of PIOs" at
www.hotsos.com/catalog). So much LIO activity causes competition for CPU and
latches that can ruin performance for everyone.
If these weren't Oracle dictionary views, I'd recommend creating an index on all_type_attrs (owner, type_name). But in this case, it's not so easy.
Please forgive me if this is too bold a suggestion, but is it possible that you don't need to run this query at all? The fastest/cheapest way to tune it is to never run it again. Since it returns 0 rows today, it's possible that you actually might not need to use it again. If you do, then I'd recommend a call to Oracle Support with the info shown below. Nested NESTED LOOPS plans have a multiplicative effect upon workload from one row source operation to its parent, and the multiple levels of nesting combined with a couple hundred rows from each operation all add up to one really inefficient execution plan.
Cary Millsap
www.hotsos.com
"Leon" <lrzhemov_at_shaw.ca> wrote in message
news:dac2c664.0205081053.6f94f27_at_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)
>
>
>
>
>
>
 |
 |