分享程序员开发的那些事...
更新时间：2022-09-05 19:23:54
[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的大量消耗。