Re: Performance degradation

"Cary Millsap" <cary.millsap_at_hotsos.com> wrote in message news:<ufm383ekbq6557_at_corp.supernews.com>...
> 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)
> >
> >
> >
> >
> >
> >
> ****************************************************************************
> ****
> >
> > 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

Since the Discussion is on LIO's i have a Doubt which i thought i will thrash it out here.

I have Just started My System.

Created the Table Emp and inserted Rows into it.

Select * from EMp;

This Returns me 4 Physical Reads, 4 Db Block Gets and 2 Consistent Gets.

What are these consistent Gets doing here. My Understanding of consistent gets is that they are Read from the RBS to get a Consistent Block so that data as of that ScN can be given. [ Correct me here if i am wrong ]

Can somebody Explain this to me.

Regards,
Ganesh R Received on Mon Jun 03 2002 - 08:20:37 CDT