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