- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Oracle explain plan changed after export
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-03-2005 10:34 AM
тАО01-03-2005 10:34 AM
Oracle explain plan changed after export
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-03-2005 02:50 PM
тАО01-03-2005 02:50 PM
Re: Oracle explain plan changed after export
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-03-2005 04:55 PM
тАО01-03-2005 04:55 PM
Re: Oracle explain plan changed after export
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-03-2005 10:42 PM
тАО01-03-2005 10:42 PM
Re: Oracle explain plan changed after export
What kind of export/import did you do? Can you post the export full instruction you used here?
Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-03-2005 10:48 PM
тАО01-03-2005 10:48 PM
Re: Oracle explain plan changed after export
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
"Reality is just a point of view." (P. K. D.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-03-2005 11:21 PM
тАО01-03-2005 11:21 PM
Re: Oracle explain plan changed after export
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-04-2005 01:51 AM
тАО01-04-2005 01:51 AM
Re: Oracle explain plan changed after export
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-04-2005 02:10 AM
тАО01-04-2005 02:10 AM
Re: Oracle explain plan changed after export
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-04-2005 02:16 AM
тАО01-04-2005 02:16 AM
Re: Oracle explain plan changed after export
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
"Reality is just a point of view." (P. K. D.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-04-2005 02:22 AM
тАО01-04-2005 02:22 AM