Re: Performance

"sybrandb" <sybrandb_at_gmail.com> wrote in message news:1158316281.381972.247590_at_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
>

It is almost the time with CBO as RBO (1,04) in test intance. So what?

Kulunut: 00:00:01.05

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=176 Card=1 Bytes=121
          )

   1    0   NESTED LOOPS (Cost=176 Card=1 Bytes=121)
   2    1     NESTED LOOPS (Cost=175 Card=1 Bytes=107)
   3    2       NESTED LOOPS (Cost=174 Card=1 Bytes=100)
   4    3         NESTED LOOPS (Cost=173 Card=824 Bytes=68392)
   5    4           VIEW (Cost=172 Card=7412 Bytes=548488)
   6    5             SORT (UNIQUE) (Cost=172 Card=7412 Bytes=771048)
   7    6               UNION-ALL
   8    7                 NESTED LOOPS (Cost=15 Card=25 Bytes=2800)
   9    8                   TABLE ACCESS (FULL) OF 'KAAPELI' (Cost=13
          Card=37 Bytes=3848)

  10    8                   INDEX (FULL SCAN) OF 'TRIV_PK' (UNIQUE)
  11    7                 TABLE ACCESS (FULL) OF 'KAAPELI' (Cost=13 Ca
          rd=7387 Bytes=768248)

  12    4           INDEX (UNIQUE SCAN) OF 'TTIL_PK' (UNIQUE)
  13    3         INDEX (RANGE SCAN) OF 'TRIV_PK' (UNIQUE)
  14    2       INDEX (UNIQUE SCAN) OF 'KARY_PK' (UNIQUE)
  15    1     TABLE ACCESS (BY INDEX ROWID) OF 'TILA' (Cost=2 Card=1 B
          ytes=14)

  16   15       INDEX (UNIQUE SCAN) OF 'TILA_PK' (UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         41  rows processed