Gather SQL Statements from AWR and Cursor Cache without need for Diag and Tuning Packs

When we talk about database upgrades and migrations the most important (and unfortunately time-/resource consuming) activity is testing.

But often testing resources are limited or, even worse, don’t exist. I’ve worked with customers where we migrated a core EBS system off AIX to Linux – but only got a 6 year old Tru64 box for testing purposes (where the recompilation took 20x as long as later on the production environment). Or the classic one: Database is too big thus we test only with 10% of the data.

I know that often your hands are tied. Everybody wants an 1:1 duplicate test system – but not everybody has one.

And here our DBaaS Cloud is coming into play.
Why? Because you can use one of our best and strongest testing tools, SQL Performance Analyzer (part of the Real Application Testing Pack) without the need to license RAT as it is included into the High and Extreme Performance Cloud offerings.

Even better:
If you have an Enterprise Edition database license on source you won’t even need Diagnostic and Tuning Pack licenses to offload your statements from your local environment’s AWR and Cursor Cache.

SQL Tuning Sets can also be accessed by way of database server APIs and
command-line interfaces. The following subprograms, part of the
DBMS_SQLTUNE package, provide an interface to manage SQL Tuning Sets and
are part of Oracle Database Enterprise Edition:

ADD_SQLSET_REFERENCE
CAPTURE_CURSOR_CACHE_SQLSET
CREATE_SQLSET
CREATE_STGTAB_SQLSET
DELETE_SQLSET
DROP_SQLSET
LOAD_SQLSET
PACK_STGTAB_SQLSET
REMOVE_SQLSET_REFERENCE
SELECT_CURSOR_CACHE
SELECT_SQLSET
SELECT_WORKLOAD_REPOSITORY
UNPACK_STGTAB_SQLSET
UPDATE_SQLSET

But please note:
Diagnostic and Tuning Packs are still available and key to performance management and diagnosibility on your systems. If you’d like to use the above packages and evaluate your statements on-premises (locally) for instance with a scheduled Tuning Task with the SQL Tuning Advisor you will still need a license for Diagnostic and Tuning Packs. If you plan to use the SQL Performance analyzer locally you will need to get a license for Real Application Testing Pack first.

See the documentation link below for the exact description (scroll down to SQL Tuning Sets):

• https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109
• http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC117

For a short example how to capture your SQL statements directly from AWR please see this fairly old blog post:

But I will publish a complete example within the next days including not only the capture from AWR and Cursor Cache but also the packaging into staging tables, the unpackaging – and of course the SPA runs.

• capture_awr.sql
• capture_cc.sql

–Mike

PS: I did check also the DBA_FEATURE_USAGE_STATISTICS view and I don’t see any offending entries when you use the listed packages/calls from above.