观察到监控软件上有一个sql，等待事件很长，哪来看看，废话不多说，直接看sql_text:

软件捕获到的

INSERT
INTO KEEPER.SALE_CARD_BALANCE
(
ACCOUNT_ID,
CARD_NO,
CARD_SORT_ID,
CARD_TYPE,
CARD_TYPE_NAME,
SUMMONS_NO,
MANAGEDBRANCHNO,
MANAGEDBRANCHNAME,
SALE_DATE,
UNIT_NO,
NOW_BALANCE,
CREATE_DATE,
RUN_DATE,
CARD_STATE,
INIT_AMOUNT
)
SELECT
/*+index(b IDX_ACCOUNT_ID)*/
A.ACCOUNT_ID,
A.CARD_NO,
A.CARD_SOTR_ID,
A.CARD_TYPE,
A.CARD_TYPE_NAME,
A.SUMMONS_NO,
A.MANAGEDBRANCHNO,
A.MANAGEDBRANCHNAME,
A.OPT_DATE AS SALE_DATE,
A.UNIT_NO,
SUM(B.AMOUNT) AS NOW_BALANCE,
SYSDATE,
:B1 AS RUN_DATE,
B.STATUS,
A.INIT_AMOUNT
FROM KEEPER.SJL_CARD_BANK A
JOIN UNITED.XSH_VALID_CARD B
ON A.ACCOUNT_ID = B.ACCOUNT_ID
AND B.AMOUNT>0
WHERE A.SALE_SYS = 'new'
AND A.DETAIL_TYPE != '废卡'
GROUP BY A.ACCOUNT_ID,
A.CARD_NO,
A.CARD_SOTR_ID,
A.CARD_TYPE,
A.CARD_TYPE_NAME,
A.SUMMONS_NO,
A.MANAGEDBRANCHNO,
A.MANAGEDBRANCHNAME,
A.OPT_DATE,
A.UNIT_NO,
A.DETAIL_TYPE,
B.STATUS,
A.ZERO_FLAG,
A.INIT_AMOUNT;

对应的执行计划：

Plan Hash Value : 1065546509

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14904088 | 1907723264 | 1676835 | 00:01:11 |
| 1 | HASH GROUP BY | | 14904088 | 1907723264 | 1676835 | 00:01:11 |
| * 2 | HASH JOIN | | 14904088 | 1907723264 | 1250043 | 00:00:53 |
| 3 | JOIN FILTER CREATE | :BF0000 | 13980272 | 1467928560 | 132686 | 00:00:06 |
| * 4 | TABLE ACCESS FULL | SJL_CARD_BANK | 13980272 | 1467928560 | 132686 | 00:00:06 |
| 5 | VIEW | VW_GBC_5 | 46903023 | 1078769529 | 961334 | 00:00:41 |
| 6 | HASH GROUP BY | | 46903023 | 609739299 | 961334 | 00:00:41 |
| 7 | JOIN FILTER USE | :BF0000 | 46903023 | 609739299 | 734937 | 00:00:32 |
| * 8 | TABLE ACCESS FULL | XSH_VALID_CARD | 46903023 | 609739299 | 734937 | 00:00:32 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("A"."ACCOUNT_ID"="ITEM_1")
* 4 - filter("A"."SALE_SYS"='new' AND "A"."DETAIL_TYPE"<>'废卡')
* 8 - filter("B"."AMOUNT">0 AND SYS_OP_BLOOM_FILTER(:BF0000,"B"."ACCOUNT_ID"))

自己能力有限，一般遇到执行比较慢的sql，首先看看执行计划，是否最优，然后看是否走索引，走索引是否是最佳索引组合，不走索引（可能是新执行的sql，没有创建索引，此时查询当前表的大小，然后再根据创建索引列的数据是否重复，再判断是否要创建索引，创建时一定要加online，否则会让你栓Q。），然后看表，索引的统计信息是否最新。还有有索引不走，可以加hiti 强制走再看看执行计划是否改变，下面我把select中的b别名改成了真实的表名，如下sql：

INSERT
INTO KEEPER.SALE_CARD_BALANCE
(
ACCOUNT_ID,
CARD_NO,
CARD_SORT_ID,
CARD_TYPE,
CARD_TYPE_NAME,
SUMMONS_NO,
MANAGEDBRANCHNO,
MANAGEDBRANCHNAME,
SALE_DATE,
UNIT_NO,
NOW_BALANCE,
CREATE_DATE,
RUN_DATE,
CARD_STATE,
INIT_AMOUNT
)
SELECT
/*+index(XSH_VALID_CARD IDX_ACCOUNT_ID)*/ 

A.ACCOUNT_ID,
A.CARD_NO,
A.CARD_SOTR_ID,
A.CARD_TYPE,
A.CARD_TYPE_NAME,
A.SUMMONS_NO,
A.MANAGEDBRANCHNO,
A.MANAGEDBRANCHNAME,
A.OPT_DATE AS SALE_DATE,
A.UNIT_NO,
SUM(B.AMOUNT) AS NOW_BALANCE,
SYSDATE,
:B1 AS RUN_DATE,
B.STATUS,
A.INIT_AMOUNT
FROM KEEPER.SJL_CARD_BANK A
JOIN UNITED.XSH_VALID_CARD B
ON A.ACCOUNT_ID = B.ACCOUNT_ID
AND B.AMOUNT>0
WHERE A.SALE_SYS = 'new'
AND A.DETAIL_TYPE != '废卡'
GROUP BY A.ACCOUNT_ID,
A.CARD_NO,
A.CARD_SOTR_ID,
A.CARD_TYPE,
A.CARD_TYPE_NAME,
A.SUMMONS_NO,
A.MANAGEDBRANCHNO,
A.MANAGEDBRANCHNAME,
A.OPT_DATE,
A.UNIT_NO,
A.DETAIL_TYPE,
B.STATUS,
A.ZERO_FLAG,
A.INIT_AMOUNT;

执行计划：

Plan Hash Value : 401238233

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14904087 | 1758682266 | 1397129 | 00:01:00 |
| 1 | HASH GROUP BY | | 14904087 | 1758682266 | 1397129 | 00:01:00 |
| * 2 | HASH JOIN | | 14904087 | 1758682266 | 1001325 | 00:00:43 |
| * 3 | TABLE ACCESS FULL | XSH_VALID_CARD | 46903023 | 609739299 | 734937 | 00:00:32 |
| * 4 | TABLE ACCESS FULL | SJL_CARD_BANK | 13980272 | 1467928560 | 132686 | 00:00:06 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("A"."ACCOUNT_ID"="B"."ACCOUNT_ID")
* 3 - filter("B"."AMOUNT">0)
* 4 - filter("A"."SALE_SYS"='new' AND "A"."DETAIL_TYPE"<>'废卡')

疑点：hiti中，用别名和原表名效果不一样吗？
还有，是否还有可以优化的地方，请各位大佬给看看