Author: Burleson

Page: 1 2

Oracle fully automated SQL tuning enables database administrators to use SQL Tuning Set (STS) to test changes to global parameters against a real-world workload. SQL Performance Advisor (SPA) and the automated SQL Plan Management (SPM) facility make life of database administrator easy by establishing an optimal baseline before tuning of individual SQL statements.

Oracle 11g fully Automated SQL Tuning:

In order to setup for fully automated SQL tuning first we must capture representative SQL workloads (SQL tuning sets) and set-up a testing environment. After that SQL Performance analyzer will be used to optimize the environment using STS.

SQL Tuning Sets:

SQL Tuning set, or STS can be defined as a set of problematic SQL statements (or representative workload). STS uses the dbms_sqltune.create_sqlset package.

Environment Setting:

In this step the initialization parameters are changed and performance is tested against a previous release of the CBO or custom experiments can be conducted to determine the effect of environmental changes on your SQL tuning set.

SQL Profiles:

SQL Profiles automatically implement the changes for any statements that execute more then 3x faster. It bypasses the generation of an execution plans for incoming SQL by replacing it with the pre-tuned access plan.

SQL Plan Baseline:

Now the SQL Plan Baseline will be created. The optimizer_capture_sql_plan_baselines initialization parameter can be set to TRUE in order to enable automatic SQL plan capture.

Workload Tests:

The workload is scheduled for execution during low usage periods so that an empirical sample of real-world execution times can be collected and compared using different execution plans from the two SQL tuning sets.  You can run the dbms_sqlpa package or use the OEM SPA Guided Workflow wizard. dbms_sqlpa package helps predict the impact of system environment changes on the performance of a SQL workload. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Best Setting for Oracle Multiblock Read Count!!
   The Power of 11g Snapshot Standby Database for Oracle 10g!!
   Effectively Prevent Oracle Minimum Downtime Migration Error!!
   Oracle Multitable Inserts, Important Concerns!!