Database Hangs after loading plan baselines from sqlset from 10gR2 to 11gR2 (Doc ID 1323444.1)

Last updated on NOVEMBER 22, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Symptoms

Changes

DB upgraded to 11gr2 and the table imported, plan unpacked and loaded to sqlset baseline.The following procedure was followed  :-

Steps in 10.2.0.5 :


Create STS:

exec dbms_sqltune.create_sqlset ( sqlset_name => '11upgrade');


Load STS from AWR baseline in 10.2.0.5:

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 24390,
end_snap_id => 24402,
baseline_name => 'system_baseline');
END;
/

DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
'system_baseline', NULL, NULL, 'elapsed_time', NULL,
NULL, NULL, 10)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => '11upgrade',
populate_cursor => baseline_cursor);
END;
/


Pack the STS and export the staging table:


Create staging table:

exec dbms_sqltune.create_stgtab_sqlset(table_name => 'STSSTG_TBL',schema_name => 'SYSTEM',tablespace_name => 'SYSTEM');


Pack STS in to staging table:

exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => '11upgrade',sqlset_owner => 'SYS',staging_table_name => 'STSSTG_TBL',staging_schema_owner => 'SYSTEM');


Steps in 11.2.0.1:

++ Export the staging file from the 10g Database.
++ Import the staging table into oracle 11g.

Unpack the STS:

exec dbms_sqltune.unpack_stgtab_sqlset(sqlset_name => '11upgrade',sqlset_owner => 'SYS',replace => TRUE,staging_table_name => 'STSSTG_TBL',staging_schema_owner => 'SYSTEM');

Load the execution plan in to sql management base in 11.2.0.1:

set serveroutput on

declare
Plan_cnt NUMBER;
BEGIN
plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => '11upgrade',sqlset_owner=> 'SYS');
dbms_output.put_line('Number of plans loaded: ' || plan_cnt);
END;
/

Subsequent shut down of the database in 11g and startup would hang the database.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms