为什么删除索引就产生递归，加上就没有？

显示全部楼层 · 2006-4-17 13:46:34

该表有2个索引：
SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

0recursive calls

4db block gets

2464consistent gets

2457physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

删除了1个索引：
SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

136recursive calls

5db block gets

2491consistent gets

2464physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

4sorts (memory)

0sorts (disk)

1rows processed

SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

0recursive calls

4db block gets

2464consistent gets

2464physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

又删除了1个索引：
SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

127recursive calls

5db block gets

2487consistent gets

2464physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

4sorts (memory)

0sorts (disk)

1rows processed

SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

0recursive calls

4db block gets

2464consistent gets

2464physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

SQL> CREATE INDEX WFXT.SP_INDEX_JFLSXXWFBH_YWLSH
2ON WFXT.SP_WF_MAIN_JFLSXX_WFBH
3(YWLSH)
4/
索引已创建。
SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

0recursive calls

4db block gets

2464consistent gets

2464physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

SQL> CREATE INDEX WFXT.SP_INDEX_JFLSXX_WFBH
2ON WFXT.SP_WF_MAIN_JFLSXX_WFBH
3(WFBH)
4/
索引已创建。
SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

0recursive calls

4db block gets

2464consistent gets

2464physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

1.为什么删除索引就产生递归，加索引就不产生？
2.在执行计划中说明是全表扫描，为什么有索引和没索引在物理读和逻辑读差别会那么大？
3.在Statistics里的db block gets和consistent gets与做SQL_TRACE产生的query和current列的值有什么关系吗？有的时候是相等的，可这次就差很多。

千问 · 2006-4-17 13:46:34

1 因为删除索引后,所以shared_pool中的与这个表相关的sql都被aged out,必须重新分析,导致较多的递归调用
2 因为索引走了最短的路径,所以在性能上有很大的提高
3 query与current就是db block gets + consistent gets 的关系呀.

千问 · 2006-4-17 13:46:34

db block gets = blocks gotten in "current" mode.That is, blocks gotten as they
exist right now.You'll see these for full table scans (segment headers are
read in current mode) and modification statements (we modify the block as it
exists "right now&quot

consistent gets = blocks gotten in consistent read mode.This is the mode we
read blocks in with a select for example.Also, when you do a searched
UPDATE/DELETE, we read the blocks in consistent read mode and then get the block
in current mode to actually do the modification.A select for update will do
this as well.

千问 · 2006-4-17 13:46:34

最初由 xzh2000 发布
[B]1 因为删除索引后,所以shared_pool中的与这个表相关的sql都被aged out,必须重新分析,导致较多的递归调用
2 因为索引走了最短的路径,所以在性能上有很大的提高
3 query与current就是db block gets + consistent gets 的关系呀. [/B]
可是，这次的query与current和db block gets + consistent gets 的关系就莫名其妙的，你看：
select count(*)
from
sp_wf_main_jflsxx_wfbh
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse50.00 0.00
0
0
1 0
Execute50.00 0.00
0
0
0 0
Fetch 100.00 0.001232012320 20 5
------- -------------- ---------- ---------- ---------- --------------------
total 200.00 0.001232012320 21 5
Misses in library cache during parse: 4
Optimizer goal: CHOOSE
Parsing user id: 51(WFXT)
对应的：
SQL> select count(*) from sp_wf_main_jflsxx_wfbh;
COUNT(*)

----------

146796

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE

10 SORT (AGGREGATE)

21 TABLE ACCESS (FULL) OF 'SP_WF_MAIN_JFLSXX_WFBH'

Statistics
----------------------------------------------------------

0recursive calls

4db block gets

2464consistent gets

2464physical reads

0redo size

366bytes sent via SQL*Net to client

425bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

千问 · 2006-4-17 13:46:34

分析一下表，再比较看看