HOL 19c SQL Tuning Advisor

« PREVIOUS «

In the previous section you did fixed plans with SQL Plan Management. But lets see what else could be done and ask the SQL Tuning Advisor (STA).

You’ll pass the SQL Tuning Set from the “Load” exercise where you captured the HammerDB workload directly from Cursor Cache to the SQL Tuning Advisor and check the results.

Analyze the SQL Tuning Set and generate recommendations

A complete script is provided: sta_cc.sql.
It will:

Generate a tuning task with the SQL Tuning Set STS_CaptureCursorCache
Run a tuning task where the SQL Tuning Advisor simulates the execution
Generate a result report in TEXT format
Generate statements to implement the findings

. upgr19
cd /home/oracle/scripts
sqlplus / as sysdba

@/home/oracle/scripts/sta_cc.sql

It will take 30 seconds – check the output by scrolling up.

At first, the findings, here I display just the first two findings for a COUNT statement on the CUSTOMER table:

-------------------------------------------------------------------------------
Object ID  : 3
Schema Name: TPCC
SQL ID	   : 7m5h0wf6stq0q
SQL Text   : SELECT COUNT(C_ID) FROM CUSTOMER WHERE C_LAST = :B3 AND C_D_ID =
	     :B2 AND C_W_ID = :B1

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 97.95%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TPCC.IDX$$_00D60001 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID
    ");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

2- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.

  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.

  id plan hash	last seen	     elapsed (s)  origin	  note
  -- ---------- -------------------- ------------ --------------- ----------------
   1 3642382161  2020-02-20/22:12:33	    0.002 AWR		  original plan
   2 1075826057  2020-02-21/10:00:03	    0.004 AWR

  Information
  -----------
  - The Original Plan appears to have the best performance, based on the
    elapsed time per execution.  However, if you know that one alternative
    plan is better than the Original Plan, you can create a SQL plan baseline
    for it. This will instruct the Oracle optimizer to pick it over any other
    choices in the future.
    execute dbms_sqltune.create_sql_plan_baseline(task_name =>
	    'STA_UPGRADE_TO_19C_CC', object_id => 3, owner_name => 'SYS',
	    plan_hash_value => xxxxxxxx);
-----------------------------------------------------------------------------------

You see that the SQL Tuning Advisor interacts with SQL Plan Management from the previous exercise as well.

When you scroll to the end, you will find the implementation section:

-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations    --
-- made by the SQL tuning advisor.			       --
--							       --
-- NOTE: this script may need to be edited for your system     --
--	 (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
execute dbms_stats.gather_table_stats(ownname => 'TPCC', tabname => 'HISTORY', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'TPCC', tabname => 'ORDERS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'TPCC', tabname => 'ORDER_LINE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
create index TPCC.IDX$$_00D60001 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID");
create index TPCC.IDX$$_00D60002 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID");
create index TPCC.IDX$$_00D60003 on TPCC.ORDERS("O_C_ID","O_D_ID","O_W_ID");
create index TPCC.IDX$$_00D60004 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID");
create index TPCC.IDX$$_00D60004 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID");
create index TPCC.IDX$$_00D60004 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID");
create index TPCC.IDX$$_00D60004 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID");

First of all, remove the duplicate recommendations (you won’t need 3 identical indexes with different names on TPCC.CUSTOMER for sure) marked in BLUE.

Fix everything

This is an exercise. Please don’t do this in a real environment without proper verification. But let us implement all the recommendations and see what happens.

Execute all the recommendations from the Advisor:

execute dbms_stats.gather_table_stats(ownname => 'TPCC', tabname => 'HISTORY', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'TPCC', tabname => 'ORDERS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'TPCC', tabname => 'ORDER_LINE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
create index TPCC.IDX$$_00D60001 on TPCC.CUSTOMER("C_LAST","C_D_ID","C_W_ID");
create index TPCC.IDX$$_00D60003 on TPCC.ORDERS("O_C_ID","O_D_ID","O_W_ID");

Wait a bit until all statements have been executed.

Afterwards repeat the SQL Performance Analyzer runs from the previous exersize and verify the results:

@/home/oracle/scripts/spa_cpu.sql
@/home/oracle/scripts/spa_report_cpu.sql
@/home/oracle/scripts/spa_elapsed.sql
@/home/oracle/scripts/spa_report_elapsed.sql
exit

Compare the two resulting reports again – and compare them to the examples from the previous run.

cd /home/oracle/scripts
firefox compare_spa_* &

It should look similar to the ones below: