记一次统计信息导致的查询慢

客户说今天有一个表在查询时，加count(1)比查询单列慢很多很多，而且加order by之后查询也很慢

现象：一个简单的查询SQL，不加order by时查询挺快的，加上order by 需要几分钟。

SQL> set autot trace
SQL> select a.Point_Acct_Balance_Id
  2  from   npoint.Point_Acct_Balance a
  3        , npoint.Point_Type         b
  4  where  a.Point_Type_Id = b.Point_Type_Id
  5         And a.Point_Acct_Id = 12003390089
  6         And a.Status_Cd In (1000, 1100)
  7         And Sysdate <= Exp_Date;

21 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3812269974

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |  1173 | 45747 |     1   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| POINT_ACCT_BALANCE      |  1173 | 45747 |     1   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                 | IDX_POINT_ACCT_BALA_PAI | 21865 |       |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter((TO_NUMBER("A"."STATUS_CD")=1000 OR TO_NUMBER("A"."STATUS_CD")=1100) AND "EXP_DATE">=SYSDATE@!)
   2 - access("A"."POINT_ACCT_ID"=12003390089)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1016  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

SQL>  
SQL>  select a.Point_Acct_Balance_Id
  2   from   npoint.Point_Acct_Balance a
  3         , npoint.Point_Type         b
  4   where  a.Point_Type_Id = b.Point_Type_Id
  5          And a.Point_Acct_Id = 12003390089
  6          And a.Status_Cd In (1000, 1100)
  7          And Sysdate <= Exp_Date
  8   order  By a.Point_Acct_Year Desc
  9   ;

21 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1300607793

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |  1173 | 50439 |     0   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| POINT_ACCT_BALANCE      |  1173 | 50439 |     0   (0)| 00:00:01 |     1 |     1 |
|   2 |   INDEX FULL SCAN DESCENDING       | IDX_POINT_ACCT_BALA_PAY |   117M|       |     0   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("A"."POINT_ACCT_ID"=12003390089 AND (TO_NUMBER("A"."STATUS_CD")=1000 OR
              TO_NUMBER("A"."STATUS_CD")=1100) AND "EXP_DATE">=SYSDATE@!)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
    4407440  consistent gets
     105328  physical reads
    6539932  redo size
       1016  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

SQL执行时走的索引不一样，导致执行效率不一样
仔细观察，发现Cost (%CPU)为1和0

 

查看表并行
SQL>  select table_name, degree from dba_tables where table_name='POINT_ACCT_BALANCE';

TABLE_NAME                     DEGREE
------------------------------ --------------------
POINT_ACCT_BALANCE                      1

SQL> 

 

查看索引的统计信息
SQL> select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from dba_indexes t where table_name ='POINT_ACCT_BALANCE';

TABLE_NAME                     INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- ----------- -------------------
POINT_ACCT_BALANCE             PK_POINT_ACCT_BALANCE                   0          0           0 2018-11-08 15:53:38
POINT_ACCT_BALANCE             IDX_POINT_ACCT_BALA_PAY                 0          0           0 2018-11-08 15:53:39
POINT_ACCT_BALANCE             IDX_POINT_ACCT_BALA_PAI                 0          0           0 2018-11-08 15:53:39

 

 

查看表的统计信息
SQL> select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from dba_tables t where table_name ='POINT_ACCT_BALANCE';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
POINT_ACCT_BALANCE

SQL> 

收集表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'NPOINT',tabname => 'POINT_ACCT_BALANCE',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade => true,no_invalidate => FALSE,degree => 4);

PL/SQL procedure successfully completed.

SQL>

再次查询
SQL> set autot trace
SQL> select a.Point_Acct_Balance_Id           
  2  from   npoint.Point_Acct_Balance a       
  3        , npoint.Point_Type         b      
  4  where  a.Point_Type_Id = b.Point_Type_Id 
  5         And a.Point_Acct_Id = 12003390089 
  6         And a.Status_Cd In (1000, 1100)   
  7         And Sysdate <= Exp_Date;          

21 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3812269974

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |     1 |    29 |    17   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| POINT_ACCT_BALANCE      |     1 |    29 |    17   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                 | IDX_POINT_ACCT_BALA_PAI |     8 |       |     4   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter((TO_NUMBER("A"."STATUS_CD")=1000 OR TO_NUMBER("A"."STATUS_CD")=1100) AND "EXP_DATE">=SYSDATE@!)
   2 - access("A"."POINT_ACCT_ID"=12003390089)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1016  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

SQL> select a.Point_Acct_Balance_Id          
  2  from   npoint.Point_Acct_Balance a      
  3        , npoint.Point_Type         b     
  4  where  a.Point_Type_Id = b.Point_Type_Id
  5         And a.Point_Acct_Id = 12003390089
  6         And a.Status_Cd In (1000, 1100)  
  7         And Sysdate <= Exp_Date          
  8  order  By a.Point_Acct_Year Desc        
  9  ;                                       

21 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1110100561

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     1 |    34 |    18   (6)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                      |                         |     1 |    34 |    18   (6)| 00:00:01 |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| POINT_ACCT_BALANCE      |     1 |    34 |    17   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                 | IDX_POINT_ACCT_BALA_PAI |     8 |       |     4   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter((TO_NUMBER("A"."STATUS_CD")=1000 OR TO_NUMBER("A"."STATUS_CD")=1100) AND "EXP_DATE">=SYSDATE@!)
   3 - access("A"."POINT_ACCT_ID"=12003390089)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
       1016  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         21  rows processed

SQL> 

问题解决。