Some Oracle 10g Tuning tips

Some Oracle 10g Tuning tips

AWR

In Oracle10g the Automatic Workload repository is the primary component of Common Manageability Infrastructure (CMI). It is the successor for the Statspack. While the Statspack is still available with 10g, just a few improvements are planned to it; therefore AWR is the preferred mechanism for diagnosing database problems.

AWR provides data used by other CMI components, such as system generated alerts and the advisors. The AWR consist of 2 main components:

- In-memory statistics

- Repository snapshot.

In-memory statistics are gathered once a second on active sessions. They are not written to the database and are aged out of memory as new statistics are generated. In memory statistics are accessed through the view V$ACTIVE_SESSION_HISTORY which queries the ASH (Active Session History) circular buffer area of the SGA (System Global Area)

AWR relies on MMON which wakes up every hour and statistics collection into the repository snapshot (you can modify the collection interval), AWR snapshot provides a persistent view of database statistics.

A script is provided ($ORACLE_HOME/rdbms/admin/awrrpt.sql) to generate a report using repository snapshot. There is also an awrrpti.sql report as well, which has essentially the same output but allows you to define and report on a specific instance.

ADDM

The Automatic Database Diagnostic Monitor (ADDM) uses the snapshot information of AWR to automatically identify performance problems and make recommendations to correct them. ADDM is the predecessor of the Oracle expert tool. ADDM functionality is embedded into the kernel making performance impact near negligible. ADDM analysis is performed every time an AWR snapshot is taken. in addition ADDM can automatically fix certain problems.

To allow use of ADDM, a PL/SQL interface called DBMS_ADVISOR has been provided. This interface may be called directly through the supplied script $ORACLE_HOME/rdbms/admin/addmrpt.sql. Besides this PL/SQL package a number of views allow you to retrieve the results of any actions performed with the DBMS_ADVISOR API.

When the parameter STATISTICS_LEVEL is set to TYPICAL or ALL, the database will automatically schedule the AWR to be populated every 60 min. To create snapshots out of this interval you need to use the dbms_workload_repository.create_snapshot () package.

To be useful in diagnosing a particular problem, the snapshots needs to be created before and after the situation you wish to examine.

The following views are available for querying ADDM information.

View	Description
DBA_ADVISOR_ACTIONS	Actions associated with all recommendations
DBA_ADVISOR_COMMANDS	Commands used by advisor in DB for specifying recommended actions
DBA_ADVISOR_DEF_PARAMETERS	Parameters and default values for all tasks in the database.
DBA_ADVISOR_DEFINITIONS	Properties of all the advisors in the database
DBA_ADVISOR_DIRECTIVES	Not documented
DBA_ADVISOR_FINDINGS	Findings discovered by all advisors
DBA_ADVISOR_JOURNAL	Journal entries for all tasks
DBA_ADVISOR_LOG	Current state of all tasks along with execution specific data such as progress monitoring and completion status
DBA_ADVISOR_OBJECT_TYPES	Object types used by all advisors
DBA_ADVISOR_OBJECTS	Objects currently referenced by all advisors
DBA_ADVISOR_PARAMETERS	Parameters and their current value for all tasks
DBA_ADVISOR_PARAMETERS_PROJ	Not documented
DBA_ADVISOR_RATIONALE	Rationales for all recommendations
DBA_ADVISOR_RECOMMENDATION	Result of complemented diagnostics tasks with action recommendation for the problems identified in each run
DBA_ADVISOR_SQLA_REC_SUM	Recommendation rollup information for all workload objects after analysis
DBA_ADVISOR_SQLA_WK_MAP	Workload references for all tasks
DBA_ADVISOR_SQLA_WK_STMTS	Workload objects after analysis
DBA_ADVISOR_SQLW_COLVOL	Not documented
DBA_ADVISOR_SQLW_JOURNAL	Journal entries for all workload
DBA_ADVISOR_SQLW_PARAMETERS	Workload parameters and their current values
DBA_ADVISOR_SQLW_STMTS	Rows that correspond to all statements in the workload
DBA_ADVISOR_SQLW_SUM	Aggregate picture of all SQL workload
DBA_ADVISOR_SQLW_TABLES	Cross-references between workload statements and tables references on it.
DBA_ADVISOR_SQLW_TABVOL	Not documented
DBA_ADVISOR_SQLW_TEMPLATES	Aggregate picture of all SQL workload template objects
DBA_ADVISOR_TASKS	Information about all tasks in the DB
DBA_ADVISOR_TEMPLATES	Information about all templates in the DB
DBA_ADVISOR_USAGE	Information for each type of advisor

SQL Tuning Advisor

This is feature automates the entire SQL tuning process. It analyzes the SQL statements and executes a complete analysis of the statements.

The advisor can be administered with the procedures in the DBMS_SQLTUNE package. To use the API the user must be granted DBA role, SELECT_CATALOG_ROLE role and the ADVISOR privilege.

Running SQL Tuning advisor using DBMS_SQLTUNE package is a two-step process. First you create a tuning task and then you execute the task. The CREATE_TUNING_TASK function returns the task name that you have provided or generates a unique task name. You can use the task name to specify this task when using other API’s.

Check for chaining rows

To see if you have chaining problems, run utlchain.sql script that Oracle provides and is in /$ORACLE_HOME/rdbms/admin subdirectory. You should check for chaining on a weekly basis and fix any problems immediately.

Analyze table customers; List chained rows;

Then run the following query accessing the CHAINED_ROWS table to check the customer table chaining:

Select HEAD_ROWID from CHAINED_ROWS where TABLE_NAME = ‘CUSTOMER’;

If no rows are returned, then you don’t have a chaining problem. If there is a chaining problem then the query will return the head_rowid for all chained rows. You can also use the “count(*)” against the CHAINED_ROWS table to find the number of chained rows. In V$SYSSTAT, the “table fetch continue row” is an indicator of chained rows as well.

To avoid row chaining, set PCTFREE correctly, the default is set to 10.

Increasing the log file size and LOG_CHECKPOINT_INTERVAL for speed

If you want to speed up large numbers of INSERTs, UPDATEs, and DELETEs, increase the sizes of your log files and make sure they are on the fastest disk. Previously, you could also increase the LOG_CHECKPOINT_INTERVAL if it was set such that it would checkpoint prior to log switch, but this parameter is being deprecated and currently is set to zero (which indicates switching based on redo log being full).

Determining if redo log file size is a problem

There could be 2 problems with the size of the redo log files:

A) Batch jobs that do not have enough total redo space to complete or are so fast that the online redo logs wrap (cycle through all the logs and start writing to the first one again) before they are archived to the offline redo logs.

B) Very long running jobs that are spending a large amount of time switching online redo logs. Long running jobs are often much faster when the entire job fits into a single online redo log. For the online transaction processing (OLTP) type of environment, smaller online redo log are usually better. Optimal online redo log switch should happen every half hour (no counting the long running batch jobs that shorten this time)

Use the query below to determinate if there is a problem, it shows the time between log switches.

SQL> l select b.recid, to_char(b.first_time,'DD-MON-YY HH:MI:SS') start_time, a.recid, to_char(a.first_time,'DD-MON-YY HH:MI:SS') end_time, round(((a.first_time - b.first_time)*25)*60,2) minutes from v$log_history a, v$log_history b where a.recid = b.recid+1 order by a.first_time a

Determining the size of your log files and checkpoint interval

SQL> l 1 select a.member, b.* 2 from v$logfile a, v$log b 3* where a.group# = b.group#