Shulou(Shulou.com)06/01 Report--

The emp table has the following data.

SQL > select ename,deptno from emp ENAME DEPTNO---SMITH 20ALLEN 30WARD 30JONES 20MARTIN 30BLAKE 30CLARK 10SCOTT 20KING 10TURNER 30ADAMS 20JAMES 30FORD 20MILLER 1014 rows selected.

Suppose we have the following simple query

Select ename,deptno from emp where ename='RICH' and deptno=10

So when Oracle executes the query, does it first compare the ename field? Or do you want to compare deptno fields first?

Obviously, the efficiency of comparing deptno and then comparing ename fields is significantly lower than comparing ename first and then comparing deptno. So how on earth does Oracle judge?

Let's look up a table first.

SQL > COL COLUMN_NAME FOR A30SQL > SELECT column_name, num_distinct, density FROM dba_tab_columns WHERE owner = 'SCOTT' AND table_name =' EMP' COLUMN_NAME NUM_DISTINCT DENSITY-- EMPNO 14.071428571ENAME 14.071428571job 5 .2MGR 6 .166666667HIREDATE 13 .076923077SAL 12 .083333333COMM 4 .25DEPTNO 3. 3333333338 rows selected.

Oracle actually knows that some of the characteristics of the data stored in your table are rare in the above statement. Through these features, the Oracle optimizer can know how to query and maximize the efficiency of execution.

The above information is called the statistical information of the object. So how do you collect statistics?

1. Analyze command

Use the analyze command to collect statistics, such as:

Collect or delete statistics for objects

Verify the structure of the object

Determine the migrated and chained rows of the table or cluster.

Example:

SQL > create user anal identified by anal; User created.SQL > grant resource,connect to anal;Grant succeeded.SQL > grant select any dictionary to anal;Grant succeeded.SQL > conn anal/analConnected.SQL > create table T1 as select * from dba_objects;SQL > create table T2 as select * from dba_objects;SQL > create table T3 as select * from dba_objects;SQL > create table t4 as select * from dba_objects;SQL > create table T5 as select * from dba_objects;SQL > create table t6 as select * from dba_objects;SQL > create unique index pk_t1_idx on T1 (object_id) SQL > create unique index pk_t2_idx on T2 (object_id); SQL > create unique index pk_t3_idx on T3 (object_id); SQL > create unique index pk_t4_idx on T4 (object_id); SQL > create unique index pk_t5_idx on T2 (object_id); SQL > create unique index pk_t6_idx on T6 (object_id)

Let's check to see if the statistics exist.

View statistics for the table

SQL > select table_name, num_rows, blocks, empty_blocks from user_tables where table_name in ('T _ 1, T _ 2, T _ 3, T _ 4, T _ 5, etc.)

View field statistics

Select table_name, column_name, num_distinct, low_value, high_value, density from user_tab_columns where table_name in ('T1,'T2,'T3, T4)

View index statistics

SQL > col table_name for a30SQL > col index_name for a30SELECT table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key avg_leaf_blocks, avg_data_blocks_per_key avg_data_blocks, clustering_factor Num_rows FROM user_indexesTABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS -T6 PK_T6_IDX 1 155 74564 11 1174 74564T5 PK_T5_IDX 1 155 74563 11 1174 74563T4 PK_T4 _ IDX 1 155 74562 11 1174 74562T3 PK_T3_IDX 1 155 74561 11 1174 74561T2 PK_T2_IDX 1 155 74560 11 1174 74560T1 PK_T1_IDX 1 155 74559 11 1174 745596 rows selected.

The table does not have any statistics, but the index already has statistics, so statistics are collected by default when the table is created.

Delete the statistics of the index first

SQL > analyze table T1 delete statistics;analyze table T2 delete statistics;analyze table T3 delete statistics;analyze table T4 delete statistics;analyze table T5 delete statistics;analyze table T6 delete statistics

Verify that statistics still exist on the index

SELECT table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key avg_leaf_blocks, avg_data_blocks_per_key avg_data_blocks, clustering_factor, num_rows FROM user_indexes

Execute the statistics command and see if the statistics have changed

Analyze table t1 compute statistics for table

-- collect information for the table and view the user_tables

Analyze table t2 compute statistics for all columns

-- collect information for table fields and view user_tab_columns

Analyze table t3 compute statistics for all indexed columns

-- collect index field information

Analyze table t4 compute statistics

-- collect table, table field, index information

Analyze table t5 compute statistics for all indexes

-- collect index information

Analyze table t6 compute statistics for table for all indexes for all columns

-- collect table, table field, index information

2. DBMS_ stats package

Oracle recommends using the DBMS_STATS package to collect statistics. This bag has a lot of functions. You can collect statistics at the database level, schema level, and table level. Statistics can also be deleted, locked, exported, imported, and so on. Let's take the most commonly used table-level statistics as an example to illustrate how DBMS_STATS should be used.

The collected statistical letters are stored in the dba_tab_statistics, dba_ind_statistics and dba_tab_col_ statistics tables.

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param ('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param (' METHOD_OPT') Degree NUMBER DEFAULT to_degree_type (get_param ('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM (' GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type (get_param ('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL No_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param ('NO_INVALIDATE')), stattype VARCHAR2 DEFAULT' DATA', force BOOLEAN DEFAULT FALSE)

The parameters are described as follows:

Example:

SQL > col table_name for a30SQL > SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len FROM user_tab_statistics TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN---T1 74559 1088 0 98T2T3T4T5T66 rows selected.

Delete statistic

DBMS_STATS.DELETE_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, cascade_columns BOOLEAN DEFAULT TRUE, cascade_indexes BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param ('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE)

Lock statistic

DBMS_STATS.LOCK_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2)

Statistics can no longer be executed after locking

SQL > exec dbms_stats.lock_table_stats (user,'T1'); PL/SQL procedure successfully completed.SQL > exec dbms_stats.gather_table_stats (user,'t1',cascade= > true); BEGIN dbms_stats.gather_table_stats (user,'t1',cascade= > true); END;*ERROR at line 1:ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 23829ORA-06512: at "SYS.DBMS_STATS", line 23880ORA-06512: at line 1

Export, import statistics

To export statistics, you must first establish a statistical table.

Syntax:

DBMS_STATS.CREATE_STAT_TABLE (ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL); SQL > exec DBMS_STATS.CREATE_STAT_TABLE (user,'STAT_TMP','SYSAUX'); PL/SQL procedure successfully completed.

two。 Export table T1 statistics

DBMS_STATS.EXPORT_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY); SQL > EXEC DBMS_STATS.EXPORT_TABLE_STATS (ownname= > USER,tabname= > 'T1century paper Stattaby >' STAT_TMP'); PL/SQL procedure successfully completed.

3. Import Statistics

Syntax:

DBMS_STATS.IMPORT_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param ('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE Stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY) SQL > exec dbms_stats.UNlock_table_stats (user,'T1'); PL/SQL procedure successfully completed.SQL > exec dbms_stats.delete_table_stats (user,'T1'); PL/SQL procedure successfully completed.SQL > EXEC DBMS_STATS.IMPORT_TABLE_STATS (ownname= > USER,tabname= >'T _ 1); PL/SQL procedure successfully completed.SQL > SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len FROM user_tab_statistics 2 3 4 5 6 TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN---T1 74559 1088 0 98T2T3T4T5T6STAT_TMP7 rows selected.

If it is a partition table, the new partition does not have time to collect statistics system, so you can use other partition statistics to generate statistics for the new partition.

DBMS_STATS.COPY_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, srcpartname VARCHAR2, dstpartname VARCHAR2, scale_factor VARCHAR2 DEFAULT 1, force BOOLEAN DEFAULT FALSE)

If the table does not already have statistics, Oracle dynamically samples some of the data in the table to generate statistics when the sql statement is executed.

SQL > show parameter optimizer_dynamic_sampling NAME TYPE VALUE -optimizer_dynamic_sampling integer 2

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.