SQL plan migration

-

 In Oracle, SQL execution plans can change over time due to various factors such as data growth, schema changes, and optimizer upgrades. This can lead to unexpected performance degradation or instability in production environments. To mitigate these issues, Oracle provides two features called SQL Plan Baselines and SQL Plan Management.


SQL Plan Baselines are a collection of accepted execution plans for SQL statements that are stored in the database. They can be used to ensure that the same execution plan is used for a specific SQL statement, even if the optimizer generates a different plan. SQL Plan Baselines can be created manually or automatically by the Automatic SQL Tuning Advisor.


SQL Plan Management is a more comprehensive solution that automates the process of creating, selecting, and evolving SQL execution plans. It consists of two components: SQL Plan Baselines and SQL Plan Management. SQL Plan Management automatically captures and manages execution plans, performs plan evolution, and maintains plan history.


To migrate SQL Plan Baselines or SQL Plan Management to other environments such as DEV, QA, or Clone, you need to follow these steps:


Export the SQL Plan Baselines or SQL Plan Management information from the production database using the DBMS_SPM package.

Import the exported information into the target database using the same package.

Verify that the imported SQL Plan Baselines or SQL Plan Management information is correct and up-to-date by checking the PLAN_TABLE or DBA_SQL_PLAN_BASELINES views.

Set the SQL Plan Baseline or SQL Plan Management configuration parameters appropriately in the target database.

Test the migrated SQL Plan Baselines or SQL Plan Management configuration in the target database to ensure that the performance is as expected.


On Source database

====================


Check the list of SQL plan baselines on Source

==============================================

select SQL_HANDLE,plan_name, enabled, accepted FROM   dba_sql_plan_baselines  ;


SQL_HANDLE                PLAN_NAME               ENABLED    ACCEPTED

------------------------ ---------------------- ---------- ----------

SQL_172a4815a43b43db      SQL_PLAN_1fak82qk3qhyv0 YES        NO

SQL_172a4815a43b43db      SQL_PLAN_1fak82qk3qhyv3 YES        NO

SQL_172a4815a43b43db      SQL_PLAN_1fak82qk3qhyv6 YES        YES


Create the staging table to hold the SQL baseline

===================================================


BEGIN

  DBMS_SPM.CREATE_STGTAB_BASELINE(

    table_name      => 'my_spm_staging_table',

    table_owner     => 'MY_SCHEMA',

    tablespace_name => 'USERS'

  );

END;

/



PL/SQL procedure successfully completed.


Check the baseline staging table

======================================


SQL> desc <my_schema>.spm_staging_tab

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 VERSION                                            NUMBER

 SIGNATURE                                          NUMBER

 SQL_HANDLE                                         VARCHAR2(30)

 OBJ_NAME                                           VARCHAR2(30)

 OBJ_TYPE                                           VARCHAR2(30)

 PLAN_ID                                            NUMBER

 SQL_TEXT                                           CLOB

 CREATOR                                            VARCHAR2(30)

 ORIGIN                                             VARCHAR2(30)

 DESCRIPTION                                        VARCHAR2(500)

 DB_VERSION                                         VARCHAR2(64)

 CREATED                                            TIMESTAMP(6)

 LAST_MODIFIED                                      TIMESTAMP(6)

 LAST_EXECUTED                                      TIMESTAMP(6)

 LAST_VERIFIED                                      TIMESTAMP(6)

 STATUS                                             NUMBER

 OPTIMIZER_COST                                     NUMBER

 MODULE                                             VARCHAR2(64)

 ACTION                                             VARCHAR2(64)

 EXECUTIONS                                         NUMBER

 ELAPSED_TIME                                       NUMBER

 CPU_TIME                                           NUMBER

 BUFFER_GETS                                        NUMBER

 DISK_READS                                         NUMBER

 DIRECT_WRITES                                      NUMBER

 ROWS_PROCESSED                                     NUMBER

 FETCHES                                            NUMBER

 END_OF_FETCH_COUNT                                 NUMBER

 CATEGORY                                           VARCHAR2(30)

 SQLFLAGS                                           NUMBER

 TASK_ID                                            NUMBER

 TASK_EXEC_NAME                                     VARCHAR2(30)

 TASK_OBJ_ID                                        NUMBER

 TASK_FND_ID                                        NUMBER

 TASK_REC_ID                                        NUMBER

 INUSE_FEATURES                                     NUMBER

 PARSE_CPU_TIME                                     NUMBER

 PRIORITY                                           NUMBER

 OPTIMIZER_ENV                                      RAW(2000)

 BIND_DATA                                          RAW(2000)

 PARSING_SCHEMA_NAME                                VARCHAR2(30)

 COMP_DATA                                          CLOB


SQL>



Now pack the baselines / load the baselines to the staging table created above

===============================================================================



SQL> SET SERVEROUTPUT ON

DECLARE

  l_plans_packed  PLS_INTEGER;

SQL>   2    3  BEGIN

  4    l_plans_packed := DBMS_SPM.pack_stgtab_baseline(

    table_name      => 'spm_staging_tab',

  5    6      table_owner     => 'BIASDBA');


  7    8    DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);

END;

  9   10  /

Plans Packed: 3


PL/SQL procedure successfully completed.



Check the baseline table after loading

===========================================



SQL> select sql_handle from <my_schema>.spm_staging_tab;


SQL_HANDLE

------------------------------

SQL_172a4815a43b43db

SQL_172a4815a43b43db

SQL_172a4815a43b43db



On target database

=====================


Create the DB link to the source database ( If the DBlink not an option then use export / import to copy the baseline staging table data to target).

========================================================================================================================================================


SQL> create public database link efipfqa1 connect to biasdba identified by biasdba using 'efipfqa1';




Check the DB link is working or not

========================================

SQL> select SQL_HANDLE from <MySchema>.spm_staging_tab@efipfqa1;


SQL_HANDLE

------------------------------

SQL_172a4815a43b43db

SQL_172a4815a43b43db

SQL_172a4815a43b43db



Create the baseline staging table using the source database baseline staging table data

===========================================================================================


SQL> create table biasdba.spm_staging_tab as select * from biasdba.spm_staging_tab@efipfqa1;


Table created.



Now Unpack / unload the baseline to the database dictionary

==============================================================


SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2    l_plans_unpacked  PLS_INTEGER;

  3  BEGIN

  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(

  4    5      table_name      => 'spm_staging_tab',

  6      table_owner     => 'BIASDBA');


  7    8    DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);

  9  END;

 10  /

Plans Unpacked: 3


PL/SQL procedure successfully completed.




Check the baselines created from the unload operation above

===================================================================

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM   dba_sql_plan_baselines  ;


SQL_HANDLE                     PLAN_NAME                      ENA ACC

------------------------------ ------------------------------ --- ---

SQL_172a4815a43b43db           SQL_PLAN_1fak82qk3qhyv0239c560 YES NO

SQL_172a4815a43b43db           SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO

SQL_172a4815a43b43db           SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES




Comments

Post a Comment

Popular posts from this blog

How to make CRS and ASM not to restart after server reboot

-
I have noticed that server rebooting is taking lot of time on a VM machine for every reboot, so I have decided to auto start of the crs and ASM service During this phase I have noticed an attribute called auto_start when I try to execute the below command crsctl stat res -p This AUTO_START attribute is there for several resources like ora.ASM ,ora.crs,ora.DATA_DG it has got 2 options restore(0) , never(2) and always (1) always (1)  Causes the resource to restart when the node restarts regardless of the resource’s state when the node stopped -restore (0)  Does not start the resource at restart time if it was in an offline state, such as STATE=OFFLINE, TARGET=OFFLINE, when the node stopped. The resource is restored to its state when the node went down. The resource is started only if it was online before and not otherwise. -never (2)  Oracle Clusterware never restarts the resource regardless of the resource’s state when the node is stopped. So I have dec...
Read more

How to replace ASM failed disk?

-
Oracle Automatic Storage Management (ASM) was introduced in Oracle 10g. ASM provides advance storage management features such as DISK I/O re-balancing, volume management and easy database file name management. It also can provide MIRRORING of data for high availability and redundancy in the event of a disk failure (Mirroring is optional). ASM guarantees that data extents (table,index row data etc.) in one disk are mirrored in another disk (normal redundancy) and in two disks (high redundancy). A few times I have faced ASM disk failures when redundancy (mirroring) was enabled and none of them resulted in an issue for an end user. ASM automatically detects the disk failure and services Oracle SQL requests by retrieving information from the mirrored (other) disk. Such a failure is handled gracefully and entirely managed by Oracle. I am very impressed by the fault tolerance capability in ASM. But soon the Oracle DBA must work with the system administrator to replaced the failed disk. If t...
Read more

Shell scripting cheatsheet

-
 SED :  it is used to replace a word  Replace a word in a file:sed 's/old_word/new_word/' file.txt Replace all occurrences of a word in a file:sed 's/old_word/new_word/g' file.txt Delete lines matching a pattern:sed '/pattern/d' file.txt AWK: Print specific columns from a CSV file:awk -F ',' '{print $1, $3}' file.csv Calculate the sum of a column in a numeric file: awk '{sum += $1} END {print sum}' file.txt Filter lines based on a condition:awk '$3 > 50 {print $1, $3}' file.txt Shebang: The shebang, also known as a hashbang, is the first line of a script and specifies the interpreter to use. For example, #!/bin/bash Variables: Variables store data for later use. $name. To declare a variable as global, use the export keyword. Command substitution: For example, files=$(ls) stores the output of the ls command in the files variable. Control structures: if-else: Conditionally execute code based on a condition. It has the form: if co...
Read more