近期迁云,云上磁盘性能很差,对当前数据库优化IO
如下SQL消耗IO资源很高:
SELECT SUM("A2"."FEE_CASH"),
'1',
"A1"."CHL_REFUND_NO",
"A2"."PAYPLAT_TRADNO",
2
FROM (SELECT MAX("A4"."UUID") "MAX(TTTT.UUID)"
FROM "HUZH_CREDIT"."B_SETTILE_DATA" "A4"
GROUP BY "A4"."PAYPLAT_TRADNO") "A3",
"HUZH_CREDIT"."B_SETTILE_DATA" "A2",
"HUZH_CREDIT"."B_BILL_REFUND" "A1"
WHERE "A2"."UUID" = "A3"."MAX(TTTT.UUID)"
AND "A2"."PAYPLAT_TRADNO" LIKE 'M201%'
AND "A2"."REF_STATE" = '1'
AND "A2"."FEE_CASH" <> 0
AND "A2"."PAYPLAT_TRADNO" = "A1"."BILL_TRAN_NO"(+)
AND "A1"."BILL_TRAN_NO"(+) LIKE 'M201%'
GROUP BY '1', "A1"."CHL_REFUND_NO", "A2"."PAYPLAT_TRADNO"
多表关联后返回数据行数34 rows 因此这里使用hash join是错误的,要走nl
对A1,A2,A3(子查询)进行数据量收集:
select count(*) from
(SELECT MAX("A4"."UUID") "MAX(TTTT.UUID)"
FROM "HUZH_CREDIT"."B_SETTILE_DATA" "A4"
GROUP BY "A4"."PAYPLAT_TRADNO")
COUNT(*)
----------
55715
select count(*) from "HUZH_CREDIT"."B_SETTILE_DATA" "A2"
WHERE "A2"."PAYPLAT_TRADNO" LIKE 'M201%'
AND "A2"."REF_STATE" = '1'
AND "A2"."FEE_CASH" <> 0;
COUNT(*)
---------
90
select count(*) from "HUZH_CREDIT"."B_BILL_REFUND" "A1"
where "A1"."BILL_TRAN_NO" LIKE 'M201%'
COUNT(*)
---------
1296
选择性收集:
select a.column_name,
b.num_rows,
a.num_distinct cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a ,dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'HUZH_CREDIT'
and a.table_name = 'B_SETTILE_DATA';
select a.column_name,
b.num_rows,
a.num_distinct cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a ,dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'HUZH_CREDIT'
and a.table_name = 'B_BILL_REFUND';
相关关联列创建索引:
A1.BILL_TRAN_NO选择性好,A2驱动A1表,在A1表关联列建立索引:
create index HUZH_CREDIT.B_BILL_REFUND_01 on HUZH_CREDIT.B_BILL_REFUND(BILL_TRAN_NO)
A2谓词过滤条件建索引:
create index HUZH_CREDIT.B_SETTILE_DATA_01 on HUZH_CREDIT.B_SETTILE_DATA(REF_STATE,PAYPLAT_TRADNO,FEE_CASH)
A2,A3关联建索引:
CREATE INDEX HUZH_CREDIT.I_B_SETTILE_DATA_05 ON HUZH_CREDIT.B_SETTILE_DATA(uuid,PAYPLAT_TRADNO);
优化后执行计划: