转 sql 语句性能比较慢 执行计划
Dear :
该条SQL 主要慢在 如下 标黄线的 步骤 ，以下是 如下3表关联
3张表的总的 数据量在20200902 统计如下：
d0： 4860
d1 :581812893
d2:107189215
绑定变量值为20200901
select count(*) from aa.d1 GL where GL.RECORD_DATE = '20200901';
COUNT(*)
----------
1221920
SQL> select count(*) from aa.d0 where FDATE= '20200901';
COUNT(*)
----------
2
SQL> select count(*) from aa.d2 where FDATE = '20200901';
COUNT(*)
----------
57968
当使用 MERGE JOIN CARTESIAN 时候，d1 中（ 时间为'20200901' 的数据达到了1221920 ） 和 d0（ 时间为'20200901' 的数据达到了2 ）进行笛卡尔查询，
没有使用关联条件，导致查询的返回结果 达到2500000 笔数据
最后在跟d2 （时间为'20200901' 的数据达到了 57968）进行netloop 查询，导致数据库执行SQL 异常增加。
建议如下：
1.d1 表的字段 和 d0 的字段，做一个关联查询,，比如 d1.column_name = d0.column_name
2.找到更好的执行计划，固定执行计划，
附录：
Current Execution Plans (last execution)
Captured while still in memory. Metrics below are for the last execution of each child cursor.
If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows
column is populated.
Inst: 1 Child: 0 Plan hash value: 3128636944
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 494 (100)| | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | | |
| 2 | HASH GROUP BY | | 1 | 200 | 494 (1)| 00:00:06 | | | 762K| 762K| |
| 3 | NESTED LOOPS | | 1 | 200 | 493 (0)| 00:00:06 | | | | | |
| 4 | NESTED LOOPS | | 1 | 200 | 493 (0)| 00:00:06 | | | | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 162 | 491 (0)| 00:00:06 | | | | | |
| 6 | PARTITION LIST SINGLE | | 1 | 114 | 0 (0)| | KEY | KEY | | | |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| d1 | 1 | 114 | 0 (0)| | KEY | KEY | | | |
|* 8 | INDEX RANGE SCAN | IDX_d1_02 | 1 | | 0 (0)| | KEY | KEY | | | |
| 9 | BUFFER SORT | | 2 | 96 | 491 (0)| 00:00:06 | | | 73728 | 73728 | |
|* 10 | TABLE ACCESS FULL | d0 | 2 | 96 | 491 (0)| 00:00:06 | | | | | |
|* 11 | INDEX RANGE SCAN | IDX_d2_01 | 49057 | | 1 (0)| 00:00:01 | | | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | d2 | 1 | 38 | 2 (0)| 00:00:01 | | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
SQL Text
INSERT INTO T096 (DATA_DATE, BRANCH, SEQ_NO, FLAG, PAY_dddd_NO, SHOU_dddd_NO, TRAN_AMT, TRAN_CNT) SELECT :B1 , GH.BRANCH, NULL, DECODE(GRA.IS_INTEREST, 'Y', 'L', 'B') FLAG, GRA.GL_dddd_NO, GRA.BASE_dddd_NO, ABS(SUM(DECODE(GH.REVERSAL, 'Y', DECODE(GH.DR_CR_IND, 'C', ABS(GH.AMOUNT), 'D', -1 * ABS(GH.AMOUNT)), 'N', DECODE(GH.DR_CR_IND, 'D', ABS(GH.AMOUNT), 'C', -1 * ABS(GH.AMOUNT))))), SUM(DECODE(GH.REVERSAL, 'N', 1, 'Y', -1)) CNT FROM d1 GH, d2 GAC, d0 GRA WHERE GH.dddd_INTERNAL_KEY = GAC.INTERNAL_KEY AND GAC.dddd_NO = GRA.GL_dddd_NO AND GH.SOURCE_TYPE = 'LO' AND GH.VALUE_DATE = GH.RECORD_DATE AND GH.RECORD_DATE = :B1 AND GH.FDATE = :B1 AND GAC.FDATE = :B1 AND GRA.FDATE = :B1 GROUP BY GRA.GL_dddd_NO, GH.BRANCH, DECODE(GRA.IS_INTEREST, 'Y', 'L', 'B'), GRA.BASE_dddd_NO
--分析过程
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| d1| 1 | 114 | 0 (0)| | KEY | KEY | | | |
|* 8 | INDEX RANGE SCAN | IDX_GL_HIST_02 | 1 | | 0 (0)| | KEY | KEY | | | |
| 9 | BUFFER SORT | | 2 | 96 | 491 (0)| 00:00:06 | | | 73728 | 73728 | |
|* 10 | TABLE ACCESS FULL | d0 | 2 | 96 | 491 (0)| 00:00:06 | | | | |
7 - filter(("GH"."VALUE_DATE"=:B1 AND "GH"."RECORD_DATE"=:B1))
8 - access("GH"."FDATE"=:B1 AND "GH"."SOURCE_TYPE"='LO')
filter("GH"."SOURCE_TYPE"='LO')
10 - filter("GRA"."FDATE"=:B1)
d0： 4860
d1:581812893
d2:107189215
select count(*) from aa.d0;
select count(*) from aa.GL_HIST;
select count(*) from aa.d2;
select count(*) from aa.d1;
COUNT(*)
----------
595877133
SQL> select count(*) from aa.d2;
COUNT(*)
----------
107155579
SQL>
select count(*) from aa.d0;
COUNT(*)
----------
4952
select count(*) from aa.d1GL where GL.RECORD_DATE = '20200901';
COUNT(*)
----------
1221920
SQL> select count(*) from aa.d0 where FDATE= '20200901';
COUNT(*)
----------
2
SQL> select count(*) from aa.d2 where FDATE = '20200901';
COUNT(*)
----------
57968
SQL Text
--------------------------------------------------------------------------------
INSERT INTO TP_B_user096 (DATA_DATE, BRANCH, SEQ_NO, FLAG, PAY_dddd_NO, SHOU_dddd_NO, TRAN_AMT, TRAN_CNT) SELECT :B1 , GH.BRANCH, NULL, DECODE(GRA.IS_INTEREST, 'Y', 'L', 'B') FLAG, GRA.GL_dddd_NO, GRA.BASE_dddd_NO, ABS(SUM(DECODE(GH.REVERSAL, 'Y', DECODE(GH.DR_CR_IND, 'C', ABS(GH.AMOUNT), 'D', -1 * ABS(GH.AMOUNT)), 'N', DECODE(GH.DR_CR_IND, 'D', ABS(GH.AMOUNT), 'C', -1 * ABS(GH.AMOUNT))))), SUM(DECODE(GH.REVERSAL, 'N', 1, 'Y', -1)) CNT FROM d1GH, d2 GAC, d0 GRA WHERE GH.dddd_INTERNAL_KEY = GAC.INTERNAL_KEY AND GAC.dddd_NO = GRA.GL_dddd_NO AND GH.SOURCE_TYPE = 'LO' AND GH.VALUE_DATE = GH.RECORD_DATE AND GH.RECORD_DATE = :B1 AND GH.FDATE = :B1 AND GAC.FDATE = :B1 AND GRA.FDATE = :B1 GROUP BY GRA.GL_dddd_NO, GH.BRANCH, DECODE(GRA.IS_INTEREST, 'Y', 'L', 'B'), GRA.BASE_dddd_NO
假设绑定变量值为20200901
SQL> select count(*) from aa.d1GL w here GL.RECORD_DATE = '20200901';
COUNT(*)
----------
1221920
##查看正在执行的SQL 绑定变量值,
###https://www.cnblogs.com/kerrycode/p/7476443.html
sql_id:dp7mzr2kg3c4z
SELECT
b.sql_id,
t.sql_text sql_text,
b.name bind_name,
b.value_string bind_value
FROM
v$sql t
JOIN
v$sql_bind_capture b on t.hash_value = b.hash_value and t.child_address = b.child_address
WHERE
b.value_string is not null
AND
b.sql_id='&sqlid'
SQL> COL NAME FOR A12;
SQL> COL DATATYPE_STRING FOR A24;
SQL> COL VALUE_STRING FOR A32;
SQL> SELECT NAME,
2 DATATYPE_STRING,
3 VALUE_STRING,
4 MAX_LENGTH,
5 LAST_CAPTURED
6 FROM V$SQL_BIND_CAPTURE
7 WHERE SQL_ID = '1t2r2p48W4P0g';
后记：
--因为是insert 慢，后继 开发反馈单独select 查询很快，于是单独拿出select 看了看
发现走的快的执行计划还是跟慢的执行计划不一样，一个用的是IDX_GL_ACCT_CORE_02。一个用的是IDX_GL_ACCT_CORE_01
Plan hash value: 4260575868
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:02.33 | 683K| | | |
| 1 | HASH GROUP BY | | 1 | 1 | 0 |00:00:02.33 | 683K| 762K| 762K| |
| 2 | NESTED LOOPS | | 1 | 1 | 0 |00:00:02.33 | 683K| | | |
| 3 | NESTED LOOPS | | 1 | 1 | 619K|00:00:02.50 | 64200 | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 1 | 619K|00:00:01.57 | 64182 | | | |
| 5 | PARTITION LIST SINGLE | | 1 | 1 | 309K|00:00:00.62 | 63303 | | | |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| GL_HIST | 1 | 1 | 309K|00:00:00.58 | 63303 | | | |
|* 7 | INDEX RANGE SCAN | IDX_GL_HIST_02 | 1 | 1 | 309K|00:00:00.18 | 9536 | | | |
| 8 | BUFFER SORT | | 309K| 2 | 619K|00:00:00.14 | 879 | 2048 | 2048 | 2048 (0)|
|* 9 | TABLE ACCESS FULL | GL_ttttttt_TRANSFER | 1 | 2 | 2 |00:00:00.01 | 879 | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|* 10 | INDEX RANGE SCAN | IDX_GL_ACCT_CORE_02 | 619K| 1 | 619K|00:00:00.73 | 18 | | | | 《- 这里走的是IDX_GL_ACCT_CORE_02
|* 11 | TABLE ACCESS BY INDEX ROWID | GL_ACCT_CORE | 619K| 1 | 0 |00:00:00.49 | 619K| | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
验证如下： 强制走 索引 IDX_GL_ACCT_CORE_01 很慢
SELECT /*+ INDEX (GAC IDX_GL_ACCT_CORE_01 )*/
'20200902',
GH.BRANCH,
NULL,
DECODE(GRA.IS_INTEREST, 'Y', 'L', 'B') FLAG,
GRA.GL_ACCT_NO,
强制走索引 IDX_GL_ACCT_CORE_02 很快，
SELECT /*+ INDEX (GAC IDX_GL_ACCT_CORE_02 )*/
'20200902',
GH.BRANCH,
这也给我们调优提供了一个思路，强制走索引 IDX_GL_ACCT_CORE_02 也是一条优化建议。
通过这个案例，感觉到O 记的自适应性，和社区的强大。这一点远远超过 开源数据库 社区。
############samipe
该问题 体现在数据库运行sql 随着数据库量的缓慢增加，加上执行计划还存在改善空间，导致该条sql 性能变慢，通过开发 改写sql 代码，问题得到规避。
Please collect a sql monitor report for this sql
Monitoring SQL Statements with Real-Time SQL Monitoring ( Doc ID 1380492.1 )
set long 1000000
set longchunksize 1000000
set lines 1000
set pages 0
set trim on
set trimspool on
set echo off
set feedback off
spool /tmp/report_sql_monitor.html
select
dbms_sqltune.report_sql_monitor(
sql_id => '<sql id>',
type => 'html',
report_level => 'all') as report
from
dual;
spool off
- 原先sql 如下：
######old sql_id b9t9c7yhu2k5d
SQL内容如下：
UPDATE db.aaaaaa a
SET acct_status =
(SELECT acct_status
FROM db1.ttttttttt subpartition(SYS_SUBP257) b
WHERE a.internal_key = b.internal_key
AND '20201030' BETWEEN b.sdate AND b.edate)
WHERE a.acct_status = 'C'
AND a.close_date > '20201031'
-》 S QL执行计划如下： cost 消耗过大，达到了15G, 平均每次执行达到了 700 seconds 秒,
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 15G(100)| | | |
| 1 | UPDATE | aaaaaaa | | | | | | |
|* 2 | TABLE ACCESS FULL | aaaaaaa | 5557 | 244K| 5320 (1)| 00:01:04 | | |
| 3 | PARTITION COMBINED ITERATOR| | 1 | 26 | 2874K (1)| 09:34:56 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | ttttttt | 1 | 26 | 2874K (1)| 09:34:56 | 241 | 241 |
------------------------------------------------------------------------------------------------------------
2.改写sql 后SQL
#####new sql_id 7wry1254a0cc1
SQL 内容如下：
MERGE INTO db.aaaaaa a
USING (SELECT internal_key, acct_status
FROM db1.ttttttttt SUBPARTITION (SYS_SUBP257) b
WHERE '20201030' BETWEEN b.sdate AND b.edate) c
ON (a.internal_key = c.internal_key)
WHEN MATCHED
THEN
UPDATE SET a.acct_status = c.acct_status
WHERE a.acct_status = 'C' AND a.close_date > '20201031'
-》 SQL 执行计划如下：cost 下降到2979K， 平均每次执行时间下降到 22.243 秒，SQL 整体提升了20倍左右。
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | | 2979K(100)| | | | | | |
| 1 | MERGE | aaaaaaa | | | | | | | | | | |
| 2 | VIEW | | | | | | | | | | | |
|* 3 | HASH JOIN | | 510K| 2082M| 28M| 2979K (1)| 09:55:51 | | | 33M| 6391K| 36M (0)|
| 4 | PARTITION COMBINED ITERATOR| | 780K| 19M| | 2874K (1)| 09:34:56 | KEY | KEY | | | |
|* 5 | TABLE ACCESS FULL | ttttttt | 780K| 19M| | 2874K (1)| 09:34:56 | 241 | 241 | | | |
| 6 | TABLE ACCESS FULL | aaaaaaa | 485K| 1968M| | 5334 (1)| 00:01:05 | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
结论：
使用merge 可以改写成标量子查询，会极大提升效率
1. 改写前的SQL执行计划相比之前未改变
2. 通过收集统计信息、提高动态采样率的方式，执行计划未改变
3. 表过大，达到1.8T，无法新建索引优化
4. 详细比对改写前的SQL最近一段时间的执行情况，发现11月01日当天，符合筛选条件需要处理的数据量比以往多得多
改写前的update语句，需要从aaaaaaa表将符合where a.acct_status = 'C' AND a.close_date > '20201031'的数据筛选出来，然后再扫描ttttttt表相应的分区匹配数据。aaaaaaa表有1条符合where条件的数据，就要扫描1次ttttttt表相应的分区。ttttttt表相应的分区大小为5G~11G。aaaaaaa表符合where条件的数据量直接影响到改写前SQL的性能
取10个子任务中的1个进行分析：
10月31日的批量，即20201030的数据，aaaaaaa表共 14条数据符合where条件
11月01日的批量，即20201031的数据，aaaaaaa表共3979条数据符合where条件
11月02日的批量，即20201101的数据，aaaaaaa表共 1条数据符合where条件
11月01日的批量，aaaaaaa表符合where条件的数据量明显增多
######sample 3
升级到19C 之后，模条sql 消耗异常高 的临时表空间
从sqlhc 报告来看：
Please collect a sql monitor report for this sql
Monitoring SQL Statements with Real-Time SQL Monitoring ( Doc ID 1380492.1 )
set long 1000000
set longchunksize 1000000
set lines 1000
set pages 0
set trim on
set trimspool on
set echo off
set feedback off
spool /tmp/report_sql_monitor.html
select
dbms_sqltune.report_sql_monitor(
sql_id => '<sql id>',
type => 'html',
report_level => 'all') as report
from
dual;
spool off
虽然通过检查 发现 sql monitor report显示"I_WRI$_OPTSTAT_OPR_TASKS_OPID" 的数据量有问题，因此我们重建了它。
实际数据量为7000行，显示数据量为248M
| 8 | ...... HASH JOIN OUTER |
. |
1735 | 40 |
|
1 | 249M | 236.1MB | 50.3GB |
|
|
|
||||||||||
|
. |
9 | ....... HASH JOIN OUTER |
. |
59 | 10 |
|
1 | 0 |
. |
. |
|
. |
. |
|||||||||
|
. |
10 | ........ NESTED LOOPS OUTER |
. |
59 | 10 |
|
1 | 249M |
. |
. |
|
|
|
|||||||||
|
. |
11 | ......... STATISTICS COLLECTOR |
. |
. |
. |
|
1 | 2M |
. |
. |
|
. |
. |
|||||||||
|
. |
12 | .......... NESTED LOOPS |
. |
2 | 6 |
|
1 | 2M |
. |
. |
|
. |
. |
|||||||||
|
. |
13 | ........... VIEW |
. |
2 | 2 |
|
1 | 307 |
. |
. |
|
. |
. |
|||||||||
|
. |
14 | ............ TABLE ACCESS FULL | SYS_TEMP_0FD9D664D_2CB1FF | 2 | 2 |
. |
1 |
. |
. |
. |
|
. |
. |
|||||||||
|
. |
15 | ........... VIEW |
. |
1 | 2 |
|
307 | 2M |
. |
. |
|
|
|
|||||||||
|
. |
16 | ............ TABLE ACCESS FULL | SYS_TEMP_0FD9D664D_2CB1FF | 2 | 2 |
. |
307 |
. |
. |
. |
|
. |
. |
|||||||||
|
. |
17 | ......... TABLE ACCESS BY INDEX ROWID BATCHED | WRI$_OPTSTAT_OPR_TASKS | 29 | 2 |
|
2M | 248M |
. |
. |
|
|
|
|||||||||
|
. |
18 | .......... INDEX RANGE SCAN | I_WRI$_OPTSTAT_OPR_TASKS_OPID | 29 | 1 |
|
2M | 248M |
. |
. |
|
|
|
|||||||||
|
. |
19 | ........ TABLE ACCESS FULL | WRI$_OPTSTAT_OPR_TASKS | 29 | 2 |
. |
. |
. |
. |
. |
|
. |
. |
临时解决方法:
目前大小是2M, 现在重建大小为 0.1M , 大小减小到90%。
SQL> select BYTES/1024/1024 from dba_segments where SEGMENT_NAME='I_WRI$_OPTSTAT_OPR_TASKS_OPID';
BYTES/1024/1024
---------------
2
SQL>
SQL> select NUM_ROWS,BLEVEL,STATUS from dba_indexes where INDEX_NAME='I_WRI$_OPTSTAT_OPR_TASKS_OPID';
NUM_ROWS BLEVEL STATUS
---------- ---------- --------
47714 1 VALID
SQL>
SQL> alter index sys."I_WRI$_OPTSTAT_OPR_TASKS_OPID" rebuild;
Index altered.
SQL> select BYTES/1024/1024 from dba_segments where SEGMENT_NAME='I_WRI$_OPTSTAT_OPR_TASKS_OPID';
BYTES/1024/1024
---------------
.9375
SQL> select NUM_ROWS,BLEVEL,STATUS from dba_indexes where INDEX_NAME='I_WRI$_OPTSTAT_OPR_TASKS_OPID';
NUM_ROWS BLEVEL STATUS
---------- ---------- --------
49321 1 VALID
SQL>
##########sample 3
第一次 11-29 9:34
第二次 12-05 9:59
update ib ib set ib.a7115 = '4' where not exists (select 1 FROM IA IA, GC GC, CR CR WHERE IA.ContractNo
= CR.Serialno and CR.objecttype = 'GuarantyContract' and CR.ObjectNo = GC.SERIALNO and GC.GUARANTYTYPE in ('010010', '010020', '010030', '050', '060', '010040') and IA.reportFl
ag in ('1','2','3','4') and IA.AssureType in ('3', '5', '7') and GC.Contractstatus in ('020') and IA.ACCOUNTNO = ib.a7101) and not exists (select 1 FROM IA IA,
GC GC, CR CR, CR CR1, BC BC WHERE IA.ContractNo = BC.Serialno and CR.objecttype = 'GuarantyCon
tract' and CR.objectno = GC.SERIALNO and GC.GUARANTYTYPE in ('010010', '010020', '010030', '050', '060', '010040') and CR1.ObjectType = 'BusinessContract' and CR1.LineNo = CR.s
erialno and BC.serialno = CR1.Objectno and IA.reportFlag in ('1','2','3','4') and IA.AssureType in ('3', '5', '7') and GC.Contractstatus in ('020') and IA.ACCOUNTNO = ib.a7101)
and ib.a7115 in ('3', '5', '7');
update ib ib set ib.a7115 = '2' where not exists (select GC.Serialno FROM IA IA, GC GC, CR CR WHERE IA.Co
ntractNo = CR.Serialno and CR.objecttype = 'GuarantyContract' and CR.ObjectNo = GC
##该问题的难点在views1 (VW_SQ_1 ) 和 view2(VW_SQ_2) 和view3(VW_SQ_3) 指向的哪个sql 语句，分解下来
分解1：
1. view1 指向 SET$1BE43060 ， 指向子块 SEL$3，SEL$4 的组合查询
SET$1BE43060 / VW_SQ_1@SEL$101A7053
FACTORIZE_JOIN(@"SET$1BE43060"("GC"@"SEL$3" "GC"@"SEL$4"))
1.1 子块 SEL$3指向的是 如下： GC,CR,IA,BC.CR1 组合查询
LEADING(@"SEL$CBDD2ED8" "GC"@"SEL$3" "VW_JF_SET$157D1F7B"@"SEL$9C666E57")
USE_NL(@"SEL$CBDD2ED8" "VW_JF_SET$157D1F7B"@"SEL$9C666E57")
INDEX_RS_ASC(@"SEL$0C5A3E93" "CR"@"SEL$3" ("CR"."OBJECTNO" "CR"."SERIALNO"))
FULL(@"SEL$0C5A3E93" "IA"@"SEL$3")
INDEX(@"SEL$0C5A3E93" "BC"@"SEL$3" ("BC"."SERIALNO"))
INDEX(@"SEL$0C5A3E93" "CR1"@"SEL$3" ("CR"."OBJECTNO" "CR"."OBJECTTYPE"
"CR"."LINENO"))
LEADING(@"SEL$0C5A3E93" "CR"@"SEL$3" "IA"@"SEL$3" "BC"@"SEL$3" "CR1"@"SEL$3")
USE_MERGE_CARTESIAN(@"SEL$0C5A3E93" "IA"@"SEL$3")
USE_NL(@"SEL$0C5A3E93" "BC"@"SEL$3")
USE_NL(@"SEL$0C5A3E93" "CR1"@"SEL$3")
LEADING(@"SEL$0C5A3E93" "CR"@"SEL$3" "IA"@"SEL$3" "BC"@"SEL$3" "CR1"@"SEL$3")
1.2 子块SEL$4指向的是 如下： GC 跟CR 跟IA 组合查询，
INDEX_RS_ASC(@"SEL$F50B1D0F" "CR"@"SEL$4" ("CR"."OBJECTNO" "CR"."SERIALNO"))
FULL(@"SEL$F50B1D0F" "IA"@"SEL$4")
LEADING(@"SEL$F50B1D0F" "CR"@"SEL$4" "IA"@"SEL$4")
USE_HASH(@"SEL$F50B1D0F" "IA"@"SEL$4")
分解2：
2. view2 指向 SEL$7DBF1F45
SEL$7DBF1F45 / VW_SQ_2@SEL$5E8B43E0
LEADING(@"SEL$7DBF1F45" "IA"@"SEL$1" "CR"@"SEL$1" "GC"@"SEL$1")
分解3：
3.view3 指向的是 SEL$C6423BE4
SEL$C6423BE4 / VW_SQ_3@SEL$68796C6C
LEADING(@"SEL$C6423BE4" "IA"@"SEL$2" "BC"@"SEL$2" "CR1"@"SEL$2" "CR"@"SEL$2" "GC"@"SEL$2")
##发现主要慢体现在子块 SEL$3如下语句：
select 1
FROM user.IA IA,
user.GC GC,
user.CR CR,
user.CR CR1,
user.BC BC,
user.ib ib
WHERE IA.ContractNo = BC.Serialno
and CR.objecttype = 'GuarantyContract'
and CR.objectno = GC.SERIALNO
and GC.GUARANTYTYPE in ('050')
and CR1.ObjectType = 'BusinessContract'
and CR1.LineNo = CR.serialno
and BC.serialno = CR1.Objectno
and IA.reportFlag in ('1', '2', '3', '4')
and IA.AssureType in ('3', '5', '7')
and GC.Contractstatus in ('020')
and IA.ACCOUNTNO = ib.a7101
手工执行执行计划没问题，全都使用了hash ,没有使用 MERGE JOIN CARTESIAN ：
----------------------------------------------------------
Plan hash value: 2567308779
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 9481 | 2342K| 89528
(1)| 00:17:55 |
| 1 | NESTED LOOPS | | 9481 | 2342K| 89528
(1)| 00:17:55 |
|* 2 | HASH JOIN | | 9481 | 2111K| 70562
(1)| 00:14:07 |
| 3 | NESTED LOOPS | | 9482 | 1629K| 11999
(1)| 00:02:24 |
|* 4 | HASH JOIN | | 9482 | 1407K| 2514
(1)| 00:00:31 |
|* 5 | HASH JOIN | | 11797 | 1105K| 1030
(1)| 00:00:13 |
|* 6 | TABLE ACCESS FULL | GC | 11794 | 380K| 854
(1)| 00:00:11 |
|* 7 | TABLE ACCESS FULL | CR | 31958 | 1966K| 176
(1)| 00:00:03 |
|* 8 | INDEX FAST FULL SCAN| IDX_CL_LINENO | 128K| 7015K| 1484
(1)| 00:00:18 |
|* 9 | INDEX UNIQUE SCAN | PK_BC | 1 | 24 | 1
(0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | IA | 4294K| 212M| 58551
(1)| 00:11:43 |
|* 11 | INDEX UNIQUE SCAN | P_BASEINFO | 1 | 25 | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
########原因：
这条语句 评估数量错误，评估出1 rows 条记录，实际返回 3750 rows
INDEX_RS_ASC(@"SEL$0C5A3E93" "CR"@"SEL$3" ("CR"."OBJECTNO" "CR"."SERIALNO"))
---index_name
CREATE INDEX "user"."I_CR" ON "user"."CR" ("OBJECTNO", "SERIALNO")
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 114K(100)| |
| 14 | MERGE JOIN CARTESIAN | | 1 | 99 | 57130 (1)| 00:11:26 |
| 15 | TABLE ACCESS BY INDEX ROWID| CR | 1 | 47 | 0 (0)| | 《- 这一步实际返回3750 rows
| 16 | INDEX RANGE SCAN | I_CR | 1 | | 0 (0)| |
| 17 | BUFFER SORT | | 4229K| 209M| 57130 (1)| 00:11:26 |
| 18 | TABLE ACCESS FULL | IA | 4229K| 209M| 57130 (1)| 00:11:26 |
|
这条语句评估错误
SQL> select count(*) from user.CR CR where CR.objecttype = 'GuarantyContract';
COUNT(*)
----------
95857
SQL> select count(*) from user.CR CR;
COUNT(*)
----------
95875
SQL> select count(*) from user.IA IA;
COUNT(*)
----------
10040466
##规避方法：
方法1. 通过了解，这张进行了如下操作插入前先 truncate表，让后再插入
建议对这张表 CR 收集统计信息，方法如下：
EXEC DBMS_STATS.GATHER_TABLE_STATS('user','CR',estimate_percent=>80,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE);
方法2. 使用merge 语句测试性能是否改善
MERGE INTO ib ib
USING (select IA.ACCOUNTNO
FROM IA IA,
GC GC,
CR CR,
CR CR1,
BC BC
WHERE IA.ContractNo = BC.Serialno
and CR.objecttype = 'GuarantyContract'
and CR.objectno = GC.SERIALNO
and GC.GUARANTYTYPE in ('050')
and CR1.ObjectType = 'BusinessContract'
and CR1.LineNo = CR.serialno
and BC.serialno = CR1.Objectno
and IA.reportFlag in ('1', '2', '3', '4')
and IA.AssureType in ('3', '5', '7')
and GC.Contractstatus in ('020')
) c
ON (c.ACCOUNTNO = ib.a7101)
WHEN MATCHED
THEN
UPDATE set ib.a7115 = '2'
方法3：user.CR CR 和 IA 做一个关联条件
select 1
FROM user.IA IA,
user.GC GC,
user.CR CR,
user.CR CR1,
user.BC BC,
user.ib ib
WHERE IA.ContractNo = BC.Serialno
and CR.objecttype = 'GuarantyContract'
and CR.objectno = GC.SERIALNO
and GC.GUARANTYTYPE in ('050')
and CR1.ObjectType = 'BusinessContract'
and CR1.LineNo = CR.serialno
and BC.serialno = CR1.Objectno
and IA.reportFlag in ('1', '2', '3', '4')
and IA.AssureType in ('3', '5', '7')
and GC.Contractstatus in ('020')
and IA.ACCOUNTNO = ib.a7101
###sample 4 没有绑定变量的sql, 并且sql 被刷出内存怎么优化
思路如下，1.先手工找到不同的变量，带入执行sql , 根据经验，可能某个变量效率高点，某个变量效率慢点。
然后分别抓取对应的执行计划，分别进行优化。
2.另外此时尝试使用sqlt 分析sql ,通常会报错，如下：
in awr reprot we find this sql ,but we can't use sqlt tunning : it report below error
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Sql Id specified: 9dh6su5n3h4pg
Tune the sql
ERROR: statement is not in the cursor cache or the workload repository.
Execute the statement and try again
3.awr 报告 也无法抓取该sql 详细信息，根据查询资料，19C 可能存在部分sql信息无法记录入awr报告，ash 报告。详细信息请见附录
解决办法
1.
so we try ask dev to re-run to produce sql in cache , we get use new sql_id:
using aaa.dddate = '2023/08/20' and aaa.dddate = '2023/08/21'
we create new sql_id :3s3s39h790hz4 / plan hash 3962013729 and
88k0hd72zjztg and plan hash 3856075020
2.
sqlt still report error: ,so we try to execcute the sql manaule and try to cache the sql
Tune the sql
~~~~~~~~~~~~
ERROR: statement is not in the cursor cache or the workload repository.
Execute the statement and try again
3.仔细分析执行计划，然后再尝试用好的执行计划的执行步骤，一步步重写sql， 问题得到解决
we try to use
select * from table(dbms_xplan.display_cursor('3s3s39h790hz4','0'));
SQL> select * from table(dbms_xplan.display_cursor('3s3s39h790hz4','0'));
SQL_ID 3s3s39h790hz4, child number 0
-------------------------------------
select distinct al.serialno, ci.customerName,
aaa.dddate,al.customerid, bc.wyserialno, bc.telphone from
acct_payment_schedule aaa, d_laaal, ccmsetl.B022_CB_CST_INF bcci,
ccmsetl.B022_CB_USER_INF bcui, business_contract bc,
customer_info ci where aaa.objectno = al.serialno and
replace(ci.certid,'-','') = bcci.cif_ctfno and ci.customerid
= al.customerid and bcui.uif_cstno = bcci.cif_cstno and bc.onlinetype =
'006' and bc.serialno = al.contractserialno and
aaa.finishdate is null and aaa.dddate = '2023/08/20'
Plan hash value: 3962013729
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 384 (100)| |
| 1 | HASH UNIQUE | | 1 | 229 | 384 (1)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 229 | 383 (1)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 5458 | 1103K| 280 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 196 | 75 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 196 | 75 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 141 | 73 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 10 | 1110 | 53 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| ACCT_PAYMENT_SCHEDULE | 10 | 430 | 33 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_ACCT_PAYMENT_SCHEDULE_02 | 37 | | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | d_laa | 1 | 68 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_ACCT_LOAN_01 | 1 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | BUSINESS_CONTRACT | 1 | 30 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_R_BUSINESS_CONTRACT | 1 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_CUSTOMER_INFO | 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_INFO | 1 | 55 | 2 (0)| 00:00:01 |
| 16 | BUFFER SORT | | 119K| 1279K| 278 (1)| 00:00:01 |
| 17 | TABLE ACCESS FULL | B022_CB_USER_INF | 119K| 1279K| 205 (1)| 00:00:01 |
| 18 | TABLE ACCESS FULL | B022_CB_CST_INF | 80347 | 1726K| 103 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BCCI"."CIF_CTFNO"=REPLACE("CI"."CERTID",'-','') AND "BCUI"."UIF_CSTNO"="BCCI"."CIF_CSTNO")
8 - filter("aaa"."FINISHDATE" IS NULL)
9 - access("aaa"."PAYDATE"='2023/08/20')
11 - access("aaa"."OBJECTNO"="AL"."SERIALNO")
12 - filter("BC"."ONLINETYPE"='006')
13 - access("BC"."SERIALNO"="AL"."CONTRACTSERIALNO")
14 - access("CI"."CUSTOMERID"="AL"."CUSTOMERID")
Note
-----
- this is an adaptive plan
53 rows selected.
set linesize 999
set pagesize 0
select * from table(dbms_xplan.display_cursor('88k0hd72zjztg','0'));
SQL_ID 88k0hd72zjztg, child number 0
-------------------------------------
select distinct al.serialno, ci.customerName,
aaa.dddate,al.customerid, bc.wyserialno, bc.telphone from
acct_payment_schedule aaa, d_laaal, ccmsetl.B022_CB_CST_INF bcci,
ccmsetl.B022_CB_USER_INF bcui, business_contract bc,
customer_info ci where aaa.objectno = al.serialno and
replace(ci.certid,'-','') = bcci.cif_ctfno and ci.customerid
= al.customerid and bcui.uif_cstno = bcci.cif_cstno and bc.onlinetype =
'006' and bc.serialno = al.contractserialno and
aaa.finishdate is null and aaa.dddate = '2023/08/21'
Plan hash value: 3856075020
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 2245 (100)| |
| 1 | HASH UNIQUE | | 1 | 229 | | 2245 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 229 | | 2244 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 229 | | 2244 (1)| 00:00:01 |
|* 4 | HASH JOIN SEMI | | 1 | 174 | | 2242 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 131 | | 2239 (1)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 101 | | 2237 (1)| 00:00:01 |
|* 7 | HASH JOIN SEMI | | 1 | 33 | 2672K| 569 (1)| 00:00:01 |
| 8 | TABLE ACCESS FULL | B022_CB_CST_INF | 80347 | 1726K| | 103 (1)| 00:00:01 |
| 9 | TABLE ACCESS FULL | B022_CB_USER_INF | 119K| 1279K| | 205 (1)| 00:00:01 |
| 10 | BUFFER SORT | | 65770 | 4367K| | 2032 (1)| 00:00:01 |
| 11 | TABLE ACCESS FULL | d_laa | 65770 | 4367K| | 1668 (1)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | BUSINESS_CONTRACT | 1 | 30 | | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_R_BUSINESS_CONTRACT | 1 | | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID BATCHED| ACCT_PAYMENT_SCHEDULE | 12 | 516 | | 3 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_ACCT_PAYMENT_SCHEDULE_02 | 1 | | | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_CUSTOMER_INFO | 1 | | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_INFO | 1 | 55 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("aaa"."OBJECTNO"="AL"."SERIALNO")
7 - access("BCUI"."UIF_CSTNO"="BCCI"."CIF_CSTNO")
12 - filter("BC"."ONLINETYPE"='006')
13 - access("BC"."SERIALNO"="AL"."CONTRACTSERIALNO")
14 - filter("aaa"."FINISHDATE" IS NULL)
15 - access("aaa"."PAYDATE"='2023/08/21')
16 - access("CI"."CUSTOMERID"="AL"."CUSTOMERID")
17 - filter("BCCI"."CIF_CTFNO"=REPLACE("CI"."CERTID",'-',''))
Note
-----
- this is an adaptive plan
53 rows selected.
SQL>
3.仔细分析执行计划，然后再尝试用好的执行计划的执行步骤，一步步重写sql， 问题得到解决
--try to running again : 08/20 number is 8 ,08/21 number is 3800 , all done in 1 seconds
select * from
acct_payment_schedule aaa, d_laaal where aaa.objectno = al.serialno
aaa.finishdate is null and aaa.dddate = '2023/08/21'
select * from
acct_payment_schedule aaa, d_laaal where aaa.objectno = al.serialno
aaa.finishdate is null and aaa.dddate = '2023/08/20'
--try to running again : 08/20 number is 8 ,08/21 number is 3800 ,all done in 1 seconds
select distinct al.serialno, ci.customerName,
aaa.dddate,al.customerid, bc.wyserialno, bc.telphone
from
acct_payment_schedule aaa, d_laaal, business_contract bc,
customer_info ci where aaa.objectno = al.serialno
and ci.customerid
= al.customerid and bc.onlinetype =
'006' and bc.serialno = al.contractserialno and
aaa.finishdate is null and aaa.dddate = '2023/08/21'
select distinct al.serialno, ci.customerName,
aaa.dddate,al.customerid, bc.wyserialno, bc.telphone
from
acct_payment_schedule aaa, d_laaal, business_contract bc,
customer_info ci where aaa.objectno = al.serialno
and ci.customerid
= al.customerid and bc.onlinetype =
'006' and bc.serialno = al.contractserialno and
aaa.finishdate is null and aaa.dddate = '2023/08/20'
---re-wirte sql
select distinct al.serialno, ci.customerName,
aaa.dddate,al.customerid, bc.wyserialno, bc.telphone from
acct_payment_schedule aaa, d_laaal, ccmsetl.B022_CB_CST_INF bcci,
ccmsetl.B022_CB_USER_INF bcui, business_contract bc,
customer_info ci where aaa.objectno = al.serialno and
replace(ci.certid,'-','') = bcci.cif_ctfno and ci.customerid
= al.customerid and bcui.uif_cstno = bcci.cif_cstno and bc.onlinetype =
'006' and bc.serialno = al.contractserialno and
aaa.finishdate is null and aaa.dddate = '2023/08/21'
select *
from
(select distinct al.serialno, ci.customerName,ci.certid,
aaa.dddate,al.customerid, bc.wyserialno, bc.telphone
from
acct_payment_schedule aaa, d_laaal, business_contract bc,
customer_info ci where aaa.objectno = al.serialno
and ci.customerid
= al.customerid and bc.onlinetype =
'006' and bc.serialno = al.contractserialno and
aaa.finishdate is null and aaa.dddate = '2023/08/20') tabtmp,ccmsetl.B022_CB_CST_INF bcci,
ccmsetl.B022_CB_USER_INF bcui
where replace(tabtmp.certid,'-','') = bcci.cif_ctfno and bcui.uif_cstno = bcci.cif_cstno
select *
from
(select distinct al.serialno, ci.customerName,ci.certid,
aaa.dddate,al.customerid, bc.wyserialno, bc.telphone
from
acct_payment_schedule aaa, d_laaal, business_contract bc,
customer_info ci where aaa.objectno = al.serialno
and ci.customerid
= al.customerid and bc.onlinetype =
'006' and bc.serialno = al.contractserialno and
aaa.finishdate is null and aaa.dddate = '2023/08/21') tabtmp,ccmsetl.B022_CB_CST_INF bcci,
ccmsetl.B022_CB_USER_INF bcui
where replace(tabtmp.certid,'-','') = bcci.cif_ctfno and bcui.uif_cstno = bcci.cif_cstno
###sample 5 带函数的sql 优化，
解决思路，此类sql 优化办法不多，大概率是函数问题，而且函数是一级套着一级函数，通常去掉函数，查询就分别快，加上函数就特别慢。
--SQL5：select ci.lineid,ci.cltypeid,ci.cltypename,ci.businesstype,getitemname('ProductType',ci.businesstype) as businessTypename,
nvl(ci.linesum5,0) as linesum5,nvl(ddddd(ci.lineid),0) as sublineavailexposure,ci.currency,ci.rotative,ci.isUseTogether,
nvl(ci.bailRatio2,0) as bailRatio2
fromdddci where ci.parentlineid is not null and ci.customerid='4002180629000000000022' and ci.bcserialno='4002220119000000001667' and ci.SCFFlag = 'SCF';
->we try to sqlt to tunning sql id 2m0tpdbmpq398, it is ok
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
->we try to sqlt to tunning sql id 89jvskn9k4qgh, it is ok
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
-》dev feedback:
是这两条sql中有个function ddddd，这个比较慢
nvl(ddddd(ci.lineid),0) as sublineavailexposure -》 函数主要调用的哪个sql
-》单sql 走的是索引
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED|ddd | 1 | 122 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX2_aaa| 3 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
慢可能是返回的lineid太多，每行记录都要调用这个方法计算
-尝试通过active_session_history top_level_sql_id 找到子sql ,但是
-slow
select nvl(ddddd('4002220119000000000469'),0) as sublineavailexposure from dual;
-quick
select nvl(ddddd('4002220119000000000470'),0) as sublineavailexposure from dual;
select nvl(ddddd('4002220119000000000471'),0) as sublineavailexposure from dual;
ccmsopr ccmsopr
1.top level f0grabk67jab7
select sql_text,sql_id from v$sql where sql_text like 'select nvl(ddddd%'
SQL_TEXT SQL_ID
select nvl(ddddd('4002220119000000000469'),0) as sublineavailexposure from dual f0grabk67jab7
-》
-尝试通过active_session_history top_level_sql_id 找到子sql ,
select sql_id,sql_exec_id,count(*) from v$active_session_history where top_level_sql_id='&sqlid' group by sql_id,sql_exec_id
-子sql 仍然带有函数，就没法继续追查了
2.child sql: 2d9c7tng384vp
select sql_id from v$active_session_history where top_level_sql_id='&sqlid'
select * from v$sql where sql_id='2d9c7tng384vp'
select * from v$active_session_history
sql_text:
SELECT :B2 - GETSUBLINEOCCUPYEXPOSURE(:B1) FROM aaaWHERE LINEID = :B1
3. child sql GETSUBLINEOCCUPYEXPOSURE procedur sql, find no more chind sql
SELECT :B2 - GETSUBLINEOCCUPYEXPOSURE(:B1 ) FROM aaaWHERE LINEID = :B1
Select sum(GetBC_CL_OccupyExposure(CLLineID,objectno,parentlineno)) from childrenline_rela Where childrenlineno = CLLineID;
