暂无图片
暂无图片
13
暂无图片
暂无图片
暂无图片

雏鹰展翅|Oracle 单表分页查询优化

原创 JiekeXu 2021-11-11
3262

近日中午一开发过来说生产有条 SQL 执行缓慢,让看一下执行计划。测试环境说也有同样的问题 SQL,那么则开始在测试环境搞一搞吧,排查过程大概记录如下,对于 优化也就是一知半解,故此只能抛砖引玉,如有错误还望指正。

开发发过来的分页 SQL 如下(敏感信息均已过滤转换),由于篇幅问题,这里不放入 PLSQL 格式化展开了。

select "v0" "SEQUENCE_NO", "v1" "PK_DELIVER_INFO", "v2" "TRANS_NO", "v3" "AGENT_ID", "v4" "TRANS_TIME", "v5" "RST_CODE", "v6" "RST_MESS", "v7" "COUNT", "v8" "QUARTER", "v9" "QUERY_BEG_DATE", "v10" "QUERY_END_DATE", "v11" "START_INDEX", "v12" "ITEMS_COUNT", "v13" "PULL_WAY", "v14" "CREATE_TIME", "v15" "UPDATE_TIME", "v16" "DELETE_TIME" from (select "x"."v0", "x"."v1", "x"."v2", "x"."v3", "x"."v4", "x"."v5", "x"."v6", "x"."v7", "x"."v8", "x"."v9", "x"."v10", "x"."v11", "x"."v12", "x"."v13", "x"."v14", "x"."v15", "x"."v16", rownum "rn" from (select "T_ORDER_INFO"."SEQUENCE_NO" "v0", "T_ORDER_INFO"."PK_DELIVER_INFO" "v1", "T_ORDER_INFO"."TRANS_NO" "v2", "T_ORDER_INFO"."AGENT_ID" "v3", "T_ORDER_INFO"."TRANS_TIME" "v4", "T_ORDER_INFO"."RST_CODE" "v5", "T_ORDER_INFO"."RST_MESS" "v6", "T_ORDER_INFO"."COUNT" "v7", "T_ORDER_INFO"."QUARTER" "v8", "T_ORDER_INFO"."QUERY_BEG_DATE" "v9", "T_ORDER_INFO"."QUERY_END_DATE" "v10", "T_ORDER_INFO"."START_INDEX" "v11", "T_ORDER_INFO"."ITEMS_COUNT" "v12", "T_ORDER_INFO"."PULL_WAY" "v13", "T_ORDER_INFO"."CREATE_TIME" "v14", "T_ORDER_INFO"."UPDATE_TIME" "v15", "T_ORDER_INFO"."DELETE_TIME" "v16" from "T_ORDER_INFO" where (1 = 1 and "T_ORDER_INFO"."AGENT_ID" = 'C002374') order by "v10" desc, "v2" desc) "x" where rownum <= (0 + 1)) where "rn" > 0 order by "rn";

下面来一起看看吧

测试数据库版本及补丁信息
Linux 6.10 11204 RAC SQL*Plus: Release 11.2.0.4.0
Database Patch Set Update : 11.2.0.4.190416

1、查看统计信息收集时间,防止统计信息过旧

SQL> set line 345 
SQL> select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name='T_ORDER_INFO' and owner='PROD';

TABLE_NAME                     OWNER                            NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
T_ORDER_INFO                   PROD                            3625092 2021-10-28 17:36:25

SQL> select count(*) from PROD.T_ORDER_INFO;

     COUNT(*)
    ----------
     3625092

2、查看创建索引情况

如下只有三个索引

SQL> select owner,index_name,table_owner,table_name,tablespace_name,last_analyzed,status from dba_indexes where table_name='T_ORDER_INFO' and table_owner='PROD'; 

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     TABLESPACE_NAME                LAST_ANALYZED       STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------- --------
PROD                           T_ORDER_INFO_UK1                PROD                          T_ORDER_INFO                   PROD_DATA                      2021-10-28 17:36:25 VALID
PROD                           T_ORDER_INFO_INDEX1             PROD                          T_ORDER_INFO                   PROD_DATA                      2021-10-28 17:36:25 VALID
PROD                           T_ORDER_INFO_PK                 PROD                          T_ORDER_INFO                   PROD_DATA                      2021-10-28 17:36:25 VALID

3、查看索引所在的列

序列为主键索引,PK_DELIVER_INFO 列为唯一索引,普通索引刚好在 AGENT_ID 我们最开始的 where 子句中。

