Starne behaviour of optimizer within in 9i vs 8i

[Database Administrator]

Hi there,

we are facing strange behaviours of the optimizer after migration from
Oracle 8.1.7 to Oracle 9.2.0.4

We've got a view:

CREATE OR REPLACE FORCE VIEW PROD_B2BORDER.VOPDISPLAYPO
AS
SELECT po.*, su.shortname, cl.companyshort, de.description,
ausu.userid
FROM prod_b2bactor.DBAUTHUSER90 ausu, prod_b2bactor.DBAUTHUSER90
auof,
prod_b2bactor.DBAUTHUSER90 aude, prod_b2bactor.DBAUTHUSER90
aucl,
prod_b2bactor.DBCLIENT cl, prod_b2bactor.DBSUPPLIER su,
prod_b2bactor.DBOFFICE ff, prod_b2bactor.DBDEPARTMENT de,
prod_B2BORDER.DBPURCHASEORDER po,
prod_b2bactor.dbclientsupplier cs
WHERE aucl.userid=ausu.userid
AND aude.userid=ausu.userid
AND auof.userid=ausu.userid
AND ausu.OIDCOLUMN='SupplierID'
AND ausu.ldapfunction='orderprocessing.order.display.e xt'
AND ( su.SUPPLIERID=ausu.ACTORID OR ausu.ACTORID=0)
AND auof.OIDCOLUMN='OfficeID'
AND auof.ldapfunction='orderprocessing.order.display.e xt'
AND (ff.OFFICEID=auof.ACTORID OR auof.ACTORID=0)
AND aude.OIDCOLUMN='DepartmentID'
AND aude.ldapfunction='orderprocessing.order.display.e xt'
AND (de.DEPARTMENTID=aude.ACTORID OR aude.ACTORID=0)
AND aucl.OIDCOLUMN='ClientID'
AND aucl.ldapfunction='orderprocessing.order.display.e xt'
AND (cl.CLIENTID=aucl.ACTORID OR aucl.ACTORID=0)
AND cs.clientid = cl.clientid
AND cs.supplierid = su.supplierid
AND po.clientsupplierid = cs.clientsupplierid
AND po.officeid=ff.officeid(+)
AND po.departmentid=de.departmentid;

And a statement with uses this view like this:

SELECT po.orderid, po.clientid, po.officeid, po.supplierid,
po.departmentid,
po.orderno, po.erpsplitno, po.clientid, cs.supplierno,
po.shortname,
po.orderdate, po.deliverydate, po.sizebreakdownduedateindays,
po.confirmationstateno, po.ordercategoryid, oc.orderoptionno,
po.isread, po.officeisread, po.companyshort, po.description,
po.confirmationdate, po.confirmationuserid, po.erpsplitnoold,
po.splitorderid, po.originalorderno, po.cancellationstateno,
po.officecancellationstateno, po.originalorderquantity,
po.orderquantity, po.quantityunitid, po.docngdeliverydate,
po.docngincoterms, po.docngsupplierprice, po.docngorderquantity
FROM vopdisplaypo po, dbclientsupplier cs, dbordercategory oc
WHERE oc.ordercategoryid = po.ordercategoryid
AND cs.clientsupplierid = po.clientsupplierid
AND NOT po.confirmationstateno IN
(4,
97,
98,
99
)
AND po.b2border = 1
AND po.orderid NOT IN (
SELECT DISTINCT OID
FROM dbstatevalue stv, dbstatetypenumber sttn
WHERE stv.statetypenumberid =
sttn.statetypenumberid
AND sttn.statetype IN (10)
AND sttn.statenumber = 1
AND po.orderid = stv.OID)
AND po.userid = 'gustav.gans'

The explain plan looks like this ON ORACLE 8i:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=FIRST_ROWS 2 315
CONCATENATION
FILTER
NESTED LOOPS 1 400 27
NESTED LOOPS 1 235 6
NESTED LOOPS 1 258 8
NESTED LOOPS 1 250 7
FILTER
NESTED LOOPS OUTER
NESTED LOOPS 1 375 24
NESTED LOOPS 1 369 23
NESTED LOOPS 1 389 26
NESTED LOOPS 1 220 5
NESTED LOOPS 1 165 4
NESTED LOOPS 1 110 3
INDEX UNIQUE SCAN PKAUTHUSER90 1 55
3
TABLE ACCESS BY INDEX
ROWID DBAUTHUSER90 1 55 1
INDEX RANGE SCAN IXAUTHUSER90TEST 1
TABLE ACCESS BY INDEX ROWID DBAUTHUSER90 1
55 1
INDEX RANGE SCAN IXAUTHUSER90TEST 1
TABLE ACCESS BY INDEX ROWID DBAUTHUSER90 1
55 1
INDEX RANGE SCAN IXAUTHUSER90TEST 1
TABLE ACCESS FULL DBDEPARTMENT 1 K 8 K 1
TABLE ACCESS BY INDEX ROWID DBPURCHASEORDER 5
K 563 K 15
INDEX RANGE SCAN IXPURCHASEORDER05 5 K
TABLE ACCESS BY INDEX ROWID DBORDERCATEGORY 8 48
1
INDEX UNIQUE SCAN PKORDERCATEGORY 8
INDEX UNIQUE SCAN PKOFFICE 52 260
TABLE ACCESS BY INDEX ROWID DBCLIENTSUPPLIER 8 K 128 K 1
INDEX UNIQUE SCAN PKCLIENTSUPPLIER 8 K
TABLE ACCESS BY INDEX ROWID DBCLIENT 19 152 1
INDEX UNIQUE SCAN PKCLIENT 19
TABLE ACCESS BY INDEX ROWID DBSUPPLIER 6 K 93 K 1
INDEX UNIQUE SCAN PKSUPPLIER 6 K
TABLE ACCESS BY INDEX ROWID DBCLIENTSUPPLIER 8 K 94 K 1
INDEX UNIQUE SCAN PKCLIENTSUPPLIER 8 K
FILTER
NESTED LOOPS 1 19 2
TABLE ACCESS BY INDEX ROWID DBSTATETYPENUMBER 1 9 1
INDEX UNIQUE SCAN UXSTATETYPENUMBER 1
INDEX UNIQUE SCAN UXSTATEOIDTYPE 1 10
FILTER
NESTED LOOPS 1 400 27
NESTED LOOPS 1 389 26
FILTER
NESTED LOOPS OUTER
NESTED LOOPS 1 375 24
NESTED LOOPS 1 369 23
NESTED LOOPS 1 258 8
NESTED LOOPS 1 250 7
NESTED LOOPS 1 235 6
NESTED LOOPS 1 220 5
NESTED LOOPS 1 165 4
NESTED LOOPS 1 110 3
TABLE ACCESS BY INDEX
ROWID DBAUTHUSER90 1 55 2
INDEX RANGE SCAN IXAUTHUSER90TEST 1
3
TABLE ACCESS BY INDEX
ROWID DBAUTHUSER90 1 55 1
INDEX RANGE SCAN IXAUTHUSER90TEST 1
TABLE ACCESS BY INDEX ROWID DBAUTHUSER90 1
55 1
INDEX RANGE SCAN IXAUTHUSER90TEST 1
TABLE ACCESS BY INDEX ROWID DBAUTHUSER90 1
55 1
INDEX RANGE SCAN IXAUTHUSER90TEST 1
TABLE ACCESS BY INDEX ROWID DBSUPPLIER 6 K 93
K 1
INDEX UNIQUE SCAN PKSUPPLIER 6 K
TABLE ACCESS BY INDEX ROWID DBCLIENTSUPPLIER 8
K 128 K 1
INDEX RANGE SCAN IXCLIENTSUPPLIER1 8 K
TABLE ACCESS BY INDEX ROWID DBCLIENT 19 152 1
INDEX UNIQUE SCAN PKCLIENT 19
TABLE ACCESS BY INDEX ROWID DBPURCHASEORDER 5 K 563
K 15
INDEX RANGE SCAN IXPURCHASEORDER02 5 K
TABLE ACCESS BY INDEX ROWID DBORDERCATEGORY 8 48 1
INDEX UNIQUE SCAN PKORDERCATEGORY 8
INDEX UNIQUE SCAN PKOFFICE 52 260
TABLE ACCESS BY INDEX ROWID DBDEPARTMENT 1 K 8 K 1
INDEX UNIQUE SCAN PKDEPARTMENT 1 K
TABLE ACCESS BY INDEX ROWID DBCLIENTSUPPLIER 8 K 94 K 1
INDEX UNIQUE SCAN PKCLIENTSUPPLIER 8 K

