[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

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 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個不起眼的索引改名,也會導致執行計劃發生變化.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2220580/，如需轉載，請註明出處，否則將追究法律責任。

[20181119]使用sql profile優化問題.txt

相關文章