Values populated in DBA_TAB_MODIFICATIONS

 Goal :

The goal is to explain why the view DBA_TAB_MODIFICATIONS  does sometimes have no values

even when the parameter STATISTICS_LEVEL  is set to TYPICAL and  the specific schema has been analyzed successful using the package DBMS_STATS.GATHER_SCHEMA_STATS.

In addition all the tables in that schema shows MONITORING=YES in the view dba_tables.


Fix:

The updates to the table *_tab_modifications are related to the volumne of updates for a table.

There is a need of approximatly 10% of datavolumn changes. Just only on single update of the row for example might not lead to fill the *_tab_modifications.


Example :

SQL>  create table test ( num  varchar2(32));

Table created.


Lets insert 100 rows to table test:


SQL>  begin

  2  for i in 1..100 loop

  3  insert into sys.test values (i);

  4  end loop;

  5  commit;

end;  6

  7  /

PL/SQL procedure successfully completed.


SQL> select count(*) from test;

  COUNT(*)

----------

       100


Gather stats for this table:


SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST');

PL/SQL procedure successfully completed.


SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';

OWNER                          TABLE_NAME                     STA   NUM_ROWS

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

    BLOCKS EMPTY_BLOCKS

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

SYS                            TEST                           NO         100

         1            0


SQL>  select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';


no rows selected


Now lets manually flush the modifications from SGA:


Note: The procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO flushes in-memory monitoring information for all tables in the dictionary.

Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS

views are updated immediately, without waiting for the Oracle database to flush them periodically (per default every 3 hours). This procedure is useful when you need up-to-date information in those views.


SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.


SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';

OWNER                          TABLE_NAME                     STA   NUM_ROWS

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

    BLOCKS EMPTY_BLOCKS

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

SYS                            TEST                           NO         100

         1            0


Now lets insert 1000 more values and check if it put entry in dba_tab_modifications:


SQL> begin

 for i in 1..1000 loop

  insert into sys.test values (i);

 end loop;

  commit;

 end;

  /

  2    3    4    5    6    7

PL/SQL procedure successfully completed.


SQL> select count(*) from test;

  COUNT(*)

----------

      1100


SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';

OWNER                          TABLE_NAME                     STA   NUM_ROWS

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

    BLOCKS EMPTY_BLOCKS

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

SYS                            TEST                           NO         100

         1            0

no rows selected


SQL>  select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';

no rows selected


SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';

OWNER                          TABLE_NAME                     STA   NUM_ROWS

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

    BLOCKS EMPTY_BLOCKS

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

SYS                            TEST                           NO         100

         1            0


SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.


SQL> SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';

OWNER                          TABLE_NAME                     STA   NUM_ROWS

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

    BLOCKS EMPTY_BLOCKS

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

SYS                            TEST                           YES        100

         1            0


SQL>  select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';

TABLE_OWNER                    TABLE_NAME

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

PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS

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

   UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS

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

SYS                            TEST

                                                                    1000

         0          0 19-JUN-12 NO              0


Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more