I didn't quite understand the use of MONITORING. From what I read in the Oracle books, it enables automatic statistics collection in the SGA and from time to time, they are flushed to the data dictionary.

Seems you're saying it only keeps track of which specific tables are being modified.

Please clarify...

Tom,

Is there an algorithm Oracle uses to establish if the
statistics is fresh or not. Any combination of total
# of rows to total # of DMLS etc..

Thanks

Tom,

Now I see what you mean. In fact, the Oracle material and online books are not very clear about this feature. Even the 8i classes I attended did not cover this subject.

Thanks a lot !!!

Tom,

If a table is set to MONITORING and its statistics are stale, will Oracle then ignore the current statistics until the tables are re-analyzed?

Tom,

Once a table has been tagged as having stale statistics, does the optimizer still use the current statistics?

I was experementing with this feature and noticed performance problems with queries until I re-analyzed the tables with stale statistics.

Thanks

Tom,

I don't agree with what you mentioned about the stale statistics. Firstly, without using MONITORING, I analyze all my tables once a week. When I set the tables to be MONITORING, I had a script to analyze tables with stale statistics every 6 hours.

With MONITORING set, some of the queries would run slow but would run ok if I went and manually analyzed the table with stale statistics.

From this I would assumed that its not using stale statistics. I have given up on this and have gone back to doing a full analyzed once a week.

Cheers

tom,

i tried the following, i wonder why there are no rows in the SYS.DBA_TAB_MODIFICATIONS ..please let me know where i went wrong.


ge$scott@workflow.us.oracle.com>ALTER TABLE EMP MONITORING;

Table altered.

ge$scott@workflow.us.oracle.com>analyze table SCOTT.EMP compute statistics;

Table analyzed.

ge$scott@workflow.us.oracle.com>select table_name, num_rows, blocks, avg_row_len from dba_tables where table_name='EMP';

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
============================== ========== ========== ===========
EMP 28 1 40

ge$scott@workflow.us.oracle.com>alter table SCOTT.EMP monitoring;

Table altered.

ge$scott@workflow.us.oracle.com>select table_name, monitoring from dba_tables where owner='SCOTT' ORDER BY 2
2 ;

TABLE_NAME MON
============================== ===
A NO
B NO
BIG NO
BONUS NO
PLAN_TABLE NO
SALGRADE NO
CHAINED_ROWS NO
DEPT NO
EMP YES

9 rows selected.

ge$scott@workflow.us.oracle.com>INSERT INTO EMP SELECT * FROM EMP;

28 rows created.

ge$scott@workflow.us.oracle.com>COMMIT;

Commit complete.

ge$scott@workflow.us.oracle.com>SELECT * FROM SYS.DBA_TAB_MODIFICATIONS;

no rows selected


i was expecting a record saying 28 inserts. please clarify

Dear Tom,

Suppose I have a table T and it undergoes lot of inserts and deletes within a short period of time. dbms_stats.SET_TABLE_STATS may not give the correct info as this table is real volatile. what would you recommend, in this case?

1, dbms_stats.SET_TABLE_STATS
2, monitoring turned on for the table and gather statistics every few minutes if the stats is stale?
3, something else ...?

Thanks

My question is about your reply to the initial poster. You said that you didn't need monitoring if you know the table is heavily updated.

If I have monitoring on for tables a, b, and c, but off for table d, would the gather stale option only get stats for tables a-c? In which case I would need to use the gather_table_stats for d.

1. Is a record created in USER_TAB_MODIFICATIONS for EACH transaction?

2.If yes, when will this data be deleted, concerned that the amount of data might become a load?

3.Can the data in _TAB_MODIFICATIONS be deleted?

4.Does DBMS_STATS.GATHER_sTALE option gets all tables that are between their LAST_ANALYZED and _TAB_MODIFICATIONS.TIMESTAMP to do the analyzes?

Hi Tom,

Could you please clarify the last answer:

   4) objects are only in the tab modifications view 
      if they are in need of fresh stats...

I did the folloeing test (full details further below):

    o gather table statistics on table T
    o update only 1 row (out of 65k) 
    o flush stats
    o query the user_tab_modifications

There was a row present in the user_tab_modifications for
table T (because of the FLUSH). Does this mean that the 
stats are stale? They shouldn't be because only 1 out
of 65K rows were changed. If they are considered stale,
then I guess FLUSH() defeats the whole monitoring purpose
and should be avoided...


SQL> desc t

 Name       Null?    Type
 ---------- -------- ----------
 I                   NUMBER(38)

SQL> select count(*) from t;

  COUNT(*)
----------
     65536

SQL>  exec DBMS_STATS.GATHER_TABLE_STATS('dwadm',tabname=>'T', estimate_percent=>5);

PL/SQL procedure successfully completed.


SQL> exec print_table('select * from user_tab_modifications where table_name=''T''');
TABLE_NAME                    : T
PARTITION_NAME                :
SUBPARTITION_NAME             :
INSERTS                       : 0
UPDATES                       : 1
DELETES                       : 0
TIMESTAMP                     : 09-dec-2004 10:09:35
TRUNCATED                     : NO
DROP_SEGMENTS                 : 0
-----------------

SQL>  update t set i=8 where rownum<2;

1 row updated.


SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.



SQL>  exec print_table('select * from user_tab_modifications where table_name=''T''');
TABLE_NAME                    : T
PARTITION_NAME                :
SUBPARTITION_NAME             :
INSERTS                       : 0
UPDATES                       : 1
DELETES                       : 0
TIMESTAMP                     : 09-dec-2004 10:14:00
TRUNCATED                     : NO
DROP_SEGMENTS                 : 0
-----------------

PL/SQL procedure successfully completed.
 

Hi Tom,

I finally found the answer (in Performance & Tuning Guide).
A table can be present in the user_tab_modifications but
it does not mean it is stale.

"Objects are considered stale when 10% of the 
 total rows have been changed. When you issue 
 GATHER_TABLE_STATS with GATHER STALE, the procedure 
 checks the USER_TAB_MODIFICATIONS view. If a monitored 
 table has been modified more than 10%, then statistics 
 are gathered again. The information about changes of 
 tables, as shown in the USER_TAB_MODIFICATIONS view, 
 can be flushed from the SGA into the data dictionary 
 with the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 
 procedure." 

The following simple test also agreed with the documentation:

SQL>  select table_name,last_analyzed from user_tables 
      where table_name='T';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
T                              2004-12-09 10:13:10

SQL> exec print_table('select * from user_tab_modifications where table_name=''T''');
TABLE_NAME                    : T
PARTITION_NAME                :
SUBPARTITION_NAME             :
INSERTS                       : 0
UPDATES                       : 1
DELETES                       : 0
TIMESTAMP                     : 09-dec-2004 10:14:00
TRUNCATED                     : NO
DROP_SEGMENTS                 : 0
-----------------

PL/SQL procedure successfully completed.


SQL> select sysdate from dual;

SYSDATE
-------------------
2004-12-09 10:53:13


SQL>  exec DBMS_STATS.GATHER_SCHEMA_STATS('DWADM',OPTIONS=>'gather stale');

PL/SQL procedure successfully completed.

SQL>  select table_name,last_analyzed from user_tables
      where table_name='T';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
T                              2004-12-09 10:13:10

SQL> exec print_table('select * from user_tab_modifications where table_name=''T''');
TABLE_NAME                    : T
PARTITION_NAME                :
SUBPARTITION_NAME             :
INSERTS                       : 0
UPDATES                       : 1
DELETES                       : 0
TIMESTAMP                     : 09-dec-2004 10:14:00
TRUNCATED                     : NO
DROP_SEGMENTS                 : 0
-----------------

PL/SQL procedure successfully completed.

Thanks 

Steve 

Hi Tom,
We have DW partitioned MVs. There are very rare cicumstances that old data (month_key -partition key) will be updated. But every day, current partition will be effected. Can I do monitoring only on the partition? so that gathering of stats should be done only for partition which has stale stats (which will be most of the time current partition), as this will save lots of time, instead of gathering stats for whole table!
Thanks

Hi Tom,
Thanks for quick response.
We have month_key as partition key, and queries will make sense only if partition key is used in the queries, thus I have created local partitioned indices. But my problem is, how do I know for which partition to gather stats? I thought monitoring on partition (if it is there!) will be quite cool!
Thanks

Hi Tom,
This is DW system, and users submit adhoc queries from reporting tool, which has predicate for month_key.

