{"id":8008,"date":"2019-01-18T08:03:49","date_gmt":"2019-01-18T08:03:49","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/database-performance-tuning\/"},"modified":"2026-11-03T19:29:13","modified_gmt":"2026-11-03T19:29:13","slug":"database-performance-tuning","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/database-performance-tuning\/","title":{"rendered":"Database performance tuning"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

Doing performance tuning on your database is one of the most common and yet the most important activity that every database administrator (DBA) must perform on an ongoing and regular basis. Experts recommend that you regularly fine-tune databases to optimized performance. Fine-tuning helps users to run their reports and queries faster and get results quicker. This post shares some techniques for troubleshooting and tuning Oracle \u00a9; databases.<\/p>\n

Tuning steps<\/strong><\/p>\n

To start with, you need to identify the area where the problem is occurring.Possible areas that can trigger the problem include the operating system, database, memory shortages, and so on. After you have identified a problem area, you can proceed with tuning the area for maximum benefits. This blog focusses on only performance tuning.<\/p>\n

Oracle has given DBAs a few tools to help with diagnosing problems and troubleshooting performance issues. These tools include the Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository (AWR) reports, which you can generate for the problematic time-period and use in your analysis. You should use the tools to look for skewed or tunable components.<\/p>\n

 Top Timed Events in AWR reports<\/strong><\/p>\n

AWR is a tool with which you can generate a report that summarizes memory usage in a statistics form by a particular instance. To generate an AWR report, run the following file in a sqlplus prompt:<\/p>\n

    @$ORACLE_HOME\/rdbms\/admin\/awrrpt.sql<\/em><\/p>\n

\nThis generates an AWR report in HTML format by  using a pre-stored template. After you have generated the AWR report, you can quickly see the Top Timed Events<\/strong> section in the AWR report, which identifies the problematic area, as shown in the following image:<\/p>\n

\"database<\/p>\n

 ADDM tuning Session <\/strong><\/p>\n

Normally, when an issue is reported, the DBA might troubleshoot only that area or resolve that specific problem. However, if the issue is not correctly identified and addressed, it might lead to bigger problems in future. The DBA can miss the bigger picture here. To help DBAs get a bigger and a complete picture about database issues, Oracle has provided another tool for DBAs, ADDM. An ADDM tuning session follows a procedure similar to the manual tuning session. The following image shows a comparison:<\/p>\n

\"database<\/p>\n

Image Source<\/em><\/a> <\/p>\n

Retrieve ADDM reports by using SQL or through Oracle Enterprise Manager (OEM). The following image shows sample SQL:<\/p>\n

\"database<\/p>\n

 Bad SQL and execution plans <\/strong><\/p>\n

After the AWR or ADDM report identifies sections of bad SQL\/SQLID, you can use DBMS\\_XPLAN to gather more information about them. The DBMS_XPLAN package supplies the following table functions that you can use to retrieve and display the execution (exec) plan:<\/p>\n

    DISPLAY
\n    DISPLAY_AWR
\n     select plan_table_output from table (DBMS_XPLAN.DISPLAY_AWR(‘fs22b3fgfh8xc’));
\n    DISPLAY_CURSOR<\/em><\/p>\n

The following image shows sample output from the `EXPLAIN PLAN` command and identifies whether the query is using a full table scan or narrowing down the data by using some indexes:<\/p>\n

\"database<\/p>\n

ADDM reports reflect whether you can achieve any performance benefit by creating some new indexes. You can also run the SQL Tuning Advisor from OEM to fine tune the queries and possibly use a better execution plan. Most of the time, using a better plan for problematic SQL resolves the major performance issue. <\/p>\n

SQL Tuning Advisor<\/strong><\/p>\n

SQL Tuning Advisor helps analyze SQL statements and obtain performance recommendations by using SQL authorization ID (SQLID) to analyze and suggest solutions. SQL Tuning Advisor analyzes the following sources:<\/p>\n