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

This article mainly explains "what is the method of viewing and collecting oracle statistical information". The content of the 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 method of viewing and collecting oracle statistical information".

1. View table statistics

SQL > alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';SQL > select t.TABLENAMEBook t.NUMparts ROWSMagneT.BLOCKSMent.LASTRANALYZED from user_tables t where table_name='WOO'

Note: view statistics through script, refer to MOS:SCRIPT-Select to show Optimizer Statistics for CBO (Doc ID 31412.1)

Second, view the index information on the table

Select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t where table_name='DUMP_TABLE'; SQL > col table_name format a11SQL > col index_name format a16SQL > select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed 2 from user_indexes t 3 where table_name='DUMP_TABLE' TABLE_NAME INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS LAST_ANAL--DUMP_TABLE WORNUM_IND 0 10 1 18-MAR-20

Check the current statistical information collection strategy

Set linesize 140col WINDOW_NAME format a17col REPEAT_INTERVAL for a55col DURATION for a15select T1. Windowchart names where t1.window_name=t2.window_name and t2.window_group_name in T1. Repeatables intervalrect 1. Recording from dba_scheduler_windows T1 revising dbathing trainerwingroupnames members T2 where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED') SQL > set linesize 140SQL > col WINDOW_NAME format a17SQL > col REPEAT_INTERVAL for a55SQL > col DURATION for a15SQL > select t1.windowkeeper name where t1.window_name=t2.window_name t1. Repeatability intervalrecy t1. Repeatability from 2 dba_scheduler_windows t1 Lottery dbaqingroupmembers t2 3 where t1.window_name=t2.window_name 4 and t2.window_group_name 5 in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED') WINDOW_NAME REPEAT_INTERVAL DURATION- MONDAY_WINDOW freq=daily;byday=MON;byhour=22 Byminute=0; bysecond=0 + 000 04:00:00TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 + 000 04:00:00WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 + 000 04:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 + 000 04:00:00FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 + 000 04:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 + 000 20:00:00SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 + 00020 04:00:00WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 00007 rows selected.

3.1. Turn off automatic statistics collection

BEGIN DBMS_SCHEDULER.DISABLE (name = >'"SYS". "SATURDAY_WINDOW"', force = > TRUE); END;/

3.2 modify the duration of automatic statistics

BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE (name = >'"SYS". "SATURDAY_WINDOW"', attribute = > 'DURATION', value = > numtodsinterval (180 minutes minute)); END; /

3.3 modify the automatic statistics start time, starting at 22:00 every Saturday

BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE (name = >'"SYS". "SATURDAY_WINDOW"', attribute = > 'REPEAT_INTERVAL', value = >' freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0'); END;/

3.4 enable automatic statistics collection

BEGIN DBMS_SCHEDULER.ENABLE (name = >'"SYS". "SATURDAY_WINDOW"'); END;/

3.5 check again whether the policy is correct

Set linesize 200col REPEAT_INTERVAL for a60col DURATION for a30select T1. Windowchart names where t1.window_name=t2.window_name and t2.window_group_name in T1. Repeatables intervalrect 1. Recording from dba_scheduler_windows T1 revising dbathing trainerwingroupnames members T2 where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED') WINDOW_NAME REPEAT_INTERVAL DURATION -MONDAY_WINDOW freq=daily Byday=MON;byhour=22;byminute=0; bysecond=0 + 000 04:00:00TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 + 000 04:00:00WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 + 000 04:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 + 000 04:00:00FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0 Bysecond=0 + 000 04:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0 + 000 20:00:00SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 + 00020 20:00:00SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 007 rows selected.

IV. Collect statistics by hand

4.1 collect index statistics

Exec dbms_stats.gather_index_stats (ownname = > 'WOO',indname = >' WORNUM_IND',estimate_percent = >'10')

4.2 collect table and index statistics

Exec dbms_stats.gather_table_stats (ownname = > 'WOO',tabname = >' DUMP_TABLE',estimate_percent = > 10 methodological opt = > 'for all indexed columns',cascade= > TRUE)

4.3 collect statistics for the table

Exec dbms_stats.gather_table_stats (ownname = > 'WOO',tabname = >' DUMP_TABLE',estimate_percent = > 10 methodological opt = > 'for all indexed columns')

4.4 collect partition table statistics

Exec dbms_stats.gather_table_stats (ownname = > 'WOO',tabname = >' DUMP_TABLE',partname = > 'pimpy 20190318' for all indexed columns',cascade= > TRUE)

4.5 collect statistics for a user

Exec dbms_stats.gather_schema_stats (ownname= > 'WOO',estimate_percent= > 10 ALL' degree = > 8 Magi Cascade = > true,granularity= >' ALL')

4.6 collect statistics for the entire database

Exec dbms_stats.gather_database_stats (estimate_percent= > 10 ALL' degree = > 8 Magi Cascade = > true,granularity= > 'ALL')

Fifth, dynamic sampling:

For the newly created table, oracle dynamically collects information about the table when it is accessed, and then collects it into the data dictionary at 10:00 in the evening.

SQL > set linesize 200SQL > set autotrace traceonlySQL > select * from DUMP_TABLE 10 rows selected.Execution Plan---Plan hash value: 1795212136 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10 | 20390 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | DUMP_TABLE | 10 | 20390 | 3 (0) | 00:00:01 |- -- Note--dynamic sampling used for this statement (level=2) Statistics 4 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 1305 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed

-dynamic sampling used for this statement (level=2) represents dynamic sampling, but does not record data dictionaries unless you manually collect statistics for the table.

The result is the same when we check it through user_tables.

SQL > set autotrace off;SQL > select num_rows, blocks, last_analyzed from user_tables where table_name = 'DUMP_TABLE'; NUM_ROWS BLOCKS LAST_ANAL- SQL >

VI. After the collection of statistical information:

SQL > set linesize 200SQL > set autotrace traceonlySQL > select * from DUMP_TABLE 10 rows selected.Execution Plan---Plan hash value: 1795212136 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10 | 470 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | DUMP_TABLE | 10 | 470 | 3 (0) | 00:00:01 |- -- Statistics--- 33 recursive calls 0 db block gets 56 consistent Gets 0 physical reads 0 redo size 1305 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 10 rows processed SQL > set autotrace off SQL > alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';SQL > select num_rows, blocks, last_analyzed from user_tables where table_name = 'DUMP_TABLE' NUM_ROWS BLOCKS LAST_ANALYZED- 104 2020-03-18 17:06:16SQL > Thank you for reading. This is the content of "what is the method of viewing and collecting oracle statistics". After the study of this article, I believe that you have a deeper understanding of what is the method of viewing and collecting oracle statistics, 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.