這是個(gè)終極問題，因?yàn)閮?yōu)化本身的復(fù)雜性實(shí)在是難以總結(jié)的，很多時(shí)候優(yōu)化的方法并不是用到了什么高深莫測的技術(shù)，而只是一個(gè)思想意識(shí)層面的差異，而這些都很可能連帶導(dǎo)致性能表現(xiàn)上的巨大差異。
所以有時(shí)候我們應(yīng)該先搞清楚需求到底是什么，SQL本身是否合理，這些思考很可能會(huì)使優(yōu)化工作事半功倍。而本文是假設(shè)SQL本身合理，從Oracle提供給我們的一些技術(shù)手段來簡單介紹下Oracle數(shù)據(jù)庫，該如何使用一些現(xiàn)有的技術(shù)來優(yōu)化一個(gè)SQL執(zhí)行的性能。
確定需要優(yōu)化的SQL文本及當(dāng)前SQL執(zhí)行計(jì)劃
確定SQL涉及的所有表及其索引的相關(guān)信息
運(yùn)行SQL Tuning Advisor 得到調(diào)整建議供優(yōu)化參考
收集表信息
收集索引信息
SQL Profile
物化視圖
1. 確定需要優(yōu)化的SQL文本及當(dāng)前SQL執(zhí)行計(jì)劃
優(yōu)化之前先確定好需要優(yōu)化的SQL文本以及當(dāng)前SQL的執(zhí)行計(jì)劃是什么樣，注意PL/SQL Developer這類工具F5看到的執(zhí)行計(jì)劃很可能并不準(zhǔn)確。
相關(guān)內(nèi)容參考：
SQL Tuning 基礎(chǔ)概述01 - Autotrace的設(shè)定
SQL Tuning 基礎(chǔ)概述02 - Explain plan的使用
SQL Tuning 基礎(chǔ)概述03 - 使用sql_trace和10046事件跟蹤執(zhí)行計(jì)劃
2. 確定SQL涉及的所有表及其索引的相關(guān)信息
確定查詢涉及到的所有表及其索引的相關(guān)基礎(chǔ)信息。比如:
各表的數(shù)據(jù)量
表和索引類型
表分區(qū)信息，每個(gè)分區(qū)的數(shù)據(jù)量
索引字段
索引分區(qū)信息
表關(guān)聯(lián)方式
結(jié)果集的數(shù)量
確定相關(guān)信息，以T2表為例：
--普通表/分區(qū)表信息select * from dba_tables where table_name = 'T2';select * from dba_part_tables where table_name = 'T2'; --普通表/分區(qū)表的每個(gè)分區(qū)大約__G大小select (t.bytes/1024/1024) "MB", t.* from dba_segments t where segment_name = 'T2'; --表數(shù)據(jù)量信息--普通表的數(shù)據(jù)量select count(1) from T2; --____數(shù)據(jù)左右--分區(qū)表的某個(gè)分區(qū)數(shù)據(jù)量select count(*) from T2 partition(P20160101); --____數(shù)據(jù)左右select count(*) from T2 partition(P20160102);--表索引信息--普通表索引及各個(gè)索引的索引列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;--分區(qū)表索引及各個(gè)索引的索引列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. 運(yùn)行SQL Tuning Advisor 得到調(diào)整建議供優(yōu)化參考
運(yùn)行SQL Tuning Advisor 得到調(diào)整建議供優(yōu)化參考, SQL Tuning Advisor得到的優(yōu)化建議僅供參考，具體如何做還需要結(jié)合業(yè)務(wù)實(shí)際情況。
4. 收集表信息
例如收集ZJY用戶下T2表的統(tǒng)計(jì)信息。（T2是range分區(qū)表，按天分區(qū)，每天數(shù)據(jù)量大概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
統(tǒng)計(jì)信息加鎖/解鎖
--鎖住表的統(tǒng)計(jì)信息exec dbms_stats.lock_table_stats('ZJY','T2');
--解鎖表的統(tǒng)計(jì)信息exec dbms_stats.unlock_table_stats('ZJY','T2');相關(guān)內(nèi)容參考：
【轉(zhuǎn)載】dbms_stats 導(dǎo)入導(dǎo)出表統(tǒng)計(jì)信息
5. 收集索引信息
例如只收集ZJY用戶下T2表的索引IDX_T2_1統(tǒng)計(jì)信息。（IDX_T2_1是分區(qū)索引，包含4個(gè)字段）
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
有時(shí)還很可能需要在業(yè)務(wù)閑時(shí)在線創(chuàng)建新的索引
--不記錄日志在線并行創(chuàng)建單列索引IDX_T2_2（并行度視生產(chǎn)環(huán)境當(dāng)前的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中的新特性，作為自動(dòng)SQL調(diào)整過程的一部分。SQL Profile是一個(gè)對(duì)象，它包含了可以幫助查詢優(yōu)化器為一個(gè)特定的SQL語句找到高效執(zhí)行計(jì)劃的信息。這些信息包括執(zhí)行環(huán)境、對(duì)象統(tǒng)計(jì)和對(duì)查詢優(yōu)化器所做評(píng)估的修正信息。它的最大優(yōu)點(diǎn)之一就是在不修改SQL語句和會(huì)話執(zhí)行環(huán)境的情況下影響查詢優(yōu)化器的決定。SQL Profile中包含的并非單個(gè)執(zhí)行計(jì)劃的信息，SQL Profile不會(huì)固定一個(gè)SQL語句的執(zhí)行計(jì)劃。當(dāng)表的數(shù)據(jù)增長或者索引創(chuàng)建、刪除，使用同一個(gè)SQL Profile的執(zhí)行計(jì)劃可能會(huì)改變，而存儲(chǔ)在SQL Profile中的信息會(huì)繼續(xù)起作用。所以，經(jīng)過一段很長的時(shí)間之后，它的信息有可能會(huì)過時(shí)，需要重新生成。
相關(guān)內(nèi)容參考：
【轉(zhuǎn)載】sql_profile的使用
【轉(zhuǎn)載】sql_profile快速綁定腳本
7. 物化視圖
Oracle的物化視圖可以用于預(yù)先計(jì)算并保存（表連接或聚集等耗時(shí)較多的操作的）結(jié)果，所以合理使用物化視圖，會(huì)在執(zhí)行查詢時(shí)避免進(jìn)行這些耗時(shí)的操作，從而快速的得到結(jié)果。
喜歡 
頂
難過
囧
圍觀
無聊
