Oracle - Sql ID Signature , FORCE_MATCHING_SIGNATURE , EXACT_MATCHING_SIGNATURE ,Adaptive Cursor Sharing

 

In this Blog we will try to get some insight on below topics which are all interlinked to understand hard parsing 

1) How different sql id having same signature , FORCE_MATCHING_SIGNATURE and EXACT_MATCHING_SIGNATURE are related 
2) What is bind peeking and Bind Seeking 
3) Adaptive Cursor Sharing  ,  is_bind_sensitive and is_bind_aware
5) Literals  vs bind variables 
6) Cursor sharing parameter 
7) Usefull sql statements 
8) What is skew data 
 


Sql performance depends on 

1) Cursore sharing  parameter  
2) Use of Bind Variables 
3) If data is skewed 
4) Optimizer mode parameter 
5) Whether histograms  statistics are gathered . 



########################################
Sqlid signature , FORCE_MATCHING_SIGNATURE and EXACT_MATCHING_SIGNATURE 
########################################

Use of bind variables in SQL statements is a must to make the transaction processing application scalable. 

To find the queries that don’t use bind variable and are parsed each time they are executed we may see Signature 

Starting with Oracle 10gR2, two interesting columns – FORCE_MATCHING_SIGNATURE and EXACT_MATCHING_SIGNATURE – were added in a number of tables and views. We could see them in V$SQL, V$SQLAREA, DBA_HIST_SQLSTAT and DBA_HIST_ACTIVE_SESS_HISTORY data dictionaries.  As you see they are present all over the place – shared pool, ASH, AWR and Statspack. That means we have good choice of source to look for problematic sql’s.

EXACT_MATCHING_SIGNATURE means, a signature is calculated on the normalized sql text. The normalization includes the removal of white space and uppercasing of all the non-literal strings.

FORCE_MATCHING_SIGNATURE means, a signature used as if CURSOR_SHARING set to FORCE. It seems to be another hash value calculated from SQL statement.

As noted before FORCE_MATCHING_SIGNATURE is calculated from sql text as if CURSOR_SHARING set to FORCE – that forces sql statements as to share cursors by replacing constants with bind variables, so all statements that differ only by the constants share the same cursor.  

Let’s have a look at the FORCE_MATCHING_SIGNATURE value for the same sql’s.
When Cursor_sharing=force ==> force_matching_signature applies
When Cursor_sharing=exact ==> exact_matching_signature applies




########################################
Bind peeking and Bind Seeking 
########################################

Sharing the same execution plan for different bind variables is not always optimal as far as different bind variables can generate different  data volume. This is why Oracle introduces bind variable peeking feature which allows Oracle to peek at the bind variable value and give it the best execution plan possible. However, bind variable peeking occurs only at hard parse time which means as far as 
the query is not hard parsed it will share the same execution plan that corresponds to the last hard parsed bind variable. 

Bind Variable Peeking was introduced in Oracle 9i. Prior to Bind Variable Peeking the Oracle optimizer did not know the value being  used in the statement and could end up choosing a poor execution plan. Bind Variable Peeking will look at the value of the bind variable the very first time the statement is executed and generate an execution plan. The cached execution plan will be optimal based on the first 
value sent to the bind variable. Subsequent executions of this statement will use the same execution plan regardless of the value of the bind variable. 

Using Bind Variable Peeking is good if the bind variable is selecting from a column which is not skewed. This means the initial  execution plan for the statement will be optimal for all values of the bind variable. For example, a bind variable for the emp.id is a good idea as the value is unique in the table. The initial execution plan will be good for all statements against id. On the other hand, using  a bind variable for emp.deptid could pose problems. Let say there are two departments. Department 20 has 3 employees and department 21 has  10,000 employees. As the emp.deptid data is skewed, the initial execution and execution plan of the statement may not be optimal for subsequent executions of the statement. Looking at select name from emp where deptid=:deptid, with deptid set to 20 returns 3 rows. 

If this was the initial statement, the optimizer would create an execution plan which would use an index. If deptid is then set to 21, where 10,000 rows are returned, the optimizer will still use the initial plan and use the index. Using the index in this case is not optimal  as a large percentage of the table will be returned. A full table scan would be better. So you see the problem with bind variable peeking. 
Oracle 11g overcomes the Bind Variable Peeking problem with the new Adaptive Cursor Sharing feature. Due to the Bind Peeking problem, some developers will purposely use literal values, for fields with highly skewed data, to avoid the Bind Variable Peeking problem.

When they use literal values it forces Oracle to create a single cursor with its own execution plan. This ensures the query will be  executed optimally.




########################################
Adaptive Cursor Sharing  ,  is_bind_sensitive and is_bind_aware
########################################

In order to avoid such situation Oracle introduces in its 11gR2 release, Adaptive Cursor Sharing allowing Oracle to adapt itself to  the bind variable when necessary without having to wait for a hard parse of the query.

In v$sql this is indicated by the columns is_bind_sensitive and is_bind_aware. The former indicates that a particular sql_id is a candidate  for adaptive cursor sharing, whereas the latter means that Oracle acts on the information it has gathered about the cursor and alters the  execution plan.

Problematic is that adaptive cursor sharing can only lead to an improved plan after the SQL statement has tanked at least once. 

