一条select语句查询很慢，如何确定问题所在？两个表相关联

vepeta

原帖由 棉花糖ONE 于 2008-3-25 17:24 发表
select /*+ use_hash(m,s) ordered full(s) */ count(0) from  mobile_mark m,notealy_send s where (substr(s.smssend_mobile,1,7)=m.mark_mobile or substr(s.smssend_mobile,3,7)=m.mark_mobile) and s.smssend_error!='0' and s.smssend_time>=to_date('2007-2-25 00:00:00','syyyy-mm-dd hh24:mi:ss');

这语句和原来那语句的逻辑读对比下看看

执行结果如下：
SQL> select /*+ use_hash(m,s) ordered full(s) */ count(0) from  mobile_mark m,notealy_send s where (substr(s.smssend_mobile,1,7)=m.mark_mobile or substr(s.smssend_mobile,3,7)=m.mark_mobile) and s.smssend_error!='0' and s.smssend_time>=to_date('2007-2-25 00:00:00','syyyy-mm-dd hh24:mi:ss');

  COUNT(0)
----------
     22099


Execution Plan
----------------------------------------------------------
Plan hash value: 3140874208

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     1 |    29 |       | 27622   (1)| 00:05:32 |
|   1 |  SORT AGGREGATE      |              |     1 |    29 |       |            |          |
|   2 |   CONCATENATION      |              |       |       |       |            |          |
|*  3 |    HASH JOIN         |              |   854K|    23M|  1856K| 13811   (1)| 00:02:46 |
|   4 |     TABLE ACCESS FULL| MOBILE_MARK  | 94746 |   740K|       |   434   (1)| 00:00:06 |
|*  5 |     TABLE ACCESS FULL| NOTEALY_SEND |   854K|    17M|       | 11942   (1)| 00:02:24 |
|*  6 |    HASH JOIN         |              | 42744 |  1210K|  1856K| 13811   (1)| 00:02:46 |
|   7 |     TABLE ACCESS FULL| MOBILE_MARK  | 94746 |   740K|       |   434   (1)| 00:00:06 |
|*  8 |     TABLE ACCESS FULL| NOTEALY_SEND |   854K|    17M|       | 11942   (1)| 00:02:24 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",3,7))
   5 - filter("S"."SMSSEND_TIME">=TO_DATE('2007-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "S"."SMSSEND_ERROR"<>'0')
   6 - access("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",1,7))
       filter(LNNVL("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",3,7)))
   8 - filter("S"."SMSSEND_TIME">=TO_DATE('2007-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "S"."SMSSEND_ERROR"<>'0')

棉花糖ONE

对比２个语句的逻辑读啊

set timing on
set　autot　trace　
run　sql

[ 本帖最后由 棉花糖ONE 于 2008-3-25 17:32 编辑 ]

vepeta

原帖由 棉花糖ONE 于 2008-3-25 17:29 发表
对比２个语句的逻辑读啊

set timing on
set　autot　trace　
run　sql

如下
SQL> set autotrace on
SQL> select /*+ use_hash(m,s) ordered full(s) */ count(0) from  mobile_mark m,notealy_send s where (substr(s.smssend_mobile,1,7)=m.mark_mobile or substr(s.smssend_mobile,3,7)=m.mark_mobile) and s.smssend_error!='0' and s.smssend_time>=to_date('2007-2-25 00:00:00','syyyy-mm-dd hh24:mi:ss');

  COUNT(0)
----------
     22099
Elapsed: 00:00:01.14

Execution Plan
----------------------------------------------------------
Plan hash value: 3140874208

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     1 |    29 |       | 27622   (1)| 00:05:32 |
|   1 |  SORT AGGREGATE      |              |     1 |    29 |       |            |          |
|   2 |   CONCATENATION      |              |       |       |       |            |          |
|*  3 |    HASH JOIN         |              |   854K|    23M|  1856K| 13811   (1)| 00:02:46 |
|   4 |     TABLE ACCESS FULL| MOBILE_MARK  | 94746 |   740K|       |   434   (1)| 00:00:06 |
|*  5 |     TABLE ACCESS FULL| NOTEALY_SEND |   854K|    17M|       | 11942   (1)| 00:02:24 |
|*  6 |    HASH JOIN         |              | 42744 |  1210K|  1856K| 13811   (1)| 00:02:46 |
|   7 |     TABLE ACCESS FULL| MOBILE_MARK  | 94746 |   740K|       |   434   (1)| 00:00:06 |
|*  8 |     TABLE ACCESS FULL| NOTEALY_SEND |   854K|    17M|       | 11942   (1)| 00:02:24 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",3,7))
   5 - filter("S"."SMSSEND_TIME">=TO_DATE('2007-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "S"."SMSSEND_ERROR"<>'0')
   6 - access("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",1,7))
       filter(LNNVL("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",3,7)))
   8 - filter("S"."SMSSEND_TIME">=TO_DATE('2007-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "S"."SMSSEND_ERROR"<>'0')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     110432  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+ use_hash(m,s) ordered full(s) */ count(0) from notealy_send s,mobile_mark m where (substr(s.smssend_mobile,1,7)=m.mark_mobile or substr(s.smssend_mobile,3,7)=m.mark_mobile) and s.smssend_error!='0' and s.smssend_time>=to_date('2007-2-25 00:00:00','syyyy-mm-dd hh24:mi:ss');

  COUNT(0)
----------
     22099
Elapsed: 00:00:01.11

Execution Plan
----------------------------------------------------------
Plan hash value: 3771521855

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     1 |    29 |       | 27626   (1)| 00:05:32 |
|   1 |  SORT AGGREGATE      |              |     1 |    29 |       |            |          |
|   2 |   CONCATENATION      |              |       |       |       |            |          |
|*  3 |    HASH JOIN         |              |   854K|    23M|    26M| 13813   (1)| 00:02:46 |
|*  4 |     TABLE ACCESS FULL| NOTEALY_SEND |   854K|    17M|       | 11942   (1)| 00:02:24 |
|   5 |     TABLE ACCESS FULL| MOBILE_MARK  | 94746 |   740K|       |   434   (1)| 00:00:06 |
|*  6 |    HASH JOIN         |              | 42744 |  1210K|    26M| 13813   (1)| 00:02:46 |
|*  7 |     TABLE ACCESS FULL| NOTEALY_SEND |   854K|    17M|       | 11942   (1)| 00:02:24 |
|   8 |     TABLE ACCESS FULL| MOBILE_MARK  | 94746 |   740K|       |   434   (1)| 00:00:06 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",3,7))
   4 - filter("S"."SMSSEND_TIME">=TO_DATE('2007-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "S"."SMSSEND_ERROR"<>'0')
   6 - access("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",1,7))
       filter(LNNVL("M"."MARK_MOBILE"=SUBSTR("S"."SMSSEND_MOBILE",3,7)))
   7 - filter("S"."SMSSEND_TIME">=TO_DATE('2007-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "S"."SMSSEND_ERROR"<>'0')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     110432  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

[ 本帖最后由 vepeta 于 2008-3-25 17:41 编辑 ]

paulyibinyi

看错 不好意思

[ 本帖最后由 paulyibinyi 于 2008-3-25 18:41 编辑 ]

棉花糖ONE

最开始没加hint最nest　loop的时候的情况是怎样的

weiran11

Could you tell us what indexes you have on both tables ? What are the index definition ?

alantany

what is your db optimizer_mode?
try:
select /*+ all_rows */ count(0) from ......