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.