彪悍的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)

1 rows 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)

1 rows 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倍以上。