toad 分析执行计划产生的影响
toad 分析执行计划产生的影响
生成环境和真实环境执行计划不一样,真实环境不走索引。测试环境几秒钟、真实环境2分多钟。先上直接计划
[sql]- SQL> SELECT COUNT(*)
- 2 FROM CLAFIMDF
- 3 WHERE CLAIM_TYPE IN ('E', 'M')
- 4 AND CLAIMS_PROCESS_STATUS in ('58', '80')
- 5 AND ORIGBILL_DESPATCH_DATE IS NULL
- 6 AND PAYOR_CODE IN (SELECT RPAD(STNCD, 7, ' ')
- 7 FROM SYM_USFFRSTN
- 8 WHERE USRID = 'ddd'
- 9 AND STNTYP = 'PY')
- 10 /
- Elapsed: 00:02:03.21
- Execution Plan
- ----------------------------------------------------------
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
- --------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 33 | 5964 (1)|
- | 1 | SORT AGGREGATE | | 1 | 33 | |
- | 2 | HASH JOIN RIGHT SEMI | | 313 | 10329 | 5964 (1)|
- | 3 | TABLE ACCESS BY INDEX ROWID| SYM_USFFRSTN | 62 | 1240 | 14 (0)|
- | 4 | INDEX RANGE SCAN | SYM_USRSTN_IDX1 | 62 | | 3 (0)|
- | 5 | TABLE ACCESS FULL | CLAFIMDF | 6570 | 85410 | 5949 (1)|
- --------------------------------------------------------------------------------------
- Note
- -----
- - 'PLAN_TABLE' is old version
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 351759 consistent gets
- 84257 physical reads
- 0 redo size
- 420 bytes sent via SQL*Net to client
- 420 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
请注意这个 - 'PLAN_TABLE' is old version ,这个是神马意思?
原因,曾经使用toad的执行计划分析,所以执行了它自带的脚本生成的plan_table
解决方式:重新创建plan_table,
SQL> drop table PLAN_TABLE;
SQL> @?/rdbms/admin/utlxplan
这个做完以后,说问题解决了,具体就不知道了。呵呵,如果不走索引,可以��看10053事件。10053事件下回补充
但是难道toad就不能随便用吗?不是的,toad的执行计划可以使用TOAD_PLAN_TABLE 来存放嘛!
完工。
评论暂时关闭