Oracle 19c Automatic SQL Plan Management Behaviors Change

 

What's New : 

Automatic SQL Plan Management is enabled by default in Oracle 19c

New SQL plan baselines will be created automatically

By default, the SPM Evolve Advisor runs daily in the maintenance window

• In 19c it can be configured to also run outside of the maintenance window

• To control it, the DBMS_SPM.CONFIGURE procedure supports a new parameter

• AUTO_SPM_EVOLVE_TASK (OFF, ON, AUTO)

• In 19c AUTO is equivalent to ON

• When enabled, it runs every hour for no longer than 30 minutes

How to check :

column parameter_value format a45

column parameter_name format a25

set pages 200

SELECT PARAMETER_NAME, PARAMETER_VALUE

  FROM   DBA_ADVISOR_PARAMETERS

  WHERE  TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK'

    AND PARAMETER_VALUE <> 'UNUSED'

  ORDER BY 1;

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines;

The new default AUTO for ALTERNATE_PLAN_BASELINE and ALTERNATE_PLAN_SOURCE enables the Automatic SQL Plan Management in Oracle Database 19c. The attribute ALTERNATE_PLAN_SOURCE lets the advisor identify the sources for plans by itself. The ALTERNATE_PLAN_BASELINE parameter determines which SQL statements are eligible for plan capture. AUTO in this case means that any SQL statement in AWR will be a potential candidate.

How to  revert to the Oracle 12.2.0.1   Behavior : 

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,

      parameter => 'ALTERNATE_PLAN_BASELINE', 

      value     => 'EXISTING'); 

END; 

/

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_SOURCE', 

      value     => 'CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY'); 

END; 

/

BEGIN

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_LIMIT',

      value => 10);

END;

/

Switching to the Oracle 19c  behaviour 

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,

      parameter => 'ALTERNATE_PLAN_BASELINE', 

      value     => 'AUTO');  

END; 

/

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_SOURCE', 

      value     => 'AUTO');  

END; 

/

BEGIN

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_LIMIT',

      value => 'UNLIMITED');

END;

/

Reference : 

https://blogs.oracle.com/optimizer/post/what-is-automatic-sql-plan-management-and-why-should-you-care

Oracle Awr data dump -- Important while migration

 

 

Most of time after migration  of database to new server we observe performance degradation . Usually we dont have historical performance data before migration which land us is big mess.

To overcome this , its very important to  take awr dump from existing database before migration . 

Extract AWR data

For Extract the AWR data from source database run the awrextr.sql script which extracts AWR data for a range of snapshots from the database into a Data Pump export file.

 

1. Run the following script for extract AWR:

@?/rdbms/admin/awrextr.sql;

2. Script ask for select DBID

Enter value for dbid:

3. Enter the number of days backup export:

Enter value for num_days: 2

4. It will list the 2 days snapshot in AWR. Choose the begin and end snapshot for export:

Enter value for begin_snap: 76

Begin Snapshot Id specified: 76

Enter value for end_snap: 86

End Snapshot Id specified: 86

5. List the Directory present in Database, Choose the directory location and dump file name:

Enter value for directory_name: ORACLE_HOME

Enter value for file_name:awrdat_76_86.dmp

6. Now dump is generated.

The AWR extract dump file will be located

in the following directory/file:

 /usr/tmp/

awrdat_76_86.dmp

Load the AWR data to target

For loading the extracted AWR data with awrload.sql script. It will first create a staging schema where the snapshot data is transferred

from the Data Pump file into the database

1. Run the AWRload.sql script for start loading data.

@?/rdbms/admin/awrload.sql

2. Specify the directory name where export file exists.

Enter value for directory_name:

3. Put the prefix of name of dump file:

Enter value for file_name:

4. Specify the name of staging schema where data loaded i.e AWRSTAGE

Enter value for schema_name:

5. Specify the default or temporary tablespace.

Enter value for default_tablespace: SYSAUX

-----------

Enter value for temporary_tablespace: TEMP

6. Loading of data is successful.

AWR Load Started ..

This operation will take a few moments. The

progress of the AWR load operation can be

monitored in the following directory/file:

/usr/tmp/

AWRDAT_76_86.log

The process will then prompt for the staging schema name, the default is AWR_STAGE. If you accept the default, the script will create the AWR_STAGE user after asking you for default tablespaces. Once it has completed the awr load process, the script will drop the AWR_STAGE user.

