sql
SELECT COUNT(1)
FROM (select distinct a.project_number "project_number",
a.project_name "project_name",
b.contract_number "contract_number",
b.contract_name "contract_name",
a.account_number "account_number",
a.account_name "account_name",
b.class_grade "class_grade", (select (v1.cost + v1.interest_adj + v1.mkt_value_chg + v1.cost_impairment_provision)
from (select v.*,
row_number() over(order by v.event_date desc) num
from xxt_sl_entry_balances_v v
where v.account_id = a.account_id) v1
where v1.num = 1) "asset_value_balance" ,
to_char(h.gl_date,'YYYY-MM') "gl_date",
b.init_date "init_date"
from amc_project_devalue_info_b a
left join amc_inter_asset_devalue_b b
on a.souce_id = b.devalue_id
left join amc_bill_asset_head_b h
on a.bill_id = h.bill_id) TMP_COUNT;
执行计划
1 #NSET2: [20, 1, 300]
2 #PIPE2: [20, 1, 300]
3 #PRJT2: [10, 1, 300]; exp_num(1), is_atom(FALSE)
4 #AAGR2: [10, 1, 300]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
5 #PRJT2: [10, 1189, 300]; exp_num(0), is_atom(FALSE)
6 #DISTINCT: [10, 1189, 300]
7 #PRJT2: [9, 1189, 300]; exp_num(10), is_atom(FALSE)
8 #INDEX JOIN LEFT JOIN2: [9, 1189, 300] ret_null(0)
9 #HASH LEFT JOIN2: [1, 1189, 300]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(exp_cast(A.SOUCE_ID)=B.DEVALUE_ID)
10 #CSCN2: [1, 1189, 300]; INDEX33557668(AMC_PROJECT_DEVALUE_INFO_B as A); btr_scan(1)
11 #CSCN2: [1, 2264, 222]; INDEX33557467(AMC_INTER_ASSET_DEVALUE_B as B); btr_scan(1)
12 #BLKUP2: [7, 1, 30]; INDEX33557203(H)
13 #SSEK2: [7, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as H), scan_range[A.BILL_ID,A.BILL_ID]
14 #SPL2: [10, 1, 3170]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
15 #PRJT2: [10, 1, 3170]; exp_num(1), is_atom(TRUE)
16 #SLCT2: [10, 1, 3170]; V1.NUM = var2
17 #PRJT2: [10, 25, 3170]; exp_num(5), is_atom(FALSE)
18 #AFUN: [10, 25, 3170]; afun_num(1); partition_num(0); order_num(1)[V.EVENT_DATE]
19 #SORT3: [10, 25, 3170]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
20 #PRJT2: [10, 1000, 3170]; exp_num(5), is_atom(FALSE)
21 #REMOTE SCAN: [10, 1000, 3170]; TMPREMOTETAB889693717@TOEBS, condition(TMPREMOTETAB889693717.ACCOUNT_ID = exp_param(no:0))
AUTOTRACE 和 ET
行号 COUNT(1)
---------- --------------------
1 1044
1 #NSET2: [12, 1->1, 300]
2 #PIPE2: [12, 1->1, 300]
3 #PRJT2: [2, 1->1, 300]; exp_num(1), is_atom(FALSE)
4 #AAGR2: [2, 1->1, 300]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
5 #PRJT2: [2, 1189->1044, 300]; exp_num(0), is_atom(FALSE)
6 #DISTINCT: [2, 1189->1044, 300], MEM_USED(8872KB), DISK_USED(0KB)
7 #PRJT2: [1, 1189->1189, 300]; exp_num(9), is_atom(FALSE)
8 #HASH LEFT JOIN2: [1, 1189->1189, 300]; key_num(1); col_num(9); partition_keys_num(0); mix(0); MEM_USED(12735KB), DISK_USED(0KB) KEY(exp_cast(A.SOUCE_ID)=B.DEVALUE_ID)
9 #CSCN2: [1, 1189->1189, 300]; INDEX33557668(AMC_PROJECT_DEVALUE_INFO_B); btr_scan(1)
10 #CSCN2: [1, 2264->2264, 222]; INDEX33557467(AMC_INTER_ASSET_DEVALUE_B); btr_scan(1)
11 #SPL2: [10, 1, 3170]; key_num(1), spool_num(0), has_variable(1), sites(-)
12 #PRJT2: [10, 1->1189, 3170]; exp_num(1), is_atom(TRUE)
13 #SLCT2: [10, 1->1189, 3170]; V1.NUM = var2
14 #PRJT2: [10, 25->54866, 3170]; exp_num(5), is_atom(FALSE)
15 #AFUN: [10, 25->54866, 3170]; afun_num(1)
16 #SORT3: [10, 25->54866, 3170]; key_num(1), is_distinct(FALSE), is_adaptive(0), MEM_USED(30720KB), DISK_USED(0KB)
17 #PRJT2: [10, 1000->54866, 3170]; exp_num(5), is_atom(FALSE)
18 #REMOTE SCAN: [10, 1000->54866, 3170] TMPREMOTETAB889665089@TOEBS
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
144 logical reads
0 physical reads
0 redo size
138 bytes sent to client
3442 bytes received from client
1 roundtrips to/from client
1189 sorts (memory)
0 sorts (disk)
1 rows processed
0 io wait time(ms)
167010 exec time(ms)
已用时间: 00:02:47.010. 执行号:21056406.
SQL> 2 3 4 et(21056406);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB)
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- -------------------- ----------------- --------------- --------------------
1 PIPE2 1 0% 12 2 5 0 0
0 0 NULL NULL 0
2 DLCK 3 0% 9 0 2 0 0
0 0 NULL NULL 0
3 SPL2 3 0% 9 11 1 0 0
0 0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB)
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- -------------------- ----------------- --------------- --------------------
4 PRJT2 3 0% 9 5 10 0 0
0 0 NULL NULL 0
5 PRJT2 4 0% 8 3 4 0 0
0 0 NULL NULL 0
6 AAGR2 25 0% 7 4 7 0 0
0 0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB)
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- -------------------- ----------------- --------------- --------------------
7 NSET2 45 0% 6 1 3 0 0
0 0 NULL NULL 0
8 CSCN2 580 0% 5 9 5 0 0
0 0 NULL NULL 0
9 CSCN2 1232 0% 4 10 9 0 0
0 0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB)
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- -------------------- ----------------- --------------- --------------------
10 DIST 1538 0% 3 6 15 8872 0
1042 2 NULL NULL 0
11 HLO2 1600 0% 2 8 24 12735 0
1133 0 NULL NULL 57
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB)
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- -------------------- ----------------- --------------- --------------------
12 PRJT2 166996896 100% 1 7 20 0 0
0 0 NULL NULL 0
12 rows got
分析
分析得知耗时100%在相关子查询上。
spl次数是1189次。
同样步骤分析oracle trace。
REMOTE SCAN 扫描次数574次。DM 比 ORACLE 多一倍。
通过分析，猜测 oracle 可能做了缓存或者去重，所以时间上oracle快一倍。
标签：10,USED,300,一例,性能,--------------------,num,SPL,NULL From： https://www.cnblogs.com/lixiaomeng/p/18159914