Hi Tom,
We don't have control over DW adhoc queries, and possible answer is 'NO'

Hi Tom,
It would be good to know the answer both ways...
Thanks

Hi Tom,
Thanks for your patience, I will check, and get back to you.
Regards

Hi Tom,

Can I alter a materialized view to monitoring, for similar reasons as I would intend to do for a table?

Thanks and Regards
Prashant

Hi Tom,

I am sorry I wasn't clear, e.g If I truncate and reload a partition in a table for which monitoring is turned on, then by looking at user_tab_modifications I can find out which partition to analyze. I believe I can do similarly for a partitioned MV as well, by saying
alter table mv_name monitoring;

Is that correct?

Thanks and Regards
Prashant

Hi Tom,

In the above followup I asked, I have one more question. I have a materialized view MV A E.g.
MV A -> FAST REFRESH
MV A -> Altered to monitoring.

I refresh MV A by fast refresh, will it also show up in user_tab_modifications. Since its a fast refresh I believe it will not truncate MV partitions, so will querying on user_tab_modifications be correct for this scenario, to find out changed partitions and then analyze the same.

Thanks and Regards
Prashant

Hi Tom,

while gathering the stats with GATHER STALE,oralce will look into user_tab_modifications for checking stale tables.

1.If the approx. count has been logged in user_tab_modifications for every 3 hours,what will happen to the modifications while gathering between this time since the view shows null columns?
2.do we need flush before gathering stats to show the user_tab_modifications up-to-date,else we might loose a bit stats? right?

All I ever wanted to know and more. Thanks for a full explanation on the monitoring and stale stats scenario.

Hi Tom how can you know the # of dmls per table on 10gr2

There is the view dba_advisor_sqlw_colvol,
but neither creating tunning tasks(of updates for examples) the table
SYS.WRI$_ADV_SQLW_COLVOL(referenced by the previous view) is updated.

How could get those tables updated so I could know the dmls per table.

Thanks


CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;
ANALYZE TABLE TEST COMPUTE STATISTICS;
UPDATE OYM.TEST SET OBJECT_ID=-OBJECT_ID;
COMMIT;
GRANT ADVISOR TO CACHITO;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'UPDATE OYM.TEST SET OBJECT_ID=-OBJECT_ID WHERE OBJECT_ID > :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'OYM',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task2',
description => 'Task to tune a query on a specified employee');
END;
/
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task2' );
END;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task2') FROM DUAL;

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = 'OYM';
SELECT * FROM DBA_ADVISOR_TASKS WHERE TASK_NAME='my_sql_tuning_task2';

SELECT * FROM dba_advisor_sqlw_colvol;
-- empty
SELECT * FROM SYS.WRI$_ADV_SQLW_COLVOL;
-- empty

Tom tHanks for that :)

Tom,
We a have a volatile table with high insert/update/delete. Several thousand queries executed on this table in an hour. We gather statistics on this table every night.
There is a purge job deletes rows those not required for business.
One case, statistics gathered when rows are 100(just after purge and alter table move) so oracle decided to full table scan. In 2 hours, they reached around 100,000. Queries started running very slow because full table scan.
Could you please recommend what is the best practice to gather statistics in these cases?
Thank you,
V

Tom.
Forogt to mention that database 9.2.0.6.
Thanks again
V

I was confused about table level monitoring for SAP / SAP BW applications... This helps..

Hi Tom,
As I understand when I enable monitoring on a table, then stats jobs gathers only stale stats( above 10% change). Please see the below listing, I am missing something(in 9i)

SQL> 
SQL> drop table t;

Table dropped.

SQL> 
SQL> create table t (c number);

Table created.

SQL> 
SQL> alter table t monitoring;

Table altered.

SQL> 
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> 
SQL> INSERT INTO T SELECT ROWNUM FROM DBA_TABLES WHERE ROWNUM<101;

100 rows created.

SQL> 
SQL> BEGIN
  2  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> 
SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname => 'T', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED,NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='T';

LAST_ANALYZED          NUM_ROWS                                                                                                                                                                         
-------------------- ----------                                                                                                                                                                         
18-feb-2009 13:40:40        100                                                                                                                                                                         

