Oracle database in-memory is not a bolt on technology to the Oracle database, It has been seamlessly integrated into the core of database as a new component of the shared global area (SGA). Oracle database in-memory is as a new feature in 12c (12.1.0.2) and above. When Oracle database is installed, Oracle database in-memory is installed and not enabled by default.
demo@ORA12C> show parameter inmemory
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_percent integer 1
optimizer_inmemory_aware boolean TRUE
Database in-memory uses an in-memory column store (IM Column store), which is a new component of the SGA, called In-memory Area. Data in the in-memory column store does not reside in the traditional row format used by buffer cache, but instead in a new column format. The size of the in-memory column store is controlled by INMEMORY_SIZE parameter, as you can see inmemory_size is set to zero and therefore Database in-memory is not enabled and there is no IM column store allocation. We can also confirm that in-memory area is not allocated by querying V$SGA.
demo@ORA12C> select name,value from v$sga;
NAME VALUE
------------------------------ ----------
Fixed Size 3055944
Variable Size 922750648
Database Buffers 4076863488
Redo Buffers 13717504
4 rows selected.
We can also confirm that in-memory is not enabled by querying feature tracking information.
demo@ORA12C> column name format a30
demo@ORA12C> select name,version,detected_usages,
2 total_samples,currently_used,first_usage_date
3 from dba_feature_usage_statistics
4 where name like 'In-%';
NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE FIRST_USAGE
----------------------- ---------- --------------- ------------- ----- -----------
In-Memory Aggregation 12.1.0.2.0 0 1 FALSE
In-Memory Column Store 12.1.0.2.0 0 1 FALSE
2 rows selected.
Enabling database in-memory is actually a multi-step process.
Step 1: first we must allocate the memory for the IM column store by setting up INMEMORY_SIZE parameter to a non-zero value.
Since IM column store is part of the SGA, we also need to ensure that SGA_TARGET parameter is set to large enough to accommodate the new IM column store along with the other components (buffer cache, shared pool, large pool etc).
demo@ORA12C> show parameter memory_max
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
inmemory_max_populate_servers integer 0
memory_max_target big integer 0
demo@ORA12C> show parameter memory_target
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
memory_target big integer 0
demo@ORA12C> show parameter sga
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 4784M
sga_target big integer 4784M
unified_audit_sga_queue_size integer 1048576
demo@ORA12C>
demo@ORA12C>
demo@ORA12C> alter system set sga_max_size = 6G scope=spfile;
System altered.
demo@ORA12C> alter system set sga_target = 6G scope=spfile;
System altered.
demo@ORA12C> alter system set inmemory_size=1G scope=spfile;
System altered.
demo@ORA12C>
Next, let’s bounce the database so that these parameter changes can take effect.
demo@ORA12C> conn sys/Password-1@ora12c as sysdba
Connected.
sys@ORA12C> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descripto
Warning: You are no longer connected to ORACLE.
sys@ORA12C>
sys@ORA12C> exit
C:\Users\Administrator>set ORACLE_SID=ORA12C
C:\Users\Administrator>sqlplus sys/Password-1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 19 23:09:24 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 6442450944 bytes
Fixed Size 3060040 bytes
Variable Size 1040191160 bytes
Database Buffers 4311744512 bytes
Redo Buffers 13713408 bytes
In-Memory Area 1073741824 bytes
Database mounted.
Database opened.
idle>
idle> conn demo/demo@ora12c
Connected.
demo@ORA12C>
demo@ORA12C> show parameter sga_target
NAME TYPE VALUE
--------------------------------------------- ----------- ------
sga_target big integer 6G
demo@ORA12C> show parameter inmemory_size
NAME TYPE VALUE
--------------------------------------------- ----------- ------
inmemory_size big integer 1G
demo@ORA12C>
demo@ORA12C> select name,value from v$sga;
NAME VALUE
------------------ ----------
Fixed Size 3060040
Variable Size 922750648
Database Buffers 4429185024
Redo Buffers 13713408
In-Memory Area 1073741824
5 rows selected.
demo@ORA12C>
As you can see, we now have an IM Column store, but database in-memory is still not in use, because no objects have been populated into the IM Column store. To confirm this we can look at the view v$im_segments.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
no rows selected
Only the object with INMEMORY attribute are populated into IM column store, the INMEMORY attribute can be specified on a Tablespace, Table, (sub) partition or materialized views. In this case let’s enable INMEMORY attribute on one of the user table BIG_TABLE.
demo@ORA12C> alter table big_table inmemory;
Table altered.
By default Oracle automatically decides when to populate this table into IM column store, as oracle typically populates the table after it has been accessed for the first time. So let’s run the query on the BIG_TABLE.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
no rows selected
demo@ORA12C> select count(*) from big_table;
COUNT(*)
----------
1000000
1 row selected.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
OWNER SEGMENT_NA INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_
---------- ---------- ------------- ---------- ------------------- ---------
DEMO BIG_TABLE 27459584 131137536 0 COMPLETED
1 row selected.
If we check the feature tracking information now, we will see Database in-memory is enabled and being used.
demo@ORA12C> exec sys.DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(sysdate);
BEGIN sys.DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(sysdate); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_FEATURE_USAGE_INTERNAL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
demo@ORA12C> conn sys/Password-@ora12c as sysdba
Connected.
sys@ORA12C> grant execute on DBMS_FEATURE_USAGE_INTERNAL to demo;
Grant succeeded.
sys@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> exec sys.DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(sysdate);
PL/SQL procedure successfully completed.
demo@ORA12C> column name format a30
demo@ORA12C> select name,version,detected_usages,
2 total_samples,currently_used,first_usage_date
3 from dba_feature_usage_statistics
4 where name like 'In-%';
NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE FIRST_USAGE
---------------------- ---------- --------------- ------------- ----- -----------
In-Memory Aggregation 12.1.0.2.0 0 1 FALSE
In-Memory Column Store 12.1.0.2.0 1 1 FALSE 19-DEC-2016
2 rows selected.
If you want to remove the table from the IM column store, you simply need to specify the NO INMEMORY attribute.
demo@ORA12C> alter table big_table no inmemory;
Table altered.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments;
no rows selected
You could also enable IM column store for a subset of columns in table using NO INMEMORY option on a set of excluded column list.
demo@ORA12C> set linesize 71
demo@ORA12C> desc big_table
Name Null? Type
-------------------- -------- -------------
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
ID NUMBER
demo@ORA12C>
demo@ORA12C> alter table big_table inmemory no inmemory(
2 OBJECT_NAME ,SUBOBJECT_NAME ,
3 DATA_OBJECT_ID ,OBJECT_TYPE ,CREATED ,
4 LAST_DDL_TIME, TIMESTAMP, STATUS,
5 TEMPORARY, GENERATED, SECONDARY,
6 NAMESPACE, EDITION_NAME, SHARING,
7 EDITIONABLE, ORACLE_MAINTAINED, ID );
Table altered.
demo@ORA12C> select count(*) from big_table;
COUNT(*)
----------
1000000
1 row selected.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
OWNER SEGMENT_NA INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_
---------- ---------- ------------- ---------- ------------------- ---------
DEMO BIG_TABLE 7536640 131137536 0 COMPLETED
1 row selected.
demo@ORA12C>
demo@ORA12C> show parameter inmemory
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_percent integer 1
optimizer_inmemory_aware boolean TRUE
Database in-memory uses an in-memory column store (IM Column store), which is a new component of the SGA, called In-memory Area. Data in the in-memory column store does not reside in the traditional row format used by buffer cache, but instead in a new column format. The size of the in-memory column store is controlled by INMEMORY_SIZE parameter, as you can see inmemory_size is set to zero and therefore Database in-memory is not enabled and there is no IM column store allocation. We can also confirm that in-memory area is not allocated by querying V$SGA.
demo@ORA12C> select name,value from v$sga;
NAME VALUE
------------------------------ ----------
Fixed Size 3055944
Variable Size 922750648
Database Buffers 4076863488
Redo Buffers 13717504
4 rows selected.
We can also confirm that in-memory is not enabled by querying feature tracking information.
demo@ORA12C> column name format a30
demo@ORA12C> select name,version,detected_usages,
2 total_samples,currently_used,first_usage_date
3 from dba_feature_usage_statistics
4 where name like 'In-%';
NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE FIRST_USAGE
----------------------- ---------- --------------- ------------- ----- -----------
In-Memory Aggregation 12.1.0.2.0 0 1 FALSE
In-Memory Column Store 12.1.0.2.0 0 1 FALSE
2 rows selected.
Enabling database in-memory is actually a multi-step process.
Step 1: first we must allocate the memory for the IM column store by setting up INMEMORY_SIZE parameter to a non-zero value.
Since IM column store is part of the SGA, we also need to ensure that SGA_TARGET parameter is set to large enough to accommodate the new IM column store along with the other components (buffer cache, shared pool, large pool etc).
demo@ORA12C> show parameter memory_max
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
inmemory_max_populate_servers integer 0
memory_max_target big integer 0
demo@ORA12C> show parameter memory_target
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
memory_target big integer 0
demo@ORA12C> show parameter sga
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 4784M
sga_target big integer 4784M
unified_audit_sga_queue_size integer 1048576
demo@ORA12C>
demo@ORA12C>
demo@ORA12C> alter system set sga_max_size = 6G scope=spfile;
System altered.
demo@ORA12C> alter system set sga_target = 6G scope=spfile;
System altered.
demo@ORA12C> alter system set inmemory_size=1G scope=spfile;
System altered.
demo@ORA12C>
Next, let’s bounce the database so that these parameter changes can take effect.
demo@ORA12C> conn sys/Password-1@ora12c as sysdba
Connected.
sys@ORA12C> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descripto
Warning: You are no longer connected to ORACLE.
sys@ORA12C>
sys@ORA12C> exit
C:\Users\Administrator>set ORACLE_SID=ORA12C
C:\Users\Administrator>sqlplus sys/Password-1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 19 23:09:24 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 6442450944 bytes
Fixed Size 3060040 bytes
Variable Size 1040191160 bytes
Database Buffers 4311744512 bytes
Redo Buffers 13713408 bytes
In-Memory Area 1073741824 bytes
Database mounted.
Database opened.
idle>
idle> conn demo/demo@ora12c
Connected.
demo@ORA12C>
demo@ORA12C> show parameter sga_target
NAME TYPE VALUE
--------------------------------------------- ----------- ------
sga_target big integer 6G
demo@ORA12C> show parameter inmemory_size
NAME TYPE VALUE
--------------------------------------------- ----------- ------
inmemory_size big integer 1G
demo@ORA12C>
demo@ORA12C> select name,value from v$sga;
NAME VALUE
------------------ ----------
Fixed Size 3060040
Variable Size 922750648
Database Buffers 4429185024
Redo Buffers 13713408
In-Memory Area 1073741824
5 rows selected.
demo@ORA12C>
As you can see, we now have an IM Column store, but database in-memory is still not in use, because no objects have been populated into the IM Column store. To confirm this we can look at the view v$im_segments.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
no rows selected
Only the object with INMEMORY attribute are populated into IM column store, the INMEMORY attribute can be specified on a Tablespace, Table, (sub) partition or materialized views. In this case let’s enable INMEMORY attribute on one of the user table BIG_TABLE.
demo@ORA12C> alter table big_table inmemory;
Table altered.
By default Oracle automatically decides when to populate this table into IM column store, as oracle typically populates the table after it has been accessed for the first time. So let’s run the query on the BIG_TABLE.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
no rows selected
demo@ORA12C> select count(*) from big_table;
COUNT(*)
----------
1000000
1 row selected.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
OWNER SEGMENT_NA INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_
---------- ---------- ------------- ---------- ------------------- ---------
DEMO BIG_TABLE 27459584 131137536 0 COMPLETED
1 row selected.
If we check the feature tracking information now, we will see Database in-memory is enabled and being used.
demo@ORA12C> exec sys.DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(sysdate);
BEGIN sys.DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(sysdate); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_FEATURE_USAGE_INTERNAL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
demo@ORA12C> conn sys/Password-@ora12c as sysdba
Connected.
sys@ORA12C> grant execute on DBMS_FEATURE_USAGE_INTERNAL to demo;
Grant succeeded.
sys@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> exec sys.DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(sysdate);
PL/SQL procedure successfully completed.
demo@ORA12C> column name format a30
demo@ORA12C> select name,version,detected_usages,
2 total_samples,currently_used,first_usage_date
3 from dba_feature_usage_statistics
4 where name like 'In-%';
NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE FIRST_USAGE
---------------------- ---------- --------------- ------------- ----- -----------
In-Memory Aggregation 12.1.0.2.0 0 1 FALSE
In-Memory Column Store 12.1.0.2.0 1 1 FALSE 19-DEC-2016
2 rows selected.
If you want to remove the table from the IM column store, you simply need to specify the NO INMEMORY attribute.
demo@ORA12C> alter table big_table no inmemory;
Table altered.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments;
no rows selected
You could also enable IM column store for a subset of columns in table using NO INMEMORY option on a set of excluded column list.
demo@ORA12C> set linesize 71
demo@ORA12C> desc big_table
Name Null? Type
-------------------- -------- -------------
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
ID NUMBER
demo@ORA12C>
demo@ORA12C> alter table big_table inmemory no inmemory(
2 OBJECT_NAME ,SUBOBJECT_NAME ,
3 DATA_OBJECT_ID ,OBJECT_TYPE ,CREATED ,
4 LAST_DDL_TIME, TIMESTAMP, STATUS,
5 TEMPORARY, GENERATED, SECONDARY,
6 NAMESPACE, EDITION_NAME, SHARING,
7 EDITIONABLE, ORACLE_MAINTAINED, ID );
Table altered.
demo@ORA12C> select count(*) from big_table;
COUNT(*)
----------
1000000
1 row selected.
demo@ORA12C> select owner,segment_name,inmemory_size,bytes,
2 bytes_not_populated,populate_status
3 from v$im_segments ;
OWNER SEGMENT_NA INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_
---------- ---------- ------------- ---------- ------------------- ---------
DEMO BIG_TABLE 7536640 131137536 0 COMPLETED
1 row selected.
demo@ORA12C>
It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted "Oracle Training in Bangalore".
ReplyDelete