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

How to understand ORACLE table statistics and column statistics, index statistics, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

ORACLE table statistics and column statistics

When we analyze the performance of some statements, we analyze some information. Such as tables, columns, indexes, histograms and so on, this article focuses on the collection and analysis of statistical information of tables and columns and indexes.

First, table statistics

1. The table has been created and statistics need to be collected

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (OWNNAME = >''

TABNAME = >''

ESTIMATE_PERCENT = > 100

METHOD_OPT = > 'for all columns size skewonly'

CASCADE = > TRUE)

END

two。 Use dba_tab_statistics to view the statistics of the table.

Col OWNER for a16

Col table_name for a20

Col PARTITION_NAME for a18

Col OBJECT_TYPE for a12

Col LAST_ANALYZED for a20

SELECT OWNER,TABLE_NAME,PARTITION_NAME,OBJECT_TYPE,NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,to_char (LAST_ANALYZED,'yyyy-mm-dd hh34:mi:ss') LAST_ANALYZED,USER_STATS

FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'MSTB_ORDER_HEADER'

OWNER TABLE_NAME PARTITION_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN LAST_ANALYZED USER_S

--

POS_ORDER MSTB_ORDER_ TABLE 40445748 1928239 000 317 2016-01-07 17 NO

POS_ORDER MSTB_ORDER_ P_MAXVALUE PARTITION 1 19 000 0 197 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2009_10 PARTITION 00 00 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2009_11 PARTITION 00 00 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2009_12 PARTITION 00 00 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_1 PARTITION 0 000 0 0 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_10 PARTITION 73496 3299 000 260 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_11 PARTITION 69908 3138 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_12 PARTITION 66918 3018 000 260 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_6 PARTITION 45502 1940 000 256 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_7 PARTITION 53236 2279 000 256 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_8 PARTITION 52568 2279 000 254 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2012_9 PARTITION 71614 3219 000 260 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2013_1 PARTITION 72958 3293 000 260 2016-01-07 16 NO

POS_ORDER MSTB_ORDER_ P_MOH_2013_10 PARTITION 75330 3449 2016-01-07 16 NO

Explanations for each column are available in the online documentation (http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2114.htm#REFRN20376)

Where blocks is the number of blocks below the high water level, and empty_blocks is the number of data blocks above the high water level.

Dbms_stats does not calculate EMPTY_BLOCKS, AVG_SPACE, and CHAIN_CNT. You can use ANALYZE charge (ANALYZE TABLE COMPUTE STATISTICS)

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.