SET LINE 234 
COL INDEX_OWNER FOR A30 
COL TABLE_OWNER FOR A15 
COL TABLE_NAME FOR A25 
COL INDEX_NAME FOR A28 
COL COLUMN_NAME FOR A20 
SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='&tablename'  ORDER BY INDEX_NAME; 

SQL> SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='T_ORDER_INFO'  and table_owner='PROD';

INDEX_OWNER                    TABLE_OWNER     TABLE_NAME      INDEX_NAME         COLUMN_NAME
------------------------------ --------------- --------------- ------------------ --------------------
PROD                           PROD            T_ORDER_DELIVER T_ORDER_DELIVER_IN SEQUENCE_NO
                                                       _INFO           FO_PK
   								         
PROD                           PROD            T_ORDER_DELIVER T_ORDER_DELIVER_IN AGENT_ID
                                                       _INFO           FO_INDEX1
   								         
PROD                           PROD            T_ORDER_DELIVER T_ORDER_DELIVER_IN PK_DELIVER_INFO
                                                       _INFO           FO_UK1  

4、查看表是否分区(如下未分区)

SQL> SELECT TABLE_NAME,PARTITIONED  FROM DBA_TABLES WHERE TABLE_NAME='T_ORDER_INFO' AND OWNER='PROD';

TABLE_NAME                     PAR
------------------------------ ---
T_ORDER_INFO                   NO

5、查看表的大小

SQL> select sum(bytes)/1024/1024 MB from dba_segments where OWNER='PROD' and SEGMENT_NAME ='T_ORDER_INFO';

        MB
----------
       768

360多万,768M也算不上大表,对于合理的分页查询应该也没有什么问题,下面就需要看看执行计划了。

SQL> select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name='T_ORDER_INFO' and owner='PROD';

TABLE_NAME      OWNER                            NUM_ROWS LAST_ANALYZED
--------------- ------------------------------ ---------- -------------------
T_ORDER_INFO    PROD                             3625092  2021-11-05 16:32:11

6、首先需要拿到原 SQL 的 SQL_ID.

可以通过 awr、ash或者 v$SQL 等视图获取,这里通过最简单的 v$SQL 视图获取。

set long 9999 line 999 pages 999
select sql_id,SQL_FULLTEXT from v$sql where sql_text like '%T_ORDER_INFO%';

5b2zcwhm267q8

SQL> @?/rdbms/admin/sqltrpt.sql

ORACLE 10g 以后提供了一个脚本 sqltrpt.sql 用来查询最耗费资源的 SQL 语句,也可以根据输入的SQL_ID,生成对应执行计划和调优建议,是一个不错的调优优化脚本。其实是sqltrpt是SQL Tune Report的缩写。这个脚本位于$ORACLE_HOME/rdbms/admin/sqltrpt.sql

7、查看执行计划

知道 sql_id 后便可以根据多种办法查看执行计划。关于执行计划多种查看方法,可查看之前的文章链接

@?/rdbms/admin/awrsqrpt.sql
Select * from table(dbms_xplan.display_awr('5b2zcwhm267q8'));

通过 AUTOTRACE 和 PLSQL 使用 F5 查看的执行计划一样。

SQL> set AUTOT TRACE
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

以上普通用户无法使用 AUTOTRACE ,需要执行脚本 plustrce.sql 创建 PLUSTRACE 角色授予普通用户即可。

cd $ORACLE_HOME/sqlplus/admin
ll plustrce.sql

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> 
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL> create role plustrace;

Role created.

SQL> 
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 
SQL> set echo off
SQL> grant PLUSTRACE to PROD;

Grant succeeded.
SQL> set autot on
SQL> conn PROD/LKKBtd7$
Connected.
SQL> set autot on
SQL> 


SQL> set AUTOT TRACE

Execution Plan
----------------------------------------------------------
Plan hash value: 3374223308

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |    10 |  8160 |       |  4031   (1)| 00:00:49 |
|   1 |  SORT ORDER BY                   |                             |    10 |  8160 |       |  4031   (1)| 00:00:49 |
|*  2 |   VIEW                           |                             |    10 |  8160 |       |  4030   (1)| 00:00:49 |
|*  3 |    COUNT STOPKEY                 |                             |       |       |       |            |          |
|   4 |     VIEW                         |                             | 46319 |    35M|       |  4030   (1)| 00:00:49 |
|*  5 |      SORT ORDER BY STOPKEY       |                             | 46319 |  8594K|    10M|  4030   (1)| 00:00:49 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO                | 46319 |  8594K|       |  2111   (1)| 00:00:26 |
|*  7 |        INDEX RANGE SCAN          | T_ORDER_INFO_INDEX1         | 46319 |       |       |   469   (0)| 00:00:06 |
------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("rn">0)
   3 - filter(ROWNUM<=10)
   5 - filter(ROWNUM<=10)
   7 - access("T_ORDER_INFO"."AGENT_ID"='C002282')
   
