If you have looked at SQL Profiles (see for example Kerry Osborne’s blog) then you may have come across the force_match option for enabling or importing a SQL profile. I received an email recently asking a few questions about this feature.
I have an Oracle 10.2.0.4 situation where I am testing using DBMS_SQLTUNE.IMPORT_SQL_PROFILE to effectively create what I might call “selective cursor sharing equals FORCE” for a given SQL (select) query(ies). The instance has cursor_sharing set to EXACT, which, I am not inclined to modify.
After running the aforementioned procedure and verifying that it is correctly set up by querying DBA_SQL_PROFILES, when I try submitting additional similar queries (with only the literal value changed), I see in V$SQL that the sql_text for the additional queries does not use a system generated bind variable, rather, it uses the exact literal value as when submitted the queries. I had expected it to behave as if cursor_sharing were set to FORCE and replace the literals with a system generated bind variable. The additional queries do, however, have the same value for the FORCE_MATCHING_SIGNATURE attribute of V$SQL.
Given the above, I need to know if I am doing everything properly.
Should Oracle be replacing the new literal values with a system generated bind variable?
Does the fact that these additional similar queries have the same FORCE_MATCHING_SIGNATURE value tell me this technique is in fact working?
Does this technique eliminate or greatly reduce hard parsing for the affected SQL statements?
I don’t usually reply to private requests for assistance with Oracle – it’s not an efficient use of my time. But occasionally a question appears that is sufficiently generic that it’s worth answering in public. In this case it’s worth making the point that force_match and cursor_sharing = force are completely unrelated.
So, in order:
1. Should Oracle be replacing the new literal values with a system generated bind variable?
No. force_match simply means the profile (i.e. set of hints) will be applied to the SQL even though the literals do not match. The optimizer will then optimize the statement in the light of those hints.
2. Does the fact that these additional similar queries have the same FORCE_MATCHING_SIGNATURE value tell me this technique is in fact working?
Yes, probably – though technically it’s indicating that the matching should have applied to these statement – but you could use dbms_xplan.display_cursor() to check whether the notes (in column xml_other of v$sql_plan) includes a comment like: ‘SQL_PROFILE XXXXXX used for this query’ if you wanted to be sure.
3. Does this technique eliminate or greatly reduce hard parsing for the affected SQL statements?
It probably reduces the work done during hard parsing if your constructed profile include hints like leading(), index() and so on – but the optimizer still has to hard parse the “profile-hinted” SQL. Any saving comes from the number of execution paths the optimizer avoids considering because the hints block them. Note: a SQL profile generated by the dbms_sqltune package will only include opt_estimate() hints, which change the arithemetic but don’t limit choice of execution paths, so a “genuine” profile could actually mean the optimizer does more work optimising a statement that it would without the hints.