My Oracle Support Banner

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

Last updated on OCTOBER 23, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.