查看: 4285|回复: 9
[性能调整]
咨询两种不同sql分页语句,在resultset遍历时的性能表现问题
电梯直达
发表于 2013-9-6 13:40
|
只看该作者
|倒序浏览
|阅读模式
本帖最后由 janwen2013 于 2013-9-6 13:42 编辑
情况是这样的,我现在表里有2千万的数据,由于需求,需要进行分页查询,分页量在5000左右,现在有两条分页语句:1.
SELECT
*
FROM
(
SELECT
row_.*,
rownum rownum_
FROM
(select * from TB_CHSS_GRJKDA order by GRDAID) row_ )
WHERE
rownum_ <= 1000000
AND rownum_ >= 900000
2.
SELECT
t_table.*
FROM
TB_CHSS_GRJKDA t_table
WHERE
ROWID IN
(
SELECT
r_id
FROM
(
SELECT
r_id,
rownum rn
FROM
(
SELECT
ROWID r_id
FROM
TB_CHSS_GRJKDA t_table order by t_table.GRDAID ASC)
WHERE
rownum <= 10001000)
WHERE
rn >= 10001000)
第一种分页,随着数据量的增大,查询时间线性增长,在分页数达到50万左右后,做查询就变的很慢,需要几分钟时间,才能完成select查询,但是在resultset的递归中,网络IO可以达到10m/s
第二种分页,查询时间不会随着数据量的增大而增长,千万级的分页也就10s左右,但是,在做resultset递归时,网络IO却只有500k/s,另外一个让人迷惑的情况是,如果表中数据量只有100万左右时,网络IO也可以达到10m/s
请问各位大牛,这是什么情况,要如何解决?
发表于 2013-9-6 14:50
|
只看该作者
第一个分页写的有问题
SELECT
*
FROM
(
SELECT
t.*,
rownum rn
FROM
(select * from TEST2 order by object_id) t
WHERE
rownum < 100
) a
where a.rn > 90
应该是这样~
楼主|
发表于 2013-9-6 15:02
|
只看该作者
iori809 发表于 2013-9-6 14:50
第一个分页写的有问题
你好,我照着你的分页试了下,在查询千万时,耗时依然很夸张,貌似用rownum分页千万数据,他会fetch前面900万数据,再去截断后面的数据
发表于 2013-9-6 15:27
|
只看该作者
janwen2013 发表于 2013-9-6 15:02
你好,我照着你的分页试了下,在查询千万时,耗时依然很夸张,貌似用rownum分页千万数据,他会fetch前面9 ...
分页的数据靠后的话~index扫描的肯定会多一些~
但你第二个写法应该也不会强那么多吧~
那个应该是走 IFFS~ 效率会有那么好吗~
无非就是不用回表~然后都是基于rowid获取数据~
如果方便的话其实贴个执行计划或者10046 你现在的信息提供的太少啊~
楼主|
发表于 2013-9-6 16:23
iori809 发表于 2013-9-6 15:27
分页的数据靠后的话~index扫描的肯定会多一些~
但你第二个写法应该也不会强那么多吧~
那个应该是走 IFF ...
第一个是rownum的,第二个是走rowid的
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1031347029
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 1 | VIEW | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS FULL| TB_CHSS_GRJKDA | 10M| 17G| 735K (1)| 02:2
7:04 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."RN">10000000)
2 - filter(ROWNUM<10001000)
已选择16行。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1057037051
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 1 | NESTED LOOPS | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 2 | VIEW | VW_NSO_1 | 10M| 114M| 37500 (
1)| 00:07:31 |
| 3 | HASH UNIQUE | | 1 | 238M|
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | VIEW | | 10M| 238M| 37500 (
1)| 00:07:31 |
|* 5 | COUNT STOPKEY | | | |
| |
| 6 | INDEX FAST FULL SCAN | SYS_C005435 | 13M| 159M| 37500 (
1)| 00:07:31 |
| 7 | TABLE ACCESS BY USER ROWID| TB_CHSS_GRJKDA | 1 | 1927 | 1 (
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RN">=10000000)
5 - filter(ROWNUM<=10001000)
已选择20行。
楼主|
发表于 2013-9-6 16:25
iori809 发表于 2013-9-6 15:27
分页的数据靠后的话~index扫描的肯定会多一些~
但你第二个写法应该也不会强那么多吧~
那个应该是走 IFF ...
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1031347029
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 1 | VIEW | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS FULL| TB_CHSS_GRJKDA | 10M| 17G| 735K (1)| 02:2
7:04 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."RN">10000000)
2 - filter(ROWNUM<10001000)
已选择16行。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1057037051
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 1 | NESTED LOOPS | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 2 | VIEW | VW_NSO_1 | 10M| 114M| 37500 (
1)| 00:07:31 |
| 3 | HASH UNIQUE | | 1 | 238M|
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | VIEW | | 10M| 238M| 37500 (
1)| 00:07:31 |
|* 5 | COUNT STOPKEY | | | |
| |
| 6 | INDEX FAST FULL SCAN | SYS_C005435 | 13M| 159M| 37500 (
1)| 00:07:31 |
| 7 | TABLE ACCESS BY USER ROWID| TB_CHSS_GRJKDA | 1 | 1927 | 1 (
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RN">=10000000)
5 - filter(ROWNUM<=10001000)
已选择20行。
楼主|
发表于 2013-9-6 16:34
iori809 发表于 2013-9-6 15:27
分页的数据靠后的话~index扫描的肯定会多一些~
但你第二个写法应该也不会强那么多吧~
那个应该是走 IFF ...
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1031347029
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 1 | VIEW | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS FULL| TB_CHSS_GRJKDA | 10M| 17G| 735K (1)| 02:2
7:04 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."RN">10000000)
2 - filter(ROWNUM<10001000)
已选择16行。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1057037051
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 1 | NESTED LOOPS | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 2 | VIEW | VW_NSO_1 | 10M| 114M| 37500 (
1)| 00:07:31 |
| 3 | HASH UNIQUE | | 1 | 238M|
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | VIEW | | 10M| 238M| 37500 (
1)| 00:07:31 |
|* 5 | COUNT STOPKEY | | | |
| |
| 6 | INDEX FAST FULL SCAN | SYS_C005435 | 13M| 159M| 37500 (
1)| 00:07:31 |
| 7 | TABLE ACCESS BY USER ROWID| TB_CHSS_GRJKDA | 1 | 1927 | 1 (
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RN">=10000000)
5 - filter(ROWNUM<=10001000)
已选择20行。
楼主|
发表于 2013-9-6 16:36
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1031347029
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 1 | VIEW | | 10M| 13G| 735K (1)| 02:2
7:04 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS FULL| TB_CHSS_GRJKDA | 10M| 17G| 735K (1)| 02:2
7:04 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."RN">10000000)
2 - filter(ROWNUM<10001000)
已选择16行。
楼主|
发表于 2013-9-6 16:37
iori809 发表于 2013-9-6 15:27
分页的数据靠后的话~index扫描的肯定会多一些~
但你第二个写法应该也不会强那么多吧~
那个应该是走 IFF ...
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1057037051
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 1 | NESTED LOOPS | | 1 | 1939 | 83525 (
2)| 00:16:43 |
| 2 | VIEW | VW_NSO_1 | 10M| 114M| 37500 (
1)| 00:07:31 |
| 3 | HASH UNIQUE | | 1 | 238M|
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | VIEW | | 10M| 238M| 37500 (
1)| 00:07:31 |
|* 5 | COUNT STOPKEY | | | |
| |
| 6 | INDEX FAST FULL SCAN | SYS_C005435 | 13M| 159M| 37500 (
1)| 00:07:31 |
| 7 | TABLE ACCESS BY USER ROWID| TB_CHSS_GRJKDA | 1 | 1927 | 1 (
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RN">=10000000)
5 - filter(ROWNUM<=10001000)
已选择20行。