Category: TUNING

A)  CREATE STAGING TABLE

First Create the staging table which will hold sql profiles to be moved over.

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>’SQL_STG_TAB’,schema_name=>’SYS’);

(This will create a table SQL_STG_TAB, which will be able to hold the required sql profiles, the structure of this table is exactly same as that of DBA_SQL_PROFILES table)

Oracle Database supports the export and import of SQL plan baselines using the Oracle Data Pump Import and Export utilities. Use the DBMS_SPM package to define a staging table, which you can use to pack and unpack SQL plan baselines.

To import a set of SQL plan baselines from one system to another:

Sometime DBAs need to create SQL Baseline for particular SQL_ID for plan stabilization

1) VERIFY CURRENTLY EXISTING SQL PLAN BASELINES

select * from dba_sql_plan_baselines;

Sometimes it is required to flush the BAD SQL_PLAN from shared_pool so that new (or old) better execution plan can be picked by SQL_ID

1) FIND ADDRESS AND HASH_VALUE OF SQL_ID

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS          HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

Finding the SQL_ID for a SQL Statement is straight forward task. You can use query like below to find the SQL_ID

SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&An_Identifiable_String%';

To Determine the SQL_ID of a SQL Statement in a PL/SQL Block below steps can be followed: