SQL profiles:
A SQL profile contains supplementary or additional statistics specific to a SQL query. SQL profile information is stored in the data dictionary. The optimizer uses this information during optimization to determine the most optimal plan.
Below steps will help to implement SQL profile for a query in different scenarios:
1. SQL Tuning Advisor task created for a slow performing query and the task includes a recommendation to create SQL profile.
2. Restore old execution plan for a query.
3. Copy execution plan for a query from one database to another database.
Prerequisite Steps
• Identify the SQL_ID and plan hash value for the slow performing query.
• Run below queries to find if there is change in plan or if a better performing plan was used.
set pages 200 linesize 200
select distinct *from (select sql_id, plan_hash_value, trunc(begin_interval_time) as begin_date
from dba_hist_snapshot natural join dba_hist_sqlstat
where begin_interval_time between
to_date('01-JAN-2021 1228','dd-mon-yyyy hh24mi')
and to_date('30-JAN-2021 1248','dd-mon-yyyy hh24mi')
and sql_id in ( ' <SQL_ID>' ) ) order by 3 desc;
Average Elapsed Time in sec for each plan:
WITH p AS (
SELECT plan_hash_value FROM gv$sql_plan
WHERE sql_id = TRIM(‘<YOUR_SQL_ID>')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL ),
m AS (SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('<YOUR_SQL_ID>')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
12233642251 .006
3453222222 .036
1234543016 .922
From above two queries it is evident that the old plan hash value (1234543016 ) performed better.
- Execution Steps
This section describe the process to create SQL profile for different scenarios discussed above.
• SQL Tuning Advisor:
o After identifying the candidate query’s SQL_ID and Plan Hash Value, login to the database using Enterprise Manager Cloud Control.
o Navigate to the query by searching the SQL_ID and execute the SQL Tuning Advisor for the query.
o If a SQL profile is recommended, accept the profile and test the query performance, with and without profile.
• Restore old execution plan/Copy SQL profile to another database:
o Run the coe_xfr_sql_profile.sql in SQLT's utl subdirectory, by inputing the SQL_ID and Plan Hash Value (good) as parameters
download sqlt and unzip the file to your custom location
cd SQLT_LOCATION/sqlt/utl
login to oracle database server:
setup database env
sqlplus "/ as sysdba"
SQL> @coe_xfr_sql_profile.sql <your sql_id> <plan_hash_value>
example:
SQL> @coe_xfr_sql_profile.sql 2fxxxxxxxxu 3731234451
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3731234451 .006
3432922222 .036
2253653324 .916
17434378898
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed:
~~~~~~~~~~~~~
SQL_ID : "2fxxxxxxxxu"
PLAN_HASH_VALUE: "3731234451"
Execute coe_xfr_sql_profile_2fxxxxxxxxu_3731234451.sql
on TARGET system in order to create a custom SQL Profile
with plan 3733642251 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
Edit the above profile creation script and in the end change the FORCE_MATCH from FALSE to TRUE.
Create the SQL PROFILE.
SQL> @coe_xfr_sql_profile_2fxxxxxxxxu_3731234451.sql
....................
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_coe_2fxxxxxxxxu_3731234451 completed
SQL> 6. Post Execution Steps
• Verify the SQL profile
SQL> select name, trunc(created) ,status , FORCE_MATCHING from dba_sql_profiles where name like '%22fxxxxxxxxu%' order by created desc;
NAME TRUNC(CRE STATUS FOR
------------------------------ --------- -------- ---
coe_2fxxxxxxxxu_3731234451 25-JAN-21 ENABLED YES
• Verify the Plan Hash Value using the query from prerequisites steps
Backout Plan
• Drop the SQL profile
As sysdba
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => '&name_of_sql_profile');
END;
/