You can bypass the initial monitoring by supplying the BIND_AWARE hint: it instructs the database that the query is bind sensitive and adaptive cursor sharing should be used from the very first execution onwards. A prerequisite for the hint to be used is that the bind variables only appear in the WHERE clause and an applicable histogram is available. The hint may improve the performance but 
you should be aware that it’s rarely the answer in the case of generic static statements, which we describe below. 

The NO_BIND_AWARE hint does exactly the opposite: it disables bind-aware cursor sharing.



########################################
Literals  vs bind variables 
########################################

A literal means the values being compared in the SQL statement are hard coded in the statement itself.
An example of a literal value would be, select name from emp where id=10.
The use of literal values will cause many unique statements to be cached as each literal value causes the statement to be different. 

This will cause more space to be used in the Share Pool. With the use of bind variables the statement remains the same, therefore there is only one statement cached as opposed to many

A bind variable is created and set to a particular value, which is then used by a SQL statement.
This allows the developer to assign any value to the bind variable and the SQL statement will use the new value. For example,  select name from emp where id=:id. The :id is the bind variable and the values being passed into the statement can change as the developer needs.



########################################
Cursor Sharing
########################################

The cursor_sharing parameter can be set to one of three values, FORCE, EXACT or SIMILAR. This parameter is really telling the Oracle  server process how to handle statements which have literal values. If the parameter is set to FORCE or SIMILAR the Oracle server process  will strip out the literal values in the SQL statements and generate system generated bind variables. With FORCE or SIMILAR all statements will go through the Bind Variable Peeking process. At first I though both SIMILAR and FORCE will expose and amplify the Bind Peeking problem

. 
In my testing I determined that SIMILAR does not expose the Bind Peeking problem, but FORCE does. 

If the parameter is set to EXACT, the  Oracle server processes the query as it is and generates an execution plan based on the query. With EXACT literal values are maintained. 

Here are the tests I performed. Notice that both SIMILAR and EXACT act the same when literal and bind variables are used.

According to MOSC Doc 11657468.8, adaptive cursor sharing can be disabled by setting hidden parameters:
 
alter system set "cursor_sharing"=exact scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;



########################################
Skew data
########################################
Skewed columns are columns in which the data is not evenly distributed among the rows.
For example, suppose:
You have a table order_lines with 100,000,000 rows
The table has a column named customer_id
You have 1,000,000 distinct customers

Some (very large) customers can have hundreds of thousands or millions of order lines.
In the above example, the data in order_lines.customer_id is skewed. On average, you'd expect each distinct customer_id to have 100 order lines
(100 million rows divided by 1 million distinct customers). But some large customers have many, many more than 100 order lines.

This hurts performance because Oracle bases its execution plan on statistics. So, statistically speaking, Oracle thinks it can access order
lines based on a non-unique index on customer_id and get only 100 records back, which it might then join to another table or whatever using 
a NESTED LOOP operation.

But, then when it actually gets 1,000,000 order lines for a particular customer, the index access and nested loop join are hideously slow. 

It would have been far better for Oracle to do a full table scan and hash join to the other table.
So, when there is skewed data, the optimal access plan depends on which particular customer you are selecting!

Oracle lets you avoid this problem by optionally gathering "histograms" on columns, so Oracle knows which values have lots of rows and 
which have only a few. That gives the Oracle optimizer the information it needs to generate the best plan in most cases.




########################################
Useful sql queries 
########################################

-- Converting Sql id to Signature 
SET SERVEROUTPUT ON
DECLARE
 SQLTEXT   CLOB;
 SIG       NUMBER;
BEGIN
 SELECT SQL_TEXT
 INTO SQLTEXT
 FROM DBA_HIST_SQLTEXT
 WHERE SQL_ID = '80xyxfffu6awb';
 SIG := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (SQLTEXT, FALSE);
 DBMS_OUTPUT.PUT_LINE ('SIGNATURE=' || SIG);
END;
/
 

-- If you want to find Bind variable for SQL_ID
col VALUE_STRING for a50  
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';


-- checking Adaptive Cursor sharing 
   select
    hash_value,
   sql_id,
   child_number,
   range_id,
   low,
   high,
   predicate
from
   v$sql_cs_selectivity;
 
select
   hash_value,
   sql_id,
   child_number,
   bucket_id,
   count
from
   v$sql_cs_histogram;
 
select
  sql_id,
  hash_value,
  plan_hash_value,
  is_bind_sensitive,
  is_bind_aware,
  sql_text
from
   v$sql;
 
select
   hash_value,
   sql_id,
   child_number,
   bind_set_hash_value,
   peeked,
   executions,
   rows_processed,
   buffer_gets
   cpu_time
from
   v$sql_cs_statistics; 

  col exact_matching_signature for 99999999999999999999999999

  col sql_text for a50

  select sql_id,force_matching_signature, exact_matching_signature, SQL_TEXT from v$sqlarea where UPPER(sql_text) like ‘%DUMMY%’ order by UPPER(sql_text);

 

 col force_matching_signature for 99999999999999999999999999

 select * from (select force_matching_signature, count(*) "Count" from v$sqlarea group by force_matching_signature order by 2 desc) where rownum<=3;

 

 select sql_fulltext from v$sql where force_matching_signature=7415896326081021278 and rownum=1;

 

########################################
References : 
########################################
Adaptive Cursor Sharing Overview [ID 740052.1]