Learn more about Automatic SQL Tuning 11.2.0.2

When studying PL/1 and Fortran 4 in the very start of 80s, Walking out to how the instructor was informing us that perhaps some day application will be able to develop application. Something like that, I am paraphrasing. In those days application growth was known as growth.

With Oracle11, this became somehow real. What happens behind the layer is that Oracle’s CBO is able to correct/change some bad efficiency programs without individual involvement. Which actually is one of the best new functions in 11g and for sure the best 11g new function for Information Middle DBAs.

But even in 11.2.0.2, the finish automated of Automated SQL Adjusting is not switched on by standard. You have to personally allow it, just like this:

BEGIN

DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(

parameter => ‘ACCEPT_SQL_PROFILES’, value => ‘TRUE’);

END;

/

After developing an 11g databases, that is all you have to do today to improve the process! For all the facts, examine the 11gR2 certification on Automated SQL Adjusting.

The new I did this in a objective crucial manufacturing databases, was in Nov 2007, as soon as Oracle 11g was available for Solaris. However, I did invest Hundreds of time before that working/testing on 11.1.0.3 as an leader specialist.

In my personal viewpoint, beginning 12g, recognizing SQL information should be allowed by standard. However, to use automatic SQL tuning and in particular SQL information the Adjusting and the Diagnostics features need to be licensed! Automated SQL tuning via information is the best way to extremely track SQL statements: CBO understands from its mistakes!

Back to 11.2.0.2: there are 3 new and modified efficiency tuning features:

• Source Administrator improvements for similar declaration lining up and CPU usage limit

• The Oracle Orion I/O Calibration Tool

• The new DBMS_AUTO_SQLTUNE program for Automated SQL Adjusting which is the new interface for personally handling the automated SQL tuning task

Here are example of how you should use them:

1. Perform the Automated SQL Adjusting procedure immediately: EXEC DBMS_AUTO_SQLTUNE.EXECUTE_AUTO_TUNING_TASK; Note: must be run as SYS!

2. Show a written text review of the automated tuning task’s history: SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

3. Modify a procedure parameter value for the everyday automatic runs: see initially how to allow automatic approval of SQL information. You can see all factors as follows:

SELECT PARAMETER_NAME, PARAMETER_VALUE, IS_DEFAULT, DESCRIPTION

FROM DBA_ADVISOR_PARAMETERS

WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’

When SQL claims are implemented by the Oracle data source, the question optimizer is used to generate the performance plans of the SQL claims. The question optimizer are operating in two modes: a regular method and a adjusting method.

In regular method, the optimizer gathers the SQL and produces an performance strategy. The regular method of the optimizer produces a reasonable performance prepare for a large proportion of SQL claims. Under regular method, the optimizer functions with very tight deadlines, usually a small portion of a second, during which it must look for a performance strategy.

In adjusting method, the optimizer works additional research to check whether the performance strategy produced under regular method can be enhanced further. The outcome of the question optimizer is not an performance strategy, but a sequence of activities, along with their reasoning and expected benefit for making a significantly excellent strategy. When running in the adjusting method, the optimizer is known to as the Automated Tuning Optimizer.

Under adjusting method, the optimizer can take several minutes to track a single declaration. It is both efforts and resource intense to produce the Automated Tuning Optimizer whenever a question has to be hard-parsed. The Automated Tuning Optimizer was created to be used for complicated and high-load SQL claims that have non-trivial impact on the entire system. The Automated Database Analytic Observe (ADDM) pro actively recognizes high-load SQL claims which are perfect applicants for SQL tuning.

Oracle Certification is more than enough for you to make your career in this field as an Oracle Certified Professional.