Example: SQL Tuning Task Options (Doc ID 2461848.1) sql tuning

 

APPLIES TO:

Oracle Life Sciences Data Hub - Version 2.4.6 and later
Information in this document applies to any platform.

GOAL

This is an example in how to apply a SQL profile.

SOLUTION

In this example, let's assume the SQL IDs are cxp6f9nd21wy2 and fprmqtmh5uyjm. Then:

Method 1:

Step 1:

DECLARE
  l_sql_tune_task_id VARCHAR2(1000);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  sql_id => 'cxp6f9nd21wy2',
  scope => DBMS_SQLTUNE.scope_comprehensive,
  time_limit => 120,
  task_name => 'cxp6f9nd21wy2',
  description => 'cxp6f9nd21wy2');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Step 2:

begin
DBMS_SQLTUNE.execute_tuning_task(task_name => 'cxp6f9nd21wy2');
end;
/

Step 3:

set lines 1000 pages 1000 long 1000000
column recommendations format a500
SELECT DBMS_SQLTUNE.report_tuning_task('cxp6f9nd21wy2') AS recommendations FROM dual;

Step 4: Final Step:

Accept profile or create base line.

execute dbms_sqltune.accept_sql_profile(task_name => 'cxp6f9nd21wy2', task_owner => 'SYS', replace => TRUE);
execute dbms_sqltune.CREATE_SQL_PLAN_BASELINE (task_name => 'cxp6f9nd21wy2', OBJECT_ID=> '', PLAN_HASH_VALUE=> '', OWNER_NAME=> '')

select sql_text, 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' delete_sql_from_shared_pool from v$sqlarea where sql_id = 'cxp6f9nd21wy2';

Method 2:

 Another option is to use the COE_XFR_SQL_PROFILE script - see NOTE:215187.1 "All About the SQLT Diagnostic Tool" for script and details.
 This is a supported method of using SQL profiles with stored outlines style hints.

Step 1:

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: fprmqtmh5uyjm

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
  1552755644 .003
  1130512974 84.667

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1552755644

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "fprmqtmh5uyjm"
PLAN_HASH_VALUE: "1552755644"

SQL>BEGIN
  2 IF :sql_text IS NULL THEN
  3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4 END IF;
  5 END;
  6 /
SQL>SET TERM OFF;
SQL>BEGIN
  2 IF :other_xml IS NULL THEN
  3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4 END IF;
  5 END;
  6 /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_fprmqtmh5uyjm_1552755644.sql on TARGET system in order to create a custom SQL Profile with plan 1552755644 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.


Step 2:

SQL> Execute coe_xfr_sql_profile_fprmqtmh5uyjm_1552755644.sql