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;