After the process completes, the AWR tables now have new data in them! You can query DBA_HIST_SNAPSHOT or any of the other DBA_HIST views (including DBA_HIST_ACTIVE_SESS_HISTORY). 

Estimating Size of Oracle Database Objects --seeing history of object/table size

Resource and performance capacity of the servers is one side of the puzzle. Equally important is to size/estimate the database for storage and the data growth. This would mean the database, the database objects, and the underlying storage subsystem would also have to be sized for today and tomorrow.

Oracle provides few packages and procedures that help determine the size of objects and indexes based on the   estimated growth size. Even further, using the DBMS_SPACE.OBJECT_GROWTH_TREND function, a growth pattern for existing tables can be obtained.

The following query will list the object growth trend for an object; the data for the trend listed is gathered from Automatic Workload Repository (AWR). The growth trends for two of the tables are listed following.

The OBJECT_GROWTH_TREND function returns four values:

• TIMEPOINT—Is a time stamp value indicating the time of the recording/reporting.

• SPACE_USAGE—Lists the amount of space used by the object at the given point in time.

• SPACE_ALLOCATED—Lists the amount of space allocated to the object in the table space at the

given point in time.

• QUALITY—Indicates the quality of data reported; there are three possible values:

• INTERPOLATED—The value did not meet the criteria of GOOD. As noted in the outputs

following, the used and allocated are same. Basically, the values do not reflect any usage.

• GOOD—The value whenever the value of TIME is based on recorded statistics. Value is

marked good if at least 80% of the value is derived from GOOD instance values.

• PROJECTED—The value of time is in the future as of the time the table was produced.

In a RAC environment, the output reflects the aggregation of values recorded across all instances in the cluster.

SELECT *

FROM TABLE(dbms_space.object_growth_trend(object_owner => 'ABDUL',

 object_name => 'HISTORY', object_type => 'TABLE'));

TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY

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

28-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

29-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

30-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

31-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

01-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

02-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

03-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED

04-JUN-14 11.12.43.052162 AM 131877793 134217728 GOOD

05-JUN-14 11.12.43.052162 AM 132003569 134369941 PROJECTED

06-JUN-14 11.12.43.052162 AM 132129344 134522153 PROJECTED

07-JUN-14 11.12.43.052162 AM 132255119 134674366 PROJECTED

08-JUN-14 11.12.43.052162 AM 132380894 134826579 PROJECTED

09-JUN-14 11.12.43.052162 AM 132506669 134978791 PROJECTED

Pga Nightmare in Oracle 19c database -- MGA / ORA-04030 / ORA-04036

Had  to  write this  Blog  Considering Lot of Pga issues we  been facing   after migration from 12c to 19c . 

This is  because   MGA is also  Part of   Pga  now .    To get more information on  Mga please refer  2638904.1 

In Oracle release 12.1:    Pga  setting was  the greater of the following:

1. 2 GB 

2. 200% of PGA_AGGREGATE_TARGET 

3. (Value of PROCESSES initialization parameter setting) * 3 MB

It will not exceed 120% of the physical memory size minus the total SGA size.

In Oracle release 12.2:   Pga  setting was as per below 

* If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.

* If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.

* If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the   total SGA size.

In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter for standby  database , and for Rac database it should be at least 5MB times the PROCESSES parameter

From 19c we need to  add Mga overhead to Pag  using below formula .   Please refer to metalink document 2808761.1  for more information . 

PGA_AGGREGATE_LIMIT = (original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected processes) * 4M)

Sql  used for analysis . 

select * from dba_hist_pgastat where name ='total PGA allocated' order by snap_id;

set lines 200;

set pages 200;

column name format a25;

column pname format a12;

column "MegaBytes" format a10;

set numwidth 6;

select s.sid, s.serial#,p.pid, p.spid,p.pname, sn.name, round(ss.value/(1024 *1024))||'Mb' "MegaBytes"

from v$sesstat ss, v$statname sn, v$session s, v$process p

where s.paddr = p.addr

and sn.statistic# = ss.statistic#

and s.sid = ss.sid

and sn.name in ('session pga memory' , 'session pga memory max')

and p.pname like 'DIA%'

order by ss.value

/

col max_pga for 99999999.9

select value/1024/1024 max_pga from v$pgastat where name='maximum PGA allocated' minus

     select value/1024/1024 max_pga from v$pgastat where name='MGA allocated (under PGA)';

