Oracle explain plan changed after export

[Dave Chamberlin]

Hello,
I have just rebuilt my 8.1.7 database , to go from 4K to 8K blocksize. I used export/import and had only minor problems. Both databases use the RULE based optimizer. Today a problem turned up - a query that ran fine before the rebuild, now hangs. A look at the explain plan shows the query using a different index in the new database than it did in the old (I still have the old database up under an assumed name and the query runs great...). Can anyone give me an idea of what is going on? Thanks

[Indira Aramandla]

Hi Dave,

RBO selects an execution plan based on available access paths and the ranking of those access paths. Chapter 4 in the Oracle8i Designing and Tuning for Performance provides a list of the access paths, their ranking, and an example of each under the section titled: Rule-Based Optimizer (RBO).
You can look at the explain plan to find out why the optimizer 'thinks' it has chosen the execution with the best rank.

Are the parameters in init.ora the same for both the database. And what is the parameter DB_FILE_MULTIBLOCK_READ_COUNT set to.

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter determines the maximum number of database blocks read in one I/O operation during a full table scan. The setting of this parameter can reduce the number of I/O calls required for a full table scan, thus improving performance. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high. The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.
If using RBO (rule based optimizer), the optimizer will not consider the selectivity of index when determining the execution path (unless it is unique index).

The maximum value of the overall multiblock read factor is calculated as:

(db_block_size * db_file_multiblock_read_count)

The value of db_file_multiblock_read_count is limited by the multiblock read factor in the operating system level.


I hope this helps.
Indira A

[Yogeeraj_1]

hi Dave,

quite difficult to troubleshoot. On each environment, I would set SQLTRACE ON, generate the trace file and run a TKPROF report and compare.

regards
yogeeraj

[Eric Antunes]

Hi Dave,

What kind of export/import did you do? Can you post the export full instruction you used here?

Regards,

Eric Antunes

[Fred Ruffet]

simply : block size incresing changes cost for full table scan. They are less expensive so they are more often used.
It will be the same increasing db_file_multiblock_read_count parameter.

One other thing, using export/import has re-organized your DB. So tables/indexes are now contiguous and reading big data in them are now sequential reads when they where random reads. This may lower costs on large reads.

Regards,

Fred

[Jean-Luc Oudart]

Dave

You say you run rule based optimizer. Could you confirm you don't have stats on the tables (otherwise CBO will be used).

Regards
Jean-Luc

[Dave Chamberlin]

The db_multiblock_read_count is 16 for both databases. This would equate to the new database having twice the max IO as the old. Still not huge compared to the max. My value for scsi_maxphys (maxiosize) is 1M. No tables were analyzed (I am looking at dba_tables where last_analyzed is not null). Is there a better way to check this? The table is an Oracle apps table (INV.MTL_MATERIAL_TRANSACTIONS) and has several M rows. It has the same indexes as before. Question - how can CBO be used if the initora file specfies RULE? The export was done as per various documents on MetaLink. I preconfigured an empty database by creating needed tablespaces and datafiles. Then used a named pipe, to connect the export to the import. The indexes were created by generating an indexfile prior to the main export/import. This was broken into scripts, etc.

[Eric Antunes]

You used RECORDLENGTH export option?

[Fred Ruffet]

last_analyzed=null is sufficent to tell table is not analyzed.
optimizer_mode parameter set to RULE is sufficent to tell you are using RBO.

Now, even if you are using RBO, it may be possible that import/export procedure has rebuild index that were invalid.

Regards,

Fred

[Dave Chamberlin]

I did not use the RECORDLENGTH option in the export. My understanding of this parm is that is just performs a write in chunks of the size specified.

[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