Generating Histograms
Histogram statistics are generated by:
SQL>EXECUTE dbms_stats.gather_table_stats('HR','EMPLOYEES',METHOD_OPT => 'FOR COLUMNS SIZE 10 salary') ;
Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedure.Oracle recommends setting the MEHTHOD_OPT to FOR ALL COLUMNS SIZE AUTO.Wtih this setting,Oracle automatically determines which columns require histograms and the number of buckets(size) of each histogram.You can also manually specify which columns should have histograms and the size of each histogram.
准备测试数据
SQL> begin 2 for i in 1..100 3 loop 4 insert into t values(i,'arcerzhang'); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> select * from t; ID NAME ---------- ------------------------------ 1 arcerzhang 2 arcerzhang 3 arcerzhang 4 arcerzhang 5 arcerzhang 6 arcerzhang 7 arcerzhang 8 arcerzhang 9 arcerzhang 10 arcerzhang 11 arcerzhang ID NAME ---------- ------------------------------ 12 arcerzhang 13 arcerzhang 14 arcerzhang 15 arcerzhang 16 arcerzhang 17 arcerzhang 18 arcerzhang 19 arcerzhang 20 arcerzhang 21 arcerzhang 22 arcerzhang ID NAME ---------- ------------------------------ 23 arcerzhang 24 arcerzhang 25 arcerzhang 26 arcerzhang 27 arcerzhang 28 arcerzhang 29 arcerzhang 30 arcerzhang 31 arcerzhang 32 arcerzhang 33 arcerzhang ID NAME ---------- ------------------------------ 34 arcerzhang 35 arcerzhang 36 arcerzhang 37 arcerzhang 38 arcerzhang 39 arcerzhang 40 arcerzhang 41 arcerzhang 42 arcerzhang 43 arcerzhang 44 arcerzhang ID NAME ---------- ------------------------------ 45 arcerzhang 46 arcerzhang 47 arcerzhang 48 arcerzhang 49 arcerzhang 50 arcerzhang 51 arcerzhang 52 arcerzhang 53 arcerzhang 54 arcerzhang 55 arcerzhang ID NAME ---------- ------------------------------ 56 arcerzhang 57 arcerzhang 58 arcerzhang 59 arcerzhang 60 arcerzhang 61 arcerzhang 62 arcerzhang 63 arcerzhang 64 arcerzhang 65 arcerzhang 66 arcerzhang ID NAME ---------- ------------------------------ 67 arcerzhang 68 arcerzhang 69 arcerzhang 70 arcerzhang 71 arcerzhang 72 arcerzhang 73 arcerzhang 74 arcerzhang 75 arcerzhang 76 arcerzhang 77 arcerzhang ID NAME ---------- ------------------------------ 78 arcerzhang 79 arcerzhang 80 arcerzhang 81 arcerzhang 82 arcerzhang 83 arcerzhang 84 arcerzhang 85 arcerzhang 86 arcerzhang 87 arcerzhang 88 arcerzhang ID NAME ---------- ------------------------------ 89 arcerzhang 90 arcerzhang 91 arcerzhang 92 arcerzhang 93 arcerzhang 94 arcerzhang 95 arcerzhang 96 arcerzhang 97 arcerzhang 98 arcerzhang 99 arcerzhang ID NAME ---------- ------------------------------ 100 arcerzhang 100 rows selected.
对ID列创建10个桶(buckets)
SQL> EXEC dbms_stats.gather_table_stats('HR','T',method_opt=>'FOR COLUMNS SIZE 10 id'); PL/SQL procedure successfully completed.
生成直方图
SQL> SELECT endpoint_number,endpoint_value FROM dba_histograms WHERE owner='HR' and table_name = 'T' AND column_name = 'ID'; ENDPOINT_NUMBER ENDPOINT_VALUE --------------- -------------- 0 1 1 10 2 20 3 30 4 40 5 50 6 60 7 70 8 80 9 90 10 100 11 rows selected.
Gather Statistics Estimates
- dbms_stats.auto_samp[le_size:
- New estimate_percent value
- MEHTOD_OPT options:
- -REPEAT:New histogram with same number of buckets
- -AUTO:Net histogram based on data distribution and application workload
- -SKEWONLY:New histogram based on data distribution
SQL>EXECUTE dbms_status.gather_schema_stats(ownname => 'OE',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all columns size AUTO');
Auto Statistics Collecting
For the dbms_status.gather_schema_stats procedure set OPTIONS to:
- Gather stale
- Gather Empty
- Gather Auto
SQL>EXEC dbms_stats.gather_schema_stats(OWNNAME => 'OE',OPTIONS => 'GATHER AUTO');
如何让一张表处于被监控状态?
alter table hr.t monitoring;
Statistics分为四大类
- Table
- Index
- Column
- System
Optimizer Cost Model
- Three columns in plan_table are:
- -cpu_cost:Estimated CPU cost of the operation
- -io_cost:Estimated I/O cost of the operation
- -temp_space:Estimated temporary space(in bytes)
- Include CPU usage
- Accounts for the effect of caching
- Accounts for index prefetching