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;