Archives August 2023

MVIEW REFRESH GROUP EXPLAINED

Refresh group is nothing but a package or tool to refresh a group of similar MVIEWS to keep them consistent with refresh.

To create a refresh group on a materialized view or group of mviews, then use the following procedure.

BEGIN
 DBMS_REFRESH.MAKE(name => 'XSCALIBAL',
 list => 'MT,RPMV',
 next_date => sysdate,
 interval => 'sysdate+ 1/1440') ;
END;
/

Check the MVIEWS which are placed under refresh group XSCALIBAL

SQL> col OWNER for a20
SQL> col NAME for a20
SQL> col RNAME for a20
SQL> col INTERVAL for a20
SQL> set lines 200 pages 1000
SQL> SELECT owner,name,rname,interval FROM all_refresh_children;

OWNER                NAME                 RNAME                INTERVAL
-------------------- -------------------- -------------------- --------------------
SYS                  MT                   XSCALIBAL            sysdate+ 1
/1440
SYS                  RPMV                 XSCALIBAL            sysdate+ 1/1440

To manually start the refresh, use refresh function.

BEGIN
 DBMS_REFRESH.REFRESH(name => 'XSCALIBAL') ;
END;
/

There may be situations where the mview refresh happen recursively even though the refresh is not needed To disable the mview refresh group temporarily or permanently, use the following procedure.

BEGIN
 DBMS_REFRESH.CHANGE( name => 'XSCALIBAL',
 next_date => NULL,
 interval => 'NULL') ;
END;
/ 

AWR Snapshots And AWR Objects Are Inconsistent Between Min AND Max Snap ID’S

Snap id from base snapshot table and the certain AWR objects are not consistent between each other with minimum and maximum snapshot totally different from snap_id column of snapshot table wrm$_snapshot.

SQL> SELECT min(snap_id),max(snap_id) FROM wrm$_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         749          755


SQL> SELECT min(snap_id),max(snap_id) FROM wrh$_sqltext;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         393          805

SQL> SELECT min(snap_id),max(snap_id) FROM wrh$_active_session_history;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         739          755

SQL> SELECT min(snap_id),max(snap_id) FROM wrh$_sql_bind_metadata;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         749          805

SQL> SELECT min(snap_id),max(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         749          755

Both wrm$_snapshot and wrh$_sql_bind_metadata are compared to find the excess rows in wrh$_sql_bind_metadata which seems to be like a mismatch.

SQL> SELECT distinct a.snap_id FROM wrh$_sql_bind_metadata a WHERE a.snap_id not in (SELECT snap_id FROM wrm$_snapshot) ORDER BY a.snap_id;

   SNAP_ID
----------
       757
       758
       759
       760
       761
       762
       763
       764
       765
       766
       767

   SNAP_ID
----------
       768
       769
       770
       771
       772
       774
       775
       776
       777
       778
       779

   SNAP_ID
----------
       780
       781
       782
       783
       784
       785
       786
       787
       788
       789
       790

   SNAP_ID
----------
       791
       792
       793
       794
       795
       796
       797
       798
       799
       800
       801

   SNAP_ID
----------
       802
       803
       804
       805
       806

Wondered if baseline can be the cause for inconsistency but there is no user created baseline apart from system moving window which is inbuilt.

SQL> col BASELINE_NAME for a20
SQL> SELECT BASELINE_ID,BASELINE_NAME,START_SNAP_ID,END_SNAP_ID,BASELINE_TYPE from wrm$_baseline;

BASELINE_ID BASELINE_NAME        START_SNAP_ID END_SNAP_ID BASELINE_TYPE
----------- -------------------- ------------- ----------- -------------
          0 SYSTEM_MOVING_WINDOW                           MOVING_WINDOW

In case, if there are orphaned rows for tables wrh$_sqltext or wrh$_sql_plan or wrh$_sqlstat, they can be purged with dbms_workload_repository.purge_sql_details little by little. The argument value are number of rows which are before the retention period. Example: If the number of orphaned rows are 1 for wrh$_sqltext, then that one row will be deleted beyond retention. All the rows inside the retention period which are needed for use are contained and not removed.

SQL> EXEC dbms_workload_repository.purge_sql_details(1,&dbid);
Enter value for dbid: 1185489427

PL/SQL procedure successfully completed.

SQL> SELECT count(*) FROM wrh$_sqltext;

  COUNT(*)
----------
      4142

SQL> SELECT count(*) FROM wrh$_sqlstat;

  COUNT(*)
----------
      4655

SQL> SELECT count(*) FROM wrh$_sql_plan;

  COUNT(*)
----------
     31478

Cause:

The SNAP_ID column in the following tables is distinct from the WRM$_SNAPSHOT column with the same name: WRH$_SQLTEXT, WRH$_SQL_PLAN, WRH$_DATAFILE, and WRH$_TEMPFILE. These tables reflect the most recent (so far) snapshot that has data for the questioned SQL.

There are two primary categories of tables for the AWR:
Standard snapshot tables. Data is recorded in snapshots. We merely need to look at the snapshot id to purge these tables.

(2) Data-normalized tables. Tables like WRH$_SQLTEXT, WRH$_SQL_PLAN, WRH$_DATAFILE, and WRH$_TEMPFILE fall under this category. We only record this information once because the SQL text or file name may be quite extensive, and this information is shared by numerous rows in the base snapshot tables.The snap_id in these tables refers to a previous snapshot rather than the snapshot where the data was gathered. Given that this is fuzzy logic, the latest snapshot at which the normalised data is referred need not be precise.

Summary:

The snap_id in WRH$_SQLTEXT should not be compared to the dropped snapshots.