Re: Performance


Jack wrote:
"sybrandb" <sybrandb@xxxxxxxxx> wrote in message
news:1158316281.381972.247590@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Jack wrote:
"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

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

Possibly the real issue here is the fact that production is on 9.0.1.4
and test is using 9.2.0.4 and changes to the CBO have been implemented.
In 9.2, for example, dynamic sampling is available (it is not in
9.0.1); this can make a difference in how the optimiser 'sees' the data
since in the absence of computed/estimated statistics the dynamic
sampling mechanism will provide some aid to the optimiser. You'll also
need to note which bugs in 9.0.1 were fixed in 9.2.0. Jonathan Lewis
probably has on his website information on how the optimiser has
changed between 9.0.1.x and 9.2.0.x; I would visit
http://www.jlcomp.demon.co.uk/ to find out more.

Since your environments aren't the same for both systems a comparison
between them isn't worth much; 9.0.1.4 on some version of Solaris (no
mention of O/S release or patch level and no mention of whether this is
x86 or SPARC) compared to 9.2.0.4 running on a Win XP box (no mention
of which service packs are installed). Also no mention of the
available memory, disk configuration, user load, server load, whether
these connections are local or networked, network speed, network health
.... too much information is missing to assess this situation
accurately.


David Fitzjarrell

.

Quantcast