SQL> set line 456 pages 456 
SQL> Select * from table(dbms_xplan.display_cursor('5b2zcwhm267q8')); 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |       |       |       |  1943 (100)|          |
|   1 |  SORT ORDER BY                   |                             |    10 |  8160 |       |  1943   (1)| 00:00:24 |
|*  2 |   VIEW                           |                             |    10 |  8160 |       |  1942   (1)| 00:00:24 |
|*  3 |    COUNT STOPKEY                 |                             |       |       |       |            |          |
|   4 |     VIEW                         |                             | 23300 |    17M|       |  1942   (1)| 00:00:24 |
|*  5 |      SORT ORDER BY STOPKEY       |                             | 23300 |  4323K|  5336K|  1942   (1)| 00:00:24 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO                | 23300 |  4323K|       |   976   (1)| 00:00:12 |
|*  7 |        INDEX RANGE SCAN          | T_ORDER_INFO_INDEX1         | 23300 |       |       |   220   (0)| 00:00:03 |
------------------------------------------------------------------------------------------------------------------------

执行计划中有排序,而且成本 cost 也很高,autotrace 出来的达 4031。

图片.png

8、优化此 SQL

AGENT_ID 建有索引,该 SQL 也是走了此索引,但是效果不佳,那么我们尝试创建一个联合索引来看看。

create index PROD.T_ORD_INFO_IDQUERY_TRANSNO on PROD.T_ORDER_INFO(AGENT_ID,QUERY_END_DATE desc,TRANS_NO desc) tablespace PROD_INDEX online; 

注意如果建立如下索引,执行计划则会出现 INDEX RANGE SCAN DESCENDING,物理读变为 3,其他基本一样,但是使用 11 节的分页 SQL 时执行计划中排序则不可避免,没有充分利用索引有序的特性,故需删除按照上面语法重新创建较好一丢丢。

create index PROD.T_ORD_INFO_IDQUERY_TRANSNO on PROD.T_ORDER_INFO(AGENT_ID,QUERY_END_DATE,TRANS_NO) tablespace PROD_INDEX online; 

drop index PROD.T_ORD_INFO_IDQUERY_TRANSNO;

9、收集表统计信息

exec dbms_stats.gather_table_stats(ownname => 'PROD', tabname => 'T_ORDER_INFO');
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name like 'T_ORDER_INFO' and owner='PROD';

10、查看执行计划

conn xxxx/xxxx
set autot on

Execution Plan
----------------------------------------------------------
Plan hash value: 3879506888

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                            |    10 |  8160 |     9  (12)| 00:00:01 |
|   1 |  SORT ORDER BY                  |                            |    10 |  8160 |     9  (12)| 00:00:01 |
|*  2 |   VIEW                          |                            |    10 |  8160 |     8   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY                |                            |       |       |            |          |
|   4 |     VIEW                        |                            |    11 |  8833 |     8   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO               | 37872 |  7064K|     8   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | T_ORD_INFO_IDQUERY_TRANSNO |    11 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

原有执行计划中有 SORT ORDER BY 的排序操作也已经消除了,Cost 成本值降低至 9,与原来的 4031 相比,提高了 440 多倍。那么该 SQL 还有优化的空间吗?

11、根据《SQL优化核心思想》8.3 一节分页优化思想改写的 SQL 如下:

select * from (select *
from (select a.*,rownum rn from (分页SQL) a) where rownum<=10)
where rn >=1;

不知道是不是作者笔误,多写了一层 select 还是怎么的,根据此分页框架改写如下 SQL 还是达不到最优,请继续往下看。

