近期，某客戶在巡檢分析AWR報告時，發現有個TOP SQL的執行效率偏慢，檢查分析SQL，發現數據塊讀取量高，分析執行計劃，發現有個查詢條件未使用到索引；
對執行計劃及表上的字段、索引進行分析，可以發現表上對應字段是有索引的；深入分析執行計劃的Predicate Information部分，可以發現有：access("SOO"."PO"=SYS_OP_C2C("A"."PORDER_NO") AND，??SYS_OP_C2C是ORACLE的一個內部函數,它在VARCHAR2和NVARCHAR2數據類型之間進行轉換。當必須執行隱式數據類型轉換時,它由SQL引擎添加。
查看字段類型，確認是PORDER_NO???VARCHAR2(20)：
SQL> desc test.TESTDATA;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------ID NOT NULL VARCHAR2(36)REC_CREATOR VARCHAR2(24)REC_CREATE_TIME DATEREC_REVISOR VARCHAR2(24)REC_REVISE_TIME DATEPORDER_NO VARCHAR2(20)
……分析過程如下：
1、SQL執行信息統計
SQL> set linesize 180 pagesize 1800
SQL> select sql_id,PLAN_HASH_VALUE,CHILD_number,EXECUTIONS,BUFFER_GETS/EXECUTIONS,ELAPSED_TIME/EXECUTIONS/1000 from v$sql where sql_id='bh0f095aumxth';SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS BUFFER_GETS/EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000
------------- --------------- ------------ ---------- ---------------------- ----------------------------
bh0f095aumxth 3442512347 0 7036 166804.411 909.259484
bh0f095aumxth 3442512347 1 2234 167502.39 928.8377422、對表字段等數據的統計
SQL> select count(*) from test.TESTDATA;COUNT(*)
----------4294427SQL> select count(distinct PORDER_NO) from test.TESTDATA;COUNT(DISTINCTPORDER_NO)
------------------------2302613、索引信息
SQL> set linesize 180 pagesize 180
SQL> col owner for a15
SQL> col COLUMN_NAME for a20
SQL> col index_name for a20
SQL> col table_name for a20
SQL> select a.OWNER,a.INDEX_NAME,a.table_name,b.column_name,a.status,b.column_length from dba_indexes a,dba_IND_COLUMNS b 2 where a.index_name=b.index_name and a.owner=b.INDEX_OWNER and a.TABLE_OWNER=b.TABLE_OWNER and a.owner='test' and a.TABLE_NAME in(upper('TESTDATA'));OWNER INDEX_NAME TABLE_NAME COLUMN_NAME STATUS COLUMN_LENGTH
--------------- -------------------- -------------------- -------------------- -------- -------------
test IDX_TESTDATA_PORD TESTDATA PORDER_NO VALID 20ER_NO
test IDX_TESTDATA_PORM TESTDATA PORDER_NO VALID 20SC
test IDX_TESTDATA_PORM TESTDATA MSC_LINE_NO VALID 4SC
test IDX_SPEC_AIM TESTDATA OUT_SPEC_AIM VALID 22
test TESTDATA_PK TESTDATA ID VALID 364、使用SQL自動優化進行分析，建議添加函數索引
SQL> set serveroutput on
SQL> var tuning_task varchar2(100);
SQL> DECLARE 2 l_sql_id v$session.prev_sql_id%TYPE; 3 l_tuning_task VARCHAR2(30); 4 BEGIN 5 l_sql_id:='bh0f095aumxth'; 6 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id); 7 :tuning_task:=l_tuning_task; 8 dbms_sqltune.execute_tuning_task(l_tuning_task); 9 dbms_output.put_line(l_tuning_task); 10 END; 11 /
TASK_67147PL/SQL procedure successfully completed.SQL> set long 999999
SQL> set LONGCHUNKSIZE 999999
SQL> set serveroutput on size 999999
SQL> set linesize 200 pagesize 1000
SQL> SELECT dbms_sqltune.report_tuning_task('TASK_67147') FROM dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_67147')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_67147
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 04/17/2025 15:33:52
Completed at : 04/17/2025 15:34:18-------------------------------------------------------------------------------
Schema Name: WIPDBA
SQL ID : bh0f095aumxth
SQL Text : SELECT A.TREAT_DESC TH,A.MTR_TYPE_DESC AS CZ ,B.TREAT_DESC ASSDTH,B.MTR_TYPE_DESC AS SDCZ FROM VIEWER.V_QM_ORDER_MTR_DATA ALEFT JOIN VIEWER.V_QM_ORDER_MTR_DATA B ON A.PORDER_NO =B.PORDER_NO AND B.WHOLE_BACKLOG_SEQ = A.WHOLE_BACKLOG_SEQ-1INNER JOIN WIPDBA.Z_SHOPORDER_OMORDER SOO ON A.PORDER_NO =SOO.PO AND SOO.STEP_ID = A.WHOLE_BACKLOG_SEQ INNER JOINWIPDBA.SHOP_ORDER SO ON SO.HANDLE = SOO.SHOPORDER_BO WHEREA.WHOLE_BACKLOG_CODE = :1 AND SO.SHOP_ORDER = :2
Bind Variables :1 - (VARCHAR2(32)):Q2 - (VARCHAR2(128)):SOB05-B250410002-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------A potentially better execution plan was found for this statement.Recommendation (estimated benefit<=10%)---------------------------------------- Consider accepting the recommended SQL profile.execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_67147',task_owner => 'SYS', replace => TRUE);Validation results------------------The SQL profile was tested by executing both its plan and the original planand measuring their respective execution statistics. A plan may have beenonly partially executed if the other could be run to completion in less time.Original Plan With SQL Profile % Improved------------- ---------------- ----------Completion Status: COMPLETE COMPLETEElapsed Time (s): 1.461191 1.002437 31.39 %CPU Time (s): 1.453814 1.001961 31.08 %User I/O Time (s): 0 0Buffer Gets: 185326 173791 6.22 %Physical Read Requests: 7 7 0 %Physical Write Requests: 0 0Physical Read Bytes: 114688 114688 0 %Physical Write Bytes: 0 0Rows Processed: 1 1Fetches: 1 1Executions: 1 12- Index Finding (see explain plans section below)
--------------------------------------------------The execution plan of this statement can be improved by creating one or moreindices.Recommendation (estimated benefit: 99.74%)------------------------------------------- Consider running the Access Advisor to improve the physical schema designor creating the recommended index.create index test.IDX$$_1064B0001 ontest.TESTDATA(SYS_OP_C2C("PORDER_NO"),"WHOLE_BACKLOG_SEQ");Rationale---------Creating the recommended indices significantly improves the execution planof this statement. However, it might be preferable to run "Access Advisor"using a representative SQL workload as opposed to a single statement. Thiswill allow to get comprehensive index recommendations which takes intoaccount index maintenance overhead and additional space consumption.-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------1- Original With Adjusted Cost
------------------------------
Plan hash value: 2019886439-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 4840 | 5209K (1)| 00:03:24 |
| 1 | NESTED LOOPS | | 22 | 4840 | 5209K (1)| 00:03:24 |
| 2 | NESTED LOOPS OUTER | | 22 | 4378 | 5209K (1)| 00:03:24 |
|* 3 | HASH JOIN | | 22 | 3036 | 55372 (2)| 00:00:03 |
| 4 | NESTED LOOPS | | 1 | 105 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 105 | 5 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SHOP_ORDER | 1 | 53 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_SHOP_ORDER | 1 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDEX_SHOPORDER_BO | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | Z_SHOPORDER_OMORDER | 1 | 52 | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | TESTDATA | 1271K| 40M| 55353 (2)| 00:00:03 |
|* 11 | VIEW PUSHED PREDICATE | V_QM_ORDER_MTR_DATA | 1 | 61 | 234K (1)| 00:00:10 |
| 12 | NESTED LOOPS | | 58 | 3016 | 234K (1)| 00:00:10 |
| 13 | NESTED LOOPS | | 242K| 3016 | 234K (1)| 00:00:10 |
|* 14 | INDEX RANGE SCAN | OM_LINE_DATA_IDX01 | 242K| 4983K| 3 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_TESTDATA_PORMSC | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TESTDATA | 1 | 31 | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | OM_LINE_DATA_IDX01 | 1 | 21 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("SOO"."PO"=SYS_OP_C2C("A"."PORDER_NO") AND"A"."WHOLE_BACKLOG_SEQ"=TO_NUMBER("SOO"."STEP_ID"))7 - access("SO"."SHOP_ORDER"=SYS_OP_C2C(:2))8 - access("SO"."HANDLE"="SOO"."SHOPORDER_BO")10 - filter("A"."WHOLE_BACKLOG_CODE"=:1)11 - filter("B"."WHOLE_BACKLOG_SEQ"(+)="A"."WHOLE_BACKLOG_SEQ"-1)14 - access("B"."PORDER_NO"="A"."PORDER_NO")filter(TO_NUMBER("B"."DEFAULT_FLAG")=0)15 - access("A"."PORDER_NO"="A"."PORDER_NO" AND "A"."MSC_LINE_NO"="B"."MSC_LINE_NO")filter("A"."PORDER_NO"="B"."PORDER_NO")17 - access("A"."PORDER_NO"="B"."PORDER_NO" AND "A"."MSC_LINE_NO"="B"."MSC_LINE_NO")filter(TO_NUMBER("B"."DEFAULT_FLAG")=0)2- Using SQL Profile
--------------------
Plan hash value: 330716963----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1540 | 55407 (2)| 00:00:03 |
| 1 | NESTED LOOPS OUTER | | 7 | 1540 | 55407 (2)| 00:00:03 |
| 2 | NESTED LOOPS | | 7 | 1113 | 55372 (2)| 00:00:03 |
|* 3 | HASH JOIN | | 22 | 3036 | 55372 (2)| 00:00:03 |
| 4 | NESTED LOOPS | | 1 | 105 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 105 | 5 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| SHOP_ORDER | 1 | 53 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_SHOP_ORDER | 1 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDEX_SHOPORDER_BO | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | Z_SHOPORDER_OMORDER | 1 | 52 | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | TESTDATA | 1271K| 40M| 55353 (2)| 00:00:03 |
|* 11 | INDEX RANGE SCAN | OM_LINE_DATA_IDX01 | 1 | 21 | 0 (0)| 00:00:01 |
|* 12 | VIEW PUSHED PREDICATE | V_QM_ORDER_MTR_DATA | 1 | 61 | 5 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 58 | 3016 | 5 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED | TESTDATA | 19 | 589 | 5 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_TESTDATA_PORDER_NO | 19 | | 3 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | OM_LINE_DATA_IDX01 | 3 | 63 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("SOO"."PO"=SYS_OP_C2C("A"."PORDER_NO") AND "A"."WHOLE_BACKLOG_SEQ"=TO_NUMBER("SOO"."STEP_ID"))7 - access("SO"."SHOP_ORDER"=SYS_OP_C2C(:2))8 - access("SO"."HANDLE"="SOO"."SHOPORDER_BO")10 - filter("A"."WHOLE_BACKLOG_CODE"=:1)11 - access("A"."PORDER_NO"="B"."PORDER_NO" AND "A"."MSC_LINE_NO"="B"."MSC_LINE_NO")filter(TO_NUMBER("B"."DEFAULT_FLAG")=0)12 - filter("B"."WHOLE_BACKLOG_SEQ"(+)="A"."WHOLE_BACKLOG_SEQ"-1)15 - access("A"."PORDER_NO"="A"."PORDER_NO")16 - access("B"."PORDER_NO"="A"."PORDER_NO" AND "A"."MSC_LINE_NO"="B"."MSC_LINE_NO")filter("A"."PORDER_NO"="B"."PORDER_NO" AND TO_NUMBER("B"."DEFAULT_FLAG")=0)3- Using New Indices
--------------------
Plan hash value: 2552130591----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 2860 | 143 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 2860 | 143 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | NESTED LOOPS OUTER | | 13 | 2860 | 143 (1)| 00:00:01 | Q1,01 | PCWP | |
| 4 | NESTED LOOPS | | 13 | 2067 | 106 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | NESTED LOOPS | | 39 | 5382 | 106 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | NESTED LOOPS | | 1 | 105 | 5 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| SHOP_ORDER | 2 | 106 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 10 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 11 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 12 | INDEX RANGE SCAN | IDX_SHOP_ORDER | 1 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| Z_SHOPORDER_OMORDER | 1 | 52 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 14 | INDEX RANGE SCAN | INDEX_SHOPORDER_BO | 1 | | 0 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED | TESTDATA | 39 | 1287 | 182 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 16 | INDEX RANGE SCAN | IDX$$_1064B0001 | 415 | | 0 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 17 | INDEX RANGE SCAN | OM_LINE_DATA_IDX01 | 1 | 21 | 0 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 18 | VIEW PUSHED PREDICATE | V_QM_ORDER_MTR_DATA | 1 | 61 | 5 (0)| 00:00:01 | Q1,01 | PCWP | |
| 19 | NESTED LOOPS | | 58 | 3016 | 5 (0)| 00:00:01 | Q1,01 | PCWP | |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED | TESTDATA | 19 | 589 | 5 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 21 | INDEX RANGE SCAN | IDX_TESTDATA_PORDER_NO | 19 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 22 | INDEX RANGE SCAN | OM_LINE_DATA_IDX01 | 3 | 63 | 0 (0)| 00:00:01 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------12 - access("SO"."SHOP_ORDER"=SYS_OP_C2C(:2))14 - access("SO"."HANDLE"="SOO"."SHOPORDER_BO")15 - filter("A"."WHOLE_BACKLOG_CODE"=:1)16 - access("SOO"."PO"="TESTDATA"."SYS_QSMMIX_VCOL_5001" AND "A"."WHOLE_BACKLOG_SEQ"=TO_NUMBER("SOO"."STEP_ID"))17 - access("A"."PORDER_NO"="B"."PORDER_NO" AND "A"."MSC_LINE_NO"="B"."MSC_LINE_NO")filter(TO_NUMBER("B"."DEFAULT_FLAG")=0)18 - filter("B"."WHOLE_BACKLOG_SEQ"(+)="A"."WHOLE_BACKLOG_SEQ"-1)21 - access("A"."PORDER_NO"="A"."PORDER_NO")22 - access("B"."PORDER_NO"="A"."PORDER_NO" AND "A"."MSC_LINE_NO"="B"."MSC_LINE_NO")filter("A"."PORDER_NO"="B"."PORDER_NO" AND TO_NUMBER("B"."DEFAULT_FLAG")=0)
5、添加索引后，查看SQL執行效率已經提升，單次SQL執行的讀取數據庫從16W下降到50個。
SQL> create index test.IDX$$_1064B0001 on test.TESTDATA(SYS_OP_C2C("PORDER_NO")) online;Index created.SQL> SQL> SQL> SQL> select sql_id,PLAN_HASH_VALUE,CHILD_number,EXECUTIONS,BUFFER_GETS/EXECUTIONS,ELAPSED_TIME/EXECUTIONS/1000 from v$sql where sql_id='bh0f095aumxth';SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS BUFFER_GETS/EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000
------------- --------------- ------------ ---------- ---------------------- ----------------------------
bh0f095aumxth 3442512347 0 7104 166868.53 909.808189
bh0f095aumxth 3442512347 1 2266 167586.396 929.766665
新的執行計劃：
SQL> /SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS BUFFER_GETS/EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000
------------- --------------- ------------ ---------- ---------------------- ----------------------------
bh0f095aumxth 1882491329 0 60 51.9166667 3.27771667
