analyze

上一篇 / 下一篇 2008-05-14 18:09:51 / 个人分类：oracle ocp 033性能调优

1、analyze
   analyze {index|table|cluster} OBJECT_NAME
   {compute|delete|estimate} statistics
   [for ... [size N]] [sample N {rows|percent}]
==============
SQL> analyze table TEST_TAB compute statistics;
表被分析
SQL>
SQL> select table_name,num_rows,blocks,empty_blocks,avg_space,avg_row_len,sample_size
2    from user_tables
3   where table_name='TEST_TAB'
4 /
TABLE_NAME                     NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE
------------------------------ -------- ---------- ------------ ---------- ----------- -----------
TEST_TAB                  793         60           68       7587          27         793
SQL> analyze table TEST_TAB delete statistics;
表被分析
SQL>
SQL> select table_name,num_rows,blocks,empty_blocks,avg_space,avg_row_len,sample_size
2 from user_tables
3 where table_name='TEST_TAB'
4 /
TABLE_NAME                     NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE
------------------------------ -------- ---------- ------------ ---------- ----------- -----------
TEST_TAB
==============
SQL> analyze index pk_TEST_TAB compute statistics;
索引被分析
SQL>
SQL> select index_name,blevel,leaf_blocks,distinct_keys,clustering_factor
2    from user_indexes
3   where index_name='PK_TEST_TAB'
4 /
INDEX_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------ ----------- ------------- -----------------
PK_TEST_TAB               1           5           793               454
SQL> analyze index pk_TEST_TAB delete statistics;
索引被分析
SQL>
SQL> select index_name,blevel,leaf_blocks,distinct_keys,clustering_factor
2    from user_indexes
3   where index_name='PK_TEST_TAB'
4 /
INDEX_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------ ----------- ------------- -----------------
PK_TEST_TAB
=================
analyze table TEST_TAB compute statistics for all columns;
表被分析
SQL>
SQL> select column_name,num_distinct,num_nulls,num_buckets,low_value,high_value
2 from user_tab_col_statistics
3 where table_name='TEST_TAB'
4 /
COLUMN_NAME                    NUM_DISTINCT NUM_NULLS NUM_BUCKETS LOW_VALUE                                                        HIGH_VALUE
------------------------------ ------------ --------- ----------- ---------------------------------------------------------------- ----------------------------------------------------------------
SERIAL_NO                         793         0          73 504442323030373038323530303030303030313535                       505A59323030373131323730303030303032323032
LAND_STATUS                               2         0           2 34                                                               35
IS_MANUAL                                 0       793           0
SQL> select endpoint_number,endpoint_value
2    from user_tab_histograms
3   where table_name='TEST_TAB' and column_name='SERIAL_NO'
4 /
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0 4.167681971270
              1 4.167681971270
              2 4.167681971270
              3 4.167681971270
              4 4.167681971270
              5 4.167681971270

SQL> analyze table TEST_TAB delete statistics;
表被分析
SQL> select column_name,num_distinct,num_nulls,num_buckets,low_value,high_value
2 from user_tab_col_statistics
3 where table_name='TEST_TAB'
4 /
COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS LOW_VALUE HIGH_VALUE
------------------------------ ------------ --------- ----------- ---------------------------------------------------------------- ----------------------------------------------------------------
SQL>
SQL> select endpoint_number,endpoint_value
2 from user_tab_histograms
3 where table_name='TEST_TAB' and column_name='SERIAL_NO'
4 /
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------