Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Starne behaviour of optimizer within in 9i vs 8i
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
AND aude.userid=ausu.userid AND auof.userid=ausu.userid AND ausu.OIDCOLUMN='SupplierID' AND ausu.ldapfunction='orderprocessing.order.display.ext' AND ( su.SUPPLIERID=ausu.ACTORID OR ausu.ACTORID=0) AND auof.OIDCOLUMN='OfficeID' AND auof.ldapfunction='orderprocessing.order.display.ext' AND (ff.OFFICEID=auof.ACTORID OR auof.ACTORID=0) AND aude.OIDCOLUMN='DepartmentID' AND aude.ldapfunction='orderprocessing.order.display.ext' AND (de.DEPARTMENTID=aude.ACTORID OR aude.ACTORID=0) AND aucl.OIDCOLUMN='ClientID' AND aucl.ldapfunction='orderprocessing.order.display.ext' 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.docngorderquantityFROM 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 )
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 Received on Mon Oct 27 2003 - 04:48:31 CST