Re: Performance


"Gints Plivna" <gints.plivna@xxxxxxxxx> wrote in message
news:1158311229.662817.321730@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Quantcast