Using Sqltxplain To Create A 'SQL Profile' To Consistently Reproduce a Good SQL Plan (Doc ID 1487302.1) SQL TUNING best

 In this Document

Goal

Solution

References

---

APPLIES TO:

Oracle E-Business Suite Performance - Version 11.5.10.2 to 12.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.

GOAL

Manage site specific performance issues through the creation of a SQL profile for a statement that can be transferred to other systems.

SOLUTION

Introduction

The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints.

Some changes in those conditions may affect the calculations of the plan and may lead the optimizer to produce sub-optimal plans, which may lead to slower performance of the statement(s) affected. There are several supported ways to try to influence the optimizer to produce the desired plan in EBS (like create custom indexes, gathering new stats for EBS tables or workload system stats), but a lot of those activities have the potential to influence more than just one statement.

10G and 11G have a feature called 'SQL Profile', which allows to the database to consistently apply the same plan (provided that you have it or had in the past) and as a result replicate the performance achieved in the past.

The benefits of using a SQL profile are that no changes to application code are necessary, only the targeted query is affected and the use of SQL profiles by the database is transparent to the end user.

SQL Profiles are useful if you have a limited number of SQL statements that are performing poorly after some change, like patching, database upgrade, etc.

Generally such issues should also be logged as E-Business Suite SR’s so that development can review whether the query can be better tuned – however running with the SQL profiles in place is a valid and supported workaround to get your system going again.

 

Types of 'SQL Profile'

There are 2 types of profiles, although currently there are no names to distinguish them

• One that "fixes" the plan and produces exactly the same plan (this would be equivalent to the old 'stored outlines'). This is the type of profile that we will be using in this note.

• One that does not "fix" the plan, but  provide some hints such as "scaling factors" that allow the optimizer to produce a new plan if conditions change, like more data. This is typically what the SQL Tuning Advisor provides

 

Supportability

SQL profiles affect the performance, but not the result of a statement and should be transparent to the application's logic. EBS customers using SQL profiles that improve performance (of custom or seeded code) are supported.

Care should be taken that SQL profiles do not cause worse performance in your target database  - for example under different data volume or usage patterns.

How to create a SQL Profile?

You can easily create a SQL profile for a statement using the script coe_xfr_sql_profile.sql provided with the tool Sqltxplain.

Refer to SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly Document 215187.1

Although the installation is NOT required (you can use the script directly), the installation of SQLT is a good idea as it will be useful when investigating performance problems.

This method requires that you have a RDBMS 10g/11g environment that currently has the SQL statement running with the good plan you want to consistently reproduce, or the good plan was chosen before and it is still in the AWR repository.

Some of the possible causes for SQL execution plan changes are:

• Stats were gathered and the plan changed
• A patch was applied or the DB was upgraded and the plan changed
• A system was cloned to a different machine and the plan changed

How to recreate the plan?

In a nutshell:

• Just run

SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

This will create a script named like coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

• Execute the script generated in the system where you want to now reproduce the same plan

 

Details:

1) Download sqltxplain utility from

SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly Document 215187.1

The script named coe_xfr_sql_profile.sql is provided under the /utl directory.

2) Find the sql_id and the plan_hash_value (if there is more than one plan) for the statement that you want to create the profile against.

a) If you have a tkprof output in 11g, it may show this information right above the statement, like:

SQL ID: dnzjmxkbmk4uy
Plan Hash: 2517809292
select distinct spid from v$process p,v$session s where p.addr = s.paddr and s.audsid =userenv('sessionid')

b) If you have the raw sql trace, you can also search for that statement and you will see the sql_id in the parsing line right above the statement in question, as in the example below:

PARSING IN CURSOR #27 len=108 dep=0 uid=65 oct=3 lid=65 tim=1346257958769515 hv=2537100126 ad='5f9042b0' sqlid='dnzjmxkbmk4uy'
select distinct spid from v$process p,v$session s where p.addr = s.paddr and s.audsid = userenv('sessionid')

 
Having the sql_id, we can obtain the "plan_hash_value" running the statement below. Note that if there is more than one plan for the same SQL, it will show multiples per the example below.

WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = TRIM('&&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('&&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;

Enter value for sql_id: dnzjmxkbmk4uy
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2517809292        .089

 

c) If you have just the sql_id from an AWR report, for example, you can use the statement above to find the plan_hash_value

d) Depending of the DB version, you may see just the 'hash_value'  (hv)  in the raw SQL trace (not to confuse with the plan_hash_value). You would see something like:

PARSING IN CURSOR #27 len=108 dep=0 uid=65 oct=3 lid=65 tim=1346257958769515 hv=2537100126 ad='5f9042b0'

 
Having the hash_value you can find the sql_id using

select sql_id from v$sql where hash_value = 'hash value from the trace';

SQL> select sql_id from v$sql where hash_value = '2537100126';
SQL_ID
-------------
dnzjmxkbmk4uy

 
 and proceed as previous steps.

3) Having the sql_id and the plan_hash_id, run the following:

sqlplus / as sysdba
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

 
This will generate a script named like:
coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

For example:
coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql

This file contains the stored profile from the database that you obtained it from.  You can now use this script to apply the same plan to another database:


4) Just execute this script in the DB where you want to create this profile

sqlplus / as sysdba
SQL> START coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql

 

REFERENCES

NOTE:457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions)
NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:271196.1 - Automatic SQL Tuning and SQL Profiles