Even though this is addressed to don, I thought I might jump in here as we just had a client that experienced the same problem. I'll bet your CPU is out the roof but it is not reflected in the statistics? If so look at an undocumented parameter " _b_tree_bitmap_plans" it flips over to true in 9i and 10g from a value of false in 8i and early 9.0. It seems to lay in wait until you reach a certain size then it kicks in driving CPU usage out the roof and in general causing the system to misbehave. You might try switching it to false. I suggest looking at the explain plan output of some of your troubled queries, they may or may not show it is trying to use this b-tree bitmap conversion path. If you get really adventurous, you can try a 10053 trace event (look at note 225598.1 on metalink for how to do this) to see exactly what the CBO is doing to resolve the query.
However, if you must get the system performing better fast and this seems to be an issue (>9.0 Oracle release, CPU Usage high, no other changes to the database and performance suddenly tanks) then try resetting this parameter to false.
Don Burleson adds:
"Its like searching a needle in a big dark room." Yes, I know the feeling! Obviously, something changed, and I always start by examining the server (maybe a new process has been added by the SA?), and then I drill-down into Oracle. Send over a 15-minute STATSPACK report, and we can have a look!
Dig Deeper on Oracle database performance problems and tuning
Related Q&A from Mike Ault
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to... Continue Reading
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists. Continue Reading
I want to find the length of a numeric datatype field in my table. How can I find it? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.