Create Oracle SQL Profile For Tuning
Looking for how to tune a SQL statement by creating a SQL Profile? The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans.
Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement.
During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.
The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query.
If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.
It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile.
The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.
Here’s the set of SQL statement you can use to trace the execution time of ACTIVE running SQL query that you wish to tune.
— Get The SQL_ID From Active Session SQL
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = ‘ACTIVE’
AND a.spid = ‘11696’
ORDER BY a.spid, c.piece
— Trace SQL Query Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, 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 s.sql_id=’4n01r8z5hgfru’
— Append the /* + gather_table_statistics */ hint into SQL Statement and execute
SELECT /* + gather_table_statistics */ sysdate … (SQL Statement)
Note: The execution OF SQL statement WITH hint /* + gather_table_statistics */ will subsequently provide the detail plan execution TIME IN the NEXT query below.
— Get The Detail Explain Plan Using SQL ID
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‘dtdqt19kfv6yx’))
— Get The History Of Explain Plan Execution Using SQL ID In AWR report
SELECT plan_table_output FROM TABLE(dbms_xplan.display_awr(‘dtdqt19kfv6yx’))
— Use Longops To Check The Estimation Runtime
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,
last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,
elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,
round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> ‘0’
Creating a SQL Profile Using DBMS_SQLTUNE
SQL Profiles (commonly known as the SQL Tuning Advisor) were introduced in Oracle 10g. The feature tunes queries by gathering information about data distribution, relations between the columns and joined tables and more useful optimizer information.
It provides recommendations which, when implemented are associated to the query and are used by the optimizer at parse time.
The following will create a SQL Profile:
SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => ‘FULL QUERY TEXT’,
profile => sqlprof_attr(‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’),
name => ‘PROFILE NAME’, force_match => TRUE/FALSE);
‘FULL QUERY TEXT’ – The value can be obtained from the the SQL_FULLTEXT column in table GV$SQLAREA or DBA_HIST_SQLTEXT.
SQL> SELECT SQL_FULLTEXT FROM GV$SQLAREA WHERE sql_id = ‘4n01r8z5hgfru’
SQL> SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE sql_id = ‘4n01r8z5hgfru’
‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’– The hint specification can be obtained from the table DBA_HIST_SQL_PLAN. For example:
SQL> SELECT extractvalue(VALUE(d), ‘/hint’) AS outline_hints
FROM xmltable(‘/*/outline_data/hint’
passing (SELECT xmltype(other_xml) AS xmlval
FROM dba_hist_sql_plan WHERE sql_id = ‘4n01r8z5hgfru’ AND plan_hash_value = ‘82930460’
AND other_xml IS NOT NULL)) d;
Or you can get it from DBMS_XPLAN.DISPLAY_AWR function as below.
SQL> SELECT plan_table_output FROM TABLE
(dbms_xplan.display_awr(‘SQL_ID’,’PLAN_HASH’,NULL,’OUTLINE’))
You can also generate the Trace File 10053 and look for the hint specification between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA. Download SQLTXPLAIN.sql from Oracle Metalink and run it to get Trace File 10053.
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.3’)
OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)
OPT_PARAM(‘_fast_full_scan_enabled’ ‘false’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$335DD26A”)
MERGE(@”SEL$3″)
OUTLINE_LEAF(@”SEL$7286615E”)
MERGE(@”SEL$5″)
OUTLINE_LEAF(@”SEL$1″)
……
END_OUTLINE_DATA
*/
FORCE_MATCH is really the main reason for using SQL Profiles, when set to TRUE it will ignore literals with exact queries and implement the profile on them (just like what cursor_sharing=force does to the entire DB). For Example: –
When force match is set to TRUE, a.segment1 = 1234 will become a.segment1 = :b1
To create a SQL Profile a user must have the following: ADVISOR role, create any sql profile privilege, alter any sql profile privilege, drop any sql profile privilege and execute priviliege on DBMS_SQLTUNE.
SQL> GRANT EXECUTE ON SYS.DBMS_SQLTUNE TO <user>;
SQL> GRANT ADVISOR TO <user>;
SQL> GRANT CREATE ANY SQL PROFILE TO <user>;
SQL> GRANT ALTER ANY SQL PROFILE TO <user>;
SQL> GRANT DROP ANY SQL PROFILE TO <user>;
List of example to create Oracle SQL Profile:
SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE
(
sql_text => ‘select * from emp’,
profile => sqlprof_attr(‘ALL_ROWS’,’IGNORE_OPTIM_EMBEDDED_HINTS’, …… ),
category => ‘DEFAULT’,
name => ‘change_emp’,
force_match => TRUE
);
DECLARE
cl_sql_text CLOB;
BEGIN
— SELECT sql_fulltext INTO cl_sql_text from gv$sqlarea where sql_id=’4n01r8z5hgfru’;
SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = ‘4n01r8z5hgfru’;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => cl_sql_text,
profile => sqlprof_attr(‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’),
name => ‘PROFILE NAME’,
force_match => TRUE);
END;
/
DECLARE
cl_sql_text CLOB;
hint_spec sys.sqlprof_attr;
BEGIN
–SELECT sql_fulltext INTO cl_sql_text FROM gv$sqlarea WHERE sql_id=’gtwyx63711jp1′;
SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = ‘gtwyx63711jp1’;
SELECT
extractvalue(VALUE(d), ‘/hint’) AS outline_hints
BULK COLLECT
INTO
hint_spec
FROM
xmltable(‘/*/outline_data/hint’
passing (
SELECT
xmltype(other_xml) AS xmlval
FROM
dba_hist_sql_plan
WHERE
sql_id = ‘gtwyx63711jp1’
AND plan_hash_value = ‘82930460’
AND other_xml IS NOT NULL
)
) d;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => cl_sql_text,
profile => hint_spec,
name => ‘PROFILE NAME’,
force_match => TRUE);
END;
/
Note: You may use the table v$sql_plan if there is no outline hints available in dba_hist_sql_plan.
Once you have finish creating the Oracle SQL Profile, check on the database system for the new SQL Profile.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
SQL> SELECT sql_attr.attr_val outline_hints
FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr
WHERE sql_profiles.signature = sql_attr.signature
AND sql_profiles.name = ‘PROFILE NAME’
ORDER BY sql_attr.attr# ASC;
Dropping a SQL Profile Using DBMS_SQLTUNE
You can drop a SQL Profile with the DROP_SQL_PROFILE procedure. For example:
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘PROFILE NAME’);
END;
/
In this example, my_sql_profile is the name of the SQL Profile you want to drop. You can also specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE is accepted.