[201600901]到底消耗在哪里.txt

--生产系统1条sql语句存在性能问题。

SELECT b.BRXM AS NAME, b.MZHM AS MZ_NO, j.JZXH AS OUT_SNO

  FROM YS_MZ_JZLS j        LEFT JOIN MS_BRDA b ON TO_CHAR (b.BRID) = TO_CHAR (j.BRBH) WHERE j.JZXH = :"SYS_B_12";

--//原语句很长，我仅仅取消大部分显示字段。不用看，一眼就能看出开发使用了to_char函数来连接2个字段。为了测试方便我改成使用文字变量。

SYSTEM@192.168.99.105:1521/dbcn> @ &r/dpc '' ''

PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  29pbn4qa9tj3w, child number 0 ------------------------------------- sELECT b.BRXM AS NAME, b.MZHM AS MZ_NO, j.JZXH AS OUT_SNO   FROM YS_MZ_JZLS j        LEFT JOIN MS_BRDA b ON TO_CHAR (b.BRID) = TO_CHAR (j.BRBH)  WHERE j.JZXH = 6151708 Plan hash value: 2137640640 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |               |      1 |        |       | 24077 (100)|          |      1 |00:00:01.59 |   88001 |       |       |          | |   1 |  NESTED LOOPS OUTER          |               |      1 |      1 |    35 | 24077   (1)| 00:04:49 |      1 |00:00:01.59 |   88001 |       |       |          | |   2 |   TABLE ACCESS BY INDEX ROWID| YS_MZ_JZLS    |      1 |      1 |    12 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          | |*  3 |    INDEX UNIQUE SCAN         | PK_YS_MZ_JZLS |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  1025K|  1025K|          | |*  4 |   TABLE ACCESS STORAGE FULL  | MS_BRDA       |      1 |      1 |    23 | 24074   (1)| 00:04:49 |      1 |00:00:01.59 |   87997 |  1025K|  1025K|          | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$9E43CB6E    2 - SEL$9E43CB6E / J@SEL$2    3 - SEL$9E43CB6E / J@SEL$2    4 - SEL$9E43CB6E / B@SEL$1 Outline Data -------------

  /*+

      BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$9E43CB6E")       MERGE(@"SEL$58A6D7F6")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1")       INDEX_RS_ASC(@"SEL$9E43CB6E" "J"@"SEL$2" ("YS_MZ_JZLS"."JZXH"))       FULL(@"SEL$9E43CB6E" "B"@"SEL$1")       LEADING(@"SEL$9E43CB6E" "J"@"SEL$2" "B"@"SEL$1")       USE_NL(@"SEL$9E43CB6E" "B"@"SEL$1")       END_OUTLINE_DATA   */

Predicate Information (identified by operation id):

---------------------------------------------------    3 - access("J"."JZXH"=6151708)    4 - storage(TO_CHAR("B"."BRID")=TO_CHAR("J"."BRBH"))        filter(TO_CHAR("B"."BRID")=TO_CHAR("J"."BRBH")) 56 rows selected.

--如果仔细看仔细计划，可以发现主要消耗在MS_BRDA的全表扫描。如果改成如下呢？(仅仅删除2边的to_char函数).

sELECT

  /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$9E43CB6E")       MERGE(@"SEL$58A6D7F6")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1")       INDEX_RS_ASC(@"SEL$9E43CB6E" "J"@"SEL$2" ("YS_MZ_JZLS"."JZXH"))       FULL(@"SEL$9E43CB6E" "B"@"SEL$1")       LEADING(@"SEL$9E43CB6E" "J"@"SEL$2" "B"@"SEL$1")       USE_NL(@"SEL$9E43CB6E" "B"@"SEL$1")       END_OUTLINE_DATA   */ b.BRXM AS NAME, b.MZHM AS MZ_NO, j.JZXH AS OUT_SNO   FROM YS_MZ_JZLS j        LEFT JOIN MS_BRDA b ON  (b.BRID) =  (j.BRBH) WHERE j.JZXH = 6151708;

SYSTEM@192.168.99.105:1521/dbcn> @ &r/dpc '' outline

PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  4r2129zwfcgt3, child number 0 ------------------------------------- sELECT   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4')       ALL_ROWS OUTLINE_LEAF(@"SEL$9E43CB6E")       MERGE(@"SEL$58A6D7F6") OUTLINE(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")      OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1") INDEX_RS_ASC(@"SEL$9E43CB6E" "J"@"SEL$2" ("YS_MZ_JZLS"."JZXH")) FULL(@"SEL$9E43CB6E" "B"@"SEL$1")       LEADING(@"SEL$9E43CB6E" "J"@"SEL$2" "B"@"SEL$1")       USE_NL(@"SEL$9E43CB6E" "B"@"SEL$1") END_OUTLINE_DATA   */ b.BRXM AS NAME, b.MZHM AS MZ_NO, j.JZXH AS OUT_SNO   FROM YS_MZ_JZLS j        LEFT JOIN MS_BRDA b ON  (b.BRID) = (j.BRBH)  WHERE j.JZXH = 6151708 Plan hash value: 2137640640 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |               |      1 |        |       | 24077 (100)|          |      1 |00:00:00.40 |   88000 |       |       |          | |   1 |  NESTED LOOPS OUTER          |               |      1 |      1 |    35 | 24077   (1)| 00:04:49 |      1 |00:00:00.40 |   88000 |       |       |          | |   2 |   TABLE ACCESS BY INDEX ROWID| YS_MZ_JZLS    |      1 |      1 |    12 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          | |*  3 |    INDEX UNIQUE SCAN         | PK_YS_MZ_JZLS |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  1025K|  1025K|          | |*  4 |   TABLE ACCESS STORAGE FULL  | MS_BRDA       |      1 |      1 |    23 | 24074   (1)| 00:04:49 |      1 |00:00:00.40 |   87996 |  1025K|  1025K|          | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$9E43CB6E    2 - SEL$9E43CB6E / J@SEL$2    3 - SEL$9E43CB6E / J@SEL$2    4 - SEL$9E43CB6E / B@SEL$1 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$9E43CB6E")       MERGE(@"SEL$58A6D7F6")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1")       INDEX_RS_ASC(@"SEL$9E43CB6E" "J"@"SEL$2" ("YS_MZ_JZLS"."JZXH"))       FULL(@"SEL$9E43CB6E" "B"@"SEL$1")       LEADING(@"SEL$9E43CB6E" "J"@"SEL$2" "B"@"SEL$1")       USE_NL(@"SEL$9E43CB6E" "B"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("J"."JZXH"=6151708)    4 - storage("B"."BRID"="J"."BRBH")        filter("B"."BRID"="J"."BRBH") 65 rows selected.

--上下对比，Plan hash value: 2137640640没有变化。差异仅仅在于前面  filter(TO_CHAR("B"."BRID")=TO_CHAR("J"."BRBH"))，后者是filter("B"."BRID"="J"."BRBH")。

--而前者全表扫描00:00:01.59，而后者00:00:00.40 ，2者的估计时间00:04:49 都一样，可以发现在to_char上消耗更多的时间。

--摘要一段OLTP的描述：

OLTP系统最容易出现瓶颈的地方就是CPU与磁盘子系统 --//现在服务器内存越来越大，磁盘子系统问题相对较小。

(1)CPU出现瓶颈常表现在逻辑读总量与计算性函数或者是过程上，逻辑读总量等于单个语句的逻辑读乘以执行次数，如果单个语句执行速

度虽然很快，但是执行次数非常多，那么，也可能会导致很大的逻辑读总量。设计的方法与优化的方法就是减少单个语句的逻辑读，或者 是减少它们的执行次数。另外，一些计算型的函数，如自定义函数、decode等的频繁使用，也会消耗大量的CPU时间，造成系统的负载升 高，正确的设计方法或者是优化方法，需要尽量避免计算过程，如保存计算结果到统计表就是一个好的方法。

--我感觉讲的太对了，一些自定义函数,内部函数要尽量避免。这些函数如果用在select显示里面，如果大量调用一样会导致cpu的大量消耗。

(2)磁盘子系统在OLTP环境中，它的承载能力一般取决于它的IOPS处理能力. 因为在OLTP环境中，磁盘物理读一般都是db file

sequential read，也就是单块读，但是这个读的次数非常频繁。如果频繁到磁盘子系统都不能承载其IOPS的时候，就会出现大的性能问 题。

==============

当然这条语句很好修改，修改如下:

sELECT b.BRXM AS NAME, b.MZHM AS MZ_NO, j.JZXH AS OUT_SNO   FROM YS_MZ_JZLS j        LEFT JOIN MS_BRDA b ON  (b.BRID) =  (j.BRBH) WHERE j.JZXH = 6151708; PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  001c742gvr6w4, child number 0 ------------------------------------- sELECT b.BRXM AS NAME, b.MZHM AS MZ_NO, j.JZXH AS OUT_SNO   FROM YS_MZ_JZLS j        LEFT JOIN MS_BRDA b ON  (b.BRID) =  (j.BRBH)  WHERE j.JZXH = 6151708 Plan hash value: 3412718593 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |               |      1 |        |       |     5 (100)|          |      1 |00:00:00.01 |       8 |       |       |          | |   1 |  NESTED LOOPS OUTER          |               |      1 |      1 |    35 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |       |       |          | |   2 |   TABLE ACCESS BY INDEX ROWID| YS_MZ_JZLS    |      1 |      1 |    12 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          | |*  3 |    INDEX UNIQUE SCAN         | PK_YS_MZ_JZLS |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  1025K|  1025K|          | |   4 |   TABLE ACCESS BY INDEX ROWID| MS_BRDA       |      1 |      1 |    23 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          | |*  5 |    INDEX UNIQUE SCAN         | PK_MS_BRDA    |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  1025K|  1025K|          | -------------------------------------------------------------------------------------------------------------------------------------------------------------------

--总结：可以即使全表扫描更大的消耗在于里面的函数。从这个意义讲除了在谓词中注意函数要注意外，select的显示中大量的使用函数

--也会导致cpu的大量消耗。