客户说今天有一个表在查询时,加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>
问题解决。