Automatic SQL Tuning Adviser:
=============================
Optimizer --- Will generate and pick execution plan
Suppose Stale Table or Wrong statistics of a table
1. Statistical Analysis
2. Accessing Path (Using Index or not)
High Level steps for SQL Tuning Adviser:
How to find the SQL ID:
=======================
select sql_id from v$sql where sql_text like 'select * from student';
Create Tuning Task:
===================
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'cdq26qbcmz1hc',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'my_tuning_task_3',
description => 'Tuning task1 for statement cdq26qbcmz1hc');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Execute Tuning Task:
====================
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');
Status of Tuning Task:
=====================
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';
Display the Recommendation:
==========================
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;
Drop the Tuning Task:
======================
execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');
Find out state Tables:
======================
set lines 160 pages 2000
col owner format a15
col table_name format a35
col last_analyzed format a35
col num_rows for 999999999999
SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed
FROM dba_tab_statistics
WHERE owner IN ('MALLIK')
AND stale_stats='YES'
ORDER BY owner;
Gather table state:
===================
execute dbms_stats.gather_table_stats(ownname =>'MALLIK',tabname =>'STUDENT',estimate_percent =>100);
Execution logs from SQL Tuning Adviser:
=======================================
1. Run SQL statement and capture the SQL ID:
[oracle@oraclelab3 ~]$ ps -ef|grep smon
oracle 4941 1 0 Nov12 ? 00:00:16 ora_smon_TESTDB
oracle 8631 5466 0 00:55 pts/1 00:00:00 grep --color=auto smon
[oracle@oraclelab3 ~]$ sqlplus mallik/mallik
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:55:21 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Nov 24 2021 00:16:14 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from student;
STNO STNAME
---------- ---------------
1 Mallik
2 John
10 AAA
10 AAA
10 AAA
10 AAA
10 AAA
10 AAA
10 AAA
9 rows selected.
2. Validate the table and make sure there table is not in stale state:
SQL> set lines 160 pages 2000
col owner format a15
col table_name format a35
col last_analyzed format a35
col num_rows for 999999999999
SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed
FROM dba_tab_statistics
WHERE owner IN ('MALLIK')
AND stale_stats='YES'
ORDER BY owner;SQL> SQL> SQL> SQL> SQL> 2 3 4 5
no rows selected
SQL> select sql_id from v$sql where sql_text like 'select * from student';
SQL_ID
-------------
cdq26qbcmz1hc
SQL>
3. Create Tuning Task for the SQL ID:
[oracle@oraclelab3 ~]$ ps -ef|grep smon
oracle 4941 1 0 Nov12 ? 00:00:16 ora_smon_TESTDB
oracle 8049 5048 0 00:48 pts/0 00:00:00 grep --color=auto smon
[oracle@oraclelab3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:58:04 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> DECLARE
2 l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'cdq26qbcmz1hc',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'my_tuning_task_1',
3 4 5 6 7 8 9 description => 'Tuning task1 for statement cdq26qbcmz1hc');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/ 10 11 12
PL/SQL procedure successfully completed.
SQL>
4. Run the Tuning Task and check the status:
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_1');
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_1';
TASK_NAME
--------------------------------------------------------------------------------
STATUS
-----------
my_tuning_task_1
COMPLETED
SQL>
5. Review the recommendation provided by Tuning Task:
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('my_tuning_task_1') from dual;
SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_tuning_task_1
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 500
Completion Status : COMPLETED
Started at : 11/24/2021 00:58:28
Completed at : 11/24/2021 00:58:28
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: MALLIK
SQL ID : cdq26qbcmz1hc
SQL Text : select * from student
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
SQL>
6. Delete Some rows from table and table will become in stale state:
SQL> select * from student;
STNO STNAME
---------- ---------------
1 Mallik
2 John
10 AAA
10 AAA
10 AAA
10 AAA
10 AAA
10 AAA
10 AAA
9 rows selected.
SQL>
SQL> delete from student where STNO=10;
7 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from student;
STNO STNAME
---------- ---------------
1 Mallik
2 John
SQL> set lines 160 pages 2000
col owner format a15
SQL> SQL> col table_name format a35
col last_analyzed format a35
col num_rows for 999999999999
SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed
FROM dba_tab_statistics
WHERE owner IN ('MALLIK')
AND stale_stats='YES'
ORDER BY owner;SQL> SQL> SQL> 2 3 4 5
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
--------------- ----------------------------------- ------------- -----------------------------------
MALLIK STUDENT 9 24-NOV-2021 00:28:55
SQL>
7. Now you can generate the new Tuning Task and see the recommendation from it:
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'cdq26qbcmz1hc',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'my_tuning_task_2',
description => 'Tuning task1 for statement cdq26qbcmz1hc');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/ 2 3 4 5 6 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_2');
PL/SQL procedure successfully completed.
SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_2';
TASK_NAME
----------------------------------------------------------------------------------------------------
STATUS
-----------
my_tuning_task_2
COMPLETED
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('my_tuning_task_2') from dual;SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_tuning_task_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 500
Completion Status : COMPLETED
Started at : 11/24/2021 01:02:18
Completed at : 11/24/2021 01:02:18
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: MALLIK
SQL ID : cdq26qbcmz1hc
SQL Text : select * from student
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')
----------------------------------------------------------------------------------------------------
Optimizer statistics for table "MALLIK"."STUDENT" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'MALLIK', tabname =>
'STUDENT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')
----------------------------------------------------------------------------------------------------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2356778634
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 63 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| STUDENT | 9 | 63 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
-------------------------------------------------------------------------------
SQL>
8. In this above Tuning task has given recommendation that table is in state stat and gather stats on the table
SQL> execute dbms_stats.gather_table_stats(ownname => 'MALLIK', tabname =>'STUDENT', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
9. Now the table is not in stale state then you can generate the new tuning task which will not give any recommendation since table is upto date.
SQL> DECLARE
2 l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
3 4 5 sql_id => 'cdq26qbcmz1hc',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'my_tuning_task_3',
description => 'Tuning task1 for statement cdq26qbcmz1hc');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
6 END;
/ 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');
PL/SQL procedure successfully completed.
SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';
TASK_NAME
----------------------------------------------------------------------------------------------------
STATUS
-----------
my_tuning_task_3
COMPLETED
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;
SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_tuning_task_3
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 500
Completion Status : COMPLETED
Started at : 11/24/2021 01:06:05
Completed at : 11/24/2021 01:06:05
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: MALLIK
SQL ID : cdq26qbcmz1hc
SQL Text : select * from student
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
SQL>
10. Drop those tuning tasks:
SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');
PL/SQL procedure successfully completed.
SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_2');
PL/SQL procedure successfully completed.
SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_3');
PL/SQL procedure successfully completed.
SQL>
Regards,
Mallik
