Manage SQL Profile in Oracle Database

Share via:

Post Views: 0

Hi Dears,

In this article, we will see Manage SQL Profile in Oracle Database

A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query.
The profile is stored in the data dictionary. The optimizer uses this information during optimization to determine the most optimal plan.
A SQL profile contains, among other statistics, a set of cardinality adjustments.
The cardinality measure is based on sampling the WHERE clause rather than on statistical projection.
A profile uses parts of the query to determine whether the estimated cardinalities are close to the actual cardinalities and,
if a mismatch exists, uses the corrected cardinalities. For example, if a SQL profile exists for SELECT * FROM t WHERE x=5 AND y=10,
then the profile stores the actual number of rows returned.
When choosing plans, the optimizer has the following sources of information:
• The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
• The supplemental statistics in the SQL profile

Check the SQL Profile in the Database

SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;

1	SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;

Check the SQL Profile internal hint information

SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;

SELECT

a.name

,b.comp_data

FROM dba_sql_profiles a

,dbmshsxp_sql_profile_attr b

WHERE a.name = b.profile_name;

Create the SQL Profiles
We can create SQL Profiles in Two ways:
1. Creating the SQL Profiles while running the manual SQL tuning task
For manually creating with SQL Tuning task

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '2qpu2862c428h',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 300,
                          task_name   => 'tuning_task_test_2qpu2862c428h',
                          description => 'Tuning task1 for statement 2qpu2862c428h');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/



EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘tuning_task_test_2qpu2862c428h’);



set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task(‘tuning_task_test_2qpu2862c428h’) from dual;

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '2qpu2862c428h',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 300,

task_name => 'tuning_task_test_2qpu2862c428h',

description => 'Tuning task1 for statement 2qpu2862c428h');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘tuning_task_test_2qpu2862c428h’);

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task(‘tuning_task_test_2qpu2862c428h’) from dual;

EXECUTE dbms_sqltune.accept_sql_profile(task_name => ‘tuning_task_test_2qpu2862c428h’, description => 'SQL profile for sql_id 2qpu2862c428h’);

1	EXECUTE dbms_sqltune.accept_sql_profile(task_name => ‘tuning_task_test_2qpu2862c428h’, description => 'SQL profile for sql_id 2qpu2862c428h’);

2. Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher)
Check SQL profiles that have automatically been created having the value “AUTO” in the TYPE column of the DBA_SQL_PROFILES views

--Check
select name, type, status, sql_text from dba_sql_profiles;

1 2	--Check select name, type, status, sql_text from dba_sql_profiles;

-- Enable
exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
-- Disable
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');

-- Enable

exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(

parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

-- Disable

DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(

parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');

Disable the SQL Profiles

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'tuning_task_test_2qpu2862c428h',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => 'tuning_task_test_2qpu2862c428h',

attribute_name => 'STATUS',

value => 'DISABLED');

END;

Drop the SQL Profile

exec dbms_sqltune.drop_sql_profile('tuning_task_test_2qpu2862c428h');

1	exec dbms_sqltune.drop_sql_profile('tuning_task_test_2qpu2862c428h');

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts

Share via:

Note: Please test scripts in Non Prod before trying in Production.

Venkata Sai Pavan

Share this post

Manage SQL Profile in Oracle Database

Add Comment Cancel reply

Venkata Sai Pavan

Share this post

You might also like...

Table/index movement using pl/sql procedure

Datapump export and import from Source PDB to Target PDB

Pluggable database save state

Add Comment Cancel reply