利用oraclespm来优化SYS_OP_C2C的执行计划

程序员文章站 2022-04-12 21:39:40

oracle版本：11.2.0.4+psu6 原SQL: SELECT * FROM ( SELECT "Project1"."ID&quo...

oracle版本：11.2.0.4+psu6
原SQL:
SELECT *
FROM (
SELECT
"Project1"."ID" AS "ID",
.....
.....
FROM ( SELECT "Project1"."ID" AS "ID", .......
FROM ( SELECT
"Extent1"."ID" AS "ID",
.........
.......
FROM "pro"."SFC_MO" "Extent1"
WHERE (("Extent1"."STATE" = ‘A’) AND ("Extent1"."INV_ORG_ID" = 200797) AND ('UNRELEASED' <> (LOWER("Extent1"."MO_STATUS_CODE"))))
) "Project1"
) "Project1"
WHERE ("Project1"."row_number" > 0)
ORDER BY "Project1"."MO_NAME" ASC)
WHERE (ROWNUM <= (10) )

原SQL ID为9x1vud9ymsz7k
select * from table(dbms_xplan.display_cursor('9x1vud9ymsz7k',''));

原执行计划：

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6198 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 96 | 189K| 6198 (1)| 00:01:15 |
|* 3 | VIEW | | 96 | 190K| 6198 (1)| 00:01:15 |
| 4 | WINDOW SORT | | 96 | 40896 | 6198 (1)| 00:01:15 |
|* 5 | TABLE ACCESS BY INDEX ROWID| SFC_MO | 96 | 40896 | 6197 (1)| 00:01:15 |
|* 6 | INDEX RANGE SCAN | IDX_MO_IX4 | 1921 | | 4728 (1)| 00:00:57 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=10)
3 - filter("Project1"."row_number">0)
5 - filter(LOWER("Extent1"."MO_STATUS_CODE")<>'UNRELEASED')
6 - access("Extent1"."INV_ORG_ID"=:P__LINQ__1)
filter(SYS_OP_C2C("Extent1"."STATE")=:P__LINQ__0)

查看SQL执行性能情况：
SQL> set line 1000
SQL> set pagesize 1000
SQL> Select sql_id, child_number chld, users_executing exe, executions exes,
2 parsing_schema_id user_id,
3 buffer_gets/executions gets,
4 disk_reads/executions reads,
5 rows_processed/executions rows_, cpu_time/executions/1000 cpu_ms,
6 Elapsed_time/executions/1000 elaps_ms,
7 USER_IO_WAIT_TIME/executions/1000 io_ms
8 From gv$sql
9 Where sql_id='9x1vud9ymsz7k'
10 and executions>0
11 order by last_load_time;

SQL_ID CHLD EXE EXES USER_ID GETS READS ROWS_ CPU_MS ELAPS_MS IO_MS
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
9x1vud9ymsz7k 0 0 27 84 352274.185 135.592593 10 1939.29996 4034.36596 670.565593
9x1vud9ymsz7k 1 0 246 84 271656.984 60.7926829 10 1537.7462 3785.39012 968.284256
9x1vud9ymsz7k 2 0 190 84 258340.553 51.6631579 10 1469.86243 3616.83806 879.202911
9x1vud9ymsz7k 3 0 18 84 245813.222 40.4444444 10 1400.17117 3505.56261 760.694667

上面显示每次执行时间为4034ms，每次逻辑读高达352274.185,确认SQL执行性能不好。

由于该SQL历史用过的所有执行计划都差，没有出现一个好的执行计划，所以无法直接用spm来绑定执行计划。需要采用手工构造一个好的执行计划，然后再用spm来修改原SQL执行计划。
我们在SQL中加入提示符/*+zengxuewen*/，方便执行后，从海量SQL中快速定位到这支SQL的SQL ID。
SELECT /*+zengxuewen*/ *
FROM (
SELECT
"Project1"."ID" AS "ID",
.....
.....
FROM ( SELECT "Project1"."ID" AS "ID", .......
FROM ( SELECT
"Extent1"."ID" AS "ID",
.........
.......
FROM "pro"."SFC_MO" "Extent1"
WHERE (("Extent1"."STATE" = :p__linq__0) AND ("Extent1"."INV_ORG_ID" = :p__linq__1) AND ('UNRELEASED' <> (LOWER("Extent1"."MO_STATUS_CODE"))))
) "Project1"
) "Project1"
WHERE ("Project1"."row_number" > 0)
ORDER BY "Project1"."MO_NAME" ASC)
WHERE (ROWNUM <= (10)

查出优化后SQL的SQL_ID:
select sql_id from v$sql where sql_text like '%zengxuewen%';
9s80gw8z9x28m

新SQL执行情况：
SQL> set autotrace off;
SQL> Select sql_id, child_number chld, users_executing exe, executions exes,
2 parsing_schema_id user_id,
3 buffer_gets/executions gets,
4 disk_reads/executions reads,
5 rows_processed/executions rows_, cpu_time/executions/1000 cpu_ms,
6 Elapsed_time/executions/1000 elaps_ms,
7 USER_IO_WAIT_TIME/executions/1000 io_ms
8 From gv$sql
9 Where sql_id='9s80gw8z9x28m'
10 and executions>0
11 order by last_load_time;

新SQL执行计划：
select * from table(dbms_xplan.display_cursor('9s80gw8z9x28m',''));

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3939607251

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20170 | 144 (0)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 11 | 22187 | 144 (0)| 00:00:02 |
|* 3 | VIEW | | 11 | 22330 | 144 (0)| 00:00:02 |
| 4 | WINDOW NOSORT | | 11 | 4686 | 144 (0)| 00:00:02 |
|* 5 | TABLE ACCESS BY INDEX ROWID| SFC_MO | 9604 | 3995K| 144 (0)| 00:00:02 |
|* 6 | INDEX RANGE SCAN | IDX_MO_X02 | 220 | | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=10)
3 - filter("Project1"."row_number">0)
5 - filter(LOWER("Extent1"."MO_STATUS_CODE")<>'UNRELEASED' AND
"Extent1"."STATE"='A')
6 - access("Extent1"."INV_ORG_ID"=200797)

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
5756 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

逻辑读由原来的352274降为16，走索引IDX_MO_X02的性能改善非常明显。

我们用SPA来修改执行计划。

查出原SQL的plan_hash_value:
select plan_hash_value from v$sql where sql_id='9x1vud9ymsz7k';
3998652997

查出优化后SQL的plan_hash_value:
select plan_hash_value from v$sql where sql_id='9s80gw8z9x28m';
3939607251

查出原SQL的sql_handle和plan_name:
select sql_handle,plan_name from dba_sql_plan_baselines where sql_text like '%Project1%';
SQL_29d01fe514ef8ae6 SQL_PLAN_2mn0zwnafz2r6fd0d9c77

再按以下三步进行执行计划修改和绑定:
DECLARE
-- Local variables here
i INTEGER;
BEGIN
i := dbms_spm.load_plans_from_cursor_cache(sql_id=>'9x1vud9ymsz7k',
plan_hash_value => '3998652997');
END;

2.
DECLARE
-- Local variables here
i INTEGER;
BEGIN
i := dbms_spm.load_plans_from_cursor_cache(sql_id=>'9s80gw8z9x28m',
plan_hash_value => '3939607251',
sql_handle=>'SQL_29d01fe514ef8ae6');
END;

3.
DECLARE
-- Local variables here
i INTEGER;
BEGIN
i := dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SQL_29d01fe514ef8ae6',
plan_name=>'SQL_PLAN_2mn0zwnafz2r6fd0d9c77');
END;

再查看绑定后的执行计划及执行情况：
Select sql_id, child_number chld, users_executing exe, executions exes,
parsing_schema_id user_id,
buffer_gets/executions gets,
disk_reads/executions reads,
rows_processed/executions rows_, cpu_time/executions/1000 cpu_ms,
Elapsed_time/executions/1000 elaps_ms,
USER_IO_WAIT_TIME/executions/1000 io_ms
From gv$sql
Where sql_id='9x1vud9ymsz7k'
and executions>0
order by last_load_time;

