当前位置:网站首页>Chapter 3 enable and adjust the size of IM column storage (im-3.1)
Chapter 3 enable and adjust the size of IM column storage (im-3.1)
2022-04-23 09:58:00 【Grainger】
IM Series articles : The third chapter Enable and adjust IM The size of the column store (IM-3.1)
By designation IM Column size to enable IM Column store . You can also adjust IM The size of the column store or disable it .
This chapter contains the following topics :
· Enable IM Overview of column storage By default ,INMEMORY_SIZE
_SIZE The initialization parameter is set to 0
, It means IM Column storage is disabled . To enable the IM Column store , Please initialize the parameters before restarting the instance INMEMORY_SIZE
Set to a non-zero value .
· assessment IM Required size of column storage Evaluate... According to your requirements IM The size of the column store , Then adjust IM The size of the column storage to meet these requirements . Applying compression can reduce memory size .
· Enable database IM Column store When filling a table or materialized view into IM Before column storage , Must be enabled for the database IM Column store .
· Dynamic increase IM The size of the column store When IM When column storage requires more memory , You can dynamically increase its size .
· Ban IM Column store You can do this by INMEMORY_SIZE
Set the initialization parameter to zero to disable IM Column store , Then reopen the database .
Enable IM Overview of column storage
By default , INMEMORY_SIZE
The initialization parameter is set to 0
, It means IM Column storage is disabled . To enable the IM Column store , Please initialize the parameters before restarting the instance INMEMORY_SIZE
Set to a non-zero value .
You can use ALTER SYSTEM
Statements are added dynamically INMEMORY_SIZE
Size settings .
By default , Tables must be used 、 Of a tablespace or materialized view CREATE
or ALTER
Of the statement INMEMORY
Clause assignment IM Fill candidates in the column store .
assessment IM Required size of column storage
Evaluate... According to your requirements IM The size of the column store , Then adjust IM The size of the column storage to meet these requirements . Applying compression can reduce memory size .
IM The amount of memory required for column storage depends on the database objects stored in it and the compression method applied to each object . by INMEMORY
When selecting the compression method for the object , Please balance the performance advantages according to the amount of memory available :
· To minimize memory size , Please select FOR CAPACITY HIGH
or FOR CAPACITY LOW
Compression method . however , These options require additional processing during query execution CPU To decompress the data .
· For best query performance , Please select FOR QUERY HIGH
or FOR QUERY LOW
Compression method . however , These options consume more memory .
adjustment IM Column storage size , Please consider the following guidelines :
1. For filling to IM Every object in the column store , Estimate the amount of memory it consumes .
Oracle Compression Advisor It is estimated that you can use MEMCOMPRESS
The compression rate achieved by clause . The consultant program uses DBMS_COMPRESSION
Interface .
2. Add individual quantities together .
notes :
After filling ,V$IM_SEGMENTS
Displays the actual size of the object on the disk and its location IM Size in column store . You can use this information to calculate the compression ratio of the filled object . however , If the object is compressed on disk , Then this query will not display the correct compression ratio .
3. Add extra space to cope with the growth of database objects , And in DML Store the updated row version after the operation .
The minimum value of dynamic resizing is 128 MB.
Enable database IM Column store
When filling a table or materialized view into IM Before column storage , Must be enabled for the database IM Column store .
precondition
This task assumes the following :
· The database is open .
· COMPATIBLE
The initialization parameter is set to 12.1.0
Or higher .
· INMEMORY_SIZE
The initialization parameter is set to 0
( The default value is ).
To enable the IM Column store :
1. stay SQL * Plus or SQL Developer in , Log in to the database with administrative privileges .
2. take INMEMORY_SIZE
The initialization parameter is set to a non-zero value .
The minimum setting is 100M.
Use ALTER SYSTEM
Statement in the server parameter file (SPFILE) When setting this initialization parameter in , Must specify SCOPE=SPFILE
.
for example , The following statement will In-Memory Area The size is set to 10 GB:
ALTER SYSTEM SET INMEMORY_SIZE = 10G SCOPE=SPFILE;
3. Close the database , Then reopen it .
You must reopen the database to initialize SGA Medium IM Column store .
4. Optional , Check that the current is IM Amount of memory allocated for column storage :
SHOW PARAMETER INMEMORY_SIZE
notes :
Enable IM After column storage , You can dynamically increase its size , Without having to reopen the database .
Example 3-1 Enable IM Column store
hypothesis INMEMORY_SIZE
The initialization parameter is set to 0. following SQL * Plus The example will INMEMORY_SIZE
Set to 10 GB, Close database instance , Then reopen the database for the changes to take effect :
SQL> SHOW PARAMETER INMEMORY_SIZE
NAME TYPE VALUE
------------------------------------ ----------- -----
inmemory_size big integer 0
SQL> ALTER SYSTEM SET INMEMORY_SIZE=10G SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 11525947392 bytes
Fixed Size 8213456 bytes
Variable Size 754977840 bytes
Database Buffers 16777216 bytes
Redo Buffers 8560640 bytes
In-Memory Area 10737418240 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER INMEMORY_SIZE
NAME TYPE VALUE
------------------------------------ ----------- -----
inmemory_size big integer 10G
Dynamic increase IM The size of the column store
When IM When column storage requires more memory , You can dynamically increase its size .
Cannot dynamically reduce IM The size of the column store . If you will INMEMORY_SIZE
Set to a value less than its current setting , Must be in ALTER SYSTEM
Specified in statement SCOPE=SPFILE
. If by designation SCOPE=SPFILE
To set this parameter , You must restart the database for the changes to take effect .
precondition
To dynamically increase IM The size of the column store , The following prerequisites must be met :
· Column storage must be enabled
· Compatibility level must be 12.2.0 Or higher
· The database instance must be in SPFILE start-up .
· IM The new size of the column store must be larger than the current size INMEMORY_SIZE
Set the size to at least 128 MB.
1. stay SQL * Plus or SQL Developer in , Log in to the database with administrative privileges .
2. Optional , Check that the current is IM Amount of memory allocated for column storage :
SHOW PARAMETER INMEMORY_SIZE
3. Use specified SCOPE=BOTH
or SCOPE=MEMORY
Of ALTER SYSTEM
Statement will INMEMORY_SIZE
The initialization parameter is set to be greater than IM The value of the current size stored in the column .
When this parameter is set dynamically , It must be set to a value higher than its current value , also SGA There must be enough memory available in the , In order to IM The size of the column store is dynamically increased to a new value .
for example , The following statement dynamically INMEMORY_SIZE
Set to 500M
:
ALTER SYSTEM SET INMEMORY_SIZE = 500M SCOPE=BOTH;
Ban IM Column store
You can do this by INMEMORY_SIZE
Set the initialization parameter to zero to disable IM Column store , Then reopen the database .
hypothesis
This task assumes that... Is enabled in an open database IM Column store .
To disable IM Column store :
1. In the server parameter file (SPFILE) Lieutenant general INMEMORY_SIZE
The initialization parameter is set to 0
.
2. Close the database .
3. Start the database instance , Then open the database .
Shandong Oracle User group (Shandong Oracle User Group), abbreviation :SDOUG, Is a full of vitality 、 Young non-profit organizations , It aims to provide an exchange platform for technology lovers in Jinan and surrounding areas .SDOUG Organize offline technology sharing activities from time to time , Promote local and surrounding IT Technological development 、 Help technology enthusiasts improve themselves . Share technology 、 Share happiness ,SDOUG On the road .
版权声明
本文为[Grainger]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204230952210886.html
边栏推荐
- [CF 1425d] danger of mad snakes
- Classic routine: DP problem of a kind of string counting
- Explanation of order and primitive root of number theory
- 利用多线程按顺序连续输出abc10次
- Golang force buckle leetcode 396 Rotation function
- 第三章 启用和调整IM列存储的大小(IM-3.1)
- 论文阅读《Integrity Monitoring Techniques for Vision Navigation Systems》——3背景
- [hdu6833] a very easy math problem
- 构建元宇宙时代敏捷制造的九种能力
- DBA常用SQL语句(1)— 概况信息
猜你喜欢
防疫登记小程序
Random neurons and random depth of dropout Technology
Nvidia最新三维重建技术Instant-ngp初探
SAP RFC_ CVI_ EI_ INBOUND_ Main BP master data creation example (Demo customer only)
2022年上海市安全员C证考试题库及答案
Yarn核心参数配置
2022年流动式起重机司机考试题库模拟考试平台操作
Number theory blocking (integer division blocking)
元宇宙时代的职业规划与执行
Sim Api User Guide(4)
随机推荐
Realizing data value through streaming data integration (4) - streaming data pipeline
代码源每日一题 div1 (701-707)
Construire neuf capacités de fabrication agile à l'ère métacosmique
Odoo server setup notes
2022年上海市安全员C证考试题库及答案
Educational Codeforces Round 81 (Rated for Div. 2)
2022茶艺师(初级)考试试题模拟考试平台操作
Integral function and Dirichlet convolution
杰理之有时候发现内存被篡改,但是没有造成异常,应该如何查找?【篇】
PHP two-dimensional array specifies that the elements are added after they are equal, otherwise new
Explanation of order and primitive root of number theory
ARM调试(1):两种在keil中实现printf重定向到串口的方法
深度选择器
中控学习型红外遥控模块支持网络和串口控制
Windows安装redis并将redis设置成服务开机自启
Es aggregation aggregation analysis
打印页面的功能实现
LeetCode-608. 树节点
DBA常用SQL语句(3)- cache、undo、索引和等待事件
ABAP implementation publishes restful services for external invocation example