首页 > 代码库 > 一次分页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级别出结果了
本文出自 “专注于Oracle性能调优” 博客，谢绝转载！
一次分页SQL优化
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。