- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: 9i Oracle Performance issues bad Cache Hits
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
тАО08-31-2005 07:10 AM
тАО08-31-2005 07:10 AM
Re: 9i Oracle Performance issues bad Cache Hits
At connect time you can alter your session to rule, or you could put a hint is every statement that you run stating to use the RULE optimizer.
CHOOSE is the default for 9i, so to use RULE you must change the init.ora parameter and restart the database.
If they tell you they are changing the optimizer at the session level you can verify with this statement.
select sql_text, OPTIMIZER_MODE from v$sql;
It will return one row for each session in the database giving you the optimizer_mode that session is running with. Unless they all come back RULE - you know you are using CBO.
As others have stated, if you use the CBO without valid statistics, then that could explain a lot of your performance issues.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 07:44 AM
тАО08-31-2005 07:44 AM
Re: 9i Oracle Performance issues bad Cache Hits
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 07:48 AM
тАО08-31-2005 07:48 AM
Re: 9i Oracle Performance issues bad Cache Hits
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 08:03 AM
тАО08-31-2005 08:03 AM
Re: 9i Oracle Performance issues bad Cache Hits
select last_analyzed from dba_tables;
select last_analyzed from dba_indexes;
If a query accesses multiple tables and any one table has statistics then it will try and use the CBO.
A lot of queries have problems because under the RBO, if an index exists, then it is used. With CBO Oracle tries to determine if the index is selective enough or if a full table scan will be faster. If your db_file_multiblock_read_count ( another init.ora parameter) is set high (16 or better) then the CBO may think full-table scans are faster than index reads.
These are all things that the DBA should be looking at -
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 08:23 AM
тАО08-31-2005 08:23 AM
Re: 9i Oracle Performance issues bad Cache Hits
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 08:35 AM
тАО08-31-2005 08:35 AM
Re: 9i Oracle Performance issues bad Cache Hits
Let the DBA's decide this one, but a valid recommendation would be to change the init.ora optmizer_mode parameter back to RULE, assuming that's what it was in the 8i database. Then gradually decide if CBO is a valid option. If the optimizer_mode is RULE ( at the database level) and you have valid statistics on all tables, then you can change individual queries to use the CBO by adding sql hints.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 09:34 AM
тАО08-31-2005 09:34 AM
Re: 9i Oracle Performance issues bad Cache Hits
Let's put it this way, I remember waaay back in 1996(or 97) when the cost optimizer was turned on w/o stats ... and sounds like your day(s) of late. I didn't think of it earlier b/c it just didn't occur to me that any parts of the planet still ran databases not yet converted to CBO yet, having made the conversion quite some time back.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 11:04 AM
тАО08-31-2005 11:04 AM
Re: 9i Oracle Performance issues bad Cache Hits
Just as a debug tool, it can be revealing to delete statistic on a suspect table, and in effect force rule based optimisation, just to force a different execution plan and see the effect (good or bad). Put the statistics back again later.
Seperately, check for full table scans, or use of inappropriate indexes, which will be slowing things down. Drop any indexes which might be getting used on the long operations. Run it again and see if performance improves. Check what index is being used now. Create you own specific indexes where appropriate.
Do this all with enterprise manager consol.
We effectively resolved peformance issues in this manner after changing from 817 to 920.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2005 11:02 PM
тАО08-31-2005 11:02 PM
Re: 9i Oracle Performance issues bad Cache Hits
One aspect is that the Optimizer has changed and you have new oracle parameters too with default value that may affect the optimisation of some of the queries.
It is strongly advised to re-run the statistics gathering as they should be issued with same Oracle version you run the database.
Regarding which optimizer is used cf. Metalink doc Note:66484.1 "Which Optimizer is Being Used ?"
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-01-2005 01:33 AM
тАО09-01-2005 01:33 AM
Re: 9i Oracle Performance issues bad Cache Hits
Did the DBA's try turning off the CBO, and did it fix the problem? Just wondering how it turned out thus far.