Category Archives: Performance

Eventual bad performance for a query with a good plan

The user calls complaining that a recurrent report, which usually completes in 15-20 minutes, is running for a few hours now. You request some more information from the user about the report and its database session, and start troubleshooting: Servers … Continue reading

Posted in Oracle, Performance | Tagged , , , , , , , | 2 Comments

Predicting Oracle Transaction Rollback Time

While waiting for a large transaction to roll back, I’ve cooked this spreadsheet which can, to some extent (not precise, just estimated – use at your own risk) predict how long it will take for the rollback to complete. The … Continue reading

Posted in Oracle, Performance, Troubleshoot | Tagged , | Leave a comment

How to confine a set of users to the Active Data Guard DB only

Active Data Guard is a new (paid) feature of Oracle 11g where a physical standby database can be open for read-only access while Redo Apply is simultaneously active. With the objective of relieving the production database from a group of … Continue reading

Posted in Oracle, Performance | Tagged , , , , | Leave a comment

ASM performance check

Database performance is highly dependent on the underlying hardware’s health and the way it was designed and implemented. This post will focus on ASM performance. For those like me lucky enough to have Enterprise Manager collecting historical statistics, the ASM … Continue reading

Posted in Oracle, Performance | Tagged | Leave a comment

Performance tip of the day: set DB_FILE_MULTIBLOCK_READ_COUNT

Beginning with 10gR2, the default value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter is not 8 anymore. It is a platform-dependent value, and it is the amount of blocks that fit in 1MB for most platforms, so it will be likely around 128 if you’re … Continue reading

Posted in Oracle, Performance | Leave a comment