| TABLE STATS and Oracle Monitoring [message #60088] |
Thu, 15 January 2004 03:30  |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Folks,
We just moved from 8.0 to 9i and one of the task is to start using DBMS_STATS instead of traditional analyze to get stats. We used to run Analyze on all objects everyday.
Since 9i has the <SPAN style="FONT-SIZE: 8.5pt; COLOR: black; FONT-FAMILY: 'Courier New'; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">'GATHER AUTO' option to gather stats on only modified objects, I would like to turn the monitoring on for all tables and indexes so that dba_tab_modification table is kept uptodate.
Could some one tell me if this has any performance impact?
Thanks
VS
|
|
|
|
| Re: TABLE STATS and Oracle Monitoring [message #60092 is a reply to message #60088] |
Thu, 15 January 2004 06:24  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi,
There is no performance impact with the DBMS_STATS, in fact it is better way of capturing Statistics. Ya U are right Gather Auto will collect both Stale and New statistics for all tables. Its good to enable monitoring on Schema itself rather than individual tables and indexes by using
sql > dbms_stats.ALTER_SCHEMA_TAB_MONITORING('EXAMPLE'
,true);
I some times feel all modifications not populated into user_tab_modifications immediately.
SO I use
SQL> exec dbms_stats.flush_database_monitoring_info;
Which populates user_tab_modifications.
DBMS_STATS uses this information to collect statistics.
I knew I ran into more than What you hv asked for. I felt like sharing this info too.
Regards
Prasad
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
