SQL PROFILE can be applied to below statements.
SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)
In this example, first we will run SQL tuning advisor against an sql_id , and then will do different operations on sql profile.
1. Run sql tuning advisor for sql_id=5dkrnbx1z8gcb
set long 1000000000
Col recommendations for a200
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '5dkrnbx1z8gcb',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '5dkrnbx1z8gcb_tuning_task_1',
description => 'Tuning task for statement 5dkrnbx1z8gcb');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5dkrnbx1z8gcb_tuning_task_1');
SET LONG 10000000;
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('5dkrnbx1z8gcb_tuning_task_1') AS recommendations FROM dual;
SET PAGESIZE 24
Find the name of the sql_profile:
select name,status,force_matching from dba_sql_profiles where task_id in ( select task_id from DBA_ADVISOR_TASKS where task_name ='5dkrnbx1z8gcb_tuning_task_1')
set pagesize 299
set lines 299
col name for a45
NAME STATUS FOR
---------------------------------- -------- ---
SYS_SQLPROF_01601b64332e0000 ENABLED NO
Alter a profile,
disable a sql_profile using steps
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_01601b64332e0000',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
To enable to changed the value from DISABLED to ENABLED.
__________________
Dropping an SQL profile:
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01601b64332e0000');
end;
/