The resultset comes back within 1 second using this optimizer values:

optimizer_mode = first_rows
optimizer_index_caching = 50
optimizer_index_cost_adj = 25

On ORACLE 9i we have got the same view, the same statement, absolutely
the same data (we did an export --> import) and updated statistics ,
but we've got this explain plan WITH A CARTESIAN product:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 27
FILTER
FILTER
NESTED LOOPS 1 398 24
NESTED LOOPS 1 391 23
NESTED LOOPS 1 383 22
NESTED LOOPS 1 370 21
NESTED LOOPS 1 358 20
FILTER
NESTED LOOPS OUTER
NESTED LOOPS 1 348 18
MERGE JOIN CARTESIAN 1 237 11
TABLE ACCESS BY INDEX ROWID DBAUTHUSER90 1
57 2
NESTED LOOPS 1 228 5
NESTED LOOPS 1 171 4
NESTED LOOPS 1 114 3
TABLE ACCESS BY INDEX
ROWID DBAUTHUSER90 1 57 2
INDEX RANGE SCAN IXAUTHUSER90TEST 1
1
TABLE ACCESS BY INDEX
ROWID DBAUTHUSER90 1 57 2
INDEX RANGE SCAN IXAUTHUSER90TEST 1
TABLE ACCESS BY INDEX
ROWID DBAUTHUSER90 1 57 2
INDEX RANGE SCAN IXAUTHUSER90TEST 1
INDEX RANGE SCAN IXAUTHUSER90TEST 1
BUFFER SORT 8 K 75 K 9
TABLE ACCESS FULL DBCLIENTSUPPLIER 8 K 75
K 6
TABLE ACCESS BY INDEX ROWID DBPURCHASEORDER 1
111 8
INDEX RANGE SCAN IXPURCHASEORDER02 48
INDEX UNIQUE SCAN PKOFFICE 1 4
TABLE ACCESS BY INDEX ROWID DBORDERCATEGORY 1 6 2
INDEX UNIQUE SCAN PKORDERCATEGORY 1
TABLE ACCESS BY INDEX ROWID DBCLIENTSUPPLIER 1 12 2
INDEX UNIQUE SCAN PKCLIENTSUPPLIER 1
TABLE ACCESS BY INDEX ROWID DBSUPPLIER 1 13 2
INDEX UNIQUE SCAN PKSUPPLIER 1
TABLE ACCESS BY INDEX ROWID DBDEPARTMENT 1 8 2
INDEX UNIQUE SCAN PKDEPARTMENT 1
TABLE ACCESS BY INDEX ROWID DBCLIENT 1 7 2
INDEX UNIQUE SCAN PKCLIENT 1
NESTED LOOPS 1 18 3
TABLE ACCESS BY INDEX ROWID DBSTATETYPENUMBER 1 9 2
INDEX UNIQUE SCAN UXSTATETYPENUMBER 14
INDEX UNIQUE SCAN UXSTATEOIDTYPE 1 9


Moreon, the statement is extremly slow. IT TAKES ABOUT 15 MINUTES to
get a resultset from the database.

Does anyboy know what the problem might be and maybe know a solution?

Any help will be appreciated!

Regards,

Christian

[Database Administrator]

On 27 Oct 2003 02:48:31 -0800, cha@christian-hartmann.de (Christian
Hartmann) wrote:

>Does anyboy know what the problem might be and maybe know a solution?

who can tell with so little details as to table definition, primary
and foreign key definition, index definitions, the type of analysis
performed, whether appropiate histograms have been created, etc, etc,
etc.

You might want to consider to refrain from posting this type of 'It
doesn't work' request completely, as crystall balls have been long
sold out here (as you well know, as you are a regular visitor,
providing 'puzzles')


--
Sybrand Bakker, Senior Oracle DBA

[Database Administrator]

On 27 Oct 2003 02:48:31 -0800, cha@christian-hartmann.de (Christian
Hartmann) wrote:

>Does anyboy know what the problem might be and maybe know a solution?

who can tell with so little details as to table definition, primary
and foreign key definition, index definitions, the type of analysis
performed, whether appropiate histograms have been created, etc, etc,
etc.

You might want to consider to refrain from posting this type of 'It
doesn't work' request completely, as crystall balls have been long
sold out here (as you well know, as you are a regular visitor,
providing 'puzzles')


--
Sybrand Bakker, Senior Oracle DBA

[Database Administrator]

cha@christian-hartmann.de (Christian Hartmann) wrote in message news:<7c275508.0310270248.76f4f06f@posting.google.com>...
> Hi there,
>
> we are facing strange behaviours of the optimizer after migration from
> Oracle 8.1.7 to Oracle 9.2.0.4

Christian,

You're not alone. The CBO in 9.2 is a different beast.
I hope that this is your test database, and not your production one.

Can you provide info as to how statistics are being gathered
(dbms_stats.gather_schema_stats w/cascade?), and what the settings of
the folloing parameters:

db_file_multiblock_read_count
compatible

I would first consider increasing the value of:

optimizer_index_caching

to 85, flush the shared pool and try again.

Try reducing the db_file_multiblock_read_count for your session, flush
the shared pool and re-run the query. If you have an 8k block size try
dbfmrc=8.

optimizer_features_enable=8.1.7 would most likely "fix" this query but
would not fix the problem. it must be set instance-wide.

you can dump a 10053 trace to see what decisions the cost based
optimizer is using. search for a paper written by Wolfgang Breitling
titled:

"A Look under the Hood of CBO - the 10053 Event".

dumping the 10053 trace file will be a good step toward having enough
info to post an iTAR. it lists all of the parameters used by the
optimizer, statistics info, base table access costs, index access
costs, etc.

hth.

Pd

[Database Administrator]

cha@christian-hartmann.de (Christian Hartmann) wrote in message news:<7c275508.0310270248.76f4f06f@posting.google.com>...
> Hi there,
>
> we are facing strange behaviours of the optimizer after migration from
> Oracle 8.1.7 to Oracle 9.2.0.4

Christian,

You're not alone. The CBO in 9.2 is a different beast.
I hope that this is your test database, and not your production one.

Can you provide info as to how statistics are being gathered
(dbms_stats.gather_schema_stats w/cascade?), and what the settings of
the folloing parameters:

db_file_multiblock_read_count
compatible

I would first consider increasing the value of:

optimizer_index_caching

to 85, flush the shared pool and try again.

Try reducing the db_file_multiblock_read_count for your session, flush
the shared pool and re-run the query. If you have an 8k block size try
dbfmrc=8.

optimizer_features_enable=8.1.7 would most likely "fix" this query but
would not fix the problem. it must be set instance-wide.

you can dump a 10053 trace to see what decisions the cost based
optimizer is using. search for a paper written by Wolfgang Breitling
titled:

"A Look under the Hood of CBO - the 10053 Event".

dumping the 10053 trace file will be a good step toward having enough
info to post an iTAR. it lists all of the parameters used by the
optimizer, statistics info, base table access costs, index access
costs, etc.

hth.

Pd

[Database Administrator]

On 27 Oct 2003 06:26:06 -0800, drak0nian@yahoo.com (Paul Drake) wrote:

>You're not alone. The CBO in 9.2 is a different beast.
>I hope that this is your test database, and not your production one.

Fortunately it is test-system. :-)

Thanks for your help. We have now the Oracle Support working on this.

I will keep you up to date.

Regards,

Christian

[Database Administrator]

On 27 Oct 2003 06:26:06 -0800, drak0nian@yahoo.com (Paul Drake) wrote:

>You're not alone. The CBO in 9.2 is a different beast.
>I hope that this is your test database, and not your production one.

Fortunately it is test-system. :-)

Thanks for your help. We have now the Oracle Support working on this.

I will keep you up to date.

Regards,

Christian

[Database Administrator]

Christian Hartmann wrote in message news:<3cptpv0ctt2rhcvjrakrj9nvgi28ljhii6@4ax.com>...

> I will keep you up to date.

Update:

Oracle-support has suggested to include the parameter

_optimizer_new_join_card_computation = false

in the init-ora file.

Now it works properly.

Regards,

Christian Hartmann

[Database Administrator]

Christian Hartmann wrote in message news:<3cptpv0ctt2rhcvjrakrj9nvgi28ljhii6@4ax.com>...

> I will keep you up to date.

Update:

Oracle-support has suggested to include the parameter

_optimizer_new_join_card_computation = false

in the init-ora file.

Now it works properly.

Regards,

Christian Hartmann