Oracle 10g Database Tuning - FAQ
- 1. Why and when should one tune?
- 2. What tools/utilities does Oracle provide to assist with performance tuning?
- 3. My query was fine last week and now it is slow. Why?
- 4. Why is Oracle not using the index?
- 5. When should one rebuild an index?
- 6. How can one optimize %ABC% queries?
- 7. Where can one find I/O statistics per table?
- 8. What is STATSPACK and how does one use it?
- 9. How does one tune the Redo Log Buffer?
1. Why and when should one tune?
One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.
One should do performance tuning for the following reasons:
- The speed of computing might be wasting valuable human time (users waiting for response);
- Enable your system to keep-up with the speed business is conducted; and
- Optimize hardware usage to save money (companies are spending millions on hardware).
2. What tools/utilities does Oracle provide to assist with performance tuning?
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
- ADDM (Automated Database Diagnostics Monitor) introduced in Oracle 10g
- TKProf
- Statspack
- Oracle Enterprise Manager - Tuning Pack (cost option)
- Old UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring
3. My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
- Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
- Has OPTIMIZER_MODE been changed in INIT.ORA?
- Has the DEGREE of parallelism been defined/changed on any table?
- Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
- Have the statistics changed?
- Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
- Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
- Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.
4. Why is Oracle not using the index?
This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index. Fundamental things that can be checked are:
- USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
- USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index decreases.
- USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
- Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN).
5. When should one rebuild an index?
You can run the ANALYZE INDEX
6. How can one optimize %ABC% queries?
It is possible to improve %ABC% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints.
If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.
7. Where can one find I/O statistics per table?
The STATSPACK and UTLESTAT reports show I/O per tablespace. However, they do not show which tables in the tablespace has the most I/O operations.
The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.
8. What is STATSPACK and how does one use it?
Statspack is a set of performance monitoring and reporting utilities provided by Oracle from Oracle8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available but no longer supported.
9. What is STATSPACK and how does one use it?
The size of the Redo Log Buffer is determined by the LOG_BUFFER parameter in your SPFILE/INIT.ORA file. The default setting is normally 512 KB or (128 KB * CPU_COUNT), whichever is greater. This is a static parameter and its size cannot be modified after instance startup.
When a transaction is committed, info in the redo log buffer is written to a Redo Log File. In addition to this, the following conditions will trigger LGWR to write the contents of the log buffer to disk:
- Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
- Every 3 seconds; or
- When a DBWn process writes modified buffers to disk (checkpoint).
Larger LOG_BUFFER values reduce log file I/O, but may increase the time OLTP users have to wait for write operations to complete. In general, values between the default and 1 to 3MB are optimal. However, you may want to make it bigger to accommodate bulk data loading, or to accommodate a system with fast CPUs and slow disks. Nevertheless, if you set this parameter to a value beyond 10M, you should think twice about what you are doing. Statistic "REDO BUFFER ALLOCATION RETRIES" shows the number of times a user process waited for space in the redo log buffer. This value is cumulative, so monitor it over a period of time while your application is running. If this value is continuously increasing, consider increasing your LOG_BUFFER (but only if you do not see checkpointing and archiving problems). "REDO LOG SPACE WAIT TIME" shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer. If this value is low, your log buffer size is most likely adequate.
