前因：
客戶咨詢，有一個業務sql（代表經常被執行且重要），全表掃描在系統占用資源很高（通過ash報告查詢得到資訊） 思路：
1.找到sql_text,sql_id
2.檢視執行計劃
3.查詢sql涉及對象的對象資料量，段大小，行數量，where條件列，是否存在索引，列的選擇讀情況如何
4.總結，優化整改
094cmrxrahdy2
SELECT 8~10個列名稱（由于設計使用者資訊，是以部分資訊不再詳細說明）
FROM Prescription
WHERE ProcFlg=0 AND(Group_No=0 OR Group_No=1 OR Group_No=99) AND MachineNo<>99 ORDER BY Presc_Class DESC, PrescriptionNo, SeqNo; SQL> select * from table(dbms_xplan.display_cursor('094cmrxrahdy2',format=>'IOSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1208083363
----------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT ORDER BY | | 335K|
|* 2 | TABLE ACCESS FULL| PRESCRIPTION | 335K|
----------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("PROCFLG"=0 AND INTERNAL_FUNCTION("GROUP_NO") AND "MACHINENO"<>99)) INTERNAL_FUNCTION 内部函數，一般執行計劃看到這個需要特殊關注，但是本次确認輸入的數值類型等同于字段類型 GROUP_NO NOT NULL NUMBER(2)
1)查詢表所在的使用者
select owner,object_name,object_type,status from dba_objects where object_name='PRESCRIPTION'
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- -------------------- ------------------- -------
PUBLIC PRESCRIPTION SYNONYM VALID
PHARMACY PRESCRIPTION TABLE VALID
2）查詢表的段大小
select sum(bytes)/1024/1024 from dba_segments where segment_name='PRESCRIPTION' and owner='PHARMACY';
SUM(BYTES)/1024/1024
--------------------
450
3）查詢表上的索引，及索引對應列名稱
select index_owner,index_name,column_name,COLUMN_POSITION from dba_ind_columns where table_owner='PHARMACY' and table_name='PRESCRIPTION'
INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- -------------------- ---------------
PHARMACY PRESCRIPTION_IDX1 PRESC_CLASS 1
PHARMACY PRESCRIPTION_IDX1 PRESCRIPTIONNO 2
PHARMACY PRESCRIPTION_IDX1 SEQNO 3
PHARMACY PRESCRIPTION_IDX2 PROCFLG 1
PHARMACY PRESCRIPTION_IDX3 PROCFLG 1
PHARMACY PRESCRIPTION_IDX3 GROUP_NO 2
PHARMACY PRESCRIPTION_IDX3 MACHINENO 3
PHARMACY PK_PRESCRIPTION PRESCRIPTIONNO 1
PHARMACY PK_PRESCRIPTION SEQNO 2
9 rows selected.
--第一，where 條件的三個列，再上述結果中，均存在對應的記錄，是以基本可以排除無索引導緻全表掃描的問題
--第二，産生疑問，存在索引，為何未使用索引？？？ 猜測資料傾斜嚴重，SQL查詢資料量過大，統計資訊不準确等資訊導緻的問題，需要進一步進行分析
4）查詢表行數量，及最後一次收集統計資訊的時間
SQL> select num_rows，last_analyzed from dba_tables where owner='PHARMACY' and table_name='PRESCRIPTION';
NUM_ROWS LAST_ANALYZED
-----------------------------
1560341 2018-11-27 22:01:31
5）查詢where 條件列的選擇性（及去重後的行數量）
WHERE ProcFlg=0 AND(Group_No=0 OR Group_No=1 OR Group_No=99) AND MachineNo<>99 看起來最差的選擇性<>條件MachineNo列
查詢發現，表總160萬行，MachineNo列隻有一個值1，也不存在Null值，where條件<>99,是無價值的條件，但是不至于影想走索引，此條件近乎無用
SQL> select count(*),count(distinct MachineNo) from PHARMACY.PRESCRIPTION;
COUNT(*) COUNT(DISTINCTMACHINENO)
---------- ------------------------
1604912 1
--
SQL> select MachineNo,count(*) from PHARMACY.PRESCRIPTION group by MachineNo;
MACHINENO COUNT(*)
---------- ----------
1 1604912
看起來選擇性最好的條件ProcFlg=0， 符合這個條件的數值表中存在49萬條記錄，占表中記錄的1/4,選擇性已經很差了 SQL> select count(*),count(distinct ProcFlg) from PHARMACY.PRESCRIPTION;
COUNT(*) COUNT(DISTINCTPROCFLG)
---------- ----------------------
1604912 4
select ProcFlg,count(*) from PHARMACY.PRESCRIPTION group by ProcFlg;
PROCFLG COUNT(*)
-1 7
1 1110365
2 995
0 493545
看起來選擇性中等的Group_No=0 OR Group_No=1 OR Group_No=99--符合條件的數值足有110萬條記錄 select count(*),count(distinct Group_No) from PHARMACY.PRESCRIPTION;
COUNT(*) COUNT(DISTINCTGROUP_NO)
---------- -----------------------
1604912 2
select Group_No,count(*) from PHARMACY.PRESCRIPTION group by Group_No;
GROUP_NO COUNT(*)
1 1111367
2 493545
--組合過濾後，隻有4種可能性，對于本次sql， --條件ProcFlg=0 存在50萬條記錄
--Group_No 1 or 0 or 99 傳回１１０萬行記錄
－－全表 1604912 －－１６０萬行記錄，提取記錄１１０/１６０＝６８．５％資料量，執行效率過低，還不如走全表掃描
SQL> select count(*),count(distinct ProcFlg||' '||Group_No) from PHARMACY.PRESCRIPTION;
COUNT(*) COUNT(DISTINCTPROCFLG||''||GROUP_NO)
---------- ------------------------------------
1604914 4
4.總結，優化整改
1）該SQL走全表掃描是正确的，全表掃描比回表查詢65%全表資料量更快
2）提高該SQL性能，無法從索引入手，因為SQL是查詢10個列，且資料量過大，不适用索引快速檢索資料
3）建議開發人員，重新針對業務邏輯，規劃新表：
01對每個表建立主鍵限制（唯一值），讓業務SQL能快速定位一個唯一的記錄，通過索引，快速檢索少量資料，減少資源的消耗（邏輯讀等消耗）；
02或者根據應用需求，将此表進行拆分多個小表，這樣即使是全表掃描，相對來說量級别減少，查詢時間可能會提升，但是資源消耗并未降低（邏輯讀等消耗）；