How Oracle 11g modifies the automatic collection statistics ratio at the table level

Editor to share with you Oracle 11g how to modify the table-level automatic collection of statistical information ratio, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Before 11g, when the amount of data in the table is modified by more than 10% of the total data, statistics will be collected automatically at night.

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.

Statistics collection window time:

11g is Monday to Friday 22:00-2:00 Saturday Sunday 6:00-2: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

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

Current statistics collection rate of the query table:

SQL > select dbms_stats.get_prefs ('STALE_PERCENT',null,'S') from dual

DBMS_STATS.GET_PREFS ('STALE_PERCENT',NULL,'S')

ten

SQL > select to_char (LAST_ANALYZED,'yyyy-mm-dd hh34:mi:ss') from DBA_TAB_STATISTICS where OWNER = 'ENTER' and TABLE_NAME =' S'

TO_CHAR (LAST_ANALYZ

-

2017-09-10 00:26:10

SQL > select * from dba_tab_modifications where table_name='S' and TABLE_OWNER='ENTER'

TABLE_OWNER TABLE_NAME

PARTITION_NAME SUBPARTITION_NAME INSERTS

-

UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS

-

ENTERPRISE S

360773

280456 0 03-OCT-17 NO 0

SQL > SELECT s.tableroomnamereparentials. Numroomrowswritings.lastworthy analyzed FROM dba_tables s where s.ownerclassENTER' and s.tableroomnamestations'

TABLE_NAME NUM_ROWS LAST_ANAL

S 29907073 10-SEP-17

You can see that the S table has 29907073 rows of data, 360773 rows of INSERT, and 280456 rows of UPDATE. Because of the large amount of data in the table, 10% is a lot of data, which is very likely to cause inaccurate statistical information.

So it was changed to 1%.

Go to the specified user to modify:

SQL > conn ENTER/XXX

Connected.

SQL > exec dbms_stats.set_table_prefs (null,'S','STALE_PERCENT',1)

PL/SQL procedure successfully completed.

SQL > select dbms_stats.get_prefs ('STALE_PERCENT',null,'S') from dual

DBMS_STATS.GET_PREFS ('STALE_PERCENT',NULL,'S')

one

The statistics collection ratio of table S has been changed to 1%.

The above is all the contents of the article "how Oracle 11g modifies the automatic statistics collection rate at the table level". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!