select * from   (select * 
 from (select a.*,rownum rn from 
                   (select "T_ORDER_INFO"."SEQUENCE_NO"     "v0",
                       "T_ORDER_INFO"."PK_DELIVER_INFO" "v1",
                       "T_ORDER_INFO"."TRANS_NO"        "v2",
                       "T_ORDER_INFO"."AGENT_ID"        "v3",
                       "T_ORDER_INFO"."TRANS_TIME"      "v4",
                       "T_ORDER_INFO"."RST_CODE"        "v5",
                       "T_ORDER_INFO"."RST_MESS"        "v6",
                       "T_ORDER_INFO"."COUNT"           "v7",
                       "T_ORDER_INFO"."QUARTER"         "v8",
                       "T_ORDER_INFO"."QUERY_BEG_DATE"  "v9",
                       "T_ORDER_INFO"."QUERY_END_DATE"  "v10",
                       "T_ORDER_INFO"."START_INDEX"     "v11",
                       "T_ORDER_INFO"."ITEMS_COUNT"     "v12",
                       "T_ORDER_INFO"."PULL_WAY"        "v13",
                       "T_ORDER_INFO"."CREATE_TIME"     "v14",
                       "T_ORDER_INFO"."UPDATE_TIME"     "v15",
                       "T_ORDER_INFO"."DELETE_TIME"     "v16"
                    from "T_ORDER_INFO"
                    where ("T_ORDER_INFO"."AGENT_ID" = 'C002282')
                    order by "v10" desc, "v2" desc
					) 
	  a) where rownum<=10
			    )
 where  rn >=1;

执行计划如下:

Plan hash value: 2456897122

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |    10 |  8160 |       |  3162   (1)| 00:00:38 |
|*  1 |  VIEW                             |                             |    10 |  8160 |       |  3162   (1)| 00:00:38 |
|*  2 |   COUNT STOPKEY                   |                             |       |       |       |            |          |
|   3 |    VIEW                           |                             | 37872 |    29M|       |  3162   (1)| 00:00:38 |
|   4 |     COUNT                         |                             |       |       |       |            |          |
|   5 |      VIEW                         |                             | 37872 |    29M|       |  3162   (1)| 00:00:38 |
|   6 |       SORT ORDER BY               |                             | 37872 |  7064K|  8672K|  3162   (1)| 00:00:38 |
|   7 |        TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO                | 37872 |  7064K|       |  1579   (1)| 00:00:19 |
|*  8 |         INDEX RANGE SCAN          | T_ORDER_INFO_INDEX1         | 37872 |       |       |   348   (0)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1173  consistent gets
          0  physical reads
          0  redo size
       2729  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

使用原有索引 T_ORDER_INFO_INDEX1,并没有用到新的联合索引,原有执行计划中有 SORT ORDER BY 的排序操作也没有消除了,Cost 成本值有原来的 4031 降低至 3162,效果不是很明显.

使用 Statistics_level 看一眼真实执行计划也是一样使用旧索引,还有排序操作。
grant select any dictionary to PROD;
conn PROD/PROD1245
alter session set Statistics_level=all;
执行上述分页 SQL
select * from table(dbms_xplan.display_cursor(null,null,‘allstats last’));

图片.png

那么,我们强制使用 hint 走联合索引在看看,结果 cost 值达 13303,还是没有达到最优。

select  /*+ index(T_ORDER_INFO T_ORD_INFO_IDQUERY_TRANSNO) */ "T_ORDER_INFO"

Execution Plan
----------------------------------------------------------
Plan hash value: 4173602263

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |    10 |  8160 | 13303   (1)| 00:02:40 |
|*  1 |  VIEW                            |                            |    10 |  8160 | 13303   (1)| 00:02:40 |
|*  2 |   COUNT STOPKEY                  |                            |       |       |            |          |
|   3 |    VIEW                          |                            | 41284 |    32M| 13303   (1)| 00:02:40 |
|   4 |     COUNT                        |                            |       |       |            |          |
|   5 |      VIEW                        |                            | 41284 |    31M| 13303   (1)| 00:02:40 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO               | 41284 |  7700K| 13303   (1)| 00:02:40 |
|*  7 |        INDEX RANGE SCAN          | T_ORD_INFO_IDQUERY_TRANSNO | 41284 |       |   469   (0)| 00:00:06 |
---------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   7 - access("T_ORDER_INFO"."AGENT_ID"='C002282')


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

12、最高效的单表分页查询写法

看过老虎刘老师的最高效分页查询语句架构只有三层,我们来套用看看。

select column_lists from
(select rownum as rn,A.* from
(select column_lists from table_name where col_1=:b0 order by col_2) A
where rownum<=:b2
) where rn>:b1;

改写完 SQL 格式化如下:

