Tales From A Lazy Fat DBA

Love all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Archive for November, 2021

Materialized Views Refresh slow ? This is what you can try …

Posted by FatDBA on November 22, 2021

Hi All,

Entire last week I was looking in to one system wide performance issue where customer reported slowness in their database since they’d moved into a new infrastructure, after data migration. Though this wasn’t the production system, but kind of equally important, and the issue was impacting their acceptance tests. I was updated that few of the Materialized Views are there and their refresh times are now extended by a great margin, since they moved to this new system.

As soon I got the access on their system, I did a quick check on the database and I found a huge list of MV refresh jobs are scheduled and almost all of them are running with a huge delay, the performance was not at all consistent, sometimes they gets refreshed within few minutes and another run goes for few hours. This initially sounded little bizarre to me, until I did a comparive stuy between the previous (fast) and the new (slow) databases. What I observed is that there is more than 500% of extra data there in the business tables in this new (slow) database and with such a huge data, all those complex MV refresh queries ultimately got slowed down here in the new system.

For an example, one of the critical MV taking ~ 200 mins to refresh an is created with COMPLETE REFRESH mode.

create MATERIALIZED VIEW MV_MYTEST_BIGREFRESH
BUILD DEFERRED
REFRESH ON DEMAND
COMPLETE
AS
select abc,xyz,cast(value as ajaaj) as test ....
............
.................



