ITPub博客

首页 > 数据库 > Oracle > [20181119]使用sql profile优化问题.txt

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

原创 Oracle 作者:lfree 时间:2018-11-19 15:11:00 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2220580/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2854
  • 访问量
    6642511