Problem solve Get help with specific problems with your technologies, process and projects.

Database performance suddenly crawling

Don, one of our production databases has not been performing well for the last few days. Prior to that it was working fine, and all of a sudden it seems as if it's crawling, however I am not getting any error recorded in my alert.log or any other log file. I have the Statspack report also, but I am not able to point out the problem. It's like searching for a needle in a big dark room. Any suggestion where to start from?

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.