Re: Performance

From: sybrandb <sybrandb_at_gmail.com>
Date: 15 Sep 2006 03:31:21 -0700
Message-ID: <1158316281.381972.247590@e3g2000cwe.googlegroups.com>

Jack wrote:
> "Gints Plivna" <gints.plivna_at_gmail.com> wrote in message
> news:1158311229.662817.321730_at_k70g2000cwa.googlegroups.com...
> > Jack wrote:
> >> Hi!
> >>
> >> Production server is a bit slow
> >> Prod Test
> >> ---------- --------------------
> >> 9.0.1.4.0 9.2.0.4
> >> SUN WinXP
> >> 20-40s 4s execution time
> >>
> >> Is ther some init parameter which affect Nested loop performance?
> >> Or any other ideas?
> >>
> >> Jack
> >> --------------------------------------------------------------
> >> Time: 00:00:06.07
> >>
> >> Execution Plan
> >> ----------------------------------------------------------
> >> 0 SELECT STATEMENT Optimizer=CHOOSE
> >> 1 0 SORT (UNIQUE)
> >> 2 1 NESTED LOOPS
> >> 3 2 NESTED LOOPS
> >> 4 3 NESTED LOOPS
> >> 5 4 NESTED LOOPS
> >> 6 5 VIEW
> >> 7 6 SORT (UNIQUE)
> >> 8 7 UNION-ALL
> >> 9 8 MERGE JOIN
> >> 10 9 SORT (JOIN)
> >> 11 10 TABLE ACCESS (FULL) OF 'KAAPELI'
> >> 12 9 SORT (JOIN)
> >> 13 12 TABLE ACCESS (FULL) OF 'TILAUSRIVI'
> >> 14 8 TABLE ACCESS (FULL) OF 'KAAPELI'
> >> 15 5 INDEX (UNIQUE SCAN) OF 'TTIL_PK' (UNIQUE)
> >> 16 4 INDEX (UNIQUE SCAN) OF 'KARY_PK' (UNIQUE)
> >> 17 3 INDEX (RANGE SCAN) OF 'TRIV_PK' (UNIQUE)
> >> 18 2 TABLE ACCESS (BY INDEX ROWID) OF 'TILA'
> >> 19 18 INDEX (UNIQUE SCAN) OF 'TILA_PK' (UNIQUE)
> >>
> >>
> >>
> >>
> >> Statistics
> >> ----------------------------------------------------------
> >> 1939 recursive calls
> >> 0 db block gets
> >> 41383 consistent gets
> >> 0 physical reads
> >> 0 redo size
> >> 1503 bytes sent via SQL*Net to client
> >> 491 bytes received via SQL*Net from client
> >> 4 SQL*Net roundtrips to/from client
> >> 1376 sorts (memory)
> >> 0 sorts (disk)
> >> 41 rows processed
> >
> > Just some questions for you to think about.
> >
> > You'v got 41 rows according to autotrace but full scanned 3 tables. As
> > there isn't any info about amount of records in them it is hard to say
> > is that the real problem. Also it is interesting that you have merge
> > joins, have you disabled hash joins?
> > And optimizer choose - it seems you haven't statistics on these tables
> > (cannot see any expected cardinalities) so using RBO (probably that's
> > why merge joins). Working with RBO is a bit deprecated now to say
> > gently :)
> > If you have statistics the CHOOSE will result in ALL_ROWS and of course
> > it is a big question whether it is the best choice for your
> > application?
> > Also UNION-ALL with following SORT (UNIQUE) is a bit suspicious do you
> > really need UNION or maybe UNION ALL in your statement?
> > Run trace and see what is the reason.
> >
> > And speaking about production server vs test server - what are
> > differences in data amounts? What are differences in simultaneous users
> > doing workload on DBs?
> >
> > Gints Plivna
> > http://www.gplivna.eu
> >
> Hi!

>


> count

> Table             Prod           Test

> -------------     ----------     --------------------

> TILAUSRIVI         11380            7300

> KAAPELI             8951            7424

> TILA                8770            7885

> kaapeliryhma          27              24

> tyotilaus           3284            2257

> til_kaapeli_tila 2675194         1612440

>

> There is selection from view til_kaapeli_tila , and that should get faster.
> That view is based on those five tables.
> CBO in 9.0 is not usefull with complex views. so we are using RBO.
> (even this view is not so complicated, it is quite simplex, one page long)

>

> workload and data amounts does not explan all, I think.
>
> Jack

I would contradict 'CBO is not usefull with complex views'! Did you make sure you changed optimizer_index_cost_adj and optimizer_index_caching from the default? People who make blanket statements like you did, usually make them because they never changed those parameters. CBO outperforms RBO in many respects, and RBO should be considered dead.
Apart from that, if you stick to RBO, you can't upgrade anymore. That is not good for you company, not good for your resume, and not good for your career.

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Fri Sep 15 2006 - 05:31:21 CDT