Oracle 12.1.0.2 发布引入了一系列的新特性,当然还有关注度非常高的ORACLE In-Memory Option,在内存中提供一种列级的存储, 在OLTP中提供更好的性能,当然在Exadata中ORACLE已在存储级提供了列级的性能优化hybrid columnar compressed (HCC) format,访问数据从内存中要快于物理磁盘, 只前的做法是把对象放到buffer cache中,In-Memory 在12.1.0.2中引入的参数就有110多个,可见是一种非常复杂的技术,关于IN-Memory不多介绍可以参考官方文档,在这里我只是简单比较一下之前版本中标row格式in buffer cache和新特性in-memory option中的column格式的执行性能.
1. Enable IN-Memory Option
2. Create a segment in PDB(not in sys schema,not in system tablespace.)
3. let the table inmemory and cache in buffer
4. To compare speeds of queries executed on data in memory using both the standard row-major format and the new columnar format.
Note:
ON Exadata machine ,the FTS event maybe show 'TABLE ACCESS STORAGE FULL'
另外有一点需要注意,虽然执行计划TABLE ACCESS前后是不一样,但是Plan hash value是相同的,Randolf Geist几年前写过How PLAN_HASH_VALUES Are Calculated解释
Conclusion:
通过上面简单的测试发现 in-memory 的响应时间有了7X的提升,Cost也要从有标准bh的13479减少到530, 但是CPU使用In-Memory是标准bh的4X, consistent gets从标准bh的49134降到in-memory的10.TABLE ACCESS访问路径有标准bh的TABLE ACCESS FULL 变成了TABLE ACCESS INMEMORY FULL. 当然在不同的物理环境可能数值有所不同,我的测试是在DELL R610的机器上,但是总体in-memory 的性能优化还是很明显的, 也有可能等不到12c r2 就会有生产环境升级到12c, 据说SAP和国内电信行来在测试,
1. Enable IN-Memory Option
[oracle@db231 ~]$ ora
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 11 14:53:58 2014
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
# in CDB
sys@ORA12102>alter system set inmemory_size=500m scope=spfile;
sys@ORA12102>shutdown immediate
sys@ORA12102>startup
sys@ORA12102>select * from v$sgainfo;
NAME BYTES RES CON_ID
-------------------------------- -------------------- --- --------------------
Fixed SGA Size 2927000 No 0
Redo Buffers 13848576 No 0
Buffer Cache Size 1191182336 Yes 0
In-Memory Area Size 536870912 No 0
...复制
2. Create a segment in PDB(not in sys schema,not in system tablespace.)
sys@ORA12102>alter pluggable database pdb12102 open;
Pluggable database altered.
# switch to PDB
sys@ORA12102>alter session set container=pdb12102;
Session altered.
sys@ORA12102>create table anbob.tt as select * from dba_objects;
Table created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
91733 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
183466 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
366932 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
733864 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
1467728 rows created.
sys@ORA12102>exec dbms_stats.gather_table_stats('ANBOB','TT');
PL/SQL procedure successfully completed.
sys@ORA12102>select bytes/1024/1024 mb,blocks from dba_segments where segment_name='TT' and owner='ANBOB';
MB BLOCKS
-------------------- --------------------
390 49920
sys@ORA12102>show parameter inme
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 500M
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE复制
3. let the table inmemory and cache in buffer
sys@ORA12102>alter table anbob.tt inmemory;
Table altered.
sys@ORA12102>alter table anbob.tt cache;
Table altered.
sys@ORA12102>SELECT TABLE_NAME,CACHE,INMEMORY,INMEMORY_PRIORITY ,INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION,INMEMORY_DUPLICATE
FROM DBA_TABLES WHERE OWNER='ANBOB';
TABLE_NAME CACHE INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- ----- -------- -------- --------------- ----------------- -------------
TT Y ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
sys@ORA12102>select * from v$im_segments;
no rows selected
Elapsed: 00:00:00.01
# execute a query load obj in memory
sys@ORA12102>select count(*) ,object_type from anbob.tt group by object_type;
..
sys@ORA12102>select segment_name,TABLESPACE_NAME,INMEMORY_SIZE, BYTES,trunc(408944640/52887552,2) comp_ratio from v$im_segments;
SEGMENT_NA TABLESPACE INMEMORY_SIZE BYTES COMP_RATIO
---------- ---------- -------------------- -------------------- --------------------
TT USERS 52887552 408944640 7.73
sys@ORA12102>select * from (
2 select
3 count(*) buffers
4 , o.owner bhobjects_owner
5 , o.object_name bhobjects_object_name
6 , o.subobject_name bhobjects_subobject_name
7 , o.object_type bhobjects_object_type
8 from
9 v$bh bh
10 , dba_objects o
11 where
12 bh.objd = o.data_object_id
13 group by
14 o.owner, o.object_name, o.subobject_name, o.object_type
15 order by
16 buffers desc
17 )
18 where object_name='ANBOB'
19 /
BUFFERS OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
-------------------- ------------------------------ ------------------------------ ------------------------------ --------------------
24528 ANBOB TT TABLE复制
4. To compare speeds of queries executed on data in memory using both the standard row-major format and the new columnar format.
sys@ORA12102>set autot trace exp stat
#下面都是第二次运行的结果
sys@ORA12102>select count(object_id) from anbob.tt where object_id between 1 and 10000;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3133740314
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 530 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS INMEMORY FULL| TT | 316K| 1545K| 530 (4)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - inmemory("OBJECT_ID"<=10000 AND "OBJECT_ID">=1)
filter("OBJECT_ID"<=10000 AND "OBJECT_ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 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
sys@ORA12102>ALTER SESSION set inmemory_query = disable;
Session altered.
sys@ORA12102>select count(object_id) from anbob.tt where object_id between 1 and 10000;
Elapsed: 00:00:00.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3133740314
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 13479 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TT | 316K| 1545K| 13479 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=10000 AND "OBJECT_ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49134 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复制
Note:
ON Exadata machine ,the FTS event maybe show 'TABLE ACCESS STORAGE FULL'
另外有一点需要注意,虽然执行计划TABLE ACCESS前后是不一样,但是Plan hash value是相同的,Randolf Geist几年前写过How PLAN_HASH_VALUES Are Calculated解释
So in summary the following conclusions can be made:
- The same PLAN_HASH_VALUE is merely an indicator that the same operations on objects of the same name are performed in the same order.
- It tells nothing about the similarity of the expected runtime performance of the execution plan, due to various reasons as demonstrated. The most significant information that is not covered by the PLAN_HASH_VALUE are the filter and access predicates, but there are other attributes, too, that are not part of the hash value calculation.
Conclusion:
通过上面简单的测试发现 in-memory 的响应时间有了7X的提升,Cost也要从有标准bh的13479减少到530, 但是CPU使用In-Memory是标准bh的4X, consistent gets从标准bh的49134降到in-memory的10.TABLE ACCESS访问路径有标准bh的TABLE ACCESS FULL 变成了TABLE ACCESS INMEMORY FULL. 当然在不同的物理环境可能数值有所不同,我的测试是在DELL R610的机器上,但是总体in-memory 的性能优化还是很明显的, 也有可能等不到12c r2 就会有生产环境升级到12c, 据说SAP和国内电信行来在测试,
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布,如何快速升级和安装?
Lucifer三思而后行
684次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车?手把手教你如何排错!
Lucifer三思而后行
634次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果,免费脚本速来下载!
陈举超
543次阅读
2025-04-20 10:07:02
【活动】分享你的压箱底干货文档,三篇解锁进阶奖励!
墨天轮编辑部
491次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
488次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
476次阅读
2025-04-22 00:13:51
一页概览:Oracle GoldenGate
甲骨文云技术
468次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
418次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
372次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
353次阅读
2025-05-05 19:28:36
热门文章
移除DataGuard Standby配置导致Primary启动失败
2023-08-17 21344浏览
使用dblink产生的”SELECT /*+ FULL(P) +*/ * FROM XXXXX P ” 解析
2023-06-20 20905浏览
Troubleshooting 'ORA-28041: Authentication protocol internal error' change password 12c R2 DB
2020-04-08 13697浏览
浅谈ORACLE免费数据库Oracle Database XE (Express Edition) 版
2018-10-31 7674浏览
High wait event ‘row cache mutex’ in 12cR2、19c
2020-08-14 5606浏览