Re: Performance

From: Jack <none_at_INVALIDmail.com>
Date: Fri, 15 Sep 2006 10:05:37 GMT
Message-ID: <RVuOg.96$3s5.89@read3.inet.fi>

"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 Received on Fri Sep 15 2006 - 05:05:37 CDT