select max_utilization from v$resource_limit where resource_name='processes';

set pages 333 lines 255

set wrap off

col "Sid/Serial" for a12

col UnixPid for a8

col OraUser for a8

col OsUser for a8

col machine for a18

col Minutes for 99990.9

col "ClientProgram" format a28

col "ServerProgram" format a32

col "Program" format a25

col "Module" format a25

col "SQL Query" format a60

col "MBmem" format 99999

col "App PID" for a8

col "SPID" for a8

BREAK ON REPORT

COMPUTE SUM LABEL "Total PGA MB" OF  "MBmem" ON REPORT

select /* Running SQL */ distinct 

       s.INST_ID "INST_ID",s.sid||','||s.serial# "Sid/Serial", s.sql_id,p.spid "SPID",s.process "App PID", s.username "DBUser",s.osuser "OSUser", s.machine,

       s.last_call_et "Time(Sec)", 

       q.sql_text "SQL Query", p.pga_alloc_mem/1024/1024 "MBmem"

from gv$session s,gv$process p, gv$sql q

where s.INST_ID=p.INST_ID and

      s.paddr=p.addr and

      s.sql_id=q.sql_id(+)

and s.username is not null

and s.sql_id is not null

and s.status <> 'INACTIVE'

and q.sql_text not like '%Running SQL%'

order by "INST_ID","Time(Sec)" desc;

 List of Related Bug :

Bug 32521805 : ORA-4030 IN MGA, MGA 20% OF PGA_AGGREGATE_LIMIT

BUG 30611650 - HIGH PGA USAGE FOR A QUERY USING XMLFOREST was filed for this kind of problem.

Bug 30028599 - ORA-4036: PGA memory used by the instance exceeds pga_aggregate_limit (Doc ID 30028599.8)

Database Failures and Hangs in RAC due to MGA Sizing and Allocation Issues (Doc ID 2831121.1)

Related  Metalink Document : 

Sizing the PGA in Oracle 19c - How to Account for the MGA Size (Doc ID 2808761.1)

MGA (Managed Global Area) Reference Note (Document 2638904.1)

Cannot Increase or Decrease The Value of PGA_AGGREGATE_LIMIT on 19c (Doc ID 2685564.1)

MMON SLAVE Process Consuming More PGA Memory in 19c (Doc ID 2721971.1)

Exacc dataguard switchover and Failover using dbaascli

 

Pre-Checks 

--> Confirm DGMGRL status is ok . And no Lag 
DGMGRL> show configuration
DGMGRL> show database standby_sb2
DGMGRL> validate database  verbose standby_sb2

select database_role  , switchover_status  from v$database;  ( on booth primary and  standby ) 
select INST_ID , DEST_ID , max(SEQUENCE#) from gv$archived_log group by INST_ID , DEST_ID ;

ALTER DATABASE SWITCHOVER TO Standby VERIFY; 

sudo dbaascli dataguard status --dbname DBNAME --details yes   ( Put Exact db name as in crs ) 
Logfile will be under /var/opt/oracle/log/dbaastools_launcher/



Below are pre check based on known issues faced :

ISSUE – Parameter missing in .INI
FIX – Add parameters ( dg_observer=no and dg_observer_host=no) are added in file /var/opt/oracle/creg/<dbname>.ini

ISSUE – Password in Wallet is not sync between PRIMARY and STANDBY
FIX – Copy the cwallet.sso with correct password to STANDBY node.





Switchover 

sudo dbaascli dataguard switchover --dbname  DBNAME( Put Exact db name in crs ) 

Failover 

sudo dbaascli dataguard failover --dbname  DBNAME( Put Exact db name in crs ) 

Post-checks 

--> Confirm DGMGRL status is ok . And no Lag 
DGMGRL> show configuration
DGMGRL> show database frkvasp1_sb2
DGMGRL> validate database  verbose standby_sb2

select database_role  , switchover_status  from v$database;  ( on booth primary and  standby ) 
select INST_ID , DEST_ID , max(SEQUENCE#) from gv$archived_log group by INST_ID , DEST_ID ;

sudo dbaascli dataguard status --dbname DBNAME--details yes

Reference : 

https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/dbaascli-dataguard-switchover.html

https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/administer-data-guard-configuration.html#GUID-9D5B2117-AA20-4468-A5AC-BD2E624C8844