[20181119]使用sql profile优化问题.txt
--//最近一段时间一直做生产系统的优化工作,遇到一个使用sql profile优化的问题,比较典型,做1个记录.
1.环境:
zzzzzz > @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2.问题语句:
--//sql_id:8tmjhhh8km97s
SELECT "MS_CF01"."CFSB"
,"MS_CF01"."CFHM"
,"MS_CF01"."FPHM"
,"MS_CF01"."CFLX"
,"MS_CF01"."KFRQ"
,"MS_CF01"."KSDM"
,"MS_CF01"."YSDM"
,"MS_CF01"."ZFPB"
,"MS_CF01"."DJLY"
FROM "MS_CF01"
WHERE (MS_CF01.BRID = :al_PatientId)
AND (MS_CF01.YSDM = :as_DoctorId OR :as_DoctorId = :"SYS_B_0")
AND (MS_CF01.CFLX = :ai_RecipeType)
AND (MS_CF01.KSDM = :as_ksdm OR :as_ksdm = :"SYS_B_1")
AND (MS_CF01.KFRQ >= :adt_startkfrq)
AND (MS_CF01.KFRQ <= :adt_endkfrq)
AND (MS_CF01.ZFPB = :"SYS_B_2")
AND (MS_CF01.CFHM IS NOT NULL);
--//执行计划如下:
Plan hash value: 313837456
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| MS_CF01 | 6 | 336 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_MS_CF01_KFRQ | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:ADT_STARTKFRQ<=:ADT_ENDKFRQ)
2 - filter(("MS_CF01"."BRID"=:AL_PATIENTID AND "MS_CF01"."CFLX"=:AI_RECIPETYPE AND
"MS_CF01"."ZFPB"=:SYS_B_2 AND ("MS_CF01"."YSDM"=:AS_DOCTORID OR :AS_DOCTORID=:SYS_B_0)
AND ("MS_CF01"."KSDM"=TO_NUMBER(:AS_KSDM) OR :AS_KSDM=:SYS_B_1) AND "MS_CF01"."CFHM"
IS NOT NULL))
3 - access("MS_CF01"."KFRQ">=:ADT_STARTKFRQ AND "MS_CF01"."KFRQ"<=:ADT_ENDKFRQ)
Note
-----
- SQL profile tuning 8tmjhhh8km97s used for this statement
--//可以发现我以前使用sql profile调整稳定过执行计划.为什么没起作用呢?
zzzzzz > select sql_id,child_number,PLAN_HASH_VALUE,buffer_gets,executions,ELAPSED_TIME from v$sql where sql_id='8tmjhhh8km97s';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE BUFFER_GETS EXECUTIONS ELAPSED_TIME
------------- ------------ --------------- ----------- ---------- ------------
8tmjhhh8km97s 0 313837456 17799239 125 52016476
--//17799239/125 = 142394.
zzzzzz > @ bind_cap 8tmjhhh8km97s ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ---------------------
8tmjhhh8km97s 0 YES :AL_PATIENTID 1 22 2018-11-19 10:07:07 NUMBER 17793154
YES :AS_DOCTORID 2 32 2018-11-19 10:07:07 CHAR(32) 2038
YES :SYS_B_0 4 32 2018-11-19 10:07:07 VARCHAR2(32) 0
YES :AI_RECIPETYPE 5 22 2018-11-19 10:07:07 NUMBER 1
YES :AS_KSDM 6 32 2018-11-19 10:07:07 CHAR(32) 539
YES :SYS_B_1 8 32 2018-11-19 10:07:07 VARCHAR2(32) 0
YES :ADT_STARTKFRQ 9 7 2018-11-19 10:07:07 DATE 2018/06/22 00:00:00
YES :ADT_ENDKFRQ 10 7 2018-11-19 10:07:07 DATE 2018/11/19 23:59:59
YES :SYS_B_2 11 22 2018-11-19 10:07:07 NUMBER 0
9 rows selected.
--//可以发现走索引范围太大,导致逻辑读很高.应该选择走字段BRID索引更佳.
3.而实际上以前我应该分析过,执行计划应该选择brid的索引.抽取sql profile的提示看看:
zzzzzz > @ spext 8tmjhhh8km97s
HINT NAME
-------------------------------------------------------------------------------------------------------------- ------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "MS_CF01"@"SEL$1", SCALE_ROWS=4953994342) tuning 8tmjhhh8km97s
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "MS_CF01"@"SEL$1", "IDX_MS_CF01_KFRQ", SCALE_ROWS=77723.6275) tuning 8tmjhhh8km97s
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "MS_CF01"@"SEL$1", "IDX_MS_CF01_BRID", SCALE_ROWS=2.265321197) tuning 8tmjhhh8km97s
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "MS_CF01"@"SEL$1", "IDX_MS_CF01_AP_KFRQ_JZXH", SCALE_ROWS=96116.24613) tuning 8tmjhhh8km97s
OPTIMIZER_FEATURES_ENABLE(default) tuning 8tmjhhh8km97s
--//昏倒.实际上这次优化,我把索引名改写了,IDX_MS_CF01_KFRQ=>I_MS_CF01_KFRQ,IDX_MS_CF01_BRID=>I_MS_CF01_BRID.
--//这样提示变成无效提示.
--//而且这个表很久没分析过,导致oracle认为索引范围范围扫描返回1行,比走brid字段索引要好.
--//删除原来的sql profile,重新建立sql_profile(步骤略):
zzzzzz > @ spext 8tmjhhh8km97s
HINT NAME
----------------------------------------------------------------------------------------------- ------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "MS_CF01"@"SEL$1", SCALE_ROWS=9318194545) tuning 8tmjhhh8km97s
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "MS_CF01"@"SEL$1", "I_MS_CF01_KFRQ", SCALE_ROWS=77614.11714) tuning 8tmjhhh8km97s
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "MS_CF01"@"SEL$1", "I_MS_CF01_BRID", SCALE_ROWS=2.027632562) tuning 8tmjhhh8km97s
OPTIMIZER_FEATURES_ENABLE(default) tuning 8tmjhhh8km97s
--//可以发现现在显示的索引提示正确了.
--//看了以后维护数据库也要注意,你可能仅仅是给索引改1个名字,也可能导致性能问题.
--//实际上这样优化还是存在一些问题,选择brid字段可能导致物理读增加,因为有一些病人存在上千次的记录在这个表中.
--//最佳的方式建立复合索引brid,KFRQ,当然这样日志也会有所增加,而且KFRQ索引说不定没有存在的必要.
--//这些都需要统筹考虑.
--//sql profile 报表提示如下:
Recommendation (estimated benefit: 71.42%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index
"XXXXXX_YYY"."I_MS_CF01_BRID" because it is a prefix of the recommended
index.
create index XXXXXX_YYY.IDX$$_166780001 on
XXXXXX_YYY.MS_CF01("BRID","CFLX","ZFPB","KFRQ");
3- Using New Indices
--------------------
Plan hash value: 277760487
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 616 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| MS_CF01 | 11 | 616 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX$$_166780001 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:ADT_STARTKFRQ<=:ADT_ENDKFRQ)
2 - filter((:AS_DOCTORID=:SYS_B_0 OR "MS_CF01"."YSDM"=:AS_DOCTORID) AND
(:AS_KSDM=:SYS_B_1 OR "MS_CF01"."KSDM"=TO_NUMBER(:AS_KSDM)) AND "MS_CF01"."CFHM" IS NOT
NULL)
3 - access("MS_CF01"."BRID"=:AL_PATIENTID AND "MS_CF01"."CFLX"=:AI_RECIPETYPE AND
"MS_CF01"."ZFPB"=:SYS_B_2 AND "MS_CF01"."KFRQ">=:ADT_STARTKFRQ AND
"MS_CF01"."KFRQ"<=:ADT_ENDKFRQ)
--//最终选择取消sql profile设置.建立索引:
CREATE INDEX XXXXXX_YYY.I_MS_CF01_BRID_KFRQ ON PORTAL_HIS.MS_CF01
(BRID, KFRQ)
LOGGING
TABLESPACE XXXXXX_YYY
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL
COMPRESS 1;
--//删除原来的brid字段索引.另外的字段KFRQ索引,留待观察,并且重新分析表.
4.附上spext脚本:
$ cat spext.sql
/* Formatted on 2015/4/10 17:03:49 (QP5 v5.252.13127.32867) */
column hint format a200
column name format a30
SELECT EXTRACTVALUE (VALUE (h), '.') AS hint,so.name
FROM SYS.sqlobj$data od
,SYS.sqlobj$ so
,TABLE
(
XMLSEQUENCE
(
EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')
)
) h
WHERE so.NAME in ( 'profile &&1', 'tuning &&1','switch tuning &&1')
AND so.signature = od.signature
AND so.CATEGORY = od.CATEGORY
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;
5.总结:
--//oracle一些优化的细节很重要,看上去1个不起眼的索引改名,也会导致执行计划发生变化.