Managing Statistics
gather_table_stats
gather_index_stats
gather_schema_stats
gather_database_stats
gather_stale_stats
Gather Statistics
SQL> insert into ta values(3,'booboo');
1 row created.
SQL> commit;
SQL> select num_rows, blocks from dba_tables where owner='SYS' and table_name='TA';
no rows selected
SQL> exec dbms_stats.gather_table_stats('sys','ta');
PL/SQL procedure successfully completed.
SQL> select num_rows, blocks from dba_tables where owner='SYS' and table_name='TA';
NUM_ROWS BLOCKS
---------- ----------
5 60
Index Statistics
1.Index level(height)
2.Number of leaf blocks and distinct keys
3.Average number of leaf blocks per key
4.Average number of data blocks per key
5.Number of index entries
6.Clustering factor
7.Data dictionary view :dba_indexes
Column Statistics
1.Number of distinct values
2.Lowest value, highest value(stored in RAW[binary] format)
3.Last analyze date and sample size
4.Data dictionary view : user_tab_col_statistics
Segment-Level Statistics
1.Logical reads:Sampled
2.Buffer busy waits:continuous
3.Db block changes :sampled
4.Physical reads:continuous
5.Physical writes:continuous
6.Physical reads direct:Continuous
7.Physical writes direct: Continuous
8.Global cache cr blocks served: continuous
9.Global cache current blocks served:Continuous
10.ITL waits:Continuous
11.Row lock waits: Continuous
setment-level statistics query:
1.v$segstat_name
2.v$segstat
3.v$segment_statistics
Using Dynamic Sampling(动态取样)
Histograms
创建直方图: dbms_stats.gather_table_stats
dba_tab_histogram
Skewed Data
不均匀的数据 应该在job_status上建直方图
begin
for i in 1..100
loop
insert into usr1.ta values(i,'boobooke');
end loop;
commit;
end;
/
SQL> select endpoint_number,endpoint_value from dba_histograms where owner='USR1' and table_name='TA' and column_name='ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 0
3 1
4 2
5 3
SQL> exec dbms_stats.gather_table_stats('usr1','TA',method_opt=>'FOR COLUMNS SIZE 10 ID');
PL/SQL procedure successfully completed
SQL> select endpoint_number,endpoint_value from dba_histograms where owner='USR1' and table_name='TA' and column_name='ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1
1 11
2 21
3 31
4 41
5 51
6 61
7 71
8 81
9 91
10 100
11 rows selected.
Optimizer Cost Mode
System Statistics: 包括IO ,CPU 等
1.gater_system_stats 自动收集
2.set_system_stats
3.get_system_stats
自动收集:
手动收集
Import System Statistics
Copy Statistics Between DBS