oracle10g客户端能访问12c,彪悍的Oracle 12C,飞一般的性能提升—–Oracle 10G 11g 12c性能指标测试实践...

说明

Oracle 12.2.0.1于2017年3月提供下载，star童鞋有个疑问，Oracle 12c到底有多牛x,和我们大部分客户正在运行的10G 、11G对比性能指标有多大提升？？？

测试结果

部分测试过程

10.2.0.5

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Mar 17 22:03:11 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> exec dbms_stats.gather_table_stats('HT','HT1');

PL/SQL procedure successfully completed.

SQL> set autot on

set timing on

set line 999

select count(1) from ht.ht1;

COUNT(1)

----------

10000000

Elapsed: 00:00:19.22

Execution Plan

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

Plan hash value: 46654064

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

| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |

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

|   0 | SELECT STATEMENT   |  |1 | 41671   (1)| 00:08:21 |

|   1 |  SORT AGGREGATE    |  |1 |       |  |

|   2 |   TABLE ACCESS FULL| HT1  |  10000K| 41671   (1)| 00:08:21 |

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

Statistics

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

1  recursive calls

0  db block gets

188900  consistent gets

88827  physical reads

0  redo size

518  bytes sent via SQL*Net to client

492  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1rows processed

Elapsed: 00:00:19.22

11.2.0.4

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot on

set timing on

set line 999

select count(1) from ht.ht1;

COUNT(1)

----------

10000000

Elapsed: 00:00:11.81

Execution Plan

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

Plan hash value: 46654064

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

| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |

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

|   0 | SELECT STATEMENT   |  |1 | 51440   (1)| 00:10:18 |

|   1 |  SORT AGGREGATE    |  |1 |       |  |

|   2 |   TABLE ACCESS FULL| HT1  |  10000K| 51440   (1)| 00:10:18 |

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

Statistics

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

1  recursive calls

0  db block gets

188883  consistent gets

188877  physical reads

0  redo size

529  bytes sent via SQL*Net to client

520  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1rows processed

Elapsed: 00:00:11.81

12.2.0.1

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(1) from ht.ht1;

COUNT(1)

----------

10000000

Elapsed: 00:00:00.91

Execution Plan

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

Plan hash value: 46654064

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

| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |

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

|   0 | SELECT STATEMENT   |  |1 | 51397   (1)| 00:00:03 |

|   1 |  SORT AGGREGATE    |  |1 |       |  |

|   2 |   TABLE ACCESS FULL| HT1  |  10000K| 51397   (1)| 00:00:03 |

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

Statistics

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

898  recursive calls

0  db block gets

190867  consistent gets

189044  physical reads

0  redo size

545  bytes sent via SQL*Net to client

552  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

332  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> /

COUNT(1)

----------

10000000

Elapsed: 00:00:00.91

说明：相同数据量全表扫描，

10G耗时：Elapsed: 00:00:19.22；

11G耗时：Elapsed: 00:00:11.81；

12C耗时：Elapsed: 00:00:00.91，12C与10G比较全表扫描性能提升21.12倍，12C与11G比较全表扫描性能提升12.97倍；通过以上测试及数据比较，12C性能显著提升。

测试总结

与10G相比12C平均性能提升20倍，与11G相比全表查询性能提升10倍以上，部分数据查询性能提升2倍以上。