Many DBA teams manage databases which support applications that generate great dollops of non-shareable SQL, i.e. SQL made unique by literal values, syntax differences or references to temporary tables. In OLTP environments non-shareable SQL is a performance inhibitor and can cause throughput bottlenecks.
Oracle database
performance stability relies largely on:
- Good schema design
- Comprehensive and up-to-date optimizer statistics
- Shareable SQL (particularly for OLTP, for warehouse is does not really matter)
- Plan Management or Profiles for SQL regression exceptions that cannot be handled by optimizer statistics and/or table structuring e.g. partitions, index organized tables or derived schema objects eg. indexes, materialized views, cubes
The goal is to manage performance using the 90-9-1 rule which is 90% optimizer statistics, 9% enhanced optimizer statistics and 1% exceptions.
SQL Plan Management allows you to fix a plan to a given sql_id which means it is good for matching shareable SQL, literal SQL which executes very frequently or SQL containing some literal values that do not change frequently.
SQL Profiles can use a force_match signature to match all statements which are essentially the same but differ only in their literal values. In other words, statements that could use bind variables. Profiles can match statements with all literals, all binds (no need for force_match). However for mixtures of literals and binds is the same force_match signature is generated. This is a documented limitation. One solution is to use cursor_sharing but this may have other implications.
When performing a database upgrade you can use the upgrade project as an opportunity to stabilise performance going forward. Even if the upgrade also involves a hardware refresh where overall performance is likely to be improved the opportunity presents itself to reduce your tuning headaches until long after the upgrade.
SQL Performance Analyzer (SPA) is an ideal method to test different target scenarios using actual production SQL. Although
SPA can capture any workload via a SQL Tuning Set (STS), with literal SQL the STS is
filled with single execution statements, so needs to be regenerated to
aggregate the execution counts and eliminate duplicates (i.e. statements having the same
force_match signature) in order to produce an accurate weighted performance
report.
So for all of you upgrading a database version underpinning an application which
pumps out challenging SQL:
- Capture a STS and eliminate duplicates and aggregate execution counts (to preserve integrity of the SPA weighted performance report)
- Execute STS using SPA in target environment ignoring all hints (break free)
- Use the optimizer settings for the target release (don’t hold it back)
- Ensure regressed SQL is the tiny minority of SQL (use optimizer statistics and derived schema objects until this is the case)
- Tune any remaining SQL using SQL profiles (if you must add hints add hints to the SQL profiles)
- Consider raising performance bugs with the application vendor if the above is not possible
So, do not just upgrade the database, leverage the database to improve performance stability and do it now.
