ORACLE 12c 内存特性研究
1 背景知识
参考
http://blog.csdn.net/zdy0_2004/article/details/50583926
2 实验
2.1 启用内存特性
|
数据库版本 SQL> select * from v$version where rownum<3 2 /
BANNER -------------------------------------------------------------------------------- CON_ID ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0- 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
启用in-memory特性
[orartksit@momdb1 ~]$ ./orcl_hdp.sh
SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 22 11:05:15 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name from v$database 2 /
NAME --------- ORCL_HDP
SQL> show parameter memo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 percent memory_max_target big integer 5008M memory_target big integer 5008M optimizer_inmemory_aware boolean TRUE
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_memory_address integer 0 SQL> alter system set inmemory_size=800m scope=spfile;
System altered.
SQL> show parameter inm
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 percent optimizer_inmemory_aware boolean TRUE SQL> alter system set inmemory_max_populate_servers=2;
System altered.
重启生效 SQL> startup force; ORACLE instance started.
Total System Global Area 5251268608 bytes Fixed Size 5294664 bytes Variable Size 3489662392 bytes Database Buffers 855638016 bytes Redo Buffers 61812736 bytes In-Memory Area 838860800 bytes Database mounted. Database opened. SQL> show parameter inm
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 2 inmemory_query string ENABLE inmemory_size big integer 800M inmemory_trickle_repopulate_servers_ integer 1 percent optimizer_inmemory_aware boolean TRUE
SQL> show sga
Total System Global Area 5251268608 bytes Fixed Size 5294664 bytes Variable Size 3523216824 bytes Database Buffers 822083584 bytes Redo Buffers 61812736 bytes In-Memory Area 838860800 bytes
|
oracle 12c 是在内存开辟了一个新的内存区域。
2.2 全表扫描（system表空间）
|
创建测试表 以下sql的运行，每次sql都运行两遍，避免硬解析。以第二次运行的性能统计信息为准！
SQL> create table t_1 as select * from dba_objects 2 /
Table created.
SQL> select count(*) from t_1 2 /
COUNT(*) ---------- 90888
SQL> set autot on SQL> select count(*) from t_1 2 /
COUNT(*) ---------- 90888
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 416 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_1 | 96825 | 416 (1)| 00:00:01 | -------------------------------------------------------------------
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1530 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
多次执行，防止硬解析 SQL> /
COUNT(*) ---------- 90888
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 416 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_1 | 96825 | 416 (1)| 00:00:01 | -------------------------------------------------------------------
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1530 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
在传统的buffer cache 内存中，逻辑读需要1530 consistent gets
放到内存中，启用内存特性
SQL> alter table t_1 inmemory 2 /
Table altered.
SQL> select count(*) from t_1 2 ;
COUNT(*) ---------- 90888
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 416 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_1 | 96825 | 416 (1)| 00:00:01 | -------------------------------------------------------------------
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 1603 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL>
似乎没什么效果，原因是system表空间不支持in-memory特性
|
2.3 全表扫描（用户表空间）
|
SQL> create user dba_hdp identified by "123123" default tablespace ts_hdp 2 /
User created.
SQL> grant dba to dba_hdp 2 /
Grant succeeded.
SQL> conn dba_hdp/123123 Connected. SQL> create table t_1 as select * from dba_objects 2 /
Table created.
SQL> alter table t_1 inmemory 2 /
Table altered.
SQL> set autot on
运行两次，避免硬解析带来的影响 SQL> select count(*) from t_1 2 /
COUNT(*) ---------- 90889
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS INMEMORY FULL| T_1 | 90889 | 16 (0)| 00:00:01 | ----------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 11 recursive calls 0 db block gets 1543 consistent gets 1527 physical reads 0 redo size 544 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed
again SQL> select count(*) from t_1
COUNT(*) ---------- 90889
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 27 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS INMEMORY FULL| T_1 | 90889 | 27 (0)| 00:00:01 | ----------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
逻辑读已经降到了3 consistent gets
|
2.4 关闭内存新特性
|
SQL> alter table t_1 no inmemory 2 /
Table altered.
SQL> select count(*) from t_1 2 /
COUNT(*) ---------- 90889
SQL> set autot on SQL> /
COUNT(*) ---------- 90889
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 426 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_1 | 90889 | 426 (1)| 00:00:01 | -------------------------------------------------------------------
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1532 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
逻辑读上升到 1532，性能与启用内存特性差距明显 |
2.5 内存表的存储
|
查看这个表在dba_extents试图中所分配的大小
SQL> select sum(bytes) from dba_extents where segment_name='T_1' 2 /
SUM(BYTES) ---------- 26214400 再看看在内存中的大小
SQL> select * from v$inmemory_area 2 / POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID --------------- ----------- ---------- -------------------------- ---------- 1MB POOL 670040064 4194304 DONE 0 64KB POOL 150994944 131072 DONE 0
可见表的数据在内存中是压缩存储的。
|
2.6 小事物（OLTP系统）
|
SQL> create table t_2(id number,name varchar2(20)) 2 /
Table created. SQL> ho strings /tmp/1.sql begin for i in 1..100 loop insert into t_2 values(i,i||'hdp'); end loop; end;
-------
Test:
SQL> select count(*) from t_1;
COUNT(*) ---------- 1300
Execution Plan ---------------------------------------------------------- Plan hash value: 3399330236
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_2 | 1300 | 3 (0)| 00:00:01 | -------------------------------------------------------------------
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 542 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
将T_2表映射到内存中
SQL> select count(*) from t_2
COUNT(*) ---------- 1300
Execution Plan ---------------------------------------------------------- Plan hash value: 3399330236
---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS INMEMORY FULL| T_2 | 1300 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 542 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
cpu 由%3降到%1
|
可见： 即oracle内存特性对于批量查询效果明显，但是对于比较小的事物，效果是不明显的！
concepts这样描述：
Business applications, ad-hoc analytic queries, and data warehouse workloads benefit most. Pure OLTP databases that perform short transactions using index lookups benefit less. |
2.7 索引还是内存扫描
在t_1表启用了内存特性，并且表上索引的情况下，oracle会根据sql执行计划的耗费，自动选择代价最小的 路线
|
SQL> select count(object_id) from t_1 2 /
COUNT(OBJECT_ID) ---------------- 90889
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
-------------------------------------------------------------------------------- ----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ----
| 0 | SELECT STATEMENT | | 1 | 5 | 27 (0)| 00:00: 01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS INMEMORY FULL| T_1 | 90889 | 443K| 27 (0)| 00:00: 01 |
-------------------------------------------------------------------------------- ----
Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 552 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
再次运行 SQL> /
COUNT(OBJECT_ID) ---------------- 90889
Execution Plan ---------------------------------------------------------- Plan hash value: 2285196706
-------------------------------------------------------------------------------- ----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ----
| 0 | SELECT STATEMENT | | 1 | 5 | 27 (0)| 00:00: 01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS INMEMORY FULL| T_1 | 90889 | 443K| 27 (0)| 00:00: 01 |
-------------------------------------------------------------------------------- ----
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 552 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 走索引的情况： SQL> select owner from t_1 where object_id=1000 2 /
OWNER -------------------------------------------------------------------------------- SYS
Execution Plan ---------------------------------------------------------- Plan hash value: 3884583340
-------------------------------------------------------------------------------- --------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP U)| Time |
-------------------------------------------------------------------------------- --------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 ( 0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_1 | 1 | 11 | 2 ( 0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INX_T1 | 1 | | 1 ( 0)| 00:00:01 |
-------------------------------------------------------------------------------- --------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_ID"=1000)
Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 540 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> /
OWNER -------------------------------------------------------------------------------- SYS
Execution Plan ---------------------------------------------------------- Plan hash value: 3884583340
-------------------------------------------------------------------------------- --------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP U)| Time |
-------------------------------------------------------------------------------- --------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 ( 0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_1 | 1 | 11 | 2 ( 0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INX_T1 | 1 | | 1 ( 0)| 00:00:01 |
-------------------------------------------------------------------------------- --------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_ID"=1000)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 540 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL>
删掉索引再次运行以上的sql SQL> drop index inx_t1 2 /
Index dropped.
SQL> select owner from t_1 where object_id=1000 2 /
OWNER -------------------------------------------------------------------------------- SYS
Execution Plan ---------------------------------------------------------- Plan hash value: 1376489960
-------------------------------------------------------------------------------- ---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ---
| 0 | SELECT STATEMENT | | 1 | 11 | 28 (4)| 00:00:0 1 |
|* 1 | TABLE ACCESS INMEMORY FULL| T_1 | 1 | 11 | 28 (4)| 00:00:0 1 |
-------------------------------------------------------------------------------- ---
Predicate Information (identified by operation id): ---------------------------------------------------
1 - inmemory("OBJECT_ID"=1000) filter("OBJECT_ID"=1000)
Statistics ---------------------------------------------------------- 44 recursive calls 0 db block gets 40 consistent gets 0 physical reads 0 redo size 540 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> /
OWNER -------------------------------------------------------------------------------- SYS
Execution Plan ---------------------------------------------------------- Plan hash value: 1376489960
-------------------------------------------------------------------------------- ---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ---
| 0 | SELECT STATEMENT | | 1 | 11 | 28 (4)| 00:00:0 1 |
|* 1 | TABLE ACCESS INMEMORY FULL| T_1 | 1 | 11 | 28 (4)| 00:00:0 1 |
-------------------------------------------------------------------------------- ---
Predicate Information (identified by operation id): ---------------------------------------------------
1 - inmemory("OBJECT_ID"=1000) filter("OBJECT_ID"=1000)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 540 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL>
虽然逻辑读下来了，但是对于CPU的消耗，比走索引要大，所以在以上，ORACLE SQL引擎，才会自动选择走索引。 |
3 总结
综上，oracle12c新特性，适用于大批量，多列的sql扫描，在数据量较大时，相对于传统的buffer读，效果明显。由此可见，在报表系统中，该特性将极大的提高性能。
官方担当描述oracle12c内存特性的应用场景：
|
The IM column store enables the database to perform scans, joins, and aggregates much faster than when it uses the on-disk format exclusively. In particular, the IM column store is useful for: • Performing fast full scans of large tables • Evaluating predicate filters that use operators such as =, <, >, and IN • Querying a subset of columns in a table, for example, selecting 5 of 100 columns • Accelerating joins by converting predicates on small dimension tables into filters on a large fact table |
注意，V$INMEMORY_AREA中的POPULATE_STATUS代表了当前数据向内存中加载的进度，在Done之前的查询不能充分使用内存列式运算。 |
---end---