Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Nov 24, 2003

Oracle Optimizer: Moving to and working with CBO - Part 5 - Page 3

By Amar Kumar Padhi



11. DML Monitoring



This option is used to automate the updating of statistics as tables are updated. When this option is enabled for a table, Oracle monitors the DML changes (including truncates) being done on the table and maintains the details in the SGA. Every three hours (or after a shutdown), the SMON process incorporates the information collected in the SGA into the data dictionary.



In Oracle 9i, this time is reduced to 15 minutes. Optionally, the routine DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO could be used.

The collected information can be viewed from the DBA_TAB_MODIFICATIONS table. This will contain statistics for stale tables that had more than 10% changes in the rows. Gathering statistics using DBMS_STATS with the GATHER STALE option will then update statistics for these tables only.

This option can be enabled at table level with the ALTER command or when creating the table. In Oracle 9i, the DBMS_STATS package has been provided with routines to enable the Monitoring option. If you are on 9i, it is recommended that you make use of DBMS_STATS to carry out all of your statistics operations.

alter table am20 monitoring;

e.g.: Monitoring option for table AM01.
SQL> exec dbms_stats.gather_table_stats('ACS', 'AM01')

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks, empty_blocks, avg_space, 
  2         avg_row_len, sample_size, last_analyzed
  3  from   dba_tables
  4  where  table_name = 'AM01';

NUM_ROWS BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
-------- ------ ------------ ---------- ----------- ----------- ---------
524288    795              0          0           3      524288 18-SEP-03

SQL> alter table am01 monitoring;

Table altered.

SQL> select monitoring from dba_tables where table_name = 'AM01';

MON
---
YES

SQL> select * from dba_tab_modifications;

no rows selected

--DML activities were carried on the table.
--The database was bounced.

SQL> select table_owner, table_name, inserts, updates, 
  2         deletes, timestamp, truncated
  3  from   dba_tab_modifications;

TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
----------- ---------- ------- ------- ------- --------- ---
ACS               AM01 1028577       0   19999 19-SEP-03 NO

SQL> exec dbms_stats.gather_schema_stats(ownname => 'ACS', -
>  options => 'GATHER STALE');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks, empty_blocks, avg_space, avg_row_len, 
  2         sample_size,Last_analyzed
  3  from   dba_tables
  4  where  table_name = 'AM01';

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
-------- ------ ------------ --------- ----------- ----------- ---------
 2057154   3112            0         0           3     2057154 19-SEP-03

SQL> select table_owner, table_name, inserts, updates, deletes, 
  2         timestamp, truncated
  3  from dba_tab_modifications;

no rows selected

In general, there is no implied way of knowing the tables that are being changed significantly. Normally, statistics are explicitly generated for the complete database when changes have occurred in only a few tables. Monitoring is a powerful option if your database is in a similar situation.

I have not come across any SGA issues related to enabling of this option. The memory used by this option is very nominal and should not result in any performance issues.

I presently use this option for tables that are frequently being changed and the optimizer results fluctuate with changes in the tables. Evaluate this option for your database.

In the next installment, we will look at Hints that are used to direct the optimizer for choosing execution plans directly from the code.

» See All Articles by Columnist Amar Kumar Padhi



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date