??????????????????????????????????????????????????????????????????????????ī??????????????????????????????????Щ??????????????????????????????
??????????????????????????????????????SQL?????????????Щ??????????????????°????????????????SQL??????????Oracle??????????Щ?????????????????Oracle??????????????Щ???е????????????SQL??е??????
????1?????????????SQL????????SQL??м??
????2?????SQL?漰?????б????????????????
????3??????SQL Tuning Advisor ??????????鷯????ο?
????4??????????
????5????????????
????6??SQL Profile
????7???????
????1. ???????????SQL????????SQL??м??
????????????????????????SQL?????????SQL????м?????????????PL/SQL Developer??????F5????????м??????????????
???????????ο???
????SQL Tuning ????????01 - Autotrace???趨
????SQL Tuning ????????02 - Explain plan?????
????SQL Tuning ????????03 - ???sql_trace??10046?????????м??
????2. ???SQL?漰?????б????????????????
???????????漰???????б?????????????????????????:
???????????????
???????????????
?????????????????????????????
???????????
???????????????
????????????
???????????????
?????????????????T2???????
????--?????/?????????
????select * from dba_tables where table_name = 'T2';
????select * from dba_part_tables where table_name = 'T2';
????--?????/?????????????????__G??С
????select (t.bytes/1024/1024) "MB"?? t.* from dba_segments t where segment_name = 'T2';
????--???????????
????--????????????
????select count(1) from T2; --____????????
????--????????????????????
????select count(*) from T2 partition(P20160101);  --____????????
????select count(*) from T2 partition(P20160102);
????--?????????
????--???????????????????????????
????select * from dba_indexes where table_name = 'T2';
????select * from dba_ind_columns where index_name in (select index_name from dba_indexes where table_name = 'T2')order by index_name?? column_position;
????--????????????????????????????
????select * from dba_part_indexes where table_name = 'T2';
????select * from dba_ind_columns where index_name in (select index_name from dba_part_indexes where table_name = 'T2') order by index_name?? column_position;
????--?????δ?С???
????--select (t.bytes/1024/1024) "MB"?? t.* from dba_segments t where segment_name in (select index_name from dba_part_indexes where table_name = 'T2') order by segment_name?? partition_name;
????3. ????SQL Tuning Advisor ??????????鷯????ο?
????????SQL Tuning Advisor ??????????鷯????ο??? SQL Tuning Advisor????????????????ο???????????????????????????????
????4. ????????
???????????ZJY?????T2?????????????T2??range????????????????????????????80w????????
????SQL> execute dbms_stats.gather_table_stats(ownname => 'ZJY'?? tabname => 'T2'?? estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE?? method_opt => 'FOR ALL COLUMNS SIZE AUTO'?? cascade => TRUE?? degree => 16);
????PL/SQL procedure successfully completed
????Executed in 5896.641 seconds
??????????????/????
????--????????????
????exec dbms_stats.lock_table_stats('ZJY'??'T2');
????--?????????????
????exec dbms_stats.unlock_table_stats('ZJY'??'T2');
????5. ??????????
????????????ZJY?????T2???????IDX_T2_1??????????IDX_T2_1???????????????4????Σ?
????SQL> execute dbms_stats.gather_index_stats(ownname => 'ZJY'?? indname => 'IDX_T2_1'?? estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE?? degree => 8);
????PL/SQL procedure successfully completed
????Executed in 44.312 seconds
?????????????????????????????????μ?????
????--??????????????д???????????IDX_T2_2?????ж????????????????CPU??????????????????????
????create index IDX_T2_2 on T2(start_time) tablespace DBS_I_JINGYU nologging parallel 12 online;
????alter index IDX_T2_2 noparallel;
????alter index IDX_T2_2 logging;
????6. SQL Profile
????SQL Profile??10g?е??????????????SQL???????????????SQL Profile????????????????????????????????????????SQL????????Ч??м???????????Щ?????????л??????????????????????????????????????????????????????????SQL???????л???????????????????????????SQL Profile?а?????????????м?????????SQL Profile?????????SQL??????м???????????????????????????????????????????SQL Profile????м????????????洢??SQL Profile?е??????????????á????????????κ??????????????????п??????????????????ɡ?
????7. ?????
????Oracle????????????????????????棨???????????????????????????????????????????????????в????????????Щ?????????????????????????