- A SQL profile is a database object that contains auxiliary statistics specific to a SQL statement.
- Conceptually, a SQL profile is to a SQL statement what object-level statistics are to a table or index. SQL profiles are created when a DBA invokes SQL Tuning Advisor.
- When profiling a SQL statement, SQL Tuning Advisor uses a specific set of bind values as input.
- The advisor compares the optimizer estimate with values obtained by executing fragments of the statement on a data sample. When significant variances are found, SQL Tuning Advisor bundles corrective actions together in a SQL profile, and then recommends its acceptance.
- The corrected statistics in a SQL profile can improve optimizer cardinality estimates, which in turn leads the optimizer to select better plans. SQL profiles provide the following benefits over other techniques for improving plans:
- Unlike hints and stored outlines, SQL profiles do not tie the optimizer to a specific plan or subplan. SQL profiles fix incorrect estimates while giving the optimizer the flexibility to pick the best plan in different situations.
- Unlike hints, no changes to application source code are necessary when using SQL profiles. The use of SQL profiles by the database is transparent to the user.
Steps to implement SQL profile
+ First need to identify the SQL and SQL id of the query?
1. If you have only SID an need to find out sql_id?
SQL> select sid,sql_id from v$session where sid='786';
SID SQL_ID
----- -------------
786 b6v53rrz9h1at
2. If we have already have SQL ID, cross check with as below
select sql_id,sql_text from v$sqltext_with_newlines where sql_id='b6v53rrz9h1at' order by piece;
SQL_TEXT
----------------------------------------------------------------
SELECT COUNT(1) FROM WF_NOTIFICATIONS WN, (SELECT WUR.ROLE_NAME
FROM WF_USER_ROLES WUR WHERE WUR.USER_NAME = :B3 AND WUR.USER_OR
IG_SYSTEM = :B2 AND WUR.USER_ORIG_SYSTEM_ID = :B1 ) WUR WHERE (
(WN.MORE_INFO_ROLE IS NULL AND WN.RECIPIENT_ROLE = WUR.ROLE_NAME
) OR (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) ) AND WN.STATUS = 'OPEN'
3. If you don't have SID,SQL id then find SQL ID using sql txt
select sql_id,SQL_TEXT from v$sqltext_with_newlines where SQL_TEXT like '%SELECT COUNT(1) FROM WF_NOTIFICATIONS WN%'
sql_id, SQL_TEXT
------- --------------------------------------------------------
b6v53rrz9h1at SELECT COUNT(1) FROM WF_NOTIFICATIONS WN, (SELECT WUR.ROLE_NAME
FROM WF_USER_ROLES WUR WHERE WUR.USER_NAME = :B3 AND WUR.USER_OR
IG_SYSTEM = :B2 AND WUR.USER_ORIG_SYSTEM_ID = :B1 ) WUR WHERE (
(WN.MORE_INFO_ROLE IS NULL AND WN.RECIPIENT_ROLE = WUR.ROLE_NAME
) OR (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) ) AND WN.STATUS = 'OPEN'
Note: you might get your own session in above statement, ignore it, you can cross check from v$mystat
SQL> select distinct sid from v$mystat;
SID
----------
38
SQL> select sid,sql_id from v$session where sid='38';
SID SQL_ID
---------- -------------
38 5fgv6fxdj350k
+ Use SQL Tuning Advisor to add SQL profile to your SQL ID
1. You need to create Tuning Task before you geneate SQL profile for your SQL ID.
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'b6v53rrz9h1at',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'b6v53rrz9h1at_tuning_task',
description => 'Tuning task for statement b6v53rrz9h1at.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Execution:
----------
SQL> set serveroutput on
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'b6v53rrz9h1at',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'b6v53rrz9h1at_tuning_task',
description => 'Tuning task for statement b6v53rrz9h1at.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
l_sql_tune_task_id: b6v53rrz9h1at_tuning_task
PL/SQL procedure successfully completed.
2. Execute the Tuning Task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&taskname');
Excution:
--------
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'b6v53rrz9h1at_tuning_task');
PL/SQL procedure successfully completed.
2. Monitor the Tuning Task status.
SELECT task_name, status FROM dba_advisor_log WHERE task_name ='&task_name';
Execution:
----------
SQL> select owner,task_name,status FROM dba_advisor_log where task_name='b6v53rrz9h1at_tuning_task';
OWNER TASK_NAME STATUS
------------------------------ ------------------------------ -----------
SYS b6v53rrz9h1at_tuning_task EXECUTING
SQL> select owner,task_name,status FROM dba_advisor_log where task_name='b6v53rrz9h1at_tuning_task';
OWNER TASK_NAME STATUS
------------------------------ ------------------------------ -----------
SYS b6v53rrz9h1at_tuning_task COMPLETED
3. Displaying the Results of a SQL Tuning Task
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('b6v53rrz9h1at_tuning_task5') AS recommendations FROM dual;
SET PAGESIZE 24
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : b6v53rrz9h1at_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/11/2018 03:02:43
Completed at : 06/11/2018 03:03:03
-------------------------------------------------------------------------------
Schema Name: XXAR
SQL ID : b6v53rrz9h1at
SQL Text : SELECT COUNT(1) FROM WF_NOTIFICATIONS WN, (SELECT WUR.ROLE_NAME
FROM WF_USER_ROLES WUR WHERE WUR.USER_NAME = :B3 AND WUR.USER_OR
IG_SYSTEM = :B2 AND WUR.USER_ORIG_SYSTEM_ID = :B1 ) WUR WHERE (
(WN.MORE_INFO_ROLE IS NULL AND WN.RECIPIENT_ROLE = WUR.ROLE_NAME
) OR (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) ) AND WN.STATUS = 'OPEN'
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
SQL Profile Finding (see explain plans section below)
---------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 96.28%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_46345',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 8 will improve its response time
96.29% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 70.31% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
4. Implementing SQL Profile to the SQLID
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_46345',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
5. List the SQL profile for the SQL id
select sql_id,sql_profile,sql_plan_baseline from v$sql where sql_id='b6v53rrz9h1at'
6. List the SQL profiles and its status
COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20
SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;
7. Disable SQL Profile
BEGIN
DBMS_SQLTUNE.alter_sql_profile (
name => 'emp_dept_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
8. Drop the SQL profile
BEGIN
DBMS_SQLTUNE.drop_sql_profile (
name => 'emp_dept_profile',
ignore => TRUE);
END;
/
Other Monitoring Queries
1. Monitor the progress of the Tuning Task in percentage
COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$ADVISOR_PROGRESS;
Implementing SQL profile with Oracle Script: (Aleternative Method)
1. Get SQL id
Use SQLs are given above to find out SQL ID
2. Run SQL Tuning Advisor for the SQL_ID
SQL> @?/rdbms/admin/sqltrpt.sql
In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.
Command to Create and Implement SQL Profile in Oracle for the SQL_ID:
--------------------------------------------------------------------
SQL> execute dbms_sqltune.accept_sql_profile(task_name => '<TASK_NAME>',task_owner => 'SYS', replace => TRUE, FORCE_MATCH => TRUE);
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;
Command to Drop SQL Profile in Oracle for the SQL_ID:
----------------------------------------------------
SQL> execute dbms_sqltune.drop_sql_profile('<SQL_PROFILE_NAME>');
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
Command to Alter SQL Profile in Oracle for the SQL_ID:
-----------------------------------------------------
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('<SQL_PROFILE_NAME>','STATUS','DISABLED');
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
If you don't know the name of the SQL Profile then use the below query
SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT%TABLE%NAME%';
Query
-----
The SQL_ID is not stored with the profiles.
You can see if a statement is using a profile by querying v$sql where sql_profile is not null.
select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and sql_profile like nvl('&sql_profile_name',sql_profile)
and sql_profile is not null
order by 1, 2, 3
/
