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_ID CHLD EXE EXES USER_ID GETS READS ROWS_ CPU_MS ELAPS_MS IO_MS
————- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———-
9s80gw8z9x28m 0 0 1 90 16 0 10 6.018 12.417 0
新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引起，需要请开发人员检查一下。