Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Starne behaviour of optimizer within in 9i vs 8i

Starne behaviour of optimizer within in 9i vs 8i

From: Christian Hartmann <cha_at_christian-hartmann.de>
Date: 27 Oct 2003 02:48:31 -0800
Message-ID: <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

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.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
                                )

   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 Received on Mon Oct 27 2003 - 04:48:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US