64W数据表，查询数据执行时间为139s，优化为1s
AFC_TXN.TXN_DATA_FUNC.fetchbatch包中的SQL查询语句过慢
cut_batch表大约有64W数据，其中执行参数为substr(t.BATCH_ID,1,2)='01'的语句为139s
给cut_batch表添加索引create index idx_cut_batch2 on cut_batch(substr(batch_id,1,2))后，执行时间为54s
修改语句为以下写法：
SELECT
os.AFC_DEVICE_ID AS AFC_DEVICE_ID,
os.BATCH_ID AS BATCH_ID,
os.DATA_DIRECTORY AS DATA_DIRECTORY,
os.RECORD_COUNT AS RECORD_COUNT
FROM (
SELECT
t.AFC_DEVICE_ID AS AFC_DEVICE_ID,
t.BATCH_ID AS BATCH_ID,
t.DATA_DIRECTORY AS DATA_DIRECTORY,
t.RECORD_COUNT AS RECORD_COUNT,
SUM(t.RECORD_COUNT) OVER (ORDER BY BATCH_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL_TXN
FROM
(SELECT
t.AFC_DEVICE_ID AS AFC_DEVICE_ID,
t.BATCH_ID AS BATCH_ID,
t.DATA_DIRECTORY AS DATA_DIRECTORY,
t.RECORD_COUNT AS RECORD_COUNT,
t.SC_BATCH_ID AS SC_BATCH_ID
FROM cut_batch_test t
WHERE substr(t.BATCH_ID,1,2) = :"SYS_B_0") t
WHERE t.SC_BATCH_ID IS NULL
) os
WHERE os.TOTAL_TXN < 300;
其执行速度达到45s
alter system set db_cache_size=200M scope=both;
alter system set large_pool_size=50M scope=both;
alter system set sort_area_size=10485760 scope=spfile;
其SQL执行速度未改变。
10046 事件按照收集信息内容，可以分成4个级别：
Level 1： 等同于SQL_TRACE 的功能
Level 4： 在Level 1的基础上增加收集绑定变量的信息
Level 8： 在Level 1 的基础上增加等待事件的信息
Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息
通过开启会话调试查询其SQL具体在oracle内部做了什么操作：
SQL>alter session set events = '10046 trace name context forever,level 12';
SQL>执行查询语句SQL
SQL>alter session set events = '10046 trace name context off';
[root@localhost:/app/oracle/diag/rdbms/xxx/xxx/trace/]tkprof AFC010C1_ora_18100.trc /root/AFC010C1_ora_18100.txt
查看AFC010C1_ora_18100.txt内容如下：
SELECT
os.AFC_DEVICE_ID AS AFC_DEVICE_ID,
os.BATCH_ID AS BATCH_ID,
os.DATA_DIRECTORY AS DATA_DIRECTORY,
os.RECORD_COUNT AS RECORD_COUNT
FROM (
SELECT
t.AFC_DEVICE_ID AS AFC_DEVICE_ID,
t.BATCH_ID AS BATCH_ID,
t.DATA_DIRECTORY AS DATA_DIRECTORY,
t.RECORD_COUNT AS RECORD_COUNT,
SUM(t.RECORD_COUNT) OVER (ORDER BY BATCH_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL_TXN
FROM
(SELECT
t.AFC_DEVICE_ID AS AFC_DEVICE_ID,
t.BATCH_ID AS BATCH_ID,
t.DATA_DIRECTORY AS DATA_DIRECTORY,
t.RECORD_COUNT AS RECORD_COUNT,
t.SC_BATCH_ID AS SC_BATCH_ID
FROM cut_batch_test t
WHERE substr(t.BATCH_ID,:"SYS_B_0",:"SYS_B_1") = :"SYS_B_2") t
WHERE t.SC_BATCH_ID IS NULL
) os
WHERE os.TOTAL_TXN < :"SYS_B_3"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 1 0 0
Fetch 1 0.95 46.97 266 16819 20 100
------- ------ --