Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

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

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

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!

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report