- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: 9i optimizer
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-28-2005 08:30 AM
тАО01-28-2005 08:30 AM
We just migrated to 9.2.0.5 from 8.1.7.4. I created a new index. Wrote a simple query:
select claim_number
from claim
where data_source = 4000;
Oracle used the index perfectly. I used dbms_stats to analyze the tables and indexes. Now Oracle no longers uses the index. It does a full table scan. The claim table is a 9 gig table. With the index it runs in seconds. Without it runs in minutes. Does anyone have any experience with the 9.2.0.5 optimizer? I don't want to use the RULE hint. We have other queries that are having the same problem. This is the simpliest one. I've heard that the 9.2.0.6 has some fixes for the optimizer. But I'd like to work this out before having to apply a patch if possible.
Things I've tried. Using gather_stats instead. Changed the optimizer to 8.1.7. Just analyzed the tables not the indexes. None of these worked. It only uses the index right after creation. Once the table is analyzed then oracle no longer uses the index.
Thanks in advance.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2005 08:47 AM
тАО01-28-2005 08:47 AM
Re: 9i optimizer
Wow, that almost sounds like too big a bug to be! Surely the world would be screaming?
I wonder what extra trigger there might be in your application to make it do that. Or, maybe indeed many more customers are suffering but did not dril down as nicely as you did.
Did you clean out your initXXX.ora in case it gathered a setting with this surprise effect?
At any rate, this is very unlikely to be an hpux specific problem. Sure it could be port specific, but more liekly it is generic. Therefor I would recommend to ask the question in an Oracle newsgroup, forum, asktom, ...
Hope this helps a tiny bit,
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2005 08:53 AM
тАО01-28-2005 08:53 AM
Re: 9i optimizer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2005 09:05 AM
тАО01-28-2005 09:05 AM
Re: 9i optimizer
How many of those rows are associated with the 'data_source' column equal to 4000?
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-29-2005 06:03 AM
тАО01-29-2005 06:03 AM
SolutionOn another not - The optimizer really pays attention to how much data it needs to read. Have you looked at your MULTIBLOCK_READ_COUNT? Is this the standard setting of 8?
I've found that the higher you set this - the more full table scans you get.
Also run
Select count(*), claim_number
from claim
group by claim_number;
What's your spread look like?
Do the same and tell me how many rows have a data_source of 4000.
Did you gather stats / anaylze the index after you created it? If not - the cost
optimzer is not going to use it. While you're at it do an analyze of 25 or 30%
or so of the table too.
OK - one more (just an fyi - its not going to
make a diff whether or not you're full table scanning).
Be careful using the feature that will manage your user's PGA. While this worked fine 9.2.0.4 - it will reduce 9.2.0.5 to about half speed for 9.2.0.5 if you're under a substantial load. This is a real monster to watch for. Please check and make sure that it's turned off. If you need I can post back later with the exact settings on that. Just let me know.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2005 06:11 AM
тАО01-31-2005 06:11 AM
Re: 9i optimizer
COUNT(*) DATA_SOURCE
---------- -----------
18534528 4000
16376369 4001
2727251 4002
1273410 4003
6024692 4004
I tried setting multiblock read count down from 32 to 8. That didn't do anything. Since this cardinality of this data is so low maybe I should use a bitmap index instead.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2005 06:12 AM
тАО01-31-2005 06:12 AM
Re: 9i optimizer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2005 11:03 AM
тАО01-31-2005 11:03 AM
Re: 9i optimizer
I like bitmap indexes but, they can usually only be used on lookup tables, charts of accounts, etc - things that don't change often.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2005 11:55 AM
тАО01-31-2005 11:55 AM
Re: 9i optimizer
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2005 02:49 PM
тАО01-31-2005 02:49 PM