SQL_ID CHLD EXE EXES USER_ID GETS READS ROWS_ CPU_MS ELAPS_MS IO_MS
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
9x1vud9ymsz7k 0 0 27 84 352274.185 135.592593 10 1939.29996 4034.36596 670.565593
9x1vud9ymsz7k 1 0 246 84 271656.984 60.7926829 10 1537.7462 3785.39012 968.284256
9x1vud9ymsz7k 2 0 190 84 258340.553 51.6631579 10 1469.86243 3616.83806 879.202911
9x1vud9ymsz7k 3 0 20 84 243800.6 39.2 10 1388.91555 3419.53545 710.0814
9x1vud9ymsz7k 4 0 22 84 11.8181818 .181818182 10 .484136364 3.38913636 2.29822727

最终生产环境优化后的执行时间由原来的4034ms降为3.38ms，逻辑读由原来的352274.185降为11.818，优化后效果非常明显。

问题延伸：
为什么IDX_MO_X02索引要比IDX_MO_IX4 性能要好?
先查看下两个索引所用字段及字段类型：
SQL> set line 1000
SQL> set pagesize 1000
SQL> col index_name for a20
SQL> col column_name for a20
SQL> col column_position for 99
SQL> col data_type for a10
SQL> select a.index_name,a.column_name,a.column_position,b.data_type from dba_ind_columns a,dba_tab_columns b where a.index_name in ('IDX_MO_IX4','IDX_MO_X02')
2 and a.TABLE_OWNER='MESPRO' and a.TABLE_NAME='SFC_MO' and a.table_owner=b.owner and a.table_name=b.table_name and a.column_name=b.column_name;

INDEX_NAME COLUMN_NAME COLUMN_POSITION DATA_TYPE
-------------------- -------------------- --------------- ----------
IDX_MO_IX4 INV_ORG_ID 1 NUMBER
IDX_MO_IX4 WORKSHOP_ID 2 VARCHAR2
IDX_MO_IX4 LINE_ID 3 VARCHAR2
IDX_MO_IX4 MITEM_ID 4 VARCHAR2
IDX_MO_IX4 STATE 5 CHAR
IDX_MO_IX4 MO_NAME 6 VARCHAR2
IDX_MO_IX4 DATETIME_SCHE_START 7 DATE
IDX_MO_X02 INV_ORG_ID 1 NUMBER
IDX_MO_X02 MO_NAME 2 VARCHAR2

然后再看一下原SQL执行计划中的谓词过滤部分如下内容：
6 - access("Extent1"."INV_ORG_ID"=:P__LINQ__1)
filter(SYS_OP_C2C("Extent1"."STATE")=:P__LINQ__0)

从上面可以看出来：
1.IDX_MO_X02索引只有INV_ORG_ID和MO_NAME两个字段组合成，而IDX_MO_IX4索引是INV_ORG_ID等7个字段组合，当只根据INV_ORG_ID字段走索引查找时，显然IDX_MO_X02要快，因为IDX_MO_X02
索引要小很多;
2.执行计划的谓词过滤中有出现SYS_OP_C2C，说明字段state有作隐式字段类型转换，表中的state的类型为char，说明传进到SQL中的值的类型为nchar的，因为SYS_OP_C2C类型转换只发生在char
和nchar中，很可能是c#.net程序中有定义变量为nchar引起，需要请开发人员检查一下。

上一篇： ASP探测本地端口之MSWINSCK.OCX方案（有编程环境）

下一篇：手脱某炒股软件Armadillo(穿山甲)壳分析

利用oraclespm来优化SYS_OP_C2C的执行计划

如何通过图片优化来提升网站排名利用图片优化提升网站排名的技巧

Python中利用all()来优化减少判断的实例分析