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

最新推荐文章于 2024-08-25 10:48:32 发布

原创最新推荐文章于 2024-08-25 10:48:32 发布 · 534 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#count(1)查询慢 #order by查询慢

Oracle 11G 同时被 2 个专栏收录

31 篇文章

订阅专栏

故障处理

12 篇文章

订阅专栏

本文探讨了SQL查询中加入COUNT(1)及ORDER BY操作导致查询效率显著下降的问题，通过对比不同执行计划，调整索引使用策略，最终实现查询速度的显著提升。

客户说今天有一个表在查询时，加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>

问题解决。