[原创]求解：recursive calls，consistent gets，physical reads 都很高，如何分析SQL
开发人员问：查询巨慢！
4954787 recursive calls
0 db block gets
11877363 consistent gets
33421 physical reads
有一次全表扫描，
4 3 TABLE ACCESS (FULL) OF 'VIP_SET' (TABLE) (Cost=2 Car
d=6 Bytes=66)
什么原因造成 recursive calls，consistent gets，physical reads 都很高呢？
4954787 recursive calls
0 db block gets
11877363 consistent gets
33421 physical reads
有一次全表扫描，
4 3 TABLE ACCESS (FULL) OF 'VIP_SET' (TABLE) (Cost=2 Car
d=6 Bytes=66)
什么原因造成 recursive calls，consistent gets，physical reads 都很高呢？
当前SQL
执行计划如下：PLAN-->CBO
代码:
Execution Plan ----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=994 Card=81283 Byt
es=12680148)
1 0 SORT (GROUP BY)
2 1 CONCATENATION
3 2 HASH JOIN (Cost=497 Card=113 Bytes=17628)
4 3 TABLE ACCESS (FULL) OF 'VIP_SET' (TABLE) (Cost=2 Car
d=6 Bytes=66)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'ACC_NBR_200701' (T
ABLE) (Cost=3 Card=1 Bytes=75)
6 5 NESTED LOOPS (Cost=495 Card=164 Bytes=23780)
7 6 TABLE ACCESS (FULL) OF 'ACCT_ITEM_DAY_TMP' (TABL
E) (Cost=2 Card=164 Bytes=11480)
8 6 INDEX (RANGE SCAN) OF 'ACC_NBR_200701_INDEX_002'
(INDEX) (Cost=2 Card=1)
9 2 TABLE ACCESS (BY INDEX ROWID) OF 'VIP_SET' (TABLE) (Co
st=1 Card=1 Bytes=11)
10 9 NESTED LOOPS (Cost=496 Card=1 Bytes=156)
11 10 NESTED LOOPS (Cost=495 Card=1 Bytes=145)
12 11 TABLE ACCESS (FULL) OF 'ACCT_ITEM_DAY_TMP' (TABL
E) (Cost=2 Card=164 Bytes=11480)
13 11 TABLE ACCESS (BY INDEX ROWID) OF 'ACC_NBR_200701
' (TABLE) (Cost=3 Card=1 Bytes=75)
14 13 INDEX (RANGE SCAN) OF 'ACC_NBR_200701_INDEX_00
2' (INDEX) (Cost=2 Card=1)
15 10 INDEX (RANGE SCAN) OF 'VIP_SET_INDEX_002' (INDEX)
(Cost=0 Card=6)
Statistics ----------------------------------------------------------
4954787 recursive calls
0 db block gets
11877363 consistent gets
33421 physical reads
0 redo size
1794661 bytes sent via SQL*Net to client
57762 bytes received via SQL*Net from client
5208 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
78095 &nbs分析acct_item_day_tmp表后计划
已选择78095行。代码:
Execution Plan ----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21484 Card=112949
Bytes=12876186)
1 0 SORT (GROUP BY) (Cost=21484 Card=112949 Bytes=12876186)
2 1 HASH JOIN (Cost=19659 Card=112949 Bytes=12876186)
3 2 TABLE ACCESS (FULL) OF 'VIP_SET' (TABLE) (Cost=2 Card=
6 Bytes=66)
4 2 HASH JOIN (Cost=19655 Card=164289 Bytes=16921767)
5 4 TABLE ACCESS (FULL) OF 'ACCT_ITEM_DAY_TMP' (TABLE) (
Cost=1414 Card=1636704 Bytes=45827712)
6 4 TABLE ACCESS (FULL) OF 'ACC_NBR_200701' (TABLE) (Cos
t=12735 Card=790526 Bytes=59289450)
Statistics ----------------------------------------------------------
4954900 recursive calls
0 db block gets
3345777 consistent gets
25970 physical reads
0 redo size
1794661 bytes sent via SQL*Net to client
57762 bytes received via SQL*Net from client
5208 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
78095 &nbs分析vip_set表后计划
已选择78095行。代码:
Execution Plan ----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21949 Card=143753
Bytes=16100336)
1 0 SORT (GROUP BY) (Cost=21949 Card=143753 Bytes=16100336)
2 1 HASH JOIN (Cost=19659 Card=143753 Bytes=16100336)
3 2 TABLE ACCESS (FULL) OF 'VIP_SET' (TABLE) (Cost=2 Card=
7 Bytes=63)
4 2 HASH JOIN (Cost=19655 Card=164289 Bytes=16921767)
5 4 TABLE ACCESS (FULL) OF 'ACCT_ITEM_DAY_TMP' (TABLE) (
Cost=1414 Card=1636704 Bytes=45827712)
6 4 TABLE ACCESS (FULL) OF 'ACC_NBR_200701' (TABLE) (Cos
t=12735 Card=790526 Bytes=59289450)
Statistics ----------------------------------------------------------
4954900 recursive calls
0 db block gets
3345777 consistent gets
29435 physical reads
0 redo size
1794661 bytes sent via SQL*Net to client
57762 bytes received via SQL*Net from client
5208 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
78095 &nbs分析acc_nbr_200701表后计划
已选择78095行。代码:
Execution Plan ----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=20576 Card=90983 B
ytes=9098300)
1 0 SORT (GROUP BY) (Cost=20576 Card=90983 Bytes=9098300)
2 1 HASH JOIN (Cost=19276 Card=90983 Bytes=9098300)
3 2 TABLE ACCESS (FULL) OF 'VIP_SET' (TABLE) (Cost=2 Card=
7 Bytes=63)
4 2 HASH JOIN (Cost=19272 Card=168968 Bytes=15376088)
5 4 TABLE ACCESS (FULL) OF 'ACC_NBR_200701' (TABLE) (Cos
t=12735 Card=790526 Bytes=49803138)
6 4 TABLE ACCESS (FULL) OF 'ACCT_ITEM_DAY_TMP' (TABLE) (
Cost=1414 Card=1636704 Bytes=45827712)
Statistics ----------------------------------------------------------
4954943 recursive calls
0 db block gets
3345777 consistent gets
32090 physical reads
0 redo size
1794661 bytes sent via SQL*Net to client
57762 bytes received via SQL*Net from client
5208 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
78095 &nbs对三张表分析之后发现，recursive calls，consistent gets，physical reads 更高！
代码:
select sum(a.arrearagesum) arrearagesum,
a.ACCT_ITEM_TYPE_ID ACCT_ITEM_TYPE_ID,
a.SERV_DEVICE SERV_DEVICE,
a.SERV_TYPE_ID SERV_TYPE_ID,
b.set_id set_id,
b.workstation_id workstation_id, from
acct_item_day_tmp a,vip_set b,acc_nbr_200701 c
where
a.acc_nbr = b.accnbr and
(a.serv_device = fc_get_serv_device(b.prod_id) or a.serv_device = b.prod_id)
and b.cust_type_id = c.CUST_TYPE_ID and b.STS_97 = c.STS_97 and c.SIGN = 0
group by
a.ACCT_ITEM_TYPE_ID,a.SERV_DEVICE,
a.SERV_TYPE_ID,b.set_id,b.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/593324/viewspace-376154/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
[原创]降序索引失效问题
请登录后发表评论
登录
全部评论
