[20251121]生产系统sql语句优化1例.txt

--//很久没有做sql优化工作，现在基本到11月份做一次例行检查，说实在真心不想看这些垃圾，发现一条隐藏很深sql语句。
--//开发写的修改条件不足，做一个记录。

1.环境：
xxx> @ ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.问题语句：
xxx> @ sql_id cma5g4w3gyz9m
-- SQL_ID = cma5g4w3gyz9m come from shared pool
update MS_YGPJ Set SYPB =:"SYS_B_0" Where YGDM =:1 And PJLX =:"SYS_B_1" ;

xxx> @ dpc cma5g4w3gyz9m '' 0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cma5g4w3gyz9m, child number 0
-------------------------------------
update MS_YGPJ Set SYPB =:"SYS_B_0" Where YGDM =:1 And PJLX =:"SYS_B_1"
Plan hash value: 2280991720
------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |            |        |       |    85 (100)|          |       |       |          |
|   1 |  UPDATE           | MS_YGPJ    |        |       |            |          |       |       |          |
|*  2 |   INDEX RANGE SCAN| PK_MS_YGPJ |     85 |  1615 |     4   (0)| 00:00:01 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1 / MS_YGPJ@UPD$1
Peeked Binds (identified by position):
--------------------------------------
   2 - (CHAR(30), CSID=852): '7763'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("YGDM"=:1 AND "PJLX"=:SYS_B_1)
       filter("PJLX"=:SYS_B_1)
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
37 rows selected.

xxx> @ ind2 pppppp_hhh.MS_YGPJ
Display indexes where table or index name matches pppppp_hhh.MS_YGPJ...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME                    DSC
----------- ---------- ---------- ---- ------------------------------ ----
pppppp_hhh  MS_YGPJ    PK_MS_YGPJ    1 YGDM
                                     2 LYRQ
                                     3 PJLX


INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE    UNIQ STATUS PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- ------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
pppppp_hhh  MS_YGPJ    PK_MS_YGPJ NORMAL     YES  VALID  NO   N     3       1027        177399     177399     169054 2025-11-21 00:00:20 1      VISIBLE

--//执行计划看上去一切正常？走索引感觉建立不太好，应该建立主键索引的字段顺序YGDM，PJLX，LYRQ，这样可以减少索引的扫描范围。

xxx> @ desczz pppppp_hhh.MS_YGPJ YGDM,PJLX,SYPB,LYRQ
eXtended describe of pppppp_hhh.MS_YGPJ

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME  <filters>
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" .
Owner      Table_Name SAMPLE_SIZE LAST_ANALYZED       Col# Column Name Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value                                High_value
---------- ---------- ----------- ------------------- ---- ----------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------------------------------- -------------------
pppppp_hhh MS_YGPJ         177399 2025-11-21 00:00:20    1 YGDM        NOT NULL   VARCHAR2(10)                  699   .00143061516          0                           1 10026                                    9995
                           177399 2025-11-21 00:00:20    2 LYRQ        NOT NULL   DATE(7)                    177399   .00000563701          0                           1 2012-06-21 10:57:29                      2025-11-20 15:18:43
                           177399 2025-11-21 00:00:20    3 PJLX        NOT NULL   NUMBER(2,0)                     3   .33333333333          0                           1 1                                        3
                           177399 2025-11-21 00:00:20    7 SYPB        NOT NULL   NUMBER(1,0)                     2   .50000000000          0                           1 0                                        1
4 rows selected.
--//177399/699/3 = 84.6，可以看出前面估计85相关键值比较准确的。
--//仔细看统计信息马上发现问题，YGDM不相同值有699条，而PJLX不相同值有3条，可想而知执行计划走这个索引并不是最佳。
--//177399/85 = 2087.047,平均要修改2087条记录，占1/85。
--//按照道理看到这里，这条语句最佳的方式调整索引字段的建立顺序或者不调整问题不大。

xxx> @ tcc pppppp_hhh.MS_YGPJ YGDM,PJLX,SYPB,LYRQ
DISPLAY TABLE_NAME COMMENTS INFORMATION (pppppp_hhh.MS_YGPJ)
OWNER      TABLE_NAME TABLE_TYPE COMMENTS
---------- ---------- ---------- -----------
pppppp_hhh MS_YGPJ    TABLE      门诊员工飘据

