Database RAC Dataguard RMAN Performance Tuning Feedback
Database Migration & Upgradation
DataGuard
ASM & RAC
Exadata
RMAN Backup & Recovery
Oracle Cloud AWS Azure Google Cloud
DevOps Ansible Kubernetes Terraform
Shell Scripting Powershell Scripting
Infomratica Others

Wednesday, November 24, 2021

Automatic SQL Tuning Adviser

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
Posted by at

4 comments:

  1. AnonymousMay 26, 2023 at 11:21 PM

    Very clear and concise. Thank you for posting!

    ReplyDelete
  2. AnonymousMarch 26, 2024 at 5:02 PM

    Hi Malik, For few sql iam getting like this -"Type of SQL statement not supported."

    ReplyDelete
    Replies
    1. AnonymousMarch 26, 2024 at 8:06 PM

      Is it applicable only to select statements?

      Delete
    2. AnonymousFebruary 12, 2026 at 10:13 AM

      what about update statement?

      Delete

Subscribe to: Post Comments (Atom)

Installing Oracle AI Database Free 26ai RPM on Linux x86-64

Installing Oracle AI Database Free 26ai RPM on Linux x86-64 Oracle AI Database Free 26ai can be installed on supported Linux platforms...