Exadata上的分页查询性能测试
Exadata上的分页查询性能测试，Exadata上的SMART SCAN STORAGE FULL FIRST ROWS对于没有索引情况下的分页查询可以而节约大量处理时间：
SQL> create table larget tablespace users as select rownum t1, rpad('M',99,'A') t2, rpad('M',99,'A') t3, rpad('M',99,'A') t4 from dual connect by level<=9999999;
Table created.
select llv.* from
(
select rownum rn, ll.* from
(select * from larget order by t1 ) ll
where rownum<=200) llv
where
llv.rn<10;
Elapsed: 00:00:07.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3494307830
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 1 | VIEW | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 10M| 1586M| | 483K (1)| 00:00:08 |
|* 4 | SORT ORDER BY STOPKEY | | 10M| 1586M| 1739M| 483K (1)| 00:00:08 |
| 5 | TABLE ACCESS STORAGE FULL FIRST ROWS| LARGET | 10M| 1586M| | 118K (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LLV"."RN"<10)
2 - filter(ROWNUM<=200)
4 - filter(ROWNUM<=200)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
432908 consistent gets
432901 physical reads
0 redo size
1513 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> alter session set cell_offload_plan_display=never;
Session altered.
Elapsed: 00:00:00.00
SQL> alter session set cell_offload_processing=false;
Session altered.
select llv.* from
(
select rownum rn, ll.* from
(select * from larget order by t1 ) ll
where rownum<=200) llv
where
llv.rn<10;
Elapsed: 00:00:17.57
Execution Plan
----------------------------------------------------------
Plan hash value: 3494307830
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 1 | VIEW | | 200 | 35800 | | 483K (1)| 00:00:08 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 10M| 1586M| | 483K (1)| 00:00:08 |
|* 4 | SORT ORDER BY STOPKEY| | 10M| 1586M| 1739M| 483K (1)| 00:00:08 |
| 5 | TABLE ACCESS FULL | LARGET | 10M| 1586M| | 118K (1)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LLV"."RN"<10)
2 - filter(ROWNUM<=200)
4 - filter(ROWNUM<=200)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
434970 consistent gets
434783 physical reads
0 redo size
1513 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> create index pk_ind on larget(t1) tablespace users;
Index created.
select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=20) llv
where
llv.rn>=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3843929721
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 35800 | 12 (0)| 00:00:01 |
|* 1 | VIEW | | 200 | 35800 | 12 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 10M| 1586M| 12 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 10M| 1586M| 12 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 200 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LLV"."RN"<10)
2 - filter(ROWNUM<=200)
5 - filter("T1" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1513 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=2200) llv
where
llv.rn>2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3843929721
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2200 | 384K| 104 (0)| 00:00:01 |
|* 1 | VIEW | | 2200 | 384K| 104 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 10M| 1586M| 104 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 10M| 1586M| 104 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 2200 | | 8 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LLV"."RN">2000)
2 - filter(ROWNUM<=2200)
5 - filter("T1" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
310 consistent gets
91 physical reads
4776 redo size
6389 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed
SQL> select llv.* from
2 (
3 select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=22000) llv
where
llv.rn>21800; 4 5 6 7
200 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3843929721
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22000 | 3845K| 1009 (0)| 00:00:01 |
|* 1 | VIEW | | 22000 | 3845K| 1009 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 10M| 1586M| 1009 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 10M| 1586M| 1009 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 22000 | | 54 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LLV"."RN">21800)
2 - filter(ROWNUM<=22000)
5 - filter("T1" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1035 consistent gets
0 physical reads
0 redo size
6789 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed
select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=220000) llv
where
llv.rn>219800;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10085 consistent gets
0 physical reads
0 redo size
6787 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed
alter table larget add primary key (t1);
CREATE MATERIALIZED VIEW LOG ON larget with primary key;
CREATE MATERIALIZED VIEW FIRST_PAGE REFRESH FAST with primary key ON demand AS
select llv.* from (select rownum rn, ll.* from (select /*+ index( larget pk_ind) */ * from larget where t1 is not null order by t1 ) ll
where rownum<=200) llv where llv.rn<10;