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.