DISPLAY COLUMN_NAME COMMENTS INFORMATION (pppppp_hhh.MS_YGPJ)
OWNER      TABLE_NAME COLUMN_NAME COMMENTS
---------- ---------- ----------- -----------
pppppp_hhh MS_YGPJ    YGDM        员工代码
pppppp_hhh MS_YGPJ    LYRQ        领用日期
pppppp_hhh MS_YGPJ    PJLX        飘据类型
pppppp_hhh MS_YGPJ    SYPB        使用判别
--//再仔细看可以发现问题update语句sypb字段，可以作出一个大胆的猜测开发sql语句写错了，少写了一个条件SYPB=NN.

xxx> @ cntg pppppp_hhh.MS_YGPJ sypb
select count(*) , sypb  from pppppp_hhh.MS_YGPJ group by sypb order by 1 desc;
  COUNT(*)       SYPB
---------- ----------
    176200          1
      1232          0

--//看到这里基本验证我的判断。
--//sql语句应该写成如下：
update MS_YGPJ Set SYPB =:"SYS_B_0" Where YGDM =:1 And PJLX =:"SYS_B_1"  and SYPB=0;
--//前面的:"SYS_B_0"值看不见可以猜测应该是1，SYPB使用了等于1，没使用等于0。

xxx> @ bind_cap cma5g4w3gyz9m ''
SQL_ID        CHILD_NUMBER WAS NAME         POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING INST_ID
------------- ------------ --- ---------- ---------- ---------- ------------------- --------------- ------------ -------
cma5g4w3gyz9m            0 YES :1                  2         32 2025-11-21 02:00:43 CHAR(32)        1707               1
                           YES :SYS_B_1            3         22 2025-11-21 02:00:43 NUMBER          3                  1

                         1 YES :1                  2         32 2025-11-20 00:17:58 CHAR(32)        9232               1
                           YES :SYS_B_1            3         22 2025-11-20 00:17:58 NUMBER          3                  1

                         2 YES :1                  2         32 2025-11-21 08:18:04 CHAR(32)        1990               1
                           YES :SYS_B_1            3         22 2025-11-21 08:18:04 NUMBER          3                  1
6 rows selected.

xxx> select count(*) , sypb  from pppppp_hhh.MS_YGPJ where YGDM='1990' and PJLX=3 group by sypb order by 1 desc;
  COUNT(*)       SYPB
---------- ----------
       851          1
         1          0
--//很明显SYPB大部分都是1，而且估算存在大的误差，当然该问题不是很大。

xxx> select sql_text,executions,ROWS_PROCESSED from gv$sqlarea where sql_id='cma5g4w3gyz9m';
SQL_TEXT                                                                         EXECUTIONS ROWS_PROCESSED
-------------------------------------------------------------------------------- ---------- --------------
update MS_YGPJ Set SYPB =:"SYS_B_0" Where YGDM =:1 And PJLX =:"SYS_B_1"                 119         152071
--//执行119次处理行数152071,平均 152071/119 = 1278.

xxx> @ seg2 pppppp_hhh.MS_YGPJ
SEG_MB OWNER                SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL  HDRBLK
------ -------------------- ------------ ------------ ------------------- ------ ------ -------
    12 pppppp_hhh           MS_YGPJ      TABLE        pppppp_hhh            1536     35 1818243
--//表不大，所以隐藏很深，很难从sql语句的执行性能上发现问题。

--//补充在表MS_YGPJ的字段SYPB上建立直方图。
BEGIN
   DBMS_STATS.gather_table_stats (
      'pppppp_hhh'
     ,'MS_YGPJ'
     ,estimate_percent   => NULL
     ,method_opt         => 'FOR TABLE FOR ALL COLUMNS SIZE repeat for columns SYPB size 254'
     ,cascade            => TRUE
     ,no_invalidate      => FALSE);
END
/

--//建立索引：
create index i_MS_YGPJ_SYPB_YGDM on MS_YGPJ(SYPB,YGDM) compress 2;
--//这样要修改代码，整个优化完成。

3.小结：
--//建议团队通过logmimer看看还有那些遗漏，使用相关视图判断SQL_REDO=SQL_UNDO语句要仔细查看。