oracle sql 分页 很慢,一次分页SQL优化

开发说业务有个统计跑不出结果，我让他把sql给我，他说不清楚，那我就直接才消耗时间最长了sql了，查找sql如下SELECT inst_id,

sql_id,

sql_fulltext,

round(exec_time / 1000000, 0) / 60 exec_time

FROM (SELECT inst_id,

sql_id,

sql_fulltext,

exec_time,

rank() over(ORDER BY exec_time DESC) exec_rank

FROM (SELECT sql_id,

sql_fulltext,

inst_id,

cpu_time,

elapsed_time,

executions,

round(elapsed_time / executions, 0) exec_time

FROM gv$sql

WHERE executions > 1))

WHERE exec_rank <= 10;

因为我对业务比较了解，因此直接就找出到了这个sql

从sql_fulltext得到sql

select

id,

agent_account,

operatorAccount,

aisleName,

interface_id,

customer_number,

serialnumber,

customer_serialnumber,

type,

money,

create_date,

status,

remark,

processed,

calls,

upNum,

area,

reversal_type,

brokerage_type,

payment_type,

refund_type,

activities

from (

select

temp.id,

temp.agent_account,

temp.operatorAccount,

temp.aisleName,

temp.interface_id,

temp.customer_number,

temp.serialnumber,

temp.customer_serialnumber,

temp.type,

temp.money,

temp.create_date,

temp.status,

temp.remark,

temp.processed,

temp.calls,

temp.upNum,

temp.area,

temp.reversal_type,

temp.brokerage_type,

temp.payment_type,

temp.refund_type,

temp.activities,

rownum row_id

from (

select

a.id,

a.agent_account,

b.login_name as operatorAccount,

c.aislename as aisleName,

a.interface_id,

a.customer_number,

a.serialnumber,

a.customer_serialnumber,

a.type,

a.money,

a.create_date,

a.status,

a.remark,

a.processed,

a.calls,

a.upNum,

a.area,

a.reversal_type,

a.brokerage_type,

a.payment_type,

a.refund_type,

a.activities

from

tb_recharge a left join tb_operator b on a.operator_id = b.id

left join tb_aisle c on a.aisle_id = c.id

WHERE a.agent_id in (select id from tb_agent where sales_id =  :1)

and a.status = :2

order by a.id desc )temp

where  rownum <= :3 ) where  row_id > :4

查询绑定变量的值

select inst_id, value_string from gv$sql_bind_capture where sql_id = '0rhttycv0upqc' and inst_id = 1

得到的值为 103585 1 10 0

sql已经成功找出来了，是一个分页语句，这种sql我们首先想到的是order by 列一定要有index，而且执行计划应该走nestloop join 才比较快。

下面来看看执行计划

Plan hash value: 3945838093

----------------------------------------------------------------------------------------------------------------------------

| Id  | Operation  | Name   | Rows  | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |   | 4 |  4052 | 92723   (1)| 00:18:33 |   |   |

|*  1 |  VIEW  |   | 4 |  4052 | 92723   (1)| 00:18:33 |   |   |

|*  2 |   COUNT STOPKEY   |   |   |   ||   |   |   |

|   3 |    VIEW   |   | 4 |  4000 | 92723   (1)| 00:18:33 |   |   |

|*  4 |     SORT ORDER BY STOPKEY  |   | 4 |   900 | 92723   (1)| 00:18:33 |   |   |

|   5 |      NESTED LOOPS OUTER   |   | 4 |   900 | 92722   (1)| 00:18:33 |   |   |

|*  6 |       HASH JOIN OUTER  |   | 4 |   832 | 92718   (1)| 00:18:33 |   |   |

|*  7 |        HASH JOIN  |   | 4 |   720 | 92715   (1)| 00:18:33 |   |   |

|*  8 | TABLE ACCESS FULL  | TB_AGENT   | 3 |30 |   206   (0)| 00:00:03 |   |   |

|   9 | PARTITION RANGE ALL  |   |  1194 |   198K| 92509   (1)| 00:18:31 | 1 |19 |

|  10 |  TABLE ACCESS BY LOCAL INDEX ROWID| TB_RECHARGE    |  1194 |   198K| 92509   (1)| 00:18:31 | 1 |19 |

|* 11 |   INDEX SKIP SCAN  | TB_RECHARGE_I3 |  1194 |   | 91316   (1)| 00:18:16 | 1 |19 |