SQL> 
SQL> INSERT INTO T SELECT ROWNUM FROM DBA_TABLES WHERE ROWNUM<2;

1 row created.

SQL> 
SQL> BEGIN
  2  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname => 'T', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED,NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='T';

LAST_ANALYZED          NUM_ROWS                                                                                                                                                                         
-------------------- ----------                                                                                                                                                                         
18-feb-2009 13:40:42        101                                                                                                                                                                         

SQL> 
SQL> spool off;

when I inserted only one row ( which is less that 10% of 100 rows) also why oracle regathered the stats?

Please clarify. Thank you for your time.

Hi Tom,
Thank you for clarifying that. I wonder why "options" parameter is not available with gather_tables_stats.

Hi Tom,
I am using

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS
(
ownname    => 'CRIS',
estimate_percent   =>  DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt    => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
options   => 'GATHER STALE',
DEGREE   => 4,
CASCADE   => TRUE,
granularity   => 'DEFAULT'
);
END;

to gather stats. I have a table TRA with monitoring ON. I checked in ALL_TAB_MODIFICATIONS that for NO partition has changed over 10%

select * from
(
  select A.PARTITION_NAME,
 (
  (INSERTS+UPDATES+DELETES) / 
  (SELECT NUM_ROWS FROM ALL_TAB_PARTITIONS 
  B WHERE TABLE_NAME='TRA' AND B.PARTITION_NAME=A.PARTITION_NAME) 
)*100 as percentage
 from all_tab_modifications A where table_name ='TRA'
 ) where percentage > 10

This returns no rows. Now when I executed the above stats gathering block and i noticed some thing like below statement being executed.

SELECT /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring parallel(t,4)  parallel_index(t,4) */
       COUNT (*), COUNT ("TRA_TRAD_AMT"), SUM (VSIZE ("TRA_TRAD_AMT")),
.....
  FROM "CRIS"."TRA" SAMPLE (19.4452929663) t

Are the stats for TRA gathered again here? Could you please clarify?

Thank you.

Hi Tom,
Thanks for the reply
I checked the dba_tables and found that the table actually analyzed again.

Now my doubt is " Why are the statistics re gathered this table even though modifications are less that 10%"

I don't know if I have to consider other factors while deciding the percentage of modifications ??

Thank You

Thanks Tom I will use the full monitoring info procedure. Th

Tom, in February, Chinni asked why the options parameters is not available with gather_table_stats, and you said that if you are targeting a single table, you MUST want to gather statistics for it.

I would suggest that there are exceptions to that rule. I have a table with weekly partitions, even if someone does implement any archiving it will have at least 6 months of rolling data (this is PeopleSoft Time & Labour). This table is processed by a scheduled nightly batch program, as well as adhoc processes on smaller subsets of data.

It is necessary that the statistics are up to date on the partition for the current week when the first batch of the week is run. If the stats show that the partition has no rows when in truth is has lots, the database understandably chooses the wrong execution plan and the batch runs several hours longer.

We cannot rely on the automatic stats job to refresh stale stats at the right time (we have run in this problem during testing). Because I know of no way to gather stale stats on just a named table, my solution has been to write a PL/SQL procedure to flush_database_monitoring_info and then use the information in user_tab_modifications to determine which partitions are stale, and gather stats for those partitions. This procedure is then called from the batch process that processes this table.

My point is that I want to target a single table, but I don't want to collect stats for lots historical partitions that haven't changed. If the automatic stats job has run and refreshed the statistics, then I'm not going to bother doing the work again.

Or, do you think this approach is flawed?

Hi Tom,
With alter table monitoring we can monitor dml only.
What would be the way to monitor sql?
We have large historic partitioned table in the database and we'd like to see if anyone is accessing it to take opportunity and reduce history.
Thanks

Thanks Tom!
This is great. Once we find out segment is accessed, what would be the best way to find out what process was running select against the segment.

Hi Tom,

In the nightly batch process we truncate and load 60+ tables. There are underlining Materialize view which gets complete refreshed after this.

Do we need to collect stats for the table before refresh? Does truncate drop the stats?

What should be the optimal methodology for performing this type of activity?

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     72386

ops$tkyte%ORA11GR2> truncate table t;

Table truncated.

ops$tkyte%ORA11GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     72386