Wednesday Nov 06, 2013

How to SET TIMING ON for parallel upgrades to 12c?

Have you asked yourself how to get timings in an Oracle Database 12c upgrade for all statements?

When you run the parallel upgrade via catctl.pl, the parallel upgrade Perl driving script in Oracle Database 12c, you may also want to get timings written in your logfile during execution. As catctl.pl does not offer an option yet the best way to achieve this is to edit the catupses.sql script in $ORACLE/rdbms/admin as this script will get called all time over and over again throughout all steps of theupgrade run.

Just add these lines marked in RED to catupses.sql and start your upgrade:

Rem =============================================
Rem Call Common session settings
Rem =============================================
@@catpses.sql

Rem =============================================
Rem  Set Timing On during the Upgrade
Rem =============================================
SET TIMING ON;

Rem =============================================
Rem Turn off PL/SQL event used by APPS
Rem =============================================
ALTER SESSION SET EVENTS='10933 trace name context off'
;


-Mike

PS: This may become the default in a future patch set ;-)


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
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers