???????????????????????????sql????????м????????????????????????Ч???????????
????dba???????????????????Щ????????????????????????????????????????????????м????????????????????
???????????洢??????????????????????????????????Щ??????????????????Щ?????????????????????Щ??????????:
?????????(????????????????г???)
????select table_name??num_rows??avg_row_len??block from dba_tables;
????dba_tab_statistics????dba_tables??????????
?????????????????dba_tab_partitions??dba_tab_subpartitions??????????????????????
?????????(?????????????(NDV)??NULL??????????????????(????)????????)
????select table_name??column_name??num_distinct??num_nulls??high_value??low_value??num_buckets??histogram from dba_tab_columns where table_name='EMP';
???????д????С???raw????????????dbms_stats.convert_raw_value?????????????????????????????????????sql???????????????utl_raw????cast??к?????
????dba_tab_col_statistics???и????????????????
????select table_name??column_name??endpoint_number??endpoint_value from dba_tab_histograms where table_name='XXX' and column_name='YYY'
????????????????????
????dba_part_col_statistics??dba_part_histograms??dba_subpart_col_statistics??dba_subpart_histograms????????????????????????????????
???????????(????????????????????????)
????select index_name??table_name??leaf_blocks??blevel??distinct_keys??clustering_factor??num_rows from dba_indexes where table_name='XXX' and index_name='YYY';
????dba_ind_statistics???и????????????
?????????????????????dba_ind_partitions??dba_ind_subpartitions?????????????
?????????(I/O?????????????cpu???????????)
????select * from sys.aux_stats$;
?????????????????????????????????????????￡??????????????????oracle???????????????????????????????oracle?????????????????????(oracle???)????????????????????????
?????????????????
???????????????????oracle????????????(??????????????10???賿2???????????????)???????????????????dbms_stats.gather_database_stats_job_proc?????
????????????????????????????????????????????????????????????????????????????????????????Ч??????????statistics_level?typical(???)????all???????????????
???????dbca?????????????????????????????????????????????? ????????????????????????
????begin
????dbms_auto_task_admin.enable(
????client_name=>'auto optimizer stats collection'??
????operation=>null??
????window_name=>null
????);
????end;
???????????????????????????? ??????????????
?????????????????????????????????????????????????????????????????????е???????п???????????????????????????????(???????????????????????)???????????????Ч???
??????????????????????????????????:
????- ??????????????NULL??oracle?????????????????????????
????begin
????dbms_stats.delete_table_stats('SCOTT'??'EMP');
????dbms_stats.lock_table_stats('SCOTT'??'EMP');
????end;
??????????????????????????????????????????????????????????????????optimizer_dynamic_sampling?????????????????2?????
????????????????д???????????????????????????????????????????????????????????Ч??? ????????汾????????
??????????????????oracle???????????汾????????????????????????dbms_stats???restore?????????л????
????????????????
??????????????????????????????????oracle????dbms_stats?????????????????????????
????gather_index_stats????????????? gather_table_stats???????к????????????? gather_schema_stats????????????ж?????????? gather_dictionary_stats???????????????????????? gather_database_stats?????????????ж??????????
??????????????????????????й????????????
??????ó???
???????estimate_percent?????????????oracle??????dbms_stats.auto_sample_size???Ч????????????????????????????1??100??????
???????????
??????????ò?????м?????????????????oracle??????dbms_stats.auto_degree????oracle?????????????ж?
????- ????????
??????????????oracle??????????????????????????????
??????ò???granularity??????÷??????????????????????????????????????????????ó??????????oracle???????granularity?AUTO??oracle??????????????????