Oracle adjustment Advisor (SQL Tuning Advisor and SQL Access Advisor)

When Oracle database performance problems occur, it is effort-saving to use the Oracle toolkit and give reasonable optimization suggestions.

The following two packages are described and their specific practices.

The smallest adjustment tool for SQL Tuning Advisor.

Use one or more SQL statements as the input content, analyze all access paths, generate suggestions for improving SQL statements, and propose indexes, materialized views and partition schemes to improve the overall performance of applications.
In addition, in the Maintenance window, Oracle automatically runs SQL Tuning Advisor for High-load statements identified and recorded by the Automatic Workload Repository (AWR ).
SQL Access Advisor

It involves all SQL statements in the workload, and proposes indexes, special views, and partitioning schemes to improve the overall performance of applications.

Small differences:

SQL Tuning Advisor adjusts each SQL statement separately
SQL Access Advisor simultaneously adjusts all SQL statements

SQL Tuning Advisor:

1. In anonymous PL/SQL blocks, define adjustment tasks for SQL statements.

Declare
Tune_task_name varchar2 (30 );
Bad_ SQL _stmt clob;
Begin
Bad_ SQL _stmt: = 'select distinct id from demo. txcl ';
Tune_task_name: = dbms_sqltune.create_tuning_task
(SQL _text => bad_ SQL _stmt,
User_name => 'Demo ',
Scope => 'computing ',
Time_limit => 60,
Task_name => 'xcl _ SQL _tuing_task ',
Description => 'see what is wrong with the select'
);
End;
/

2. Set the time limit for a task to 60 minutes.

Begin
Dbms_sqltune.set_tuning_task_parameter
(Task_name => 'xcl _ SQL _tuing_task ',
Parameter => 'time _ LIMIT ',
Value => 30
);
End;
/

3. Start the adjustment task using the EXECUTE_TUNING_TASK process.

Begin
Dbms_sqltune.execute_tuning_task
(Task_name => 'xcl _ SQL _tuing_task ');
End;
/

4. Check the task status by connecting DBA_ADVISOR_TASKS and V $ ADVISOR_PROGRESS.
Select task_name, status, sofar, totalwork

From dba_advisor_tasks
Join v $ advisor_progress using (task_id)
Where task_name = 'xcl _ SQL _tuing_task ';

5. view the suggestions for the adjustment task.

Select dbms_sqltune.report_tuning_task ('xcl _ SQL _tuing_task ') from dual;

For more details, please continue to read the highlights on the next page:

 

Recommended reading:

How to install Oracle 11g on Linux

Detailed description of the installation process of Oracle 11g Database in Linux

How to install Oracle 11g R2 single-instance database on CentOS 5.6

To install Oracle Clusterware on an Oracle vmvm

Install Oracle 11 GB single-instance database on Linux under vmvm

1 2 Next Page