• The default value for the OPTIMIZER_MODE initialization parameter is ALL_ROWS.
• Automatic Statistics Collection
• Changes in the DBMS_STATS Package
• Dynamic Sampling

Oracle determines at compile time whether a query would benefit from dynamic sampling.

Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query to estimate statistics.

OPTIMIZER_DYNAMIC_SAMPLING takes values from zero (OFF) to 10 (default is 2).

• Table Monitoring

If you use either the GATHER AUTO or STALE settings when you use the DBMS_STATS package, you don抰 need to explicitly enable table monitoring in Oracle Database 10g; the MONITORING and NO MONITORING keywords are deprecated.

Oracle uses the DBA_TAB_MODIFICATIONS view to determine which objects have stale statistics.

Setting the STATISTICS_LEVEL to BASIC turns off the default table monitoring feature.

• Collection for Dictionary Objects

You can gather fixed object statistics by using the GATHER_DATABASE_STATS procedure and setting the GATHER_FIXED argument to TRUE (the default is FALSE).

You can also use the new procedure:

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL')

You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to analyze any dictionary objects or fixed objects.

To collect statistics for the real dictionary tables:

• Use the DBMS_STATS.GATHER_DATABASE_STATS procedure, by setting the GATHER_SYS argument to TRUE. Alternatively, you can use the GATHER_SCHEMA_STATS ('SYS') option.
• Use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure.

Providing SQL Statements to the SQL Tuning Advisor

• Create a new set of statements as an input for the SQL Tuning Advisor.
• The ADDM may often recommend high-load statements.
• Choose a SQL statement that抯 stored in the AWR.
• Choose a SQL statement from the database cursor cache.

How the SQL Tuning Advisor Works

The optimizer will work in the new tuning mode wherein it conducts an in-depth analysis to come up with a set of recommendations, the rationale for them and the expected benefit if you follow the recommendations.

When working in tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer (ATO).

The ATO performs the following tuning tasks:

• Statistics analysis
• SQL profiling
• Access path analysis
• SQL structure analysis

Statistics Analysis

ATO recommends collecting new statistics for specific objects, if required.

SQL Profiling

The ATO抯 goal at this stage is to verify that its own estimates of factors like column selectivity and cardinality of database objects are valid.

• Dynamic data sampling

Using a sample of the data, the ATO can check if its own estimates for the statement in question are significantly off the mark.

• Partial execution

The ATO may partially execute a SQL statement, so it can check if whether a plan derived purely from inspection of the estimated statistics is actually the best plan.

• Past execution history statistics

The ATO may also use any existing history of the SQL statement抯 execution to determine appropriate settings for parameters like OPTIMIZER_MODE.

The output of this phase is a SQL Profile of the concerned SQL statement. If you create that SQL profile, it will be used later by the optimizer when it executes the same SQL statement in the normal mode. A SQL profile is simply a set of auxiliary or supplementary information about a SQL statement.

Access Path Analysis

The ATO analyzes the potential impact of using improved access methods, such as additional or different indexes.

SQL Structure Analysis

The ATO may also make recommendations to modify the structure, both the syntax and semantics, in your SQL statements.

SQL Tuning Advisor Recommendations

The SQL Tuning Advisor can recommend that you do the following:

• Create indexes to speed up access paths
• Accept a SQL profile, so you can generate a better execution plan
• Gather optimizer statistics for objects with no or stale statistics
• Rewrite queries based on the advisor抯 advice

Using the SQL Tuning Advisor

Using the DBMS_SQLTUNE Package

The DBMS_SQLTUNE package is the main Oracle Database 10g interface to tune SQL statements.

Following are the required steps:

1. Create a task. You can use the CREATE_TUNING_TASK procedure to create a task to tune either a single statement or several statements.

execute :v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text=>'select count(*) from hr.employees,hr.dept')

2. Execute the task. You start the tuning process by running the EXECUTE_TUNING_TASK procedure.

SET LONG 1000

SET LONGCHUNKSIZE 1000

SET LINESIZE 100

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( :v_task) FROM DUAL;

3. Get the tuning report. By using the REPORT_TUNING_TASK procedure.
4. Use DROP_TUNING_TASK to drop a task, removing all results associated with the task.

Managing SQL Profiles

Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to create a SQL profile based on the recommendations of the ATO.

Managing SQL Tuning Categories

• Any created SQL Profile will be assigned to a category defined by the parameter SQLTUNE_CATEGORY.
• By default, SQLTUNE_CATEGORY has the value of DEFAULT.
• You can change the SQL tuning category for all users with the following command:

ALTER SYSTEM SET SQLTUNE_CATEGORY = PROD

• To change a session抯 tuning category, use the following command:

ALTER SESSION SET SQLTUNE_CATEGORY = DEV

You may also use the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure to change the SQL tuning category.

Using the Database Control to Run the SQL Tuning Advisor

Under the Performance tab, click the Advisor Central link and then click the SQL Tuning Advisor link.

There are several possible sources for the tuning advisor抯 SQL Tuning Set (STS) input:

• high-load SQL statements identified by the ADDM
• statements in the cursor cache
• statements from the AWR
• a custom workload
• another new STS.

The SQL Access Advisor primarily provides advice regarding the creation of indexes, materialized views, and materialized view logs, in order to improve query performance.

Providing Input for the SQL Access Advisor

There are four main sources of input for the advisor: SQL cache, user-defined workload, hypothetical workload, and STS from the AWR.

Modes of Operation

You can operate the SQL Access Advisor in two modes:

Limited (partial)

In this mode, the advisor will concern itself with only problematic or high cost SQL statements ignoring statements with a cost below a certain threshold.

Comprehensive (full)

In this mode, the advisor will perform. a complete and exhaustive analysis of all SQL statements in a representative set of SQL statements, after considering the impact on the entire workload.

You can also use workload filters to specify which kinds of SQL statements the SQL Access Advisor should select for analysis.

Managing the SQL Access Advisor

Using the DBMS_ADVISOR Package

1. Create and manage a task, by using a SQL workload object and a SQL Access task.
2. Specify task parameters, including workload and access parameters.
3. Using the workload object, gather the workload.
4. Using the SQL workload object and the SQL Access task, analyze the data.

You can also use the QUICK_TUNE procedure to quickly analyze a single SQL statement:

VARIABLE task_name VARCHAR2(255);

VARIABLE sql_stmt VARCHAR2(4000);

sql_stmt := 'SELECT COUNT(*) FROM customers WHERE cust_region=''TX''';

task_name := 'MY_QUICKTUNE_TASK';

DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name, sql_stmt);

Using the Database Control to Run the SQL Access Advisor

Under the Performance tab, click the Advisor Central link and then click the SQL Access Advisor link.

Note: Oracle creates the new indexes in the schema and tablespaces of the table on which they are created. If a user issues a query that leads to a recommendation to create a materialized view, Oracle creates the materialized view in that user抯 schema and tablespace.

The Database Home Page

Three major tuning areas the OEM Database Control will show you: CPU and wait classes, top SQL statements, and top sessions in the instance.

The Database Performance Page

This page shows the three main items:

Host

The Host part of the page shows two important graphs:

• Average Run Queue: This shows how hard the CPU is running.
• Paging Rate: This shows the rate at which the host server is writing memory pages to the swap area on disk.

Sessions waiting and working

The sessions graph shows which active sessions are on the CPU and which are waiting for resources like locks, disk I/O, and so on.

Instance throughput

If your instance throughput is decreasing, along with an increasing amount of contention within the database, you should start looking into tuning your database.

Skipping Unusable Indexes

In Oracle Database 10g, the SKIP_UNUSABLE_INDEXES parameter is a dynamic initialization parameter and its default value is TRUE. This setting disables error reporting of indexes and index partitions marked as UNUSABLE.

Note: This setting does not disable error reporting for unusable indexes that are unique because allowing insert and update operations on the table might violate the corresponding constraint.

Note: The database still records an alert message in the alert.log file whenever an index is marked as unusable.

Using Hash-Partitioned Global Indexes

• In Oracle 10g, you can create hash-partitioned global indexes. (Previous releases support only range-partitioned global indexes.)
• You can hash-partition indexes on tables, partitioned tables, and index-organized tables.
• This feature provides higher throughput for applications with large numbers of concurrent insertions.
• If you have queries with range predicates, for example, hash partitioned indexes perform. better than range-partitioned indexes.
• You can抰 perform. the following operations on hash-partitioned global indexes: ALTER INDEX REBUILD, ALTER TABLE SPLIT INDEX PARTITION, ALTER TABLE MERGE INDEX PARTITITON, and ALTER INDEX MODIFY PARTITION.

CREATE INDEX sales_hash

on sales_items (sales_id) GLOBAL

PARTITION BY HASH (sales_id) (

partition p1 tablespace tbs_1,

partition p2 tablespace tbs_2,

partition p3 tablespace tbs_3)

CREATE INDEX sales_hash

on sales_items (sales_id) GLOBAL

PARTITION BY HASH (sales_id)

partitions 4

store in (tbs_1,tbs_2,tbs_3,tbs_4)

• To add a new index partition

ALTER INDEX sales_hash ADD PARTITION p4

TABLESPACE tbs_4 [PARALLEL]

Notice the following for the previous command:

• The newly added partition is populated with index entries rehashed from an existing partition of the index as determined by the hash mapping function.
• If a partition name is not specified, a system-generated name of form. SYS_P### is assigned to the index partition.
• If a tablespace name is not specified, the partition is placed in a tablespace specified in the index-level STORE IN list, or user, or system default tablespace, in that order.
• To reverse adding a partition, or in other words to reduce by one the number of index partitions, you coalesce one of the index partitions then you destroy it. Coalescing a partition distributes index entries of an index partition into one of the index partitions determined by the hash function.

ALTER INDEX sales_hash COALESCE PARTITION PARALLEL

Using the New UPDATE INDEXES Clause

Using the new UPDATE INDEXES clause during a partitioned table DDL command will help you do two things:

• specify storage attributes for the corresponding local index segments. This was not available in previous versions.
• have Oracle automatically rebuild them.

ALTER TABLE MY_PARTS

MOVE PARTITION my_part1 TABLESPACE new_tbsp

UPDATE INDEXES

(my_parts_idx

(PARTITION my_part1 TABLESPACE my_tbsp))

Bitmap Index Storage Enhancements

Oracle Database 10g provides enhancements for handling DML operations involving bitmap indexes. These improvements eliminate the slowdown of bitmap index performance, which occurs under certain DML situations. Bitmap indexes now perform. better and are less likely to be fragmented when subjected to large volumes of single-row DML operations.