近期，某客户在巡检分析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 DATE
REC_REVISOR VARCHAR2(24)
REC_REVISE_TIME DATE
PORDER_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.837742
2、对表字段等数据的统计
SQL> select count(*) from test.TESTDATA;
COUNT(*)
----------
4294427
SQL> select count(distinct PORDER_NO) from test.TESTDATA;
COUNT(DISTINCTPORDER_NO)
------------------------
230261
3、索引信息
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 20
ER_NO
test IDX_TESTDATA_PORM TESTDATA PORDER_NO VALID 20
SC
test IDX_TESTDATA_PORM TESTDATA MSC_LINE_NO VALID 4
SC
test IDX_SPEC_AIM TESTDATA OUT_SPEC_AIM VALID 22
test TESTDATA_PK TESTDATA ID VALID 36
4、使用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_67147
PL/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 AS
SDTH,B.MTR_TYPE_DESC AS SDCZ FROM VIEWER.V_QM_ORDER_MTR_DATA A
LEFT JOIN VIEWER.V_QM_ORDER_MTR_DATA B ON A.PORDER_NO =
B.PORDER_NO AND B.WHOLE_BACKLOG_SEQ = A.WHOLE_BACKLOG_SEQ-1
INNER JOIN WIPDBA.Z_SHOPORDER_OMORDER SOO ON A.PORDER_NO =
SOO.PO AND SOO.STEP_ID = A.WHOLE_BACKLOG_SEQ INNER JOIN
WIPDBA.SHOP_ORDER SO ON SO.HANDLE = SOO.SHOPORDER_BO WHERE
A.WHOLE_BACKLOG_CODE = :1 AND SO.SHOP_ORDER = :2
Bind Variables :
1 - (VARCHAR2(32)):Q
2 - (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 plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): 1.461191 1.002437 31.39 %
CPU Time (s): 1.453814 1.001961 31.08 %
User I/O Time (s): 0 0
Buffer Gets: 185326 173791 6.22 %
Physical Read Requests: 7 7 0 %
Physical Write Requests: 0 0
Physical Read Bytes: 114688 114688 0 %
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.74%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index test.IDX$$_1064B0001 on
test.TESTDATA(SYS_OP_C2C("PORDER_NO"),"WHOLE_BACKLOG_SEQ");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account 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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
