| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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.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
)
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
 |
 |