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_RB_ACCT_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 记的自适应性，和社区的强大。这一点远远超过 开源数据库 社区。