This post is a general outline as how I go about my tuning endevours in a typical Oracle database environment, and is more aimed at getting feedback for improvement than anything else.
I divide my performance tuning of Oracle in two modes: Proactive and Reactive.
In proactive mode, I make sure that the hardware is strong enough with plenty of memory, CPU, network and Disk.
As I prepare my Oracle database environment (11g), I utilize ASM and ACFS with flash recovery area with block change tracking enable for RMAN.
I also use automatic feature of the database like Automatic Memory Management, Automatic Tablespace management and ASM and so on.
I run nightly batch jobs to keep the statistics up to date and configure the optimizer optimally according to requirements..
I daily analyze the findings of the ADDM and look into the alert log file.
I also analyze the prospects of indexing at the table level, partitioning the tables, materialized view usage.
In reactive mode, as some user come running complaining that "database is slow", I try to follow the following steps:
I identify the part of application running slow. At the start of that part, I start tracing via DBMS_MONITOR, and then at the end of that part I finish the tracing. I run trace file through the profiler and identify the culprit SQL.
Once SQL is identified, I get its execution plan and other information and calculate its selectivity and on the basis of its selectivity I check to see that whether it's using the appropriate access path or not (Index or FTS). Is parsing is problem or its doing lots of logical reads?
If selectivity is high and according to plan, query is also using an index, then I check the clustering factor of the index.
If selectivity is low and FTS is being done, I check the partitioning options.
I keep the measurements of the start and end of the exercise and yes I don't tune too much, I just keep the users happy.
I welcome corrections, suggestions, improvements and feedback in the comments.
No comments:
Post a Comment