Basic operations related to SQL Profile

TO ACCPET A SQL PROFILE

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_96’,replace => TRUE);

If you want the profile to be registered with a custom name use,

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_96’,name => ‘CUSTOM_PROF_Query1’);

TO DISABLE SQL PROFILE

SQL> connect /as sysdba
Connected.

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('sql_profile_name','STATUS','DISABLED');

PL/SQL procedure successfully completed.

TO DELETE SQL PROFILE

begin
 dbms_sqltune.drop_sql_profile (
 name => 'SYS_SQLPROF_014c3eab45fe0000',
 ignore => true);
 end;
 /

HAS SQL USED THE PROFILE?

Look at OUTLINE_CATEGORY and SQL_PROFILE columns. NULL indicates that no Outline/Profile was used.

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Basic operations related to SQL Profile

Related posts: