sql pofile

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)

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;

/

source : https://dbaclass.com/article/sql-profile-oracle/