SQL TUNING ADVISOR :-
o The SQL Tuning
Advisor takes one or more SQL statements as an input and invokes the
Automatic Tuning Optimizer to perform SQL tuning on the statements.
o The output of the
SQL Tuning Advisor is in the form of an recommendations, along with a rationale
for each recommendation and its expected benefit.The recommendation relates to
collection of statistics on objects, creation of new indexes, restructuring of
the SQL statement, or creation of a SQL profile. You can choose to accept the
recommendation to complete the tuning of the SQL statements.
o You can also run
the SQL Tuning Advisor selectively on a single or a set of SQL statements that
have been identified as problematic.
o Find the problematic SQL_ID from
v$session you would like to analyze. Usually the AWR has the top SQL_IDs
column.
Connect sys dba and
grant advisor privilege to user
SQL>
conn / as sysdba
Connected.
SQL>
grant advisor to vbt;
Grant
succeeded.
SQL>
conn vbt/vbt
Connected.
Create tuning task under
the user
SQL>
DECLARE
my_task_name
VARCHAR2(30);
BEGIN
my_task_name
:= DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id
=> '88kyzk5sty9ff',
scope
=> 'COMPREHENSIVE',
time_limit
=> 3600,
task_name
=> 'my_sql_tuning_task_1',
description
=> 'Tune query using sqlid');
end;
/ 2
3 4 5
6 7 8
9 10 11
PL/SQL
procedure successfully completed.
Execute the task
SQL>
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name => 'my_sql_tuning_task_1');
end;
/ 2
3 4
PL/SQL
procedure successfully completed.
Monitor the task
executing
SQL>
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME
='my_sql_tuning_task_1';
TASK_NAME
----------------------------------------------------------------------------------------------------
STATUS
-----------
my_sql_tuning_task_1
COMPLETED
1
row selected.
Report Tuning task
SQL>
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1')
----------------------------------------------------------------------------------------------------
GENERAL
INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning
Task Name : my_sql_tuning_task_1
Tuning
Task Owner : VBT
Workload
Type : Single SQL Statement
Scope : COMPREHENSIVE
Time
Limit(seconds): 3600
Completion
Status : COMPLETED
Started
at : 09/28/2021 03:41:45
Completed
at : 09/28/2021 03:41:45
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema
Name: SYS
SQL
ID : 88kyzk5sty9ff
SQL
Text :
select address, hash_value from gv$sqlarea where
hash_value='1375207257'
There
are no recommendations to improve the statement.
1
row selected.
Detailed report for
tuning task
SQL>
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1','TEXT','ALL','ALL')
----------------------------------------------------------------------------------------------------
GENERAL
INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning
Task Name : my_sql_tuning_task_1
Tuning
Task Owner : VBT
Tuning
Task ID : 203
Workload
Type : Single SQL Statement
Execution
Count : 1
Current
Execution : EXEC_265
Execution
Type : TUNE SQL
Scope : COMPREHENSIVE
Time
Limit(seconds): 3600
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1','TEXT','ALL','ALL')
Completion
Status : COMPLETED
Started
at : 09/28/2021 03:41:45
Completed
at : 09/28/2021 03:41:45
-------------------------------------------------------------------------------
Schema
Name: SYS
SQL
ID : 88kyzk5sty9ff
SQL
Text :
select address, hash_value from gv$sqlarea where
hash_value='1375207257'
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1','TEXT','ALL','ALL')
----------------------------------------------------------------------------------------------------
There
are no recommendations to improve the statement.
-------------------------------------------------------------------------------
EXPLAIN
PLANS SECTION
-------------------------------------------------------------------------------
1-
Original
Plan
hash value: 1696808874
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1','TEXT','ALL','ALL')
----------------------------------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
32 | 0 (0)| 00:
00:01
|
|* 1 |
FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD_SQLID (ind:1) | 1 |
32 | 0 (0)| 00:
00:01
|
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1','TEXT','ALL','ALL')
----------------------------------------------------------------------------------------------------
Query
Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1 /
X$KGLCURSOR_CHILD_SQLID@SEL$2
Predicate
Information (identified by operation id):
1 -
filter("KGLNAHSH"=1375207257 AND "KGLOBT02"<>0)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1','TEXT','ALL','ALL')
Column
Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "KGLHDPAR"[RAW,8],
"KGLNAHSH"[NUMBER,22], "KGLOBT02"[NUMBER,22]
No comments:
Post a Comment