Monthly Archives: June 2011

EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE Explained

Let us start with the definition of the columns. As per Oracle Documentation EXACT_MATCHING_SIGNATURE is a  signature calculated on the normalized SQL text. The normalization includes the removal of white space and the upper casing of all non-literal strings. This signature is used when  CURSOR_SHARING parameter is set to EXACT where as FORCE_MATCHING_SIGNATURE is the signature that Oracle calculates when the CURSOR_SHARING parameter is set to FORCE.

Now lets discuss some  of the values that these columns can have and their significance.

Case-1: When  EXACT_MATCHING_SIGNATURE  and FORCE_MATCHING_SIGNATURE=0 This is the scenario when the command type is PL/SQL package, INSERT,ALTER INDEX,LOCK, SET ROLE and SELECTS/UPDATES from actual SYS  table($, # and  not views)  or internal Oracle statements. There may be more commands but in general , If Oracle thinks that SQL cannot be reused, then values are marked as 0.

Example-1: In the example below, I am  inserting some dummy values  into test table and then check the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE

SQL>create table test (col1 number, col2 number);

Table created.

SQL>insert into test values (1,1);

1 row created.

SQL>insert into test values (2,2);

1 row created.

SQL>insert into test values (3,3);

1 row created.

SQL>commit;

SQL>select sql_id, sql_text from v$sql where sql_text like ‘insert%test%’;

SQL_ID         SQL_TEXT
————-  ————————————————————————————-
1mqr33wjhx7ym     insert into test values (3,3)
9263ja18wuf3x      insert into test values (1,1)
0mjxttvc7zcrg          insert into test values (2,2)

The query below checks  the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE;  As discussed earlier they should be 0.

SQL>SELECT sql_id, exact_matching_signature, force_matching_signature from v$sql where sql_id=’1mqr33wjhx7ym’;

SQL_ID         EXACT_MATCHING_SIGNATURE     FORCE_MATCHING_SIGNATURE

————–  ————————-    ——————————
1mqr33wjhx7ym     0                 0

Case-2: when EXACT_MATCHING_SIGNATURE=FORCE_MATCHING_SIGNATURE. This is the scenario when bind variables are used.

Example-2:The goal of this example is to prove that  EXACT_MATCHING_SIGNATURE is equal to  FORCE_MATCHING_SIGNATURE when bind variables are used. In order to achieve that, I  have defined variable VAR_OBJECT_ID and then  execute  the select statement below multiple times with different values VAR_OBJECT_ID  from multiple sessions.

SQL>column EXACT_MATCHING_SIGNATURE format 99999999999999999999999999999999999999999999999
SQL>column FORCE_MATCHING_SIGNATURE format 99999999999999999999999999999999999999999999999

SQL>VARIABLE VAR_OBJECT_ID NUMBER;
SQL>EXEC :VAR_OBJECT_ID:=345339;

SQL>SELECT /* With Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=:VAR_OBJECT_ID;

OBJECT_NAME
——————————————————————————————————————————–
DBA_HIST_LIBRARYCACHE

SQL>EXEC :VAR_OBJECT_ID:=345348

PL/SQL procedure successfully completed.

SQL>
SQL>SELECT /* With Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=:VAR_OBJECT_ID;

OBJECT_NAME
——————————————————————————–
DBA_HIST_SGA

SQL>EXEC :VAR_OBJECT_ID:=345342

PL/SQL procedure successfully completed.

SQL>SELECT /* With Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=:VAR_OBJECT_ID;

OBJECT_NAME
——————————————————————————–
DBA_HIST_DB_CACHE_ADVICE

The query below checks  the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATUREAs discussed earlier they should match because of using bind variables

SQL>SELECT sql_id, executions, exact_matching_signature, force_matching_signature from v$sql where sql_id=’anh9f1x3qfsc7′;

SQL_ID        EXECUTIONS                          EXACT_MATCHING_SIGNATURE                          FORCE_MATCHING_SIGNATURE
————- ———- ————————————————- ————————————————-
anh9f1x3qfsc7          3                               5933628278648932257                               5933628278648932257

Case-3: when EXACT_MATCHING_SIGNATURE<>FORCE_MATCHING_SIGNATURE. This is the scenario  when different literals are used in  the SQL statement.  SQL statement will be shared if CURSOR_SHARING is set to FORCE.

Example-3: The goal of this example is to prove that  EXACT_MATCHING_SIGNATURE is NOT equal to FORCE_MATCHING_SIGNATURE when bind variables are NOT  used. In order to achieve that, I am  executing  the select statement below multiple times with different literal values.

Please note that I have NOT set CURSOR_SHARING to FORCE.

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345335;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LATCH_PARENT

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345338;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LATCH_MISSES_SUMMARY

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345339;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LIBRARYCACHE

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345340;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LIBRARYCACHE

The query below checks  the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATUREAs discussed earlier they should NOT match because of using literals.

SQL>SELECT SQL_ID, SQL_TEXT, EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE FROM V$SQL WHERE SQL_TEXT LIKE ‘%Without Bind Value%’;

SQL_ID        SQL_TEXT                                            EXACT_MATCHING_SIGNATURE          FORCE_MATCHING_SIGNATURE
————- —————————————- ———————————– ———————————
3mh63hsg30ry0 SELECT /* Without Bind Value */                         15981458635320564018              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345339

bykcv3pwz1bqq SELECT /* Without Bind Value */                          9089800361790826758              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345338

fb40628mbxu49 SELECT /* Without Bind Value */                           550928455875090853              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345340

dwjac8zk268r8 SELECT /* Without Bind Value */                          1750778464710645288              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345335

SQL>SELECT SQL_ID FROM V$SQL WHERE FORCE_MATCHING_SIGNATURE=14612835381060113580;

SQL_ID
————-
3mh63hsg30ry0
bykcv3pwz1bqq
fb40628mbxu49
dwjac8zk268r8

How useful are these column values?
1. To find SQL statements that are identical but do not using bind variables. Prior to 10g , there was no accurate way getting  this result set.  From 10 onwars , run the following query.

SQL>column sql_text format a50
SQL>set linesize 150 pagesize 2000

SQL> set long 3000

SQL>column FORCE_MATCHING_SIGNATURE format 99999999999999999999999999999999999999999
SELECT SQL_ID , SQL_TEXT, force_matching_signature FROM V$SQL WHERE force_matching_signature IN (SELECT force_matching_signature
FROM v$sql
WHERE force_matching_signature <> 0
AND force_matching_signature <> exact_matching_signature
GROUP BY force_matching_signature
HAVING COUNT(*) > 1)
ORDER BY 3

2.  This column is also very useful to check if  SQL statements are being reused when CURSOR_SHARING is set to FORCE.

Advertisements

LTOM (On-Board Monitor)

I came across LTOM while researching  on Oracle tools to collect data proactively before upgrading to 11gR2.  I was surprised that this wonderful tool existed and kind of proves the fact that there is no end to attaining knowledge; you always  learn something new everyday.

Some of the reasons I  was  impressed with this tool is

  1. LTOM is Proactive
  2. LTOM is tightly  integrated with OS
  3. LTOM collects data real time
  4. LTOM provide real time problem  detection and collects necessary diagnostic traces when the problem occurs, so you don’t have to wait for next occurrence of the  issue

LTOM   supports

  1. Automatic Hang Detection
  2. System Profile
  3. Automatic Session Tracing

As usual , do not use LTOM in production without blessings from Oracle support and testing. Refer  to metalink doc ID 352363.1 for more information

How to find if Oracle Procedure/Table/Index is used?

You are given the task of cleaning up your database like drop unused tables, indexes and   procedures.

If you are running Oracle10g and above, consider yourself lucky; otherwise there is no easy way to get this done.

Even this approach has some pitfalls but should be good enough for most requirements. The approach is limited by the policies you set for AWR repository.

Query DBA_HIST_SQLTEXT where COMMAND_TYPE=47 will list all PL/SQL executions. Also you can get description of numbers in the “COMMAND_TYPE” column by querying AUDIT_ACTIONS table.

Query DBA_HIST_SQL_PLAN for OBJECT_NAME to get tables and indexes that are being used

Note: Oracle 10g/11g requires additional licensing to be purchased for using AWR.

Please note that all the methods describe are not  100% reliable depending on whether the data is captured in DBA_HIST  or whether  your optimizer is picking the correct  index or Oracle using indexes for internal purpose like indexes on foreign key to maintain relational integrity constraints.   So test , test and validate every approach as every application is different

thanks