[20181119]使用sql profile优化问题.txt

来源：这里教程网时间：2026-03-03 12:14:59 作者：

[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个不起眼的索引改名,也会导致执行计划发生变化.

编辑推荐：

返回列表

[20181119]使用sql profile优化问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推