分区性能测试-CSDN博客

1、非分区关键字主键

sql>alter system flush buffer_cache;

sql>alter system flush shared_pool;

SQL> select * from hzwsj.ADAPTER_ORIGINALDATAT_p where eventid='f85d323221a3418a8385b5be49e72958'and;

执行计划

----------------------------------------------------------

Plan hash value: 3655760732

---------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1221 | 3 (0)| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| ADAPTER_ORIGINALDATAT_P | 1 | 1221 | 3 (0)| 00:00:01 | ROWID | ROWID |

|* 2 | INDEX UNIQUE SCAN | PK_ADAPTER_ORIGINALDATAT_P | 1 | | 2 (0)| 00:00:01 | | |

---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EVENTID"='f85d323221a3418a8385b5be49e72958')

统计信息

----------------------------------------------------------

8396 recursive calls

0 db block gets

1580 consistent gets

0 physical reads

0 redo size

10388 bytes sent via SQL*Net to client

4238 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

113 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select * from hzwsj.ADAPTER_ORIGINALDATAT where eventid='f85d323221a3418a8385b5be49e72958';

执行计划

----------------------------------------------------------

Plan hash value: 105606556

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1221 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| ADAPTER_ORIGINALDATAT | 1 | 1221 | 3 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_ADAPTER_ORIGINALDATAT | 1 | | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EVENTID"='f85d323221a3418a8385b5be49e72958')

统计信息

----------------------------------------------------------

1060 recursive calls

0 db block gets

180 consistent gets

0 physical reads

0 redo size

10388 bytes sent via SQL*Net to client

4238 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

10 sorts (memory)

0 sorts (disk)

1 rows processed

有分区扫描块为1580，无分区扫描块为180

2、关键字

非分区关键字无索引

SQL> select * from hzwsj.ADAPTER_ORIGINALDATAT where RECORDCLASSIFYING='Ipt_LeaveRecord';

已选择1663行。

执行计划

----------------------------------------------------------

Plan hash value: 2580261741

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 52632 | 61M| 105K (1)| 00:21:05 |

|* 1 | TABLE ACCESS FULL| ADAPTER_ORIGINALDATAT | 52632 | 61M| 105K (1)| 00:21:05 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("RECORDCLASSIFYING"='Ipt_LeaveRecord')

统计信息

----------------------------------------------------------

1062 recursive calls

0 db block gets

387602 consistent gets

387375 physical reads

0 redo size

15345610 bytes sent via SQL*Net to client

6961325 bytes received via SQL*Net from client

4991 SQL*Net roundtrips to/from client

10 sorts (memory)

0 sorts (disk)

1663 rows processed

分区关键字无索引

SQL> select * from hzwsj.ADAPTER_ORIGINALDATAT_p where RECORDCLASSIFYING='Ipt_LeaveRecord';

已选择1663行。

执行计划

----------------------------------------------------------

Plan hash value: 977578274

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1663 | 2034K| 453 (1)| 00:00:06 | | |

| 1 | PARTITION LIST SINGLE| | 1663 | 2034K| 453 (1)| 00:00:06 | KEY | KEY |

| 2 | TABLE ACCESS FULL | ADAPTER_ORIGINALDATAT_P | 1663 | 2034K| 453 (1)| 00:00:06 | 1 | 1 |

-----------------------------------------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

8294 recursive calls

0 db block gets

3231 consistent gets

1684 physical reads

116 redo size

15345610 bytes sent via SQL*Net to client

6961325 bytes received via SQL*Net from client

4991 SQL*Net roundtrips to/from client

109 sorts (memory)

0 sorts (disk)

1663 rows processed

非分区关键字有索引

SQL> select * from hzwsj.ADAPTER_ORIGINALDATAT where RECORDCLASSIFYING='Ipt_LeaveRecord';

已选择1663行。

执行计划

----------------------------------------------------------

Plan hash value: 1258942847

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 52632 | 60M| 20568 (1)| 00:04:07 |

| 1 | TABLE ACCESS BY INDEX ROWID| ADAPTER_ORIGINALDATAT | 52632 | 60M| 20568 (1)| 00:04:07 |

|* 2 | INDEX RANGE SCAN | IDX_RECORDCLASSIFYING | 52632 | | 178 (1)| 00:00:03 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("RECORDCLASSIFYING"='Ipt_LeaveRecord')

统计信息

----------------------------------------------------------

1099 recursive calls

0 db block gets

3521 consistent gets

1654 physical reads

0 redo size

15345610 bytes sent via SQL*Net to client

6961325 bytes received via SQL*Net from client

4991 SQL*Net roundtrips to/from client

10 sorts (memory)

0 sorts (disk)

1663 rows processed

分区关键字 1684

非分区非关键字无索引 387375

非分区关键字有索引 1654

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26764973/viewspace-1461709/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26764973/viewspace-1461709/