高手们好! 一下sql语句中包含两个表:
1. bc_waive_printed_charge SELECT Count(*) FROM bc_waive_printed_charge ---11554 rowsindex 有:index1:pps , index2:misref2. bc_mis_tnSELECT Count(*) FROM bc_mis_tn ----693996 rowsindex 有 index1:(pps,tran_date) , index2:misref
-----外连接sql语句--------
SQL> SELECT
2 a.misref,
3 a.pps,
4 To_char(To_Date(a.tran_period,'yyyy/mm')+1,'MON/yyyy'),
5 Nvl(b.waive_amount,a.amount),
6 Decode(b.waive_reason,NULL,'N','Y') AS waive,
7 b.waive_reason,
8 b.staff_create AS waive_by,
9 b.cus_rmk_code,
10 b.waive_misref,b.create_date
11 FROM bc_waive_printed_charge b,bc_mis_tn a
12 WHERE a.pps=b.pps(+)
13 AND a.misref=b.misref(+)
14 AND re_code=75
15 ORDER BY a.tran_period;67296 rows selected.Elapsed: 00:00:38.15Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3493 Card=67294
Bytes=6863988) 1 0 SORT (ORDER BY) (Cost=3493 Card=67294 Bytes=6863988)
2 1 HASH JOIN (RIGHT OUTER) (Cost=1921 Card=67294 Bytes=6863
988) 3 2 TABLE ACCESS (FULL) OF 'BC_WAIVE_PRINTED_CHARGE' (TABL
E) (Cost=31 Card=11554 Bytes=831888) 4 2 TABLE ACCESS (FULL) OF 'BC_MIS_TN' (TABLE) (Cost=1889
Card=67294 Bytes=2018820)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9539 consistent gets
0 physical reads
0 redo size
2573913 bytes sent via SQL*Net to client
31681 bytes received via SQL*Net from client
4488 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
67296 rows processed-----不带外连接sql语句--------
SQL>SELECT
a.misref,
a.pps,
To_char(To_Date(a.tran_period,'yyyy/mm')+1,'MON/yyyy'),
Nvl(b.waive_amount,a.amount),
Decode(b.waive_reason,NULL,'N','Y') AS waive,
b.waive_reason,
b.staff_create AS waive_by,
b.cus_rmk_code,
b.waive_misref,b.create_date
FROM bc_waive_printed_charge b,bc_mis_tn a
WHERE a.pps=b.pps
AND a.misref=b.misref
AND re_code=75
ORDER BY a.tran_period;
11554 rows selected.
Elapsed: 00:00:06.83Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1922 Card=1120 B
ytes=114240) 1 0 SORT (ORDER BY) (Cost=1922 Card=1120 Bytes=114240)
2 1 HASH JOIN (Cost=1921 Card=1120 Bytes=114240)
3 2 TABLE ACCESS (FULL) OF 'BC_WAIVE_PRINTED_CHARGE' (TABL
E) (Cost=31 Card=11554 Bytes=831888) 4 2 TABLE ACCESS (FULL) OF 'BC_MIS_TN' (TABLE) (Cost=1889
Card=67294 Bytes=2018820)Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9539 consistent gets
0 physical reads
0 redo size
735694 bytes sent via SQL*Net to client
5669 bytes received via SQL*Net from client
772 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11554 rows processed1.如何优化带有外连接的sql语句呢?
2.为什么TABLE ACCESS 是FULL SCAN呢? 如何走索引呢?sql
1. bc_waive_printed_charge SELECT Count(*) FROM bc_waive_printed_charge ---11554 rowsindex 有:index1:pps , index2:misref2. bc_mis_tnSELECT Count(*) FROM bc_mis_tn ----693996 rowsindex 有 index1:(pps,tran_date) , index2:misref
-----外连接sql语句--------
SQL> SELECT
2 a.misref,
3 a.pps,
4 To_char(To_Date(a.tran_period,'yyyy/mm')+1,'MON/yyyy'),
5 Nvl(b.waive_amount,a.amount),
6 Decode(b.waive_reason,NULL,'N','Y') AS waive,
7 b.waive_reason,
8 b.staff_create AS waive_by,
9 b.cus_rmk_code,
10 b.waive_misref,b.create_date
11 FROM bc_waive_printed_charge b,bc_mis_tn a
12 WHERE a.pps=b.pps(+)
13 AND a.misref=b.misref(+)
14 AND re_code=75
15 ORDER BY a.tran_period;67296 rows selected.Elapsed: 00:00:38.15Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3493 Card=67294
Bytes=6863988) 1 0 SORT (ORDER BY) (Cost=3493 Card=67294 Bytes=6863988)
2 1 HASH JOIN (RIGHT OUTER) (Cost=1921 Card=67294 Bytes=6863
988) 3 2 TABLE ACCESS (FULL) OF 'BC_WAIVE_PRINTED_CHARGE' (TABL
E) (Cost=31 Card=11554 Bytes=831888) 4 2 TABLE ACCESS (FULL) OF 'BC_MIS_TN' (TABLE) (Cost=1889
Card=67294 Bytes=2018820)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9539 consistent gets
0 physical reads
0 redo size
2573913 bytes sent via SQL*Net to client
31681 bytes received via SQL*Net from client
4488 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
67296 rows processed-----不带外连接sql语句--------
SQL>SELECT
a.misref,
a.pps,
To_char(To_Date(a.tran_period,'yyyy/mm')+1,'MON/yyyy'),
Nvl(b.waive_amount,a.amount),
Decode(b.waive_reason,NULL,'N','Y') AS waive,
b.waive_reason,
b.staff_create AS waive_by,
b.cus_rmk_code,
b.waive_misref,b.create_date
FROM bc_waive_printed_charge b,bc_mis_tn a
WHERE a.pps=b.pps
AND a.misref=b.misref
AND re_code=75
ORDER BY a.tran_period;
11554 rows selected.
Elapsed: 00:00:06.83Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1922 Card=1120 B
ytes=114240) 1 0 SORT (ORDER BY) (Cost=1922 Card=1120 Bytes=114240)
2 1 HASH JOIN (Cost=1921 Card=1120 Bytes=114240)
3 2 TABLE ACCESS (FULL) OF 'BC_WAIVE_PRINTED_CHARGE' (TABL
E) (Cost=31 Card=11554 Bytes=831888) 4 2 TABLE ACCESS (FULL) OF 'BC_MIS_TN' (TABLE) (Cost=1889
Card=67294 Bytes=2018820)Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9539 consistent gets
0 physical reads
0 redo size
735694 bytes sent via SQL*Net to client
5669 bytes received via SQL*Net from client
772 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11554 rows processed1.如何优化带有外连接的sql语句呢?
2.为什么TABLE ACCESS 是FULL SCAN呢? 如何走索引呢?sql
外连接一般来说不走索引扫描,执行的都是全表扫描
建议hint 强制Oracle走索引进行比较试试cost开销
搞一下,一般来说可以降低开销
在搞sql前先收集下统计信息先
如果把上面这些搞了,执行计划还不对
弄个10053试一下,看cbo是咋弄出来的执行计划
返回行和逻辑读的比率大概是7个,还是有优化的空间的我不清楚你的业务逻辑,也许根据业务改写sql会比较有效果这里先试试
772 SQL*Net roundtrips to/from client
11554 rows processedSQL*Net roundtrips=[ rows processed/arraysize]+2把arraysize设置大点试试arraysize缺省为15,命令set arraysize n 可设定arraysize的值
修改arraysize确实可以提高性能,要为了这个sql语句修改整个系统的arraysize?我的意思把arraysize改成固定值。会有什么副作用吗?而且我也没那权限呢。
这是Oracle提供的行预取功能,session级,set arraysize 5000 即可,缺省是15
如果我以上的sql语句是创建一个view呢,也能用 set arraysize的方法? 问题是用户查询这个view的时候如何设置呢?