Oracle DBA Daily Experiences

DBA_TAB_MODIFICATIONS view contains tables that were modified since last time statistics was gathered on the table. It contains the number of inserts, deletes and updates have occurred on the table. In 10g this is turned on automatically when the statistics_level is typical/all.

By calling DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO one can flush monitoring information from memory into dictionary. When DBMS_STATS.GATHER_*_STATS is called it calls DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO as one of the step to flush modification information.

In the example below it shows when dbms_stats.gather_schema_stats is called it flushes modification information in the dictionary.

SQL> select timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;

TABLE_NAME TIMESTAMP
—————————— ——————–
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

SQL> create table scott.table3( x number );

SQL> insert into scott.table3 values ( 10 );

1 row created.

– insert few rows in table3
SQL> /

..

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_schema_stats(‘SYS’);

PL/SQL procedure successfully completed.

SQL> select table_name, timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;
TABLE_NAME TIMESTAMP
—————————— ——————–
TABLE3 09-FEB-2010 22:23:10
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

Information on DBA_TAB_MODIFICATIONS
SQL> desc dba_tab_modifications;
Name Null? Type
—————————————– ——– —————————-
TABLE_OWNER VARCHAR2(30) — table owner
TABLE_NAME VARCHAR2(30) — table name
PARTITION_NAME VARCHAR2(30) — partition name
SUBPARTITION_NAME VARCHAR2(30) — sub partition name
INSERTS NUMBER — number of inserts in the table
UPDATES NUMBER — number of updates in the table
DELETES NUMBER — number of deletes in table
TIMESTAMP DATE — timestamp when the table was
TRUNCATED VARCHAR2(3) — if table was truncated
DROP_SEGMENTS NUMBER — if segments were dropped

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’ is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set to “BASIC” table monitoring is disabled. In 10g when the value of statistics_level is “typical” or “all” table monitoring is turned enabled. In 9i one can enable/disable table monitoring by calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/
– shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE
…

Note: To find schema level stats that are stale one can call DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

The view has the monitoring information about tables user_tab_modifications, all_tab_modifications and dba_tab_modifications.

To use automatic shared memory management, the value of statistics_level must be set to “typical” or “all”. The parameter can be changed without restarting the database.

The following views shows information on SGA resize operations:
V$SGA_RESIZE_OPS – the view contains information about the last 400 completed SGA resize operations.
V$SGA_DYNAMIC_COMPONENTS – Shows current size, minimum size, maximum size, last operation (LAST_OPER_TYPE value: GROW, SHRINK, STATIC), OPER_COUNT (# of times SGA operations performed, at startup the value is 0) during the instance started of SGA components.
V$SGA_DYNAMIC_FREE_MEMORY – Shows free memory available for future SGA memory resize.
V$SGA_CURRENT_RESIZE_OPS – Currently SGA resize operations in progress.
V$SGAINFO – has usage and free size informaton on SGA size of different components.
V$SGASTAT – detail information on usage of SGA
V$SGA_DYNAMIC_COMPONENTS – information on dynamic components of SGA
V$SGA_TARGET_ADVICE – gives information on tuning of SGA_TARGET

Automatic memory management has it’s own background process (mman). It monitors the instance to find the best memory allocation for SGA.

If the database is iin automatic shared memory enabled the value of statistics_level cannot be changed to basic. If the value of sga_target is not set or if the value is 0 automatic shared memory management, statistics_level can be set to basic which means automatic memory management is turned off.

SQL> alter system set statistics_level=basic;
alter system set statistics_level=basic
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

If SGA_MAX_SIZE is not set or it is less than SGA_TARGET then Oracle will SGA_MAX_SIZE to be the same value as SGA_TARGET. If one tries to increase SGA_TARGET to more than SGA_MAX_SIZE then you will get ORA-00823 so you may want to set SGA_MAX_SIZE which will allow one to increase SGA with restart of the instance.

SQL> show parameter sga;

NAME TYPE VALUE
———————————— ———– ——————————
..
sga_max_size big integer 160M
sga_target big integer 160M

SQL> alter system set sga_target=170M;
alter system set sga_target=170M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size