本帖最后由 wouwouwou 于 2014-12-30 15:36 编辑
其中有如下的sql,原来是秒出的,现在要几十秒。
SQL> SELECT count(*)
2 FROM Tpr_Pm_Ap_Min a
3 WHERE Begintime = to_date('2014-12-27 7:30:00','yyyy-mm-dd hh24:mi:ss')
4 AND NOT EXISTS (SELECT 1
5 FROM Tca_Vendor b
6 WHERE A.Vendor = B.Vendor
7 AND B.Status = 0);
COUNT(*)
----------
210988
Executed in 62.297 seconds
其中Tca_Vendor 是小表,16条记录,status=0的记录为0.
SQL> SELECT count(*) FROM Tca_Vendor b;
COUNT(*)
----------
16
Executed in 0.047 seconds
SQL> SELECT * FROM Tca_Vendor b WHERE B.Status = 0;
VENDOR STATUS
------------------------------------------------------------ ------
Executed in 0.047 seconds
SQL>
现在如果去掉连接,才能秒出。
SQL> SELECT count(*)
2 FROM Tpr_Pm_Ap_Min a
3 WHERE Begintime = to_date('2014-12-27 7:30:00', 'yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
210988
Executed in 0.047 seconds
感觉语句简单,即使重启,执行计划应该也不会有什么变化,那这大概会是什么问题呢?
现在的执行计划如下:
SQL> explain plan for
2
2 SELECT count(*)
3 FROM Tpr_Pm_Ap_Min a
4 WHERE Begintime = to_date('2014-12-27 7:30:00','yyyy-mm-dd hh24:mi:ss')
5 AND NOT EXISTS (SELECT 1
6 FROM Tca_Vendor b
7 WHERE A.Vendor = B.Vendor
8 AND B.Status = 0);
Explained
Executed in 0.031 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1254719166
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22
| 1 | SORT AGGREGATE | | 1 | 22
|* 2 | HASH JOIN ANTI | | 1 | 22
| 3 | PARTITION RANGE SINGLE | | 1 | 13
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TPR_PM_AP_MIN | 1 | 13
|* 5 | INDEX RANGE SCAN | IDX_TPR_PM_AP_MIN | 1 |
|* 6 | TABLE ACCESS FULL | TCA_VENDOR | 1 | 9
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."VENDOR"="B"."VENDOR")
5 - access("BEGINTIME"=TO_DATE(' 2014-12-27 07:30:00', 'syyyy-mm-dd hh24:mi:s
6 - filter("B"."STATUS"=0)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
24 rows selected
Executed in 0.203 seconds
SQL>
求解疑及解决方法。