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

[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语句要仔细查看。