- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Execution Plan on CBO vs Rule
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
тАО09-06-2004 03:00 PM
тАО09-06-2004 03:00 PM
The DB optimizer_mode is choose. We analyze all database every week. However, some SQL statement execution time is too long. If we add a hint '/*+ rule */', the execution plan is also changed. Then its execution time became faster than CBO. What's going on?
Thanks
Eric
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2004 08:04 PM
тАО09-06-2004 08:04 PM
Re: Execution Plan on CBO vs Rule
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2004 08:18 PM
тАО09-06-2004 08:18 PM
Re: Execution Plan on CBO vs Rule
-------------
select owner,table_name,to_char(LAST_ANALYZED,'YYMMDD')
from dba_table
--------------
The result is expected. We reiterate many queries/DML are fast based on CBO. However, few queries are slow against CBO. This is a big database with 180 GB and it works over 2 years. Can someone share your experience on this?
Thanks
Eric
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2004 08:29 PM
тАО09-06-2004 08:29 PM
Re: Execution Plan on CBO vs Rule
Sometimes the CBO method is not the best !
Saying that ususally it's better than the old RBO (desupported after 9i ?)
I some case we have to change oracle behaviour either by changeing init.ora parameters or adding SQL hints in the code or changing the way the data is loaded into a table (Physical parameters). It's all about tuning !
CBO makes your life easier but it 's not the ultimate performance master !
This means that DBA have still some work to do.
see attachment
Could you tell us how you update the statistics ?
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2004 08:30 PM
тАО09-06-2004 08:30 PM
Re: Execution Plan on CBO vs Rule
forget the attachment
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2004 08:31 PM
тАО09-06-2004 08:31 PM
Re: Execution Plan on CBO vs Rule
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2004 08:38 PM
тАО09-06-2004 08:38 PM
Re: Execution Plan on CBO vs Rule
On init
timed_statistics = true
optimizer_mode = cost
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2004 08:39 PM
тАО09-06-2004 08:39 PM
Re: Execution Plan on CBO vs Rule
You have many solutions to correct such a problem :
Modify the query
Add hints
Open a TAR
As long as the two first may be unsufficent (or can not be done), you may have to contact Oracle support. Note that this third solution may be long.
Apparently you can modify queries so you can do it before using this hint.
Note that RBO will effectivly be desupported after 9i, and may really disappear in future releases.
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
тАО09-06-2004 11:31 PM
тАО09-06-2004 11:31 PM
Re: Execution Plan on CBO vs Rule
You have just assigned points to 8 of 46 responses... :-(
We need points to survive. :-)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-07-2004 05:03 AM
тАО09-07-2004 05:03 AM
Re: Execution Plan on CBO vs Rule
You might also want to look at Histograms. I have heard that they work well, however I have not had the available time to look into them more.
Thanks,
Brian