Re: Performance

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 Received on Fri Sep 15 2006 - 04:07:09 CDT