Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the knowledge of automatically collecting statistical information in Oracle 11g system". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn "what is the knowledge of Oracle 11g system automatically collecting statistical information?"

Before 11g, when the amount of data in the table is modified by more than 10% of the total data, statistics will be automatically collected at night. How to judge 10%. Previous posts have studied: the collection principle and experiment of Oracle automatic statistics. This STALE_PERCENT=10%, cannot be modified. If the table is very large, 10% is a lot of data, which is very likely to cause inaccurate statistical information.

After 11g, STALE_PERCENT=10% can be modified, divided into global (DBMS_STATS.SET_GLOBAL_PREFS) and table level (DBMS_STATS.SET_TABLE_PREFS). Table-level settings:

Modified to 5% (range 1-100): exec dbms_stats.set_table_prefs (null,'table_name','STALE_PERCENT',5)

Restore to 10%: exec dbms_stats.set_table_prefs (null,'table_name','STALE_PERCENT',null)

% query: select dbms_stats.get_prefs ('STALE_PERCENT',null,'table_name') from dual

-11g is Monday to Friday 22:00-2:00 Saturday Sunday 6:00-4:00

SELECT w.window_name, w.repeat_interval, w.duration, w.enabled

FROM dba_autotask_window_clients c, dba_scheduler_windows w

WHERE c.window_name = w.window_name

AND c.optimizer_stats = 'ENABLED'

WINDOW_NAME REPEAT_INTERVAL DURATION

-

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 + 000 20:00:00

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 + 000 20:00:00

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

-- disable automatic collection

Exec DBMS_AUTO_TASK_ADMIN.DISABLE (client_name = > 'auto optimizer stats collection',operation = > NULL,window_name = > NULL)

-enable automatic collection

Exec DBMS_AUTO_TASK_ADMIN.ENABLE (client_name = > 'auto optimizer stats collection',operation = > NULL,window_name = > NULL)

-- View job

Select client_name,status from dba_autotask_client

CLIENT_NAME STATUS

-

Auto optimizer stats collection ENABLED

Auto space advisor ENABLED

Sql tuning advisor ENABLED

-- collect statistics for all users under the current database

Exec dbms_stats.gather_database_stats (user)

-- collect statistics of all objects under the current database user

Exec dbms_stats.gather_schema_stats (user)

-- collect statistics from data dictionaries

Exec dbms_stats.gather_dictionary_stats ()

-- when the system has large partition tables, it will be slow to collect all of them all all the time. After 11g, you can set INCREMENTAL to collect only those partitions where the data changes.

Exec dbms_stats.set_table_prefs (user,'table_name','INCREMENTAL','TRUE');-- partitions that collect only changes in data

Exec dbms_stats.set_table_prefs (user,'table_name','INCREMENTAL','FALSE');-- all must be collected

Select dbms_stats.get_prefs ('INCREMENTAL',null,'table_name') from dual;-- to view the value of the partition table INCREMENTAL

-- get the statistics collection setting options for global

Select dbms_stats.get_prefs ('method_opt') from dual

Select dbms_stats.get_prefs ('concurrent') from dual

Select dbms_stats.get_prefs ('GRANULARITY') from dual

Select dbms_stats.get_prefs ('INCREMENTAL') from dual

-- set the statistics collection options for global

Exec DBMS_STATS.SET_PARAM ('DEGREE',4)

Exec DBMS_STATS.SET_PARAM ('INCREMENTAL','TRUE')

Thank you for your reading, the above is the content of "what is the knowledge of Oracle 11g system automatically collecting statistical information". After the study of this article, I believe you have a deeper understanding of the knowledge of Oracle 11g system automatically collecting statistical information, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.