执行时间在1秒以下的SQL同样也会引发性能问题

某客户的操作人员反应很慢不能操作,管理人员登录小机系统后发现CPU使用到了95%以上。而且这种情况持续了几个月。小机是IBM的P520,配置是2颗4核的CPU,内存是32G,Oracle是10.2.0.5。topas与生成的AWR报告如下:
10

3

4

从上面的load profile部分可以看到每秒执行的sql与事务数并不高,因为是周末并没有太多人使用系统。
5

从上面的top 等待事件来看主要是CPU time。如是查看这个时间段生成的ADDM报告:

          DETAILED ADDM REPORT FOR TASK 'TASK_72988' WITH ID 72988
          --------------------------------------------------------

              Analysis Period: 20-DEC-2015 from 09:00:33 to 10:00:34
         Database ID/Instance: 1329630138/1
      Database/Instance Names: ORCL/orcl
                    Host Name: dbserv
             Database Version: 10.2.0.5.0
               Snapshot Range: from 26245 to 26246
                Database Time: 16644 seconds
        Average Database Load: 4.6 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FINDING 1: 93% impact (15444 seconds)
-------------------------------------
SQL statements consuming significant database time were found.

   RECOMMENDATION 1: SQL Tuning, 100% benefit (38004 seconds)
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
         "fb44z8kbnu8wg".
         RELEVANT OBJECT: SQL statement with SQL_ID fb44z8kbnu8wg and
         PLAN_HASH 1055903002
         SELECT COUNT(EmailDetail.F_EMAIL_DETAIL_ID) AS UnReadCount FROM
         T_LK_EMAIL_EMAILGROUP EmailGroup LEFT OUTER JOIN T_LK_EMAIL_DETAIL
         EmailDetail ON EmailGroup.F_EMAIL_GROUP_ID=EmailDetail.F_EMAIL_GROUP_
         ID AND (EmailDetail.F_RECEIVER_ID=:param0 OR
         (EmailDetail.F_SENDER_ID=:param0 AND EmailDetail.F_RECEIVER_ID IS
         NULL)) AND EmailDetail.F_EMAIL_STATE='0' AND
         EmailDetail.F_RECORD_STATE='0' WHERE EmailGroup.F_PARENT_ID=0 AND
         (EmailGroup.F_EMAIL_GROUP_ID!=4 OR (EmailGroup.F_GROUP_TYPE='USER'
         AND EmailGroup.F_USER_ID=:param0))
      ACTION: Investigate the SQL statement with SQL_ID "fb44z8kbnu8wg" for
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID fb44z8kbnu8wg and
         PLAN_HASH 1055903002
         SELECT COUNT(EmailDetail.F_EMAIL_DETAIL_ID) AS UnReadCount FROM
         T_LK_EMAIL_EMAILGROUP EmailGroup LEFT OUTER JOIN T_LK_EMAIL_DETAIL
         EmailDetail ON EmailGroup.F_EMAIL_GROUP_ID=EmailDetail.F_EMAIL_GROUP_
         ID AND (EmailDetail.F_RECEIVER_ID=:param0 OR
         (EmailDetail.F_SENDER_ID=:param0 AND EmailDetail.F_RECEIVER_ID IS
         NULL)) AND EmailDetail.F_EMAIL_STATE='0' AND
         EmailDetail.F_RECORD_STATE='0' WHERE EmailGroup.F_PARENT_ID=0 AND
         (EmailGroup.F_EMAIL_GROUP_ID!=4 OR (EmailGroup.F_GROUP_TYPE='USER'
         AND EmailGroup.F_USER_ID=:param0))
      RATIONALE: SQL statement with SQL_ID "fb44z8kbnu8wg" was executed 16029
         times and had an average elapsed time of 0.93 seconds.

  

FINDING 2: 76% impact (12602 seconds)
-------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.

   RECOMMENDATION 1: SQL Tuning, 100% benefit (38004 seconds)
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
         "fb44z8kbnu8wg".
         RELEVANT OBJECT: SQL statement with SQL_ID fb44z8kbnu8wg and
         PLAN_HASH 1055903002
         SELECT COUNT(EmailDetail.F_EMAIL_DETAIL_ID) AS UnReadCount FROM
         T_LK_EMAIL_EMAILGROUP EmailGroup LEFT OUTER JOIN T_LK_EMAIL_DETAIL
         EmailDetail ON EmailGroup.F_EMAIL_GROUP_ID=EmailDetail.F_EMAIL_GROUP_
         ID AND (EmailDetail.F_RECEIVER_ID=:param0 OR
         (EmailDetail.F_SENDER_ID=:param0 AND EmailDetail.F_RECEIVER_ID IS
         NULL)) AND EmailDetail.F_EMAIL_STATE='0' AND
         EmailDetail.F_RECORD_STATE='0' WHERE EmailGroup.F_PARENT_ID=0 AND
         (EmailGroup.F_EMAIL_GROUP_ID!=4 OR (EmailGroup.F_GROUP_TYPE='USER'
         AND EmailGroup.F_USER_ID=:param0))
      ACTION: Investigate the SQL statement with SQL_ID "fb44z8kbnu8wg" for
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID fb44z8kbnu8wg and
         PLAN_HASH 1055903002
         SELECT COUNT(EmailDetail.F_EMAIL_DETAIL_ID) AS UnReadCount FROM
         T_LK_EMAIL_EMAILGROUP EmailGroup LEFT OUTER JOIN T_LK_EMAIL_DETAIL
         EmailDetail ON EmailGroup.F_EMAIL_GROUP_ID=EmailDetail.F_EMAIL_GROUP_
         ID AND (EmailDetail.F_RECEIVER_ID=:param0 OR
         (EmailDetail.F_SENDER_ID=:param0 AND EmailDetail.F_RECEIVER_ID IS
         NULL)) AND EmailDetail.F_EMAIL_STATE='0' AND
         EmailDetail.F_RECORD_STATE='0' WHERE EmailGroup.F_PARENT_ID=0 AND
         (EmailGroup.F_EMAIL_GROUP_ID!=4 OR (EmailGroup.F_GROUP_TYPE='USER'
         AND EmailGroup.F_USER_ID=:param0))
      RATIONALE: SQL statement with SQL_ID "fb44z8kbnu8wg" was executed 16029
         times and had an average elapsed time of 0.93 seconds.
      RATIONALE: Average CPU used per execution was 0.76 seconds.

  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          ADDITIONAL INFORMATION
          ----------------------

Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.

The database's maintenance windows were active during 99% of the analysis
period.

The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.

An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.

task_name
---------
TASK_72988

6

7

从上面的信息ADDM报告与top sql部分可以看到在快照26245到26246之间Database Time为16644秒。而找到的一条SQL消耗了76%的CPU时间。如果对这两条SQL执行优化应该可以将CPU消耗显著降低。而该SQL虽然每执行一次的时间是0.93秒,消耗的CPU时间只有0.76秒,但在周末的时间内一个小时都执行了16,029次,消耗的CPU时间是12249秒而且小机的CPU数量只有2颗(6核),那么每秒该SQL的执行次数就是=16029/3600=4.5次,所以大部分的CPU被该SQL所消耗了。这还是周末,如果上班时间该SQL执行的次数会以倍数增加,那么CPU的消耗就会更高。

SQL语句如下:

SELECT COUNT(EmailDetail.F_EMAIL_DETAIL_ID) AS UnReadCount
  FROM T_LK_EMAIL_EMAILGROUP EmailGroup
  LEFT OUTER JOIN T_LK_EMAIL_DETAIL EmailDetail ON EmailGroup.F_EMAIL_GROUP_ID =
                                                   EmailDetail.F_EMAIL_GROUP_ID
                                               AND (EmailDetail.F_RECEIVER_ID =3440
                                                    OR
                                                   (EmailDetail.F_SENDER_ID =3418
                                                    AND
                                                   EmailDetail.F_RECEIVER_ID IS NULL))
                                               AND EmailDetail.F_EMAIL_STATE = '0'
                                               AND EmailDetail.F_RECORD_STATE = '0'
 WHERE EmailGroup.F_PARENT_ID = 0
   AND (EmailGroup.F_EMAIL_GROUP_ID != 4 OR
       (EmailGroup.F_GROUP_TYPE = 'USER' AND
       EmailGroup.F_USER_ID = 23402));

其执行计划如下:
8
从执行计划来看该SQL的cost也不高(执行时间是0.93秒,cpu时间是0.76秒),从SQL的执行计划来看见两个表是使用的嵌套循环,而驱动表T_LK_EMAIL_EMAILGROUP的数据量是1w多行,T_LK_EMAIL_DETAIL表的数量是20w行左右。而表T_LK_EMAIL_EMAILGROUP执行全表扫描后满足查询条件的记录有4条,所以就得对表T_LK_EMAIL_DETAIL中的记录遍历4次来找到与驱动表相匹配的记录,虽然每次执行时间不长,但是在并发执行次数高,而物理CPU数量不足的情况下还是会引发性能问题。而这两个表有等值连接条件F_EMAIL_GROUP_ID,而且在驱动表中F_EMAIL_GROUP_ID列创建了索引,所以这里选择在表T_LK_EMAIL_DETAIL表的F_EMAIL_GROUP_ID列上创建索引之后执行计划如下所示:
9
SQL执行计划的COST显示增加了,但SQL执行时间只有0.1s提高了10倍。在对该SQL优化之后,业务系统恢复正常,CPU使用率也维持在20%左右。
11

所以在优化时,不能简单的根据SQL执行时间来判断该SQL是否会引发性能问题,要具体问题具体分析。

AIX filesystemcache引发的Oracle事故

今天上班同事说医保数据库expdp导出没有完成,同时医保业务人员报告登录系统有时能登录,有时不能登录。Expdp导出日志如下:

[IBMP740-1:root:/yb_oradata/RLZYbak/dpdump]#cat insur_changde_150921_2330.log
Export: Release 10.2.0.4.0 - 64bit Production on Monday, 21 September, 2015 23:30:00

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "INSUR_CHANGDE"."SYS_EXPORT_SCHEMA_07":  insur_changde/******** directory=dump_RLZY dumpfile=insur_changde_150921_2330.dmp logfile=insur_changde_150921_2330.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 492.0 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
. . exported "INSUR_CHANGDE"."MT_BIZ_SCENE_FIN"          51.86 GB 1111873243 rows
. . exported "INSUR_CHANGDE"."MT_FEE_FIN"                22.76 GB 133817090 rows

从上面的expdp日志信息来看并没有错误,更像理导出进程停止,如果查看dba_datapump_job视图来查看有没有被异常终止的epxdp导出job
1

从上面的信息可以看到insur_changde用户出现了多个expdp导出异常终止的job。从job命名规则可以看到最近的异常终止job是sys_export_schema_07,而且状态是空闲的。那么重新连接sys_export_schema_07这个job来查看job状态。

[IBMP740-1:oracle:/yb_oradata/RLZYbak]$expdp 'insur_changde/"power$20140224"' attach=SYS_EXPORT_SCHEMA_07

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 22 September, 2015 16:51:51

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_SCHEMA_07
  Owner: INSUR_CHANGDE                  
  Operation: EXPORT                         
  Creator Privs: FALSE                          
  GUID: 20448E2327C5015EE053C0A80201015E
  Start Time: Tuesday, 22 September, 2015 16:51:56
  Mode: SCHEMA                         
  Instance: RLZY
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        insur_changde/******** directory=dump_RLZY dumpfile=insur_changde_150921_2330.dmp logfile=insur_changde_150921_2330.log 
  State: IDLING                         
  Bytes Processed: 80,139,523,792
  Percent Done: 41
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /yb_oradata/RLZYbak/dpdump/insur_changde_150921_2330.dmp
    bytes written: 80,145,354,752
  
Worker 1 Status:
  State: UNDEFINED                      
  Object Schema: INSUR_CHANGDE
  Object Name: LV_INDIPAR
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 3
  Total Objects: 1,225
  Completed Rows: 288,824,659
  Worker Parallelism: 1
  
Worker 1 Status:
  State: UNDEFINED                      
  Object Schema: INSUR_CHANGDE
  Object Name: LV_INDIPAR
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 3
  Total Objects: 1,225
  Completed Rows: 288,824,659
  Worker Parallelism: 1

从上面的信息可以看出现在expdp job正在导出的表为LV_INDIPAR表,状态为UNDEFINED,也没有其它有用信息。那么为什么expdp job会异常终止了。检查alert.log文件在执行expdp导出时出现了以下错误信息。

Starting control autobackup
Control autobackup written to SBT_TAPE device
	comment 'API Version 2.0,MMS Version 1.2.0.0',
	media 'backup_nw.023.RO'
	handle 'c-1589671076-20150921-00'
Mon Sep 21 23:30:02 2015
The value (30) of MAXTRANS parameter ignored.
kupprdp: master process DM00 started with pid=188, OS id=23527444
         to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_SCHEMA_07', 'INSUR_CHANGDE', 'KUPC$C_1_20150921233002', 'KUPC$S_1_20150921233002', 0);
kupprdp: worker process DW01 started with worker id=1, pid=189, OS id=1704856
         to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_07', 'INSUR_CHANGDE');
Tue Sep 22 00:24:18 2015
ksvcreate: Process(q001) creation failed
Tue Sep 22 00:24:38 2015
Process startup failed, error stack:
Tue Sep 22 00:24:39 2015
Errors in file /oracle/admin/RLZY/bdump/rlzy_psp0_7471450.trc:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
Tue Sep 22 00:24:39 2015
Process q001 died, see its trace file
Tue Sep 22 00:24:39 2015
ksvcreate: Process(q001) creation failed
Tue Sep 22 00:24:51 2015

从上面信息可以看到expdp job是在21号的23:30开始执行,在22号的00:24:39出现了故障并在/oracle/admin/RLZY/bdump/rlzy_psp0_7471450.trc文件中生成了错误信息如下。

*** 2015-09-20 00:24:36.347
Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

根据MOS文章Troubleshooting ORA-27300 ORA-27301 ORA-27302 errors (Doc ID 579365.1),出现这种错误信息主要是因为内存或交换区被用尽的原因,如是检查系统内存与交换区的使用情况

[IBMP740-1:root:/]#topas_nmon
lqtopas_nmonqqh=HelpqqqqqqqqqqqqqHost=IBMP740-1qqqqqqRefresh=2 secsqqq16:58.25
x Memory x
x          Physical  PageSpace |        pages/sec  In     Out | FileSystemCache                                                                                       x
x% Used       99.8%     68.0%  | to Paging Space   0.0    0.0 | (numperm) 49.6%                                                                                       x
x% Free        0.2%     32.0%  | to File System  586.6   11.7 | Process   42.7%                                                                                       x
xMB Used   63572.0MB 11142.9MB | Page Scans      126.5        | System     7.6%                                                                                       x
xMB Free     108.0MB  5241.1MB | Page Cycles       0.0        | Free       0.2%                                                                                       x
xTotal(MB) 63680.0MB 16384.0MB | Page Steals     126.5        |           ------                                                                                      x
x                              | Page Faults    1317.1        | Total    100.0%                                                                                       x
x------------------------------------------------------------ | numclient 49.6%                                                                                       x
xMin/Maxperm     1853MB(  3%)  55589MB( 90%) < --% of RAM      | maxclient 90.0%                                                                                       x
xMin/Maxfree     960   1088       Total Virtual   78.2GB      | User      89.3%                                                                                       x
xMin/Maxpgahead    2      8    Accessed Virtual   33.6GB 43.0%| Pinned     9.4%                                                                                       x
x                                                             | lruable pages   15811872.0  

从上面的信息可以看到物理内存为63680.0MB,交换区为16384.0M了,物理内存使用了63572.0M,交换区使用了11142.9M,物理内存了可用内存只有108.0M占总物理内存的0.2%,交换区是5241.1M占总交换区的32%。FileSystemCache (numperm) 49.6% 说明AIX 文件系统缓存占用了物理内存的49.6%,Process 42.7%说明进程占用了物理内存的42.7%,System 7.6%说明系统占用了物理内存的7.6%, Free 0.2%说明了可用的物理内存只有0.2%。并且可以看到Maxperm=90%,maxclient=90%,说明文件系统缓存使用物理内存的最大限制为物理内存的90%。

检查AIX系统中消耗内存前10的进程,如下所示大部分是Oracle相关进程

[IBMP740-1:root:/]#ps -ealf | head -1 ; ps -ealf | sort -rn +9 | head 
       F S      UID      PID     PPID   C PRI NI ADDR    SZ    WCHAN    STIME    TTY  TIME CMD
  240001 A   oracle  6553662        1   0  60 20 a31123590 115936            Jun 27      - 22:05 ora_lgwr_RLZY
  240001 A   oracle 57671750        1   0  60 20 c41744590 111768 f1000e0004ee48c8   Sep 16      - 28:48 oracleRLZY (LOCAL=NO)
  240001 A   oracle 61735218        1   0  60 20 c44fc4590 109912 f1000e00100440c8   Sep 16      - 31:40 oracleRLZY (LOCAL=NO)
  240001 A   oracle 58982776        1   0  60 20 fb447b590 109528 f1000e0004a0b8c8   Sep 16      - 12:57 oracleRLZY (LOCAL=NO)
  240001 A   oracle 26935684        1   0  60 20 f416f4590 108264            Jun 27      -  2:07 ora_arc1_RLZY
  240001 A   oracle 26870144        1   0  60 20 cf16cf590 108264            Jun 27      -  2:37 ora_arc0_RLZY
  240001 A   oracle  7536818        1   0  60 20 a71127590 108248            Jun 27      - 15:59 ora_cjq0_RLZY
  240001 A   oracle  7733430        1   0  60 20 8a0e0a590 106096            Jun 27      -  8:54 ora_dbw0_RLZY
  240001 A   oracle  8913722        1  24  72 20 864c86590 104764            Sep 16      - 18:14 oracleRLZY (LOCAL=NO)
  240001 A   oracle 26214712        1   0  60 20 944194590 104584          16:51:55      -  0:00 ora_dm00_RLZY

[IBMP740-1:root:/]#topas -M
Topas Monitor for host:    IBMP740-1   Interval:   2    Tue Sep 22 17:13:05 2015
================================================================================
REF1    SRAD  TOTALMEM  INUSE    FREE    FILECACHE  HOMETHRDS  CPUS
--------------------------------------------------------------------------------
   0     0     60.4G    60.3G    106.5    30.8G        748      0-31
   1     1       0.0      0.0      0.0      0.0        625      32-63
================================================================================
CPU     SRAD  TOTALDISP   LOCALDISP%  NEARDISP%   FARDISP%
------------------------------------------------------------
  36       1       439      100.0         0.0        0.0
  60       1       345      100.0         0.0        0.0
  56       1       184      100.0         0.0        0.0
   0       0       144      100.0         0.0        0.0
  32       1        93      100.0         0.0        0.0
  16       0        88      100.0         0.0        0.0
   8       0        54      100.0         0.0        0.0
  40       1        43      100.0         0.0        0.0
  12       0        36      100.0         0.0        0.0
  20       0        28      100.0         0.0        0.0
   4       0        28      100.0         0.0        0.0
  28       0        21      100.0         0.0        0.0
  44       1        18      100.0         0.0        0.0
  24       0        12      100.0         0.0        0.0
  52       1        11      100.0         0.0        0.0
  48       1         1      100.0         0.0        0.0
  17       0         0      0.0           0.0        0.0
  18       0         0      0.0           0.0        0.0
  19       0         0      0.0           0.0        0.0
  10       0         0      0.0           0.0        0.0
  21       0         0      0.0           0.0        0.0
  22       0         0      0.0           0.0        0.0
  23       0         0      0.0           0.0        0.0
   9       0         0      0.0           0.0        0.0
  25       0         0      0.0           0.0        0.0
  26       0         0      0.0           0.0        0.0
  27       0         0      0.0           0.0        0.0
   7       0         0      0.0           0.0        0.0
  29       0         0      0.0           0.0        0.0
  30       0         0      0.0           0.0        0.0
  31       0         0      0.0           0.0        0.0
   6       0         0      0.0           0.0        0.0
  33       1         0      0.0           0.0        0.0
   5       0         0      0.0           0.0        0.0

从上面的信息可知除了系统所用的物理内存之外,总的可用物理内存是60.4G,使用了60.3G,可用106.5M,文件系统缓存是30.8G。
使用操作系统命令vmo -a –F来查看操作系统参数

[IBMP740-1:root:/]#vmo -a -F
             ame_cpus_per_pool = n/a
               ame_maxfree_mem = n/a
           ame_min_ucpool_size = n/a
               ame_minfree_mem = n/a
               ams_loan_policy = n/a
  enhanced_affinity_affin_time = 1
enhanced_affinity_vmpool_limit = 10
                esid_allocator = 0
           force_relalias_lite = 0
             kernel_heap_psize = 65536
                  lgpg_regions = 0
                     lgpg_size = 0
               low_ps_handling = 1
                       maxfree = 1088
                       maxperm = 14230680
                        maxpin = 13137354
                       maxpin% = 80
                 memory_frames = 16302080
                 memplace_data = 0
          memplace_mapped_file = 0
        memplace_shm_anonymous = 0
            memplace_shm_named = 0
                memplace_stack = 0
                 memplace_text = 0
        memplace_unmapped_file = 0
                       minfree = 960
                       minperm = 474353
                      minperm% = 3
                     nokilluid = 0
                       npskill = 32768
                       npswarn = 131072
           num_locks_per_semid = 1
                     numpsblks = 4194304
               pinnable_frames = 14750156
           relalias_percentage = 0
                         scrub = 0
                      v_pinshm = 0
              vmm_default_pspa = 0
                vmm_klock_mode = 1
            wlm_memlimit_nonpg = 1
##Restricted tunables
               ame_sys_memview = n/a
                cpu_scale_memp = 8
         data_stagger_interval = 161
                         defps = 1
enhanced_affinity_attach_limit = 100
     enhanced_affinity_balance = 100
     enhanced_affinity_private = 40
      enhanced_memory_affinity = 1
                     framesets = 2
                     htabscale = n/a
                  kernel_psize = 65536
          large_page_heap_size = 0
               lru_file_repage = 0
             lru_poll_interval = 10
                     lrubucket = 131072
                    maxclient% = 90
                      maxperm% = 90
               mbuf_heap_psize = 65536
               memory_affinity = 1
          multiple_semid_lists = 0
                 munmap_npages = 16384
                     npsrpgmax = 262144
                     npsrpgmin = 196608
                   npsscrubmax = 262144
                   npsscrubmin = 196608
            num_sem_undo_lists = 0
             num_sems_per_lock = 1
              num_spec_dataseg = 0
                numperm_global = 1
             page_steal_method = 1
          psm_timeout_interval = 20000
             relalias_lockmode = 1
                      rpgclean = 0
                    rpgcontrol = 2
                    scrubclean = 0
                shm_1tb_shared = 12
           shm_1tb_unsh_enable = 1
              shm_1tb_unshared = 256
         soft_min_lgpgs_vmpool = 0
              spec_dataseg_int = 512
              strict_maxclient = 1
                strict_maxperm = 0
                   sync_npages = 0
                 thrpgio_inval = 1024
                thrpgio_npages = 1024
               vm_mmap_areload = 0
          vm_modlist_threshold = -1
              vm_pvlist_dohard = 0
              vm_pvlist_szpcnt = 0
               vmm_fork_policy = 1
            vmm_mpsize_support = 2
               vmm_vmap_policy = 0
                  vtiol_avg_ms = 200
                  vtiol_minreq = 25
            vtiol_minth_active = 1
                    vtiol_mode = 0
               vtiol_pgin_mode = 2
              vtiol_pgout_mode = 2
               vtiol_q_cpu_pct = 2500
          vtiol_thread_cpu_pct = 5000

主要是maxclient% = 90,maxperm% = 90参数,说明文件系统缓存使用物理内存的最大限制为物理内存的90%。所以这里只需要将maxclient%与maxperm%参数调小,让系统有空闲内存来分配给新产生的进程来执行特定操作。调整maxclient%与maxperm%参数。

[IBMP740-1:root:/]#vmo -p -o maxclient%=20
Modification to restricted tunable maxclient%, confirmation required yes/no yes
Setting maxclient% to 20 in nextboot file
Setting maxclient% to 20
Warning: a restricted tunable has been modified
[IBMP740-1:root:/]#vmo -p -o maxperm%=20
Modification to restricted tunable maxperm%, confirmation required yes/no yes
Setting maxperm% to 20 in nextboot file
Setting maxperm% to 20
Warning: a restricted tunable has been modified

调整后再次查看操作系统参数

[IBMP740-1:root:/]#vmo -a -F
             ame_cpus_per_pool = n/a
               ame_maxfree_mem = n/a
           ame_min_ucpool_size = n/a
               ame_minfree_mem = n/a
               ams_loan_policy = n/a
  enhanced_affinity_affin_time = 1
enhanced_affinity_vmpool_limit = 10
                esid_allocator = 0
           force_relalias_lite = 0
             kernel_heap_psize = 65536
                  lgpg_regions = 0
                     lgpg_size = 0
               low_ps_handling = 1
                       maxfree = 1088
                       maxperm = 3162370
                        maxpin = 13137354
                       maxpin% = 80
                 memory_frames = 16302080
                 memplace_data = 0
          memplace_mapped_file = 0
        memplace_shm_anonymous = 0
            memplace_shm_named = 0
                memplace_stack = 0
                 memplace_text = 0
        memplace_unmapped_file = 0
                       minfree = 960
                       minperm = 790590
                      minperm% = 5
                     nokilluid = 0
                       npskill = 32768
                       npswarn = 131072
           num_locks_per_semid = 1
                     numpsblks = 4194304
               pinnable_frames = 14770780
           relalias_percentage = 0
                         scrub = 0
                      v_pinshm = 0
              vmm_default_pspa = 0
                vmm_klock_mode = 1
            wlm_memlimit_nonpg = 1
##Restricted tunables
               ame_sys_memview = n/a
                cpu_scale_memp = 8
         data_stagger_interval = 161
                         defps = 1
enhanced_affinity_attach_limit = 100
     enhanced_affinity_balance = 100
     enhanced_affinity_private = 40
      enhanced_memory_affinity = 1
                     framesets = 2
                     htabscale = n/a
                  kernel_psize = 65536
          large_page_heap_size = 0
               lru_file_repage = 0
             lru_poll_interval = 10
                     lrubucket = 131072
                    maxclient% = 20
                      maxperm% = 20
               mbuf_heap_psize = 65536
               memory_affinity = 1
          multiple_semid_lists = 0
                 munmap_npages = 16384
                     npsrpgmax = 262144
                     npsrpgmin = 196608
                   npsscrubmax = 262144
                   npsscrubmin = 196608
            num_sem_undo_lists = 0
             num_sems_per_lock = 1
              num_spec_dataseg = 0
                numperm_global = 1
             page_steal_method = 1
          psm_timeout_interval = 20000
             relalias_lockmode = 1
                      rpgclean = 0
                    rpgcontrol = 2
                    scrubclean = 0
                shm_1tb_shared = 12
           shm_1tb_unsh_enable = 1
              shm_1tb_unshared = 256
         soft_min_lgpgs_vmpool = 0
              spec_dataseg_int = 512
              strict_maxclient = 1
                strict_maxperm = 0
                   sync_npages = 0
                 thrpgio_inval = 1024
                thrpgio_npages = 1024
               vm_mmap_areload = 0
          vm_modlist_threshold = -1
              vm_pvlist_dohard = 0
              vm_pvlist_szpcnt = 0
               vmm_fork_policy = 1
            vmm_mpsize_support = 2
               vmm_vmap_policy = 0
                  vtiol_avg_ms = 200
                  vtiol_minreq = 25
            vtiol_minth_active = 1
                    vtiol_mode = 0
               vtiol_pgin_mode = 2
              vtiol_pgout_mode = 2
               vtiol_q_cpu_pct = 2500
          vtiol_thread_cpu_pct = 5000

从上面的结果看到修改生效了,文件系统缓存最大可以使用20%的物理内存。

[IBMP740-1:root:/]#topas_nmon
lqtopas_nmonqqh=HelpqqqqqqqqqqqqqHost=IBMP740-1qqqqqqRefresh=2 secsqqq17:44.52
x Memory x
x          Physical  PageSpace |        pages/sec  In     Out | FileSystemCache                                                                                       x
x% Used       69.8%     67.2%  | to Paging Space   0.5    0.0 | (numperm) 19.3%                                                                                       x
x% Free       30.2%     32.8%  | to File System 9455.5    8.4 | Process   42.9%                                                                                       x
xMB Used   44476.3MB 11010.5MB | Page Scans     9562.1        | System     7.6%                                                                                       x
xMB Free   19203.7MB  5373.5MB | Page Cycles       0.0        | Free      30.2%                                                                                       x
xTotal(MB) 63680.0MB 16384.0MB | Page Steals    9510.6        |           ------                                                                                      x
x                              | Page Faults    7478.9        | Total    100.0%                                                                                       x
x------------------------------------------------------------ | numclient 19.3%                                                                                       x
xMin/Maxperm     3088MB(  5%)  12353MB( 20%) < --% of RAM      | maxclient 20.0%                                                                                       x
xMin/Maxfree     960   1088       Total Virtual   78.2GB      | User      59.3%                                                                                       x
xMin/Maxpgahead    2      8    Accessed Virtual   33.6GB 43.0%| Pinned     9.4%                                                                                       x
x                                                             | lruable pages   15811872.0

可以看到现在空闲物理内存为30.2%,文件系统缓存(FileSystemCache) 19.2%,maxperm,maxclient为20%。现在执行expdp导出正常。

通过这个问题可以看出,AIX为了提高系统IO能力将空闲的物理内存作为文件系统缓存来使用,而且缺省参数可以使用物理内存的90%,这个缺省值在实际的生产环境中是很容易将内存耗尽的,所以AIX推荐的缺省值也是有问题的。

谓词条件的数据类型随意书写对SQL性能造成巨大的影响

最近在优化某系统中发现许多SQL语句在书写谓词条件(wheret条件)时完全不根据表结构定义的字段数据类型来,而是随意书写谓词条件,这样造成原来能走正确索引的结果不能使用该索引,其结果就是查询语句的性能很差,这里将我所遇到的两种情况介绍一下.

第一种情况是谓词条件进行了数据类型的转换转换使得CBO无法使用索引:
其SQL语句如下所示,该SQL的功能是统计一年社保中心一年内由于各种伤害或骨折所发生的医疗费用

select a.hospital_id,
       c.hospital_name,
       count(distinct a.serial_no) rc,
       round(sum(b.real_pay), 2) ylfyze,
       round(sum(case
                   when b.fund_id in ('001') then
                    b.real_pay
                   else
                    0
                 end),
             2) tczc,
       round(sum(case
                   when b.fund_id in ('201') then
                    b.real_pay
                   else
                    0
                 end),
             2) zffy,
       round(sum(case
                   when b.fund_id in ('003', '999') then
                    b.real_pay
                   else
                    0
                 end),
             2) yyzf
  from mt_biz_fin a, mt_pay_record_fin b, bs_hospital c, bs_disease d
 where a.hospital_id = b.hospital_id
   and a.serial_no = b.serial_no
   and a.hospital_id = c.hospital_id
   and a.fin_disease = d.icd
   and d.center_id = a.center_id
   and a.valid_flag = 1
   and b.valid_flag = 1
   and a.biz_type = 12
   and a.pers_type in (1, 2)
   and (d.disease like '%伤%' or d.disease like '%骨折%')
   and a.center_id = '430740'
   and to_char(a.fin_date, 'yyyymmdd') >= '20140101'
   and to_char(a.fin_date, 'yyyymmdd') < = '20141231'
 group by a.hospital_id, c.hospital_name
 order by a.hospital_id

上述SQL执行情况如下,其执行时间为4分40秒

SQL> set timing on
SQL> set autotrace traceonly
SQL> select c.hospital_id,
  2         c.hospital_name,
  3         count(distinct a.serial_no) rc,
  4         round(sum(b.real_pay), 2) ylfyze,
  5         round(sum(case
  6                     when b.fund_id in ('001') then
  7                      b.real_pay
  8                     else
  9                      0
 10                   end),
 11               2) tczc,
 12         round(sum(case
 13                     when b.fund_id in ('201') then
 14                      b.real_pay
 15                     else
 16                      0
 17                   end),
 18               2) zffy,
 19         round(sum(case
 20                     when b.fund_id in ('003', '999') then
 21                      b.real_pay
 22                     else
 23                      0
 24                   end),
 25               2) yyzf
 26    from mt_biz_fin a, mt_pay_record_fin b, bs_hospital c, bs_disease d
 27   where a.hospital_id = b.hospital_id
 28     and a.serial_no = b.serial_no
 29     and a.hospital_id = c.hospital_id
 30     and a.fin_disease = d.icd
 31     and d.center_id = a.center_id
 32     and a.valid_flag = 1
 33     and b.valid_flag = 1
 34     and a.biz_type = 12
 35     and a.pers_type in (1, 2)
 36     and (d.disease like '%伤%' or d.disease like '%骨折%')
 37     and a.center_id = '430740'
 38     and to_char(a.fin_date, 'yyyymmdd') >= '20140101'
 39     and to_char(a.fin_date, 'yyyymmdd') < = '20141231'
 40   group by c.hospital_id, c.hospital_name
 41   order by c.hospital_id
 42  ;

Elapsed: 00:04:39.59

Execution Plan
----------------------------------------------------------
Plan hash value: 1467084556

---------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |     1 |   148 |  4254  (20)| 00:00:04 |
|   1 |  SORT GROUP BY                   |                      |     1 |   148 |  4254  (20)| 00:00:04 |
|*  2 |   TABLE ACCESS BY INDEX ROWID    | MT_PAY_RECORD_FIN    |     1 |    31 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                      |     1 |   148 |  4252  (20)| 00:00:04 |
|   4 |     NESTED LOOPS                 |                      |     1 |   117 |  4251  (20)| 00:00:04 |
|   5 |      NESTED LOOPS                |                      |     3 |   252 |  4250  (20)| 00:00:04 |
|   6 |       INDEX FULL SCAN            | IDX_BS_HOSPITAL_NAME |  1227 | 39264 |     2   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN           |     1 |    52 |     3   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN          | PK_MT_BIZ_FIN        |     1 |       |     3   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS BY INDEX ROWID | BS_DISEASE           |     1 |    33 |     1   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN           | INX_BS_DISEASE_01    |     1 |       |     1   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN             | I_MT_PAY_RECORD_FIN_1|     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
   7 - filter(TO_NUMBER("A"."VALID_FLAG")=1 AND (TO_NUMBER("A"."PERS_TYPE")=1 OR
              TO_NUMBER("A"."PERS_TYPE")=2))
   8 - access("A"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "A"."CENTER_ID"='430740')
       filter("A"."CENTER_ID"='430740' AND TO_NUMBER("A"."BIZ_TYPE")=12 AND
              TO_CHAR(INTERNAL_FUNCTION("A"."FIN_DATE"),'yyyymmdd')>='20140101' AND
              TO_CHAR(INTERNAL_FUNCTION("A"."FIN_DATE"),'yyyymmdd')< ='20141231')
   9 - filter("D"."DISEASE" LIKE '%伤%' OR "D"."DISEASE" LIKE '%骨折%')
  10 - access("D"."CENTER_ID"='430740' AND "A"."FIN_DISEASE"="D"."ICD")
  11 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     161233  consistent gets
      83048  physical reads
        624  redo size
       1197  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

上述SQL对于表BS_HOSPITAL只查询了hospital_name列,而在BS_HOSPITAL表中存在索引IDX_BS_HOSPITAL_NAME(hospital_name,hospital_id)所以首先对IDX_BS_HOSPITAL_NAME索引全扫描这样就不用再回表查询从索引中就是得到hospital_name列的值作为结果集1。再通过对MT_BIZ_FIN表执行索引(PK_MT_BIZ_FIN)范围扫描,再回表查询返回其记录作为结果集2,再以结果集1作为驱动表进行嵌套循环连接。再与表BS_DISEASE,I_MT_PAY_RECORD_FIN_1执行嵌套循环连接,再执行分组排序。其实在MT_BIZ_FIN表中存在复合索引INDI_MT_BIZ_FIN_F_H(FIN_DATE,HOSPITAL_ID,BIZ_TYPE, TREATMENT_TYPE, CENTER_ID),而查询条件中用到了find_date,hospital_id,biz_type,center_id,只是这里因为谓词条件中对于fin_date条件是to_char(a.fin_date, 'yyyymmdd') >= '20140101' and to_char(a.fin_date, 'yyyymmdd') < = '20141231',而fin_date(费用完成时间)是日期类型,这里将find_date转换成字符型所以没有办法使用索引INDI_MT_BIZ_FIN_F_H。 将to_char(a.fin_date, 'yyyymmdd') >= '20140101' and to_char(a.fin_date, 'yyyymmdd') < = '20141231'条件改写成 a.fin_date between to_date('20140101','yyyymmdd') and to_date('20141231','yyyymmdd') ,改写后其SQL语句如下所示:

select  c.hospital_id,
       c.hospital_name,
       count(distinct a.serial_no) rc,
       round(sum(b.real_pay), 2) ylfyze,
       round(sum(case
                   when b.fund_id in ('001') then
                    b.real_pay
                   else
                    0
                 end),
             2) tczc,
       round(sum(case
                   when b.fund_id in ('201') then
                    b.real_pay
                   else
                    0
                 end),
             2) zffy,
       round(sum(case
                   when b.fund_id in ('003', '999') then
                    b.real_pay
                   else
                    0
                 end),
             2) yyzf
  from mt_biz_fin a, mt_pay_record_fin b, bs_hospital c, bs_disease d
 where a.hospital_id = b.hospital_id
   and a.serial_no = b.serial_no
   and a.hospital_id = c.hospital_id
   and a.fin_disease = d.icd
   and d.center_id = a.center_id
   and a.valid_flag = 1
   and b.valid_flag = 1
   and a.biz_type = 12
   and a.pers_type in (1, 2)
   and (d.disease like '%伤%' or d.disease like '%骨折%')
   and a.center_id = '430740'
   and a.fin_date between to_date('20140101','yyyymmdd') and to_date('20141231','yyyymmdd')
group by c.hospital_id, c.hospital_name
 order by c.hospital_id

来实际执行一次,其执行结果如下所示,现在执行时间稳定在1-2秒之间,能满足客户要求。

SQL> set autotrace traceonly
SQL> select  c.hospital_id,
  2         c.hospital_name,
  3         count(distinct a.serial_no) rc,
  4         round(sum(b.real_pay), 2) ylfyze,
  5         round(sum(case
  6                     when b.fund_id in ('001') then
  7                      b.real_pay
  8                     else
  9                      0
 10                   end),
 11               2) tczc,
 12         round(sum(case
 13                     when b.fund_id in ('201') then
 14                      b.real_pay
 15                     else
 16                      0
 17                   end),
 18               2) zffy,
 19         round(sum(case
 20                     when b.fund_id in ('003', '999') then
 21                      b.real_pay
 22                     else
 23                      0
 24                   end),
 25               2) yyzf
 26    from mt_biz_fin a, mt_pay_record_fin b, bs_hospital c, bs_disease d
 27   where a.hospital_id = b.hospital_id
 28     and a.serial_no = b.serial_no
 29     and a.hospital_id = c.hospital_id
 30     and a.fin_disease = d.icd
 31     and d.center_id = a.center_id
 32     and a.valid_flag = 1
 33     and b.valid_flag = 1
 34     and a.biz_type = 12
 35     and a.pers_type in (1, 2)
 36     and (d.disease like '%伤%' or d.disease like '%骨折%')
 37     and a.center_id = '430740'
 38     and a.fin_date between to_date('20140101','yyyymmdd') and to_date('20141231','yyyymmdd')
 39  group by c.hospital_id, c.hospital_name
 40   order by c.hospital_id
 41  ;

Elapsed: 00:00:01.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1467084556

---------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |    17 |  2516 |  1529  (15)| 00:00:02 |
|   1 |  SORT GROUP BY                   |                      |    17 |  2516 |  1529  (15)| 00:00:02 |
|*  2 |   TABLE ACCESS BY INDEX ROWID    | MT_PAY_RECORD_FIN    |     1 |    31 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                      |    17 |  2516 |  1528  (15)| 00:00:02 |
|   4 |     NESTED LOOPS                 |                      |    33 |  3861 |  1521  (15)| 00:00:02 |
|   5 |      NESTED LOOPS                |                      |   354 | 29736 |  1450  (16)| 00:00:02 |
|   6 |       INDEX FULL SCAN            | IDX_BS_HOSPITAL_NAME |  1227 | 39264 |     2   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN           |     1 |    52 |     1   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN          | INDI_MT_BIZ_FIN_F_H  |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS BY INDEX ROWID | BS_DISEASE           |     1 |    33 |     1   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN           | INX_BS_DISEASE_01    |     1 |       |     1   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN             | I_MT_PAY_RECORD_FIN_1|     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
   7 - filter(TO_NUMBER("A"."VALID_FLAG")=1 AND (TO_NUMBER("A"."PERS_TYPE")=1 OR
              TO_NUMBER("A"."PERS_TYPE")=2))
   8 - access("A"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "A"."FIN_DATE">=TO_DATE(' 2014-01-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."CENTER_ID"='430740' AND "A"."FIN_DATE"< =TO_DATE('
              2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("A"."CENTER_ID"='430740' AND TO_NUMBER("A"."BIZ_TYPE")=12)
   9 - filter("D"."DISEASE" LIKE '%伤%' OR "D"."DISEASE" LIKE '%骨折%')
  10 - access("D"."CENTER_ID"='430740' AND "A"."FIN_DISEASE"="D"."ICD")
  11 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      71411  consistent gets
          0  physical reads
          0  redo size
       1197  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed


第二种谓词条件的数据类型隐式转换无法使用索引的情况,其原始SQL语句如下所示,查询一个医疗机构的费用支出情况

select  a.hospital_id,
       count(distinct a.serial_no) rc,
       round(sum(b.real_pay), 2) ylfyze,
       round(sum(case
                   when b.fund_id in ('001') then
                    b.real_pay
                   else
                    0
                 end),
             2) tczc,
       round(sum(case
                   when b.fund_id in ('201') then
                    b.real_pay
                   else
                    0
                 end),
             2) zffy,
       round(sum(case
                   when b.fund_id in ('003', '999') then
                    b.real_pay
                   else
                    0
                 end),
             2) yyzf
  from mt_biz_fin a, mt_pay_record_fin b
 where a.hospital_id = b.hospital_id
   and a.serial_no = b.serial_no
   and a.valid_flag = '1'
   and b.valid_flag = '1'
   and a.biz_type = '12'
   and a.pers_type in ('1', '2')    
   and b.hospital_id=4307000231
group by a.hospital_id

该SQL的执行计划如下所示,执行了1分22秒:

SQL> set autotrace traceonly
SQL> select  a.hospital_id,
  2         count(distinct a.serial_no) rc,
  3         round(sum(b.real_pay), 2) ylfyze,
  4         round(sum(case
  5                     when b.fund_id in ('001') then
  6                      b.real_pay
  7                     else
  8                      0
  9                   end),
 10               2) tczc,
 11         round(sum(case
 12                     when b.fund_id in ('201') then
 13                      b.real_pay
 14                     else
 15                      0
 16                   end),
 17               2) zffy,
 18         round(sum(case
 19                     when b.fund_id in ('003', '999') then
 20                      b.real_pay
 21                     else
 22                      0
 23                   end),
 24               2) yyzf
 25    from mt_biz_fin a, mt_pay_record_fin b
 26   where a.hospital_id = b.hospital_id
 27     and a.serial_no = b.serial_no
 28     and a.valid_flag = ‘1’
 29     and b.valid_flag = ‘1’
 30     and a.biz_type = ‘12’
 31     and a.pers_type in ('1', '2')    
 32     and b.hospital_id=4307000231
 33  group by a.hospital_id
 34  ;

no rows selected

Elapsed: 00:01:22.20

Execution Plan
----------------------------------------------------------
Plan hash value: 3673479381

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    61 |   127K (16)| 00:01:56 |
|   1 |  SORT GROUP BY               |                   |     1 |    61 |   127K (16)| 00:01:56 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |     1 |    30 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                   |    45 |  2745 |   127K (16)| 00:01:56 |
|*  4 |     TABLE ACCESS FULL        | MT_PAY_RECORD_FIN |  8327 |   252K|   123K (16)| 00:01:53 |
|*  5 |     INDEX RANGE SCAN         | PK_MT_BIZ_FIN     |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."BIZ_TYPE"='12’ AND "A"."VALID_FLAG"='1’ AND
              ("A"."PERS_TYPE"='1’ OR "A"."PERS_TYPE"='2’)
   4 - filter(TO_NUMBER("B"."HOSPITAL_ID")=4307000231 AND "B"."VALID_FLAG"='1')
   5 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     572386  consistent gets
     383935  physical reads
          0  redo size
        638  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

从执行计划中可以看到在访问表MT_PAY_RECORD_FIN时使用的全表扫描,而在表MT_PAY_RECORD_FIN上存在索引PK_MT_PAY_RECORD_FIN(HOSPITAL_ID, SERIAL_NO)为什么没有使用该索引了,查询条件中的谓词条件是b.hospital_id=4307000231而从Predicate Information信息中的4 – filter(TO_NUMBER(“B”.”HOSPITAL_ID”)=4307000231
可知hospital_id在表中是字符型,而在书写查询条件时使用的是数字类型,这里CBO进行数据类型的隐式转换。所以使用不了索引。我们需要写成b.hospital_id=’4307000231′,修改后的SQL如下所示:

select  a.hospital_id,
       count(distinct a.serial_no) rc,
       round(sum(b.real_pay), 2) ylfyze,
       round(sum(case
                   when b.fund_id in ('001') then
                    b.real_pay
                   else
                    0
                 end),
             2) tczc,
       round(sum(case
                   when b.fund_id in ('201') then
                    b.real_pay
                   else
                    0
                 end),
             2) zffy,
       round(sum(case
                   when b.fund_id in ('003', '999') then
                    b.real_pay
                   else
                    0
                 end),
             2) yyzf
  from mt_biz_fin a, mt_pay_record_fin b
 where a.hospital_id = b.hospital_id
   and a.serial_no = b.serial_no
   and a.valid_flag = '1'
   and b.valid_flag = '1'
   and a.biz_type = '12'
   and a.pers_type in ('1', '2')    
   and b.hospital_id='4307000231'
group by a.hospital_id

来真实执行一次,现在能使用索引之后执行时间只要0.1秒

SQL> select  a.hospital_id,
  2         count(distinct a.serial_no) rc,
  3         round(sum(b.real_pay), 2) ylfyze,
  4         round(sum(case
  5                     when b.fund_id in ('001') then
  6                      b.real_pay
  7                     else
  8                      0
  9                   end),
 10               2) tczc,
 11         round(sum(case
 12                     when b.fund_id in ('201') then
 13                      b.real_pay
 14                     else
 15                      0
 16                   end),
 17               2) zffy,
 18         round(sum(case
 19                     when b.fund_id in ('003', '999') then
 20                      b.real_pay
 21                     else
 22                      0
 23                   end),
 24               2) yyzf
 25    from mt_biz_fin a, mt_pay_record_fin b
 26   where a.hospital_id = b.hospital_id
 27     and a.serial_no = b.serial_no
 28     and a.valid_flag = '1'
 29     and b.valid_flag = '1'
 30     and a.biz_type = '12'
 31     and a.pers_type in ('1', '2')    
 32     and b.hospital_id='4307000231'
 33  group by a.hospital_id
 34  ;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3142857175

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     1 |    61 |   115   (1)| 00:00:01 |
|   1 |  SORT GROUP BY                 |                     |     1 |    61 |   115   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN   |     1 |    31 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                     |   139 |  8479 |   115   (1)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN          |   139 |  4170 |    87   (2)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | INDI_MT_BIZ_FIN_H_F |   371 |       |    19   (6)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | PK_MT_PAY_RECORD_FIN|     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."VALID_FLAG"='1')
   4 - filter("A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1' OR
             A"."PERS_TYPE"='2'))
   5 - access("A"."HOSPITAL_ID"='4307000231')
       filter("A"."BIZ_TYPE"='12')
   6 - access("B"."HOSPITAL_ID"='4307000231' AND "A"."SERIAL_NO"="B"."SERIAL_NO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        203  consistent gets
          0  physical reads
          0  redo size
        638  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

从上面的执行计划可以看到现在访问表MT_PAY_RECORD_FIN能正确使用索引PK_MT_PAY_RECORD_FIN,但这里CBO并不是先访问表MT_PAY_RECORD_FIN,这里执行了谓词传递,从Predicate Information 中的 5 – access(“A”.”HOSPITAL_ID”=’4307000231′)可知是先对索引INDI_MT_BIZ_FIN_H_F执行索引范围,但是在查询条件中并没有写a.hospital_id=’4307000231’这个条件,这就是谓词传递的结果,因为有b.hospital_id=’4307000231′ and a.hospital_id=b.hospital_id,所以CBO推导出a.hospital_id=’4307000231’。

在优化这个系统时发现好多类似这两种情况的SQL,都是因为在书写SQL语句时根本就没有注意字段的类型,不同的开发人员书写的SQL语句,有的人谓词数据类型书写正确,有的人谓词数据类型书写不正确。希望开发人员在书写SQL谓词条件时注意数据类型,一定要书写正确。