Re: Performance

From: Jack <none_at_INVALIDmail.com>
Date: Wed, 20 Sep 2006 07:27:47 GMT
Message-ID: <T36Qg.37$yu2.16@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!

Thanks for everyone and specially for Gints for your skarp analysis. It is now solved by optimising code.

I would prefer to get that slow server faster but nevermind. Somehow optimising someting witch takes 10 s seemed waste of time for me, but as sayd when that damm prod server is ten times slover it will take 100s ;)
(that server is not a rocket and quite hard loaded by other systems)

Thanks
Jack Received on Wed Sep 20 2006 - 02:27:47 CDT