ORACLE 12c 内存特性研究
来源：互联网 发布：淘宝自行车用品店推荐 编辑：程序博客网 时间：2024/06/04 01:04
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---
- ORACLE 12c 内存特性研究
- Maclean Liu对Oracle Database 12c新特性研究汇总
- Oracle 12c新特性
- Oracle 12c 新特性
- Oracle Database 12c十二大新特性
- Oracle 12c 新特性-Pluggable Database
- Oracle 12C的十二个新特性
- Oracle DB 12c 新特性
- Oracle Database 12c十二大新特性
- oracle 12c：新特性-DDL日志
- oracle 12c：新特性-扩展数据类型
- Oracle-12c新特性pdf
- Oracle 12C Study--12c新特性-权限分析
- 【Oracle Database 12c新特性】 12c DataPump Expdp/Impdp新特性
- 解读 Oracle 12c 的 12 个新特性
- 解读 Oracle 12c 的 12 个新特性
- 解读 Oracle 12c 的 12 个新特性
- Oracle 12c新特性 - Native Top N 查询
- [FAQ13618]L版本之后如何开启AAL功能
- [Gradle中文教程系列]-跟我学Gradle-7.3:打包-为Web工程打不同环境包
- #warning的用处
- jquery如何实现倒计时效果
- 时间触发和状态机思想的嵌入式系统
- ORACLE 12c 内存特性研究
- xgboost入门以及windows下安装及使用一
- android中view随手指移动
- 回调函数的面向对象设计
- 微信公众号开发
- 买房
- 音频视频编码格式
- C语言建造自己的调试信息系统
- object-c万能解决bug思路
