Poor Performance related to ADS (Part2)

(read Part1 here)

Why do I need a part two ? well in part 1 i said  that “I’m (almost) sure we suffered from Very Long Parse Time for Queries in In-Memory Database (Doc ID 2102106.1).”
So glad I added (almost) because now it seems we still have the same issue during our last batch run. I did a few tests after setting inmemory_query=disable and it worked fine, but my testing was obviously not good enough.

My next step was to disable Automatic Dynamic Statistics (ADS) by setting optimizer_dynamic_sampling=0. I monitored the batch for two nights and it worked out well. I now have a workaround that works.
My concerns are that disable ADS is an instance wide setting, possibly having a negative impact on other sqls executing.
I have a case with Oracle Support, I want to know if this is a (existing) bug or not. I am suspicious since it was seen after applying the latest PSU Patch.

My problem is fairly isolated, I have three set of similar sqls, with one force_matching signature for each set. I think a better solution is to have a workaround for these three force_matching_signatures. I plan to use sql profiles to fix this. If you want to read more about sql profiles you can do it here. The short version is that with sql profiles we give the optimizer corrected statistics and optimizer hints so it can make a better decision when optimizing the sql. Note that we do not lock it to a specific plan, we just fix incorrect estimates and add optimizer hints.

The reason why I find sql profiles useful is the setting force_match which can be set to TRUE/FALSE. Force_match is tightly integrated with force_matching_signatures, since it is the force_matching_signature that tells the sql profile if it is a match or not.
Looking at the documentation it says:


This parameter controls statement matching. Typically, an accepted SQL profile is associated with the SQL statement through a SQL signature that is generated using a hash function. This hash function changes the SQL statement to upper case and removes all extra whites spaces before generating the signature. Thus, the same SQL profile works for all SQL statements in which the only difference is case and white spaces.

By setting force_match to true, the SQL profile additionally targets all SQL statements that have the same text after the literal values in the WHERE clause have been replaced by bind variables. This setting may be useful for applications that use only literal values because it enables SQL with text differing only in its literal values to share a SQL profile. If both literal values and bind variables are in the SQL text, or if force_match is set to false (default), then the literal values in the WHERE clause are not replaced by bind variables.”

If we set this parameter to TRUE all sqls that is identical after removing white space, convert the sql to UPPER case and replace literals with bind variables will use the sql profile. This is exactly what I see will happen with my large sets of similar sqls. Note that if you mix literals and bind variables in your original sql this transformation (normalization) will not happen.

In my ASH report, taken during the batch Monday evening I have three force_matching_signature.


The top two signatures points out two similar sqls, but the third has no sqls listed.
We can see that we have many  “# of Sampled SQL Versions” for each force_matching_signature.

I will check the third force_matching_signature to check which sql it relates to.

SQL> select sql_id from v$sql where force_matching_signature=16123686858932869393


10798 rows selected.

SQL> select sql_text from v$sql where sql_id = 'bcptfjnt53zzq';

where SCEDBT like '8153564 1' and SCEKCO like '00100' and SCEDOC like '226954' 
and SCEDCT like 'PO' and SCEDLN like'1000'

It is a similar sql coming from the application so we need to handle these sqls as well.

I will test my workaround for one of my force_matching_signatures

I want to manually create a sql_profile for one of my sqls related to force_matching_signature= 16123686858932869393. When that sql_profile is in place having force_match=true I expect all of my sqls having the same force_matching_signature to use it.

SQL Profiles are generated when you run a tuning task, either manually or thru the automatic tuning feature. Looking at the oracle documentation on dbms_sqltune which is the package that handles sql profiles there are no program named create_sql_profile.
You can drop, alter, accept. The script I found do use dbms_sqltune to create sql_profile manually, see below.

So I searched for “build sql:profiles manually Oracle” and came across a blog by Kerry Osborne: “Oracle Support Sanctions Manually Created SQL Profiles!

In that blog Kerry mentioned a script named coe_xfr_sql_profile.
Reading the documentation of the script it generates another script containing the commands to create a manual custom SQL Profile out of a known plan from
memory or AWR, it takes two parameters sql_id and plan_hash_value.
(If you plan to use the script read the documentation in the script)
So I used one of my sqls from the ash report related to the force_matching_signature 16123686858932869393.



I execute the sql and then I get the sql_id and plan_hash_value by using dbms_xplan.display_cursor. I also verify that the force_matching_signature is the same.


I run coe_xfr_sql_profile adding sql_id and plan_hash_value. Out come a new script coe_xfr_sql_profile_an7g7mn6tpb14_883491408.sql that contains all the steps to create a sql_profile for this statement with that plan. Note that prior to executing the script you MUST change force_match=true in the script If you don’t do that the workaround won’t work.


This is the output after running script coe_xfr_sql_profile_an7g7mn6tpb14_883491408.sql. As you see, hints are also part of the sql profile. I mentioned earlier that setting optimizer dynamic sampling= 0 fixed my issue. This change is also included in the sql profile. The parameter hint is vital since it will tell the optimizer to not perform Automatic Dynamic Statistics (ADS), which is something I believe causing my performance problem. I also changed force_match to TRUE which will make all sqls having the same force_matching_signature to use this sql profile.

You can confirm that we have a sql_profile on sql_id an7g7mn6tpb14 by looking at grid control.


or by query dba_sql_profiles


What is the output if I run my sql again ?


My execution has the same sql_id and plan_hash_value as when I created my profile and you can see in the Note section that my sql profile is used.


In my next execution in the listing above I have changed one of the values. I changed SCEDCT like ‘PO’ to SCEDCT like ‘AA’. As you see we now have a new sql_id  10v47g6jbs95p and the same plan. Since this is a new sql it should be hard parsed.
In the Note section you can see that we are using the same sql profile as in my original sql_id. This should mean that as long as we run the same  sql and just changes the literal values we will use the sql_profile and ADS will not be performed.

The nice thing I think with this solution is that it will fix the Automatic Dynamic Statistics (ADS) issue for this statement no matter what values we use.

Now I need to do the same with the other two force matching signatures I have in my ASH report. When my three sql profiles are in place I should be able to reset optimizer dynamic sampling back to the default value of 2.

We have also started a discussion with our developers and explained to them why they should use  bind variables in cases like these. They plan to rewrite the application. The high amount of unique sqls is affecting our shared pool which has grown to a very large size. Shared Pool is about 65% of the SGA and I think these sqls is one of the reasons for it to have grown to that size..

Thanks for reading, if you have any comments or questions feel free to contact me.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s