SELECT * 
FROM ( SELECT OWNER, 
              MVIEW_NAME, 
              CONTAINER_NAME, 
              REFRESH_MODE, 
              LAST_REFRESH_TYPE, 
              STALENESS, 
              round((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS 
       FROM ALL_MVIEWS 
       WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')
     ) 
ORDER BY REFRESH_TIME_MINS DESC;
 
OWNER   MVIEW_NAME                       CONTAINER_NAME                   REFRESH_MODE LAST_REFRESH_TYPE STALENESS           REFRESH_TIME_MINS
------- -------------------------------- -------------------------------- ------------ ----------------- -------------------------------------
PRASH   MV_MYTEST_BIGREFRESH             MV_MYTEST_BIGREFRESH             DEMAND        COMPLETE      COMPLETE                196.75

Though I did multiple optimizations in order to stabilize performance, but would like to talk about few of the refresh tuning techniques that I have applied and finally worked for me.

Atomic_Refresh with value FALSE.
What caught my eye are all those long running DELETE statements running underneath those costly MV refresh jobs. This was the very first thing I tried with those complex and long running COMPLETE MV refresh, this is the optional parameter atomic_refresh of the procedure dbms_mview.refresh and is very useful. If the parameter is set to FALSE, the materialized view is deleted with a much faster TRUNCATE command. I immediately got the gain, as the runtime was dropped ~ 60% .. At the same time I have observed a great relax in redo generation as DELETE is replaced with TRUNCATE, and hence a great drop in ‘checkpoint incomplete‘ and ‘Log File Sync‘ waits.

The parameter atomic_refresh=FALSE works only with “complete” refresh, so “truncate” is only possible with “complete“. But there is one demerit too for this method, and is that no data is visible to the users during the refresh, so, choose wisely. If this is feasible in your environment, you can use the following command for a Complete Refresh:

BEGIN
dbms_mview.refresh(‘MV_MYTEST_BIGREFRESH_JOB’, method => ‘C’, atomic_refresh => FALSE);
END;


-- See below the REFRESH_TIME_MINS got dropped by ~ 60% after using setting atomic_refres = false
SELECT * 
FROM ( SELECT OWNER, 
              MVIEW_NAME, 
              CONTAINER_NAME, 
              REFRESH_MODE, 
              LAST_REFRESH_TYPE, 
              STALENESS, 
              round((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS 
       FROM ALL_MVIEWS 
       WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')
     ) 
ORDER BY REFRESH_TIME_MINS DESC;
 
OWNER   MVIEW_NAME                       CONTAINER_NAME                   REFRESH_MODE LAST_REFRESH_TYPE STALENESS           REFRESH_TIME_MINS
------- -------------------------------- -------------------------------- ------------ ----------------- -------------------------------------
PRASH   MV_MYTEST_BIGREFRESH             MV_MYTEST_BIGREFRESH             DEMAND        COMPLETE      COMPLETE                79.12

Adding PX (Parallelism) to the base tables or to the refresh command itself.
Though with atomic_refrsh FALSE, I was able to cut down a great percentage of the overall elapsed time, but still customer asked for me, maybe some 30% more …
I decided to try PX to the base tables or to the refresh statement, among other parameters, this procedure takes one parameter PARALLELISM. At the outset, it appears that the PARALLELISM parameter should invoke a parallel refresh of the materialized view. However, it does not; it simply changes the requested degree of parallelism if it already executes in parallel. You can invoke this procedure as:

BEGIN
dbms_mview.refresh(‘MV_MYTEST_BIGREFRESH_JOB’, method => ‘C’, atomic_refresh => FALSE, PARALLELISM=>4); 
END;



-- See below the REFRESH_TIME_MINS got dropped by further ~30% after using PARALLELISM to the master/base MV tables.
SELECT * 
FROM ( SELECT OWNER, 
              MVIEW_NAME, 
              CONTAINER_NAME, 
              REFRESH_MODE, 
              LAST_REFRESH_TYPE, 
              STALENESS, 
              round((LAST_REFRESH_END_TIME-LAST_REFRESH_DATE)*24*60,2) as REFRESH_TIME_MINS 
       FROM ALL_MVIEWS 
       WHERE LAST_REFRESH_TYPE IN ('FAST','COMPLETE')
     ) 
ORDER BY REFRESH_TIME_MINS DESC;
 
OWNER   MVIEW_NAME                       CONTAINER_NAME                   REFRESH_MODE LAST_REFRESH_TYPE STALENESS           REFRESH_TIME_MINS
------- -------------------------------- -------------------------------- ------------ ----------------- -------------------------------------
PRASH   MV_MYTEST_BIGREFRESH             MV_MYTEST_BIGREFRESH             DEMAND        COMPLETE      COMPLETE                 55.08
  

You can also add PX to the base tables. When the master table of the materialized view has the PARALLEL attribute set to > 1, then the creation as well as the refresh processes will be parallelized. Whether or not you specify the PARALLELISM parameter in the REFRESH clause, doesn’t matter.

ALTER TABLE DIXIT_TEST_TABLE PARALLEL (DEGREE 4);

You can also add PX hint to the MV definition too! When the materialized view is created with a PARALLEL hint, then the creation as well as the refresh processes, will be parallelized. Whether or not you specify the PARALLELISM parameter in the REFRESH clause, doesn’t matter.
This is how you can ad a parallel hint in the materialized view definition!

CREATE MATERIALIZED VIEW TEST_MV
AS
SELECT /*+ PARALLEL(DIXIT_TEST_TABLE, 4) */ MONTHLY_ID, SALARY_DATE, SUM(ANNUAL_COUNTS)
FROM DIXIT_TEST_TABLE GROUP BY MONTHLY_ID, SALARY_DATE;

Stats Collection:
This is very vital that all of the tables, Indexes which your MV refresh references via the code, should be fresh and well collected. Look of for possibilities to add column level stats i.e Histograms wherever there is a scope, specially for low cardinality columns. You can also leave this to the optimizer to decide, by using METHOD_OPT=>’FOR ALL COLUMNS SIZE AUTO’

Other techniques:

  • Try for FAST/Incremental Refresh mode. With FAST refresh, only the changes since the last refresh are applied to the materialized view. Personally I have found the FAST refresh slower than the COMPLETE refresh mode, maybe because I used atomic_refresh parameter that replaced those costly DELETEs with fast TRUNCATE … Not sure why they called it FAST 🙂 To be sure that my materialized view can be fast refresh, we can also use explain_mview procedure and check the capability_name called “REFRESH_FAST”. The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following script ..
SQL> @$ORACLE_HOME/rdbms/admin/utlxmv.sql

SQL> exec dbms_mview.explain_mview('MV_MYTEST_BIGREFRESH_JOB');
PL/SQL procedure successfully completed.
 
SQL> select capability_name,possible,related_text,msgtxt from mv_capabilities_table;

CAPABILITY_NAME                POSSIBLE             RELATED_TEXT            MSGTXT
------------------------------ -------------------- ----------------------- ----------------------------------------------------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y <<<<<<<<---------------
REWRITE                        Y
PCT_TABLE                      N                    ID_ID_CANADA_HISTORY_T1 relation is not a partitioned table
PCT_TABLE                      N                    ID_ID_CANADA_HISTORY_TV relation is not a partitioned table
PCT_TABLE                      N                    DWH_SITE                relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_PCT               N                                            PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N                                            general rewrite is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE              N                    ID_ID_CANADA_HISTORY_T1 relation is not a partitioned table
PCT_TABLE_REWRITE              N                    ID_ID_CANADA_HISTORY_TV relation is not a partitioned table
PCT_TABLE_REWRITE              N                    DWH_SITE                relation is not a partitioned table
 
18 rows selected.
  • Out-of-place refresh is a new option on DBMS_MVIEW. REFRESH in Oracle 12c. The complete refresh process builds a new table which seamlessly becomes the materialized view, the old table is dropped. It only works in conjunction with non-atomic refresh.
  • DBMS_ADVISOR contains a procedure called TUNE_MVIEW that is used when working with the query rewrite mechanism. DBMS_MVIEW.EXPLAIN_MVIEW and DBMS_MVIEW.EXPLAIN_REWRITE that could be used to diagnose why a materialized view wasn’t being used for query rewrite. The DBMS_ADVISOR.TUNE_MVIEW procedure has the capability to add additional aggregate columns and materialized view logs to the view definition so that it becomes fast refreshable.

Well, all after I’ve performed above three methods, I was able to reduce the MV refresh downtimes by more than 92% … 🙂

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | Leave a Comment »

Redis cluster node with status DISCONNECTED – [ERR] Not all 16384 slots are covered by nodes, and a crude method to deal that …

Posted by FatDBA on November 8, 2021

Hi All,

Today’s post doesn’t follow the usual problem-solution approach, but about a hit and trial that fixed my problem on Redis. This is about a strange problem which I’d encountered while doing a POC, when one of the cluster node, after reboot stopped responding and coming with status as ‘disconnected‘. This was a multi master configuration (4 master nodes) with no slaves, Redis version was 6.2.6 on RHEL7. For simulation purposes, I have invoked four different Redis server instances on the same node/machine using separate ports. One running on port 6391 is not responding.

Note: This is just a test I did on one sandbox environment and it’s might not match your problem, I recommend not to try these steps in production as they are just part of hit-and-trial that I did.

-- Four different Redis Server Instances running on same host.
[root@fatdba redis-cluster]# ps -ef|grep redis
root       3337   3177  0 23:14 pts/1    00:00:03 redis-server 127.0.0.1:6391 [cluster]
root       3763   3177  0 23:20 pts/1    00:00:01 redis-server 127.0.0.1:6392 [cluster]
root       3814   3177  0 23:21 pts/1    00:00:01 redis-server 127.0.0.1:6394 [cluster]
root       4001   3177  0 23:24 pts/1    00:00:01 redis-server 127.0.0.1:6390 [cluster]



-- one with port 6391 is with status 'disconnected'.
[root@fatdba node0]# redis-cli -a secret -p 6390 cluster nodes
bdcc3381941e141c89110bbcffe66c73bc927054 :0@0 master,noaddr - 1635737041203 1635737041203 2 disconnected 4096-8191
8751bde72e08fd1b08cc34c82cd5868475320a6a 127.0.0.1:6392@16392 master - 0 1635737634979 3 connected 8192-12287
a2b89ff5be2eafeba1ae3aea78f871d0ccc16283 127.0.0.1:6390@16390 myself,master - 0 1635737633000 1 connected 0-4095
c548fe11041b636d627cafffd23f8ad642ac4085 127.0.0.1:6394@16394 master - 0 1635737634000 4 connected 12288-16383
[root@fatdba node0]#

Cluster check command is showing details only for three healthy masters and throwing error at the end of the check '[ERR] Not all 16384 slots are covered by nodes'. I tried to remove the problematic node using CLUSTER FORGET and re-add it to the cluster using addnode but even that didn’t worked, ideally it should, but it didn’t, and it was still coming with status ‘disconnected‘.

Meanwhile the 'cluster info' command showing known nodes as 4 with cluster state as OK.

[root@fatdba redis-cluster]# redis-cli -a secret --cluster check 127.0.0.1:6394
127.0.0.1:6394 (c548fe11...) -> 0 keys | 4096 slots | 0 slaves.
127.0.0.1:6390 (a2b89ff5...) -> 0 keys | 4096 slots | 0 slaves.
127.0.0.1:6392 (8751bde7...) -> 0 keys | 4096 slots | 0 slaves.
[OK] 0 keys in 3 masters.
0.00 keys per slot on average.
>>> Performing Cluster Check (using node 127.0.0.1:6394)
M: c548fe11041b636d627cafffd23f8ad642ac4085 127.0.0.1:6394
   slots:[12288-16383] (4096 slots) master
M: a2b89ff5be2eafeba1ae3aea78f871d0ccc16283 127.0.0.1:6390
   slots:[0-4095] (4096 slots) master
M: 8751bde72e08fd1b08cc34c82cd5868475320a6a 127.0.0.1:6392
   slots:[8192-12287] (4096 slots) master
[OK] All nodes agree about slots configuration.
>>> Check for open slots...
>>> Check slots coverage...
[ERR] Not all 16384 slots are covered by nodes.



127.0.0.1:6391> cluster info
cluster_state:ok
cluster_slots_assigned:16384
cluster_slots_ok:16384
cluster_slots_pfail:0
cluster_slots_fail:0
cluster_known_nodes:4
cluster_size:4
cluster_current_epoch:4
cluster_my_epoch:1
cluster_stats_messages_ping_sent:3342
cluster_stats_messages_pong_sent:3732
cluster_stats_messages_sent:7074
cluster_stats_messages_ping_received:3732
cluster_stats_messages_pong_received:3339
cluster_stats_messages_received:7071

So, we tried all possibilities but still our problem endured …

Now, if you have carefully checked the syntax that I’ve used to connect with any of the nodes is via -a option, as there was the password set, so every time it asks me to input the passcode. Just for the trial purpose I removed the password (PassThru parameter in Redis config file) and recreated the cluster, and oddly that trick worked. Of course that is something you will never do in production setups, but can try when learning the database or if not running on production and you’ve the luxury to play around with the database – Just a crude method to deal the issue 🙂

Still it’s incomprehensible for me about how this password removal thing fixed the issue 😦 or maybe I was doing some mistake, I am perplexed and will be troubleshooting it further …

[root@fatdba node0]# redis-cli --cluster create 127.0.0.1:6390 127.0.0.1:6391 127.0.0.1:6392 127.0.0.1:6394 --cluster-replicas 0
>>> Performing hash slots allocation on 4 nodes...
Master[0] -> Slots 0 - 4095
Master[1] -> Slots 4096 - 8191
Master[2] -> Slots 8192 - 12287
Master[3] -> Slots 12288 - 16383
M: 4c9785e37002cf340842ee176d21244497898d4a 127.0.0.1:6390
   slots:[0-4095] (4096 slots) master
M: ce3c377b47d78f45cf824355926a4763eb4eb6fa 127.0.0.1:6391
   slots:[4096-8191] (4096 slots) master
M: 08f772eab05b03b5559b15af0848822a383093d9 127.0.0.1:6392
   slots:[8192-12287] (4096 slots) master
M: 923df80e2201c6c35de10560dc04420b5742ea58 127.0.0.1:6394
   slots:[12288-16383] (4096 slots) master
Can I set the above configuration? (type 'yes' to accept): yes
>>> Nodes configuration updated
>>> Assign a different config epoch to each node
>>> Sending CLUSTER MEET messages to join the cluster
Waiting for the cluster to join
...
>>> Performing Cluster Check (using node 127.0.0.1:6390)
M: 4c9785e37002cf340842ee176d21244497898d4a 127.0.0.1:6390
   slots:[0-4095] (4096 slots) master
M: 923df80e2201c6c35de10560dc04420b5742ea58 127.0.0.1:6394
   slots:[12288-16383] (4096 slots) master
M: ce3c377b47d78f45cf824355926a4763eb4eb6fa 127.0.0.1:6391
   slots:[4096-8191] (4096 slots) master
M: 08f772eab05b03b5559b15af0848822a383093d9 127.0.0.1:6392
   slots:[8192-12287] (4096 slots) master
[OK] All nodes agree about slots configuration.
>>> Check for open slots...
>>> Check slots coverage...
[OK] All 16384 slots covered.

-- Let's check once again the cluster status
127.0.0.1:6391> cluster nodes
4c9785e37002cf340842ee176d21244497898d4a 127.0.0.1:6390@16390 master - 0 1635742000349 1 connected 0-4095
923df80e2201c6c35de10560dc04420b5742ea58 127.0.0.1:6394@16394 master - 0 1635741998338 4 connected 12288-16383
ce3c377b47d78f45cf824355926a4763eb4eb6fa 127.0.0.1:6391@16391 myself,master - 0 1635741998000 2 connected 4096-8191
08f772eab05b03b5559b15af0848822a383093d9 127.0.0.1:6392@16392 master - 0 1635741999344 3 connected 8192-12287
127.0.0.1:6391>


127.0.0.1:6391> cluster keyslot prashant
(integer) 6942

127.0.0.1:6391> keys *
(empty array)

-- No keys, let me try to create one string type key.
127.0.0.1:6391> set prashant dixit
OK

127.0.0.1:6391> get prashant
"dixit"

Hope It Helped
Prashant Dixit

Posted in troubleshooting | Tagged: , , , | 1 Comment »

Confused about using PURGE_ALL to remove optimizer statistics from the SYSAUX ?

Posted by FatDBA on November 7, 2021

Hi All,

Recently while working in one for the project where SYSAUX was growing at an alarming rate, customer operations team decided to purge old stats from the SYSAUX as that was occupying around 50% of the total occupancies. Though MMON purges these stats regularly from the OPTSTAT tables, but due to a known bug (14373728) it failed to that. I mean ideally it should purge these statistics according to the default retention, which is 30 days in this case, but it seem it can not due to that bug. MMON performs these purge activities automatically, but it has a limit of 5 minutes to perform these activities. So if the purging takes more than 5 mins, then the activities are aborted and as a result the stats are not purged.

Due to some reasons they don’t want to apply the patch, but agreed to purge all stats from the OPTSTAT related tables within SYSAUX. But, the team was very perplexed to perform the PURGE_ALL as a big-bang (not using sysdate-x) approach, as they weren’t sure if PURGE_ALL will remove all the stats including current statistics on objects (Tables, Indexes etc.) or it only touches OPTSTAT tables and truncates them.

About optimizer stats tables (OPTSTAT in short), they contain backup information about stats collection and involves tables like WRI$_OPTSTAT_OPR, WRI$_OPTSTAT_AUX_HISTORY, WRI$_OPTSTAT_TAB_HISTORY, WRI$_OPTSTAT_IND_HISTORY, WRI$_OPTSTAT_HISTGRM_HISTORY, WRI$_OPTSTAT_HISTHEAD_HISTORY.

Even the comments from Oracle customer support has raised the confusion, as their explanation was too hazy and with lot of muddiness about using EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL). So, I decided to do a quick demo to clear all the doubts around this subject.

All below mentioned tests were performed on Oracle 21c database.

-- Here I first created a sample table and generated 10000 random rows. 
[oracle@fatdba ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Nov 3 23:53:14 2021
Version 21.3.0.0.0


SQL> CREATE TABLE tab1 AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 0
         ELSE TRUNC(DBMS_RANDOM.value(1,10))
       END AS record_type,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 10000;

Table created.



SQL> select count(*) from tab1;

  COUNT(*)
----------
     10000


SQL> ALTER TABLE tab1 ADD CONSTRAINT tab1_pk PRIMARY KEY (id);

Table altered.


SQL> CREATE INDEX tab1_record_type_idx ON tab1(record_type);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

PL/SQL procedure successfully completed.


SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'TAB1_RECORD_TYPE_IDX');

PL/SQL procedure successfully completed.

Okay, so we have a table ready with 10000 random rows with a primary key constraint and have created one index on record_type column and have gathered stats on the table and the index. Next, I will purge all stats from OPTSTAT tables using PURGE_ALL function

23:13:05 SQL> EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.13
23:13:09 SQL>


-- Now the same can be confirmed by querying OPTSTAT tables WRI$_OPTSTAT_IND_HISTORY and  WRI$_OPTSTAT_TAB_HISTORY for Tables and Indexes, 
-- And they all are gone, no more data is there in these tables after flush.

23:15:55 SQL> select OBJ#, flags, rowcnt, ANALYZETIME from WRI$_OPTSTAT_IND_HISTORY where obj#=77267;

no rows selected

23:16:01 SQL> select FLAGS,ROWCNT, OBJ#,ANALYZETIME from WRI$_OPTSTAT_TAB_HISTORY where obj#=77256;

no rows selected

Now, lets check if tables and index has the stats collection date coming or not, and yes they are there! means still the table has their current stats and didn’t got purged after we executed PURGE_ALL on OPTSTAT tables.

23:13:37 SQL> select index_name, index_type, last_analyzed from dba_indexes where index_name='TAB1_RECORD_TYPE_IDX';

INDEX_NAME                     INDEX_TYPE                  LAST_ANALYZED
------------------------------ --------------------------- --------------------
TAB1_RECORD_TYPE_IDX           NORMAL                      03-NOV-2021 23:12:07

Elapsed: 00:00:00.00

23:13:50 SQL> select table_name, last_analyzed from dba_tables where table_name='TAB1';

TABLE_NAME           LAST_ANALYZED
-------------------- --------------------
TAB1                 03-NOV-2021 23:08:32

Elapsed: 00:00:00.00

I am now going to recollect stats on both of them, Tables and its Index, and with that, the last_analyzed date/time is refreshed with the latest timestamp.

repeating same steps for TABLE. And one more time, I see that the TABLE HISTORY related OPTSTAT table has only previous/historical values not the latest timestamp from stats collection.
I mean it should should have ’03-NOV-2021 23:26:07′ as a latest entry in the table, but has only got ’03-NOV-2021 23:17:28′ when last stats were collected.

23:25:54 SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

PL/SQL procedure successfully completed.


23:26:15 SQL> select table_name, last_analyzed from dba_tables where table_name='TAB1';

TABLE_NAME           LAST_ANALYZED
-------------------- --------------------
TAB1                 03-NOV-2021 23:26:07


23:26:08 SQL> select FLAGS,ROWCNT, OBJ#,ANALYZETIME from WRI$_OPTSTAT_TAB_HISTORY where obj#=77256;

     FLAGS     ROWCNT       OBJ# ANALYZETIME
---------- ---------- ---------- --------------------
        10      10000      77256 03-NOV-2021 23:08:32
        10      10000      77256 03-NOV-2021 23:17:28 ----> previous value, not the latest timestamp of 03-NOV-2021 23:26:07









-- One more time, for Table ....

23:29:05 SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

PL/SQL procedure successfully completed.


23:29:11 SQL> 23:29:11 SQL>  select table_name, last_analyzed from dba_tables where table_name='TAB1';


TABLE_NAME           LAST_ANALYZED
-------------------- --------------------
TAB1                 03-NOV-2021 23:29:07


23:29:07 SQL> select FLAGS,ROWCNT, OBJ#,ANALYZETIME from WRI$_OPTSTAT_TAB_HISTORY where obj#=77256;


     FLAGS     ROWCNT       OBJ# ANALYZETIME
---------- ---------- ---------- --------------------
        10      10000      77256 03-NOV-2021 23:08:32
        10      10000      77256 03-NOV-2021 23:17:28
        10      10000      77256 03-NOV-2021 23:26:07 ----> previous value, not the latest timestamp of 03-NOV-2021 23:29:0

Let’s do this on Indexes!

23:32:26 SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'TAB1_RECORD_TYPE_IDX');

PL/SQL procedure successfully completed.

23:32:34 SQL> select index_name, index_type, last_analyzed from dba_indexes where index_name='TAB1_RECORD_TYPE_IDX';

INDEX_NAME                     INDEX_TYPE                  LAST_ANALYZED
------------------------------ --------------------------- --------------------
TAB1_RECORD_TYPE_IDX           NORMAL                      03-NOV-2021 23:32:27


23:32:33 SQL> select OBJ#, flags, rowcnt, ANALYZETIME from WRI$_OPTSTAT_IND_HISTORY where obj#=77267;

      OBJ#      FLAGS     ROWCNT ANALYZETIME
---------- ---------- ---------- --------------------
     77267         10      10000 03-NOV-2021 23:12:07
     77267         10      10000 03-NOV-2021 23:31:37 ---> Has only got '03-NOV-2021 23:31:37' which is a previous timestamp/history, not the latest of 03-NOV-2021 23:32:27

So, from above tests it’s clear that the PURGE_ALL only touches and truncates WRI$_OPTSTAT_XXXX tables and as they always have the previous stats collection details, your present stats on objects will always be there, untouched and safe. So, this command is very safe to run and is best when you are thinking of doing an iterative purge using SYSDATE-X as it’s always very slow as compared to PURGE_ALL which truncates the table and doesn’t delete records from it, and the reason of its agility.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , | Leave a Comment »

 
%d bloggers like this: