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

Oracle 11g adds the feature of extended statistics in statistics collection. It can collect statistics on related columns in a table or on function expressions. So that the selection rate, cost estimation is more accurate, but also easier to follow the correct implementation plan. The benefits of collecting statistics on relevant columns are obvious. For example, there is a logical relationship between the two columns, but if only the statistics of the two columns are collected separately, the selection rate of {(An AND B) is calculated according to the multi-conditional selection rate: OPSEL [a] * An OR B [b]; (OPSEL [a] + OPSEL [b]-OPSEL [b]) (the selection rate of NOT An is: 1-OPSEL [a])}, the estimated selection rate may deviate greatly. The following test: DB Version:11.2.0.4-generate test data drop table scott.test01 purge; create table scott.test01 as select * from dba_objects;-update object_name to be the same as object_type for testing. Update scott.test01 set object_name=object_type; commit; 1. Collect single-column statistics and view the execution plan-- collect single-column statistics begin dbms_stats.gather_table_stats ('scott','test01'); end;-- View the number of rows in the table select table_name,num_rows from dba_tables where owner =' SCOTT' and table_name = 'TEST01' / * TABLE_NAME NUM_ROWS TEST01 87212 * /-- generate the execution plan explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX' of the statement SELECT lpad (', 2 * (LEVEL-1)) | | operation operation, options, object_name, cardinality, bytes, io_cost, cpu_cost, cost, time FROM plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id / * OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME SELECT STATEMENT 41 3362 347 35338490 348 5 TABLE ACCESS FULL TEST01 41 3362 347 35338490 348 5 * / you can see that the estimated number of return rows is 41, which is obviously a far cry from the actual rollback -- number of rows estimated select rpad (column_name, 30,') column_name, rpad (num_distinct, 8,') num_distinct, rpad (utl_raw.cast_to_varchar2 (low_value), 15,'') low_value, rpad (utl_raw.cast_to_varchar2 (high_value), 10,') high_value, rpad (num_nulls, 8) Num_nulls, rpad (avg_col_len, 6,') avg_col_len, rpad (density, 20,') density, histogram from dba_tab_col_statistics where owner = 'SCOTT' and table_name =' TEST01' and column_name in ('OBJECT_NAME','OBJECT_TYPE') / * COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM OBJECT_NAME 46 CLUSTER XML SCHEMA Y 0 9. 0217391304347826 NONE OBJECT_TYPE 46 CLUSTER XML SCHEMA Y 0 9 .0217391304347826 NONE * / the estimated number of rows returned is 41 It is obtained by multiplying the density of two columns and then multiplying the number of rows of the table. 0217391304347826rows. 0217391304347826rows 8721241.215500945179641.2. Collect multi-column extension statistics and view the execution plan-- collect multi-column extension statistics begin dbms_stats.gather_table_stats ('scott','test01',method_opt = >' for columns (object_name,object_type)'); end;-- generate the execution plan explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX' of the statement SELECT lpad (', 2 * (LEVEL-1)) | | operation operation, options, object_name, cardinality, bytes, io_cost, cpu_cost, cost, time FROM plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id / * OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME SELECT STATEMENT 5303 498482 36285951 348 5 TABLE ACCESS FULL TEST01 5303 498482 347 36285951 348 5 * / you can see that the estimated number of returned rows is 5303, which is basically close to the actual number of returned rows. PS: 1. To expand the collection of statistics, you can use select dbms_stats.create_extended_stats ('scott','test01',' (object_name,object_type)') from dual to create extended statistics columns, and then dbms_stats.gather_table_stats ('scott','test01') to collect statistics, or you can directly create extended statistics and collect statistics at the same time in the method_opt attribute in dbms_stats.gather_table_stats. 2.oracle 11g can collect not only multi-column extension statistics, but also function and expression extension 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.