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.
–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.
Hello Mike,
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.
Did you manage to publish the example? Sorry but I did not find it.
Hi Narendra,
some of the scripts are in the SCRIPTS section of the blog. Please find them there.
The others are in our slide deck for Virtual Classroom Seminar 3 and 9:
https://mikedietrichde.com/videos/
Cheers
Mike