Thursday Jan 26, 2017

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.

RAT in the Cloud - without the need to license SPA SQL Performance Analyzer

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):

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.

Thursday Jul 30, 2015

SQL Monitoring - Limitation at 300 lines per statement

One of the best parts of my job at Oracle:
I still learn something new every day.

Yesterday I've learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won't be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan. 

Now you may think: Who the heck has statements longer than 300 lines?
Well ... sometimes that is beyond your influence as in this particular case this is of course done by the application.

Solution:

SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;

or set in your spfile:

_sqlmon_max_planlines=800

This limitation is described in:

MOS Note:1613163.1
How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring?

If you'd like to read a bit more about SQL Real Time Monitoring please follow one of these links - and be aware that it's part of the Tuning Pack license and VERY helpful in many everyday situations. You'll have to have STATISTICS_LEVEL either TYPICAL (the default) or ALL and CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' (the default as well).

 

--Mike 

Monday Jul 16, 2012

How to select statements from AWR?

Simple question - and more a reminder to myself as I'd assume that many people have their examples somewhere already. But in upgrade and migration projects it's sometime useful to simply select SQL statements directly from between two AWR snapshots into a SQL Tuning Set (STS). This will give you the possibility to later put a failing plan directly into a SQL Plan Baseline, part of the free Enterprise Edition's SQL Plan Management feature.

You'll need to create a SQL Tuning Set and simply fill it with staments from an AWR snapshot interval:


exec DBMS_SQLTUNE.CREATE_SQLSET('MD_STS','SYS')
/

DECLARE
  cur sys_refcursor;

BEGIN

open cur for

   select value(p)
from table(dbms_sqltune.select_workload_repository(
      begin_snap => 4711,
      end_snap => 4788,

      basic_filter => 'parsing_schema_name not in
                 (''DBSNMP'',''SYS'',''ORACLE_OCM'')',

      ranking_measure1 => 'elapsed_time',

      result_limit => 250)) p;

    dbms_sqltune.load_sqlset('MD_STS', cur);

  close cur;

END;

/

And fixing a failing plan and overwriting it with the plan from before the upgrade would work that way:

DECLARE
 my_plans PLS_INTEGER;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => 'MD_STS',
   basic_filter => 'sql_id="b25h7qc53gowp"',
   fixed => 'YES');
END;
/

- Mike

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search


Archives
« February 2017
SunMonTueWedThuFriSat
   
2
4
5
6
7
10
11
12
14
15
18
19
22
24
25
26
27
28
    
       
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers