Forum Stats

  • 3,759,511 Users
  • 2,251,557 Discussions
  • 7,870,689 Comments

Discussions

Migrate DB to 19c; What about baselines ?

PeaceMonger
PeaceMonger Member Posts: 23 Blue Ribbon
edited Oct 14, 2021 1:01PM in General Database Discussions

Oracle RDBMS software version info

Current version : 12.1 in HP-UX (non-CDB)

Target version : 19c in RHEL


I have a 12.1 DB (non-CDB) which uses lot of manual and auto-captured baselines. It has the following setting.


SQL> show parameter baseline


PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- -------------
optimizer_capture_sql_plan_baselines                         boolean     TRUE
optimizer_use_sql_plan_baselines                             boolean     TRUE
SQL>


This DB is 3 Tera Bytes in size. I am thinking of using export import (expdp/impdp) to migrate this to a 19c pluggable database.

Baselines are stored in SYSAUX tablespace. So, baselines will also be taken care by expdp and impdp. Right ?


This DB apparently has some stored outlines too. So, will this be taken care by expdp and impdp as well ?

Answers

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,216 Bronze Trophy

    I don't know how this application worked without problems with this kind of setting.

    You are creating a SPM plan into the SPB baseline for each query executed 2 times!!!!

    Your table space SYSAUX must be full to the brim and your queries that have multiple SPM plans most likely suffer from many parsing problems.

    Try to get the number (you need then to get this by sql_id) of parent sql_id using SPM baselines

    select count(1) from gv$sql where sql_plan_baseline is not null;
    

    And the number of ACCPETED and ENABLED baseline you have on your database

    select count(1) from dba_sql_plan_baselines where accepted ='YES' and ENABLED ='YES';
    

    Best regards

    Mohamed Houri

    PeaceMonger
  • PeaceMonger
    PeaceMonger Member Posts: 23 Blue Ribbon

    Thank You Mohammed.


    So, you are implying that OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter should ideally be set to FALSE unless there is a compelling reason. Right ?

    DBAs in my shop are not very knowledgeable about baselines. I think OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES was set to TRUE in this DB before some migration for stability. But, the DBAs just did not bother to revert it to FALSE.


    Output of your queries.


    sSQL> select count(1) from gv$sql where sql_plan_baseline is not null;
    
     COUNT(1)
    ----------
       19568
    
    SQL> select sql_id, count(1) from gv$sql where sql_plan_baseline is not null group by sql_id order by count(*) desc;
    
    SQL_ID     COUNT(1)
    ------------- ----------
    9mx5p1ug5em4v     17
    6baz2m1nv0v9d     14
    n8kq5hc7zyzxt     13
    4nh08ds11y7zh     13
    851sb49kmu13t     11
    jvm6a47n5fm85     11
    .
    .
    .
    .
    <snipped for readability>
    
    9000+ rows are returned
    
    
    SQL> select count(1) from dba_sql_plan_baselines where accepted ='YES' and ENABLED ='YES';
    
     COUNT(1)
    ----------
      274632
    
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,216 Bronze Trophy

    This is exactly what I have expected. It is a not a good idea to have the parameter optimizer_capture_sql_plan_baselines set to TRUE. It needs to be set back to its default value FALSE.

    If I was at your place I will definitely set back this value to FALSE in my 19c upgrade TESTs and create a SPM baseline when needed but manually using

    DECLARE
      l_plans_loaded PLS_INTEGER;
    BEGIN
      l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
               sql_id     => '&sql_id'
    	, plan_hash_value => '&plan_hash_value');
    END;
    /
    

    Best regards

    Mohamed Houri

    PeaceMonger