The objects with INMEMORY attribute are populated into IM column store. This INMEMORY attribute can be specified on a Tablespace, Table, (sub) partition or materialized views, for a partitioned table, all the partitions inherit the INMEMORY attribute but it is possible to populate just a subset of partitions or sub-partitions.
You can identify which tables have the INMEMORY attribute, by looking at the new INMEMORY column from user_tables dictionary tables.
demo@ORA12C> select table_name, inmemory
2 from user_tables;
TABLE_NAME INMEMORY
--------------- --------
BIG_TABLE ENABLED
SALES
2 rows selected.
In the example above, you will notice that table SALES don’t have a value for this “INMEMORY” column. Since the table SALES is partitioned, the INMEMORY attribute will be recorded at user_tab_partitions dictionary tables.
demo@ORA12C> select table_name,partition_name, inmemory
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME INMEMORY
--------------- --------------- --------
SALES P1 ENABLED
SALES P2 ENABLED
2 rows selected.
The IM column store is populated by a set of background processes referred to as worker processes (ora_w001_xxxx). Each worker process is given a subset of database blocks from the object to populate into the IM column store. Population is a streaming mechanism, simultaneously columnizing and compressing the data.
demo@ORA12C> select program,pname,
2 regexp_replace( tracefile ,
3 '\S+\\(\S+)\.\S+','\1') process_name
4 from v$process where pname like 'W%';
PROGRAM PNAME PROCESS_NAME
-------------------- ----- ---------------------
ORACLE.EXE (W000) W000 ora12c_w000_3780
ORACLE.EXE (W001) W001 ora12c_w001_3860
ORACLE.EXE (W002) W002 ora12c_w002_1872
ORACLE.EXE (W003) W003 ora12c_w003_3200
ORACLE.EXE (W005) W005 ora12c_w005_3352
ORACLE.EXE (W004) W004 ora12c_w004_3696
6 rows selected.
demo@ORA12C>
The compression used during the population of the IM column store is different to any of Oracle’s previous types of compression. These new compression algorithms not only help save space but also improve query performance by allowing queries to execute directly against the compressed columns. This means all scanning and filtering operations will execute on a much smaller amount of data. Data is only decompressed when it is required for the result set.
Inmemory compression is specified using the keyword MEMCOMPRESS, a sub-clause of INMEMORY attribute, there are six levels each of which provides different level of compression and performance.
demo@ORA12C> create table sales
2 partition by hash(object_id)
3 ( partition p1 inmemory no memcompress ,
4 partition p2 inmemory memcompress for dml,
5 partition p3 inmemory memcompress for query low,
6 partition p4 inmemory memcompress for query high,
7 partition p5 inmemory memcompress for capacity low,
8 partition p6 inmemory memcompress for capacity high )
9 as
10 select * from big_table;
Table created.
demo@ORA12C> select table_name, partition_name,
2 inmemory, inmemory_compression
3 from user_tab_partitions;
TABLE_NAME PARTITION_NAME INMEMORY INMEMORY_COMPRESS
--------------- --------------- -------- -----------------
SALES P1 ENABLED NO MEMCOMPRESS
SALES P2 ENABLED FOR DML
SALES P3 ENABLED FOR QUERY LOW
SALES P4 ENABLED FOR QUERY HIGH
SALES P5 ENABLED FOR CAPACITY LOW
SALES P6 ENABLED FOR CAPACITY HIGH
6 rows selected.
demo@ORA12C>
Objects are populated into the IM column store either in a prioritized list immediately after the database is opened or after they are scanned (queried) for the first time. The order in which objects are populated is controlled by the keyword PRIORITY, a sub-clause of the INMEMORY attribute. There are five levels from CRITICAL ,HIGH, MEDIUM, LOW to NONE.
The default PRIORITY is NONE, which means an object is populated only after it is scanned for the first time. All objects at a given priority level must be fully populated before the population for any objects at a lower priority level can commence
You can identify the PRIORITY levels that have been specified on the table or partitions by looking at user_tables or user_tab_partitions.
demo@ORA12C> alter table sales modify partition p5 inmemory priority critical;
Table altered.
demo@ORA12C> select table_name,partition_name,
2 inmemory , inmemory_priority
3 from user_tab_partitions;
TABLE_NAME PARTITION_NAME INMEMORY INMEMORY
--------------- --------------- -------- --------
SALES P1 ENABLED NONE
SALES P2 ENABLED NONE
SALES P3 ENABLED NONE
SALES P4 ENABLED NONE
SALES P5 ENABLED CRITICAL
SALES P6 ENABLED NONE
6 rows selected.
demo@ORA12C>
V$IM_SEGMENTS can be used to monitor the contents of these IM column store. These views show which objects are currently populated, or being populated, into the IM column store (POPULATE_STATUS), as well as indicating whether the entire object has been populated (BYTES_NOT_POPULATED).
demo@ORA12C> select segment_name,
2 partition_name,
3 inmemory_size,bytes,
4 bytes_not_populated,
5 populate_status,
6 round(bytes/inmemory_size,2) compress_factor
7 from v$im_segments ;
SEGMENT_NA PAR INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ COMPRESS_FACTOR
---------- --- ------------- ---------- ------------------- --------- ---------------
SALES P4 4325376 33554432 0 COMPLETED 7.76
SALES P1 13762560 16777216 0 COMPLETED 1.22
SALES P6 2228224 16777216 0 COMPLETED 7.53
SALES P3 7471104 33554432 0 COMPLETED 4.49
BIG_TABLE 7536640 131137536 0 COMPLETED 17.4
SALES P5 4325376 16777216 0 COMPLETED 3.88
SALES P2 13762560 16777216 0 COMPLETED 1.22
7 rows selected.
demo@ORA12C>
You can identify which tables have the INMEMORY attribute, by looking at the new INMEMORY column from user_tables dictionary tables.
demo@ORA12C> select table_name, inmemory
2 from user_tables;
TABLE_NAME INMEMORY
--------------- --------
BIG_TABLE ENABLED
SALES
2 rows selected.
In the example above, you will notice that table SALES don’t have a value for this “INMEMORY” column. Since the table SALES is partitioned, the INMEMORY attribute will be recorded at user_tab_partitions dictionary tables.
demo@ORA12C> select table_name,partition_name, inmemory
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME INMEMORY
--------------- --------------- --------
SALES P1 ENABLED
SALES P2 ENABLED
2 rows selected.
The IM column store is populated by a set of background processes referred to as worker processes (ora_w001_xxxx). Each worker process is given a subset of database blocks from the object to populate into the IM column store. Population is a streaming mechanism, simultaneously columnizing and compressing the data.
demo@ORA12C> select program,pname,
2 regexp_replace( tracefile ,
3 '\S+\\(\S+)\.\S+','\1') process_name
4 from v$process where pname like 'W%';
PROGRAM PNAME PROCESS_NAME
-------------------- ----- ---------------------
ORACLE.EXE (W000) W000 ora12c_w000_3780
ORACLE.EXE (W001) W001 ora12c_w001_3860
ORACLE.EXE (W002) W002 ora12c_w002_1872
ORACLE.EXE (W003) W003 ora12c_w003_3200
ORACLE.EXE (W005) W005 ora12c_w005_3352
ORACLE.EXE (W004) W004 ora12c_w004_3696
6 rows selected.
demo@ORA12C>
The compression used during the population of the IM column store is different to any of Oracle’s previous types of compression. These new compression algorithms not only help save space but also improve query performance by allowing queries to execute directly against the compressed columns. This means all scanning and filtering operations will execute on a much smaller amount of data. Data is only decompressed when it is required for the result set.
Inmemory compression is specified using the keyword MEMCOMPRESS, a sub-clause of INMEMORY attribute, there are six levels each of which provides different level of compression and performance.
demo@ORA12C> create table sales
2 partition by hash(object_id)
3 ( partition p1 inmemory no memcompress ,
4 partition p2 inmemory memcompress for dml,
5 partition p3 inmemory memcompress for query low,
6 partition p4 inmemory memcompress for query high,
7 partition p5 inmemory memcompress for capacity low,
8 partition p6 inmemory memcompress for capacity high )
9 as
10 select * from big_table;
Table created.
demo@ORA12C> select table_name, partition_name,
2 inmemory, inmemory_compression
3 from user_tab_partitions;
TABLE_NAME PARTITION_NAME INMEMORY INMEMORY_COMPRESS
--------------- --------------- -------- -----------------
SALES P1 ENABLED NO MEMCOMPRESS
SALES P2 ENABLED FOR DML
SALES P3 ENABLED FOR QUERY LOW
SALES P4 ENABLED FOR QUERY HIGH
SALES P5 ENABLED FOR CAPACITY LOW
SALES P6 ENABLED FOR CAPACITY HIGH
6 rows selected.
demo@ORA12C>
Objects are populated into the IM column store either in a prioritized list immediately after the database is opened or after they are scanned (queried) for the first time. The order in which objects are populated is controlled by the keyword PRIORITY, a sub-clause of the INMEMORY attribute. There are five levels from CRITICAL ,HIGH, MEDIUM, LOW to NONE.
The default PRIORITY is NONE, which means an object is populated only after it is scanned for the first time. All objects at a given priority level must be fully populated before the population for any objects at a lower priority level can commence
You can identify the PRIORITY levels that have been specified on the table or partitions by looking at user_tables or user_tab_partitions.
demo@ORA12C> alter table sales modify partition p5 inmemory priority critical;
Table altered.
demo@ORA12C> select table_name,partition_name,
2 inmemory , inmemory_priority
3 from user_tab_partitions;
TABLE_NAME PARTITION_NAME INMEMORY INMEMORY
--------------- --------------- -------- --------
SALES P1 ENABLED NONE
SALES P2 ENABLED NONE
SALES P3 ENABLED NONE
SALES P4 ENABLED NONE
SALES P5 ENABLED CRITICAL
SALES P6 ENABLED NONE
6 rows selected.
demo@ORA12C>
V$IM_SEGMENTS can be used to monitor the contents of these IM column store. These views show which objects are currently populated, or being populated, into the IM column store (POPULATE_STATUS), as well as indicating whether the entire object has been populated (BYTES_NOT_POPULATED).
demo@ORA12C> select segment_name,
2 partition_name,
3 inmemory_size,bytes,
4 bytes_not_populated,
5 populate_status,
6 round(bytes/inmemory_size,2) compress_factor
7 from v$im_segments ;
SEGMENT_NA PAR INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ COMPRESS_FACTOR
---------- --- ------------- ---------- ------------------- --------- ---------------
SALES P4 4325376 33554432 0 COMPLETED 7.76
SALES P1 13762560 16777216 0 COMPLETED 1.22
SALES P6 2228224 16777216 0 COMPLETED 7.53
SALES P3 7471104 33554432 0 COMPLETED 4.49
BIG_TABLE 7536640 131137536 0 COMPLETED 17.4
SALES P5 4325376 16777216 0 COMPLETED 3.88
SALES P2 13762560 16777216 0 COMPLETED 1.22
7 rows selected.
demo@ORA12C>
No comments:
Post a Comment