Introduction
You might have seen an earlier post on the automatic SQL tuning set (ASTS). I want to talk about it again because relaxed the license for automatic SPM, and this feature relies on ASTS being turned on.
Since ASTS captures and stores individual SQL statement information in the SYSAUX tablespace, you can expect space to be consumed if the ASTS is enabled. Some customers reported extreme growth, so we surveyed why this was happening. Two important patches were subsequently created to address this:
Patch/Bug 15878434 – EXTEND LITERAL REPLACEMENT TO SUPPORT MERGE STATEMENTS TOO
Patch/Bug 35488357 – REDUCE NUMBER OF SQL STATEMENTS WITH SAME FORCE MATCHING SIGNATURE IN AUTO SQL TUNING SET
Do you need these patches?
Patch 35488357 reduces the number of dynamic SQL statements we capture in an initial batch when the SQL statement is seen for the first time. For example, if you application generates SQL like this, the database could capture up to 1000 versions:
select col from table where id = 10; select col from table where id = 20; select col from table where id = 30; ...etc
Patch 15878434 reduces this to 100. Technically speaking, ASTS captures no more than 100 SQL statements with the same force matching signature (instead of the previous 1000). This is now ASTS throttles the number of statements captured, even if dynamic SQL is used.
If your application uses bind values throughout, or uses literals in a limited way, there’s no need to apply this patch.
Patch 15878434 enables the correct treatment of force matching signatures for MERGE statements. For example, MERGE statements using literals instead of bind variables would have different force matching signatures, so ASTS would capture all versions without limit. For example,
merge into tab1 t1 using ... set t1.col1 = 'some literal value 1', col1 = 'some other literal 1' ... merge into tab1 t1 using ... set t1.col1 = 'some literal value 2', col1 = 'some other literal 2' ... merge into tab1 t1 using ... set t1.col1 = 'some literal value 3', col1 = 'some other literal 3' ... ...
This accounts for why some customer saw indefinite or significant growth in the automatic SQL tuning set. The patch means that the MERGE statements above will now have the same force matching signature, and we will capture no more that 100 versions.
If your application uses bind variables and not literals in MERGE statements, there’s no need to apply this patch.
A Problematic Use Case
We observed a problematic use case generated by an open source ORM framework. It generated SQL statements like this:
insert into t1 values (:bind1, :bind2, :bind3, :bind4 ... ); insert into t1 values (:bind1, :bind2, :bind2, :bind3 ... ); insert into t1 values (:bind1, :bind4, :bind5, :bind6 ... ); ...
Even though these SQL statements use bind values, the bind names vary, so each INSERT statement has a different force matching signature. If there are a large number of bind variables, the number of combinations can be huge. ASTS will capture each one. Oracle is of the opinion that this behavior should be addressed in the client-side tool, rather than the Oracle Database. Note that I approached the relevant open source community.
Can I get the patches?
The patches are currently not in the RU, but I have asked for this to be expedited. The patches are relatively new and no customer has requested them yet, so you won’t see them in MOS yet. You can be the first to request them!
What if my database uses nothing but dynamic SQL?
SQL plan management is designed to work with repeatable SQL statements, so if your system uses only dynamic SQL, and you cannot use forced cursor sharing, then SPM will not add significant value.
Nevertheless, automation such as automatic indexing relies on the ASTS, and auto indexing can add value even if SQL is not repeatable. This is why we capture a subset of dynamic SQL in ASTS. We have seen use cases with very large numbers of distinct SQL statements, which is why we reduced the maximum capture for a given signature from 1000 to 100.