Oracle explain plan changed after export

[Eric Antunes]

You followed note 1011167.7?

Maybe Fred is right: use adadmin (as apps user - usualy appsmgr) to compile all database objects.

[Patti Johnson]

Dave,

Did you also verify that the indexes do not have statistics. I remember an issue with 8i and Apps 11.0 where rebuilding indexes with the parallel option which set the parallel degree on the indexes - that caused the CBO to be used even with the RBO set at the db level.

Check for dba_indexes for indexes with a value for degree greater than 1.

You can correct with
alter index noparallel;

If that does not do it please post the old and new execution plans.

Patti

[Dave Chamberlin]

I used ML doc 1011167.7 as a starting point for the export (I precreated tablespaces and datafiles to minimize downtime). I have already run scripts to compile all invalid objects after the process. I have attached a screen shot showing the explain plan (new). The old is identical except for the index MLT_MATERIAL_TRANSACTIONS_N2 is used instead of N1. Both indexes are composite: the N1 index has INVENTORY_ITEM_ID first, N2 has TRANSACTION_SOURCE_ID first.

[Eric Antunes]

Hi Dave,

Sorry about the delay...

Try to put the inventory_item_id condition first and to use organization_id column in the query (I always use it in my queries to INV tables), like this:

"...
where msi.inventory_item_id = :b3 and
msi.organization_id = :bn and
mmt.inventory_item_id = msi.inventory_item_id and
...
"

Finaly, if you use a date interval in this query it may execute via MLT_MATERIAL_TRANSACTIONS_N1:

"...
mmt.transaction_date between :p_date_from and :p_date_to and
...
"

Best Regards,

Eric Antunes

[Patti Johnson]

Have you looked at the trace file created by an actual run of the query, not just the plan from SQL Navigator? On my system the index on transaction_source_id is more selective than inventory_item_id, so the plan does appear to be the correct one. A level 12 trace would give you better information as to where the time is being spent.

Also check out metalink note 70008.1 - this gives an explaination of the problems with using parallel index rebuilds. It should be fixed in 8.1.7, but I would make sure all the indexes are set to noparallel just in case.

Patti

[Brian Crabtree]

Dave,

You can also issue an 'alter session set db_file_multiblock_read_count = 8' and see if that changes the explain plan for the query as well.

Brian