select "v0",
       "v1",
       "v2",
       "v3",
       "v4",
       "v5",
       "v6",
       "v7",
       "v8",
       "v9",
       "v10",
       "v11",
       "v12",
       "v13",
       "v14",
       "v15",
       "v16"
  from (select rownum as rn, A.*
          from (select "T_ORDER_INFO"."SEQUENCE_NO"     "v0",
                       "T_ORDER_INFO"."PK_DELIVER_INFO" "v1",
                       "T_ORDER_INFO"."TRANS_NO"        "v2",
                       "T_ORDER_INFO"."AGENT_ID"        "v3",
                       "T_ORDER_INFO"."TRANS_TIME"      "v4",
                       "T_ORDER_INFO"."RST_CODE"        "v5",
                       "T_ORDER_INFO"."RST_MESS"        "v6",
                       "T_ORDER_INFO"."COUNT"           "v7",
                       "T_ORDER_INFO"."QUARTER"         "v8",
                       "T_ORDER_INFO"."QUERY_BEG_DATE"  "v9",
                       "T_ORDER_INFO"."QUERY_END_DATE"  "v10",
                       "T_ORDER_INFO"."START_INDEX"     "v11",
                       "T_ORDER_INFO"."ITEMS_COUNT"     "v12",
                       "T_ORDER_INFO"."PULL_WAY"        "v13",
                       "T_ORDER_INFO"."CREATE_TIME"     "v14",
                       "T_ORDER_INFO"."UPDATE_TIME"     "v15",
                       "T_ORDER_INFO"."DELETE_TIME"     "v16"
                  from "T_ORDER_INFO"
                 where "T_ORDER_INFO"."AGENT_ID" = 'C002282'
                 order by "v10" desc, "v2" desc) A
         where rownum <= 10)
 where rn > 0;

我们来看一看执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 2845846103

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |    10 |  8160 |     8   (0)| 00:00:01 |
|*  1 |  VIEW                          |                            |    10 |  8160 |     8   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |                            |       |       |            |          |
|   3 |    VIEW                        |                            |    11 |  8833 |     8   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO               | 37872 |  7064K|     8   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T_ORD_INFO_IDQUERY_TRANSNO |    11 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------- 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       2635  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Plan hash value: 2845846103

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |      1 |        |     10 |00:00:00.01 |      10 |
|*  1 |  VIEW                          |                            |      1 |     10 |     10 |00:00:00.01 |      10 |
|*  2 |   COUNT STOPKEY                |                            |      1 |        |     10 |00:00:00.01 |      10 |
|   3 |    VIEW                        |                            |      1 |     10 |     10 |00:00:00.01 |      10 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO               |      1 |  41284 |     10 |00:00:00.01 |      10 |
|*  5 |      INDEX RANGE SCAN          | T_ORD_INFO_IDQUERY_TRANSNO |      1 |     10 |     10 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------------

执行计划中没有排序,没有物理读,cost 只有 8,查询结果秒出,这才是最优的结果。 最后来一起看看落落大神总结的分页优化思路:

单表分页语句优化思路:如果分页语句中有排序(order by),要利用索引已经排序特性,将order by的列按照排序的先后顺序包含在索引中,同时要注意排序是升序还是降序。如果分页语句中有过滤条件,我们要注意过滤条件是否有等值过滤条件,如果有等值过滤条件,要将等值过滤条件优先组合在一起,然后将排序列放在等值过滤条件后面,最后将非等值过滤列放排序列后面。如果分页语句中没有等值过滤条件,我们应该先将排序列放在索引前面,将非等值过滤列放后面,最后利用rownum的COUNT STOPKEY特性来优化分页SQL。如果分页中没有过滤条件,可以将排序列和常量组合(object_name,0)创建索引。如果分页中没有排序,可以直接利用rownum的COUNT STOPKEY特性来优化分页SQL。

如果我们想一眼看出分页语句执行计划是正确还是错误的,先看分页语句有没有ORDER BY,再看执行计划有没有SORT ORDER BY,如果执行计划中有SORTORDER BY,执行计划一般都是错误的分页语句中也不能有distinct、group by、max、min、avg、union、union all等关键字。因为当分页语句中有这些关键字,我们需要等表关联完或者数据都跑完之后再来分页,这样性能很差。

多表关联分页优化思路:多表关联分页语句,如果有排序,只能对其中一个表进行排序,让参与排序的表作为嵌套循环的驱动表,并且要控制驱动表返回的数据顺序与排序的顺序一致,其余表的连接列要创建好索引。
如果有外连接,我们只能选择主表的列作为排序列,语句中不能有distinct、group by、max、min、avg、union、union all,执行计划中不能出现SORT ORDER BY。

——————————————————————–—–————

公众号:JiekeXu DBA之路
墨天轮:https://www.modb.pro/u/4347
CSDN :https://blog.csdn.net/JiekeXu
腾讯云:https://cloud.tencent.com/developer/user/5645107

————————————————————————----———
图片.png

最后修改时间:2021-11-15 19:06:58
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
3人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论