|  12 |        TABLE ACCESS FULL  | TB_AISLE   |16 |   448 | 3   (0)| 00:00:01 |   |   |

|  13 |       TABLE ACCESS BY INDEX ROWID  | TB_OPERATOR    | 1 |17 | 1   (0)| 00:00:01 |   |   |

|* 14 |        INDEX UNIQUE SCAN  | TB_OPERATOR_PK | 1 |   | 0   (0)| 00:00:01 |   |   |

----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ROW_ID">0)

2 - filter(ROWNUM<=10)

4 - filter(ROWNUM<=10)

6 - access("A"."AISLE_ID"="C"."ID"(+))

7 - access("A"."AGENT_ID"="ID")

8 - filter("SALES_ID"=103585)

11 - access("A"."STATUS"=1)

filter("A"."STATUS"=1)

14 - access("A"."OPERATOR_ID"="B"."ID"(+))

分析：执行计划id 为 7 8  9三行，8和9 hashjoin后返回4行数据，这个显然不准，至少也应该有1000多行(我查了下实际数据有70万行)，而且id为9 使用index skip scan，

然后hash join 的结果集作为驱动表与TB_OPERATOR nestloops join 这尼玛能出结果才怪，解决思路如下

先看tb_recharge的索引，status在TB_RECHARGE_I3组合索引的第五个字段SELECT index_name, column_name, table_name, COLUMN_POSITION

FROM user_ind_columns

WHERE table_name = 'TB_RECHARGE'

INDEX_NAME       COLUMN_NAME TABLE_NAMECOLUMN_POSITION

------------------------------ ------------------------- ------------------------------ ---------------

TB_RECHARGEBK_PK       ID TB_RECHARGE      1

TB_RECHARGE_UNIQUE2       CUSTOMER_SERIALNUMBER TB_RECHARGE      2

TB_RECHARGE_UNIQUE2       AGENT_ACCOUNT TB_RECHARGE      1

TB_RECHARGE_I3       CUSTOMER_NUMBER TB_RECHARGE      1

TB_RECHARGE_I3       CREATE_DATE TB_RECHARGE      2

TB_RECHARGE_I3       AGENT_ACCOUNT TB_RECHARGE      3

TB_RECHARGE_I3       MONEY TB_RECHARGE      4

TB_RECHARGE_I3       STATUS TB_RECHARGE      5

TB_RECHARGE_I5       CREATE_DATE TB_RECHARGE      1

TB_RECHARGE_UNIQUE1       SERIALNUMBER TB_RECHARGE      1

sql中tb_recharge用到了status 和id字段因此我创建了这个索引

create index idx_tb_recharge_status_id(status,id)

创建索引后执行计划如下SQL>  select  *from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3852339816

---------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     4 |  4052 |  2037(1)| 00:00:25 |       |       |

|*  1 |  VIEW  |      |     4 |  4052 |  2037(1)| 00:00:25 |       |       |

|*  2 |   COUNT STOPKEY   |      |       |       |    |      |       |       |

|   3 |    VIEW   |      |     4 |  4000 |  2037(1)| 00:00:25 |       |       |

|*  4 |     SORT ORDER BY STOPKEY  |      |     4 |   900 |  2037(1)| 00:00:25 |       |       |

|   5 |      NESTED LOOPS OUTER   |      |     4 |   900 |  2036(1)| 00:00:25 |       |       |

|*  6 |       HASH JOIN OUTER  |      |     4 |   832 |  2032(1)| 00:00:25 |       |       |

|*  7 |        HASH JOIN  |      |     4 |   720 |  2029(1)| 00:00:25 |       |       |

|*  8 | TABLE ACCESS FULL  | TB_AGENT      |     3 |    30 |   206(0)| 00:00:03 |       |       |

|   9 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_RECHARGE       |  1194 |   198K|  1822(0)| 00:00:22 | ROWID | ROWID |

|* 10 |  INDEX RANGE SCAN  | IDX_TB_RECHARGE_STATUS_ID |  1194 |       |    13(0)| 00:00:01 |       |       |

|  11 |        TABLE ACCESS FULL  | TB_AISLE      |    16 |   448 |     3(0)| 00:00:01 |       |       |

|  12 |       TABLE ACCESS BY INDEX ROWID  | TB_OPERATOR       |     1 |    17 |     1(0)| 00:00:01 |       |       |

|* 13 |        INDEX UNIQUE SCAN  | TB_OPERATOR_PK      |     1 |       |     0(0)| 00:00:01 |       |       |

---------------------------------------------------------------------------------------------------------------------------------------

上面这个sql跑10s出结果了，其实这里还是没有走分页的特性，tb_agent.id 是pk，因此可以直接把in改成join，最后又加了hint然其走nl，最后结果如下SELECT id,

status,

agent_account,

operatorAccount,

aisleName,

interface_id,

customer_number,

serialnumber,

customer_serialnumber,

TYPE,

money,

create_date,

remark,

processed,

calls,

upNum,

area,

reversal_type,

brokerage_type,

payment_type,

refund_type,

activities

FROM (SELECT temp.id,

temp.agent_account,

temp.operatorAccount,

temp.aisleName,

temp.interface_id,

temp.customer_number,

temp.serialnumber,

temp.customer_serialnumber,

temp.type,

temp.money,

temp.create_date,

temp.status,

temp.remark,

temp.processed,

temp.calls,

temp.upNum,

temp.area,

temp.reversal_type,

temp.brokerage_type,

temp.payment_type,

temp.refund_type,

temp.activities,

rownum row_id

FROM (SELECT

/*+ use_nl(a,ta) use_nl(a,b) use_nl(a,c) leading(a,b,c) */

a.id,

a.agent_account,

b.login_name            AS operatorAccount,

c.aislename             AS aisleName,

a.interface_id,

a.customer_number,

a.serialnumber,

a.customer_serialnumber,

a.type,

a.money,

a.create_date,

a.status,

a.remark,

a.processed,

a.calls,

a.upNum,

a.area,

a.reversal_type,

a.brokerage_type,

a.payment_type,

a.refund_type,

a.activities

FROM (SELECT id FROM tb_agent WHERE sales_id = 103585) ta

INNER JOIN tb_recharge a

ON ta.id = a.agent_id

LEFT JOIN tb_operator b

ON a.operator_id = b.id

LEFT JOIN tb_aisle c

ON a.aisle_id = c.id

WHERE a.status = 1

ORDER BY a.id DESC) temp

WHERE rownum <= 10)

WHERE row_id > 0

SQL>  select  *from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3946970243

---------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     4 |  4052 |  5405(1)| 00:01:05 |       |       |

|*  1 |  VIEW  |      |     4 |  4052 |  5405(1)| 00:01:05 |       |       |

|*  2 |   COUNT STOPKEY   |      |       |       |    |      |       |       |

|   3 |    VIEW   |      |     4 |  4000 |  5405(1)| 00:01:05 |       |       |

|   4 |     NESTED LOOPS  |      |     4 |   900 |  5405(1)| 00:01:05 |       |       |

|   5 |      NESTED LOOPS  |      |  1194 |   900 |  5405(1)| 00:01:05 |       |       |

|   6 |       NESTED LOOPS OUTER  |      |  1194 |   250K|  4211(1)| 00:00:51 |       |       |

|   7 |        NESTED LOOPS OUTER  |      |  1194 |   218K|  3017(1)| 00:00:37 |       |       |

|   8 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_RECHARGE       |  1194 |   198K|  1822(0)| 00:00:22 | ROWID | ROWID |

|*  9 |  INDEX RANGE SCAN DESCENDING  | IDX_TB_RECHARGE_STATUS_ID |  1194 |       |    13(0)| 00:00:01 |       |       |

|  10 | TABLE ACCESS BY INDEX ROWID  | TB_OPERATOR       |     1 |    17 |     1(0)| 00:00:01 |       |       |

|* 11 |  INDEX UNIQUE SCAN  | TB_OPERATOR_PK      |     1 |       |     0(0)| 00:00:01 |       |       |

|  12 |        TABLE ACCESS BY INDEX ROWID  | TB_AISLE      |     1 |    28 |     1(0)| 00:00:01 |       |       |

|* 13 | INDEX UNIQUE SCAN  | TB_AISLEV_PK      |     1 |       |     0(0)| 00:00:01 |       |       |

|* 14 |       INDEX UNIQUE SCAN   | TB_AGENT_PK       |     1 |       |     0(0)| 00:00:01 |       |       |

|* 15 |      TABLE ACCESS BY INDEX ROWID  | TB_AGENT      |     1 |    10 |     1(0)| 00:00:01 |       |       |

---------------------------------------------------------------------------------------------------------------------------------------

最后ms级别出结果了