当前位置：网站首页>The fourth chapter is to enable the filling object of IM and enable ADO for im column storage (IM 4.8)

Automatic data optimization （ADO） Create a strategy , And automatically perform operations according to these policies , To implement ILM Strategy . ADO Use heat maps , And track data access patterns .

notes :

This chapter assumes that you are familiar with ILM、ADO and Heap Map Basic concepts of .

This section contains the following topics ：

About ADO Strategy and IM Column store stay Oracle Database 12c The first 2 edition （12.2） in ,ADO adopt ADO Strategic management IM Column store . You can only create at the segment level with INMEMORY Clause ADO Strategy .
ADO and IM The purpose of column storage from Oracle Database 12c The first 2 edition （12.2） Start ,ADO take IM Column storage management as a new data tier .
ADO How to use with column data from ADO Point of view ,IM Column storage is another storage tier .
ADO and IM Control of column storage Use HEAT_MAP Initialization parameters enable heat map （Heat Map）. adopt SQL and PL / SQL Interface control ADO.
by IM Column store creation ADO Strategy You can use ADO The strategy is based on the heat map （Heat Map） Statistics settings , Modify or delete the of an object INMEMORY Clause .

About ADO Strategy and IM Column store

stay Oracle Database 12c The first 2 edition （12.2） in ,ADO adopt ADO Strategic management IM Column store . You can only create at the segment level with INMEMORY Clause ADO Strategy .

Database will ADO Strategy （ Such as the properties of the object ） Treat as object .ADO The policy is at the database level , Not instance level . Oracle The database supports the following types of DatabaseIn-Memory Of ADO Strategy ：

· INMEMORY Strategy

This policy uses INMEMORY Attribute tag object , Enable them for IM Padding in column store .

· Recompression strategy

This policy will change INMEMORY Compression level on object .

· NO INMEMORY Strategy

This strategy starts from IM Delete an object from the column store , And delete it INMEMORY object .

Oracle The database supports the following conditions, which apply to policy ：

· Specific number of days since the object was modified

from DBA_HEAT_MAP_SEGMENT In the view SEGMENT_WRITE_TIME Column to get this value .

· Specific number of days since the object was accessed

This value is DBA_HEAT_MAP_SEGMENT Columns in view SEGMENT_WRITE_TIME,FULL_SCAN and LOOKUP_SCAN The greater of .

· Specific number of days since the object was created

from DBA_OBJECTS Medium CREATED Column to get this value .

· User defined functions return Boolean values

ADO and IM The purpose of column storage

from OracleDatabase 12c Release 2（12.2） Start ,ADO take IM Column storage management as a new data tier .

You can create policies to IM Column storage reduces performance from IM Evict object from column store , And fill objects as they improve performance .ADO Use HeatMap Statistics to manage IM Column store .

INMEMORY The purpose of the strategy

In many databases , The segment has undergone significant changes since its creation . To maximize performance , When writing activity decreases ,ADO It can fill IM These segments in the column store . for example , If you add partitions to the table every day , You can create a policy , Fill... One day after creation sales_2016_d100 Partition ：

ALTER TABLE sales MODIFY PARTITION sales_2016_d100

  ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY

    PRIORITY HIGH

  AFTER 1 DAYS OF CREATION

Again , You may know that write activity on the table decreased two months after creation , And you want to fill this object when this time condition is met ：

ALTER TABLE 2016_ski_sales

  ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY

    PRIORITY CRITICAL

  AFTER 60 DAYS OF CREATION

The above strategy leads to 2016_ski_sales All existing and new partitions of the table inherit this policy . When the segment meets the policy conditions , The database will use the specified INMEMORY Clause marks each partition independently . If the segment already has INMEMORY Strategy , The database will ignore the new policy .

The purpose of the recompression strategy

Depending on the access mode, you may need to IM Compressed data in column storage . for example , You may need to be in DML After the activity stops 2 Day will segment from DML Change compression to query compression ：

ALTER TABLE lineorders

  ILM ADD POLICY MODIFY INMEMORY MEMCOMPRESS FOR QUERY HIGH

  AFTER 2 DAYS OF NO MODIFICATION

If the object is not filled in IM Column storage , Then this policy only changes the compression property . If the object is filled in IM Column storage , be ADO Refill the object with the new compression level . If the segment does not already have INMEMORY attribute , The database will ignore the policy .

NO INMEMORY The purpose of the strategy

To optimize IM Space in column storage , You may need to use NO INMEMORY Policy eviction invalid segment . This strategy also helps prevent the number of inactive segments caused by infrequent queries . for example , If specific sales Reports on partitions run frequently throughout the year , But it usually doesn't run weekly , Then you may want to evict the partition after a week of no access ：

ALTER TABLE sales MODIFY PARTITION sales_2015_q1

  ILM ADD POLICY NO INMEMORY AFTER 7 DAYS OF NO ACCESS;

If 1998 Year of sales Tables are rarely queried , Then you may want to be in a place where there is no access 1 Expelled in days ：

ALTER TABLE sales_1998

  ILM ADD POLICY NO INMEMORY AFTER 1 DAYS OF NO ACCESS;

Queries for expelled segments are never blocked . The database can access data through the traditional buffer caching mechanism .

ADO How to use with column data

from ADO Perspective ,IM Column storage is another storage tier .

This section contains the following topics ：

Heat Map How to work When enabled ,Heat Map Automatically discover data access patterns . ADO Use Heat Map Data implements user-defined policies at the database level .
How to conduct strategy evaluation IM The policy evaluation use of the column storage policy is different from other ADO The evaluation of the strategy is the same infrastructure . The database automatically evaluates and executes policies during maintenance windows .

Heat Map How to work

When enabled ,HeatMap Automatically discover data access patterns . ADO Use Heat Map Data implements user-defined policies at the database level .

Heat Map Automatically track usage information at the row and segment levels . In line level ,Heat Map Tracking data modification time , Then aggregate these times to the block level . At the segment level ,Heat Map Tracking changes , Time for full table scan and index lookup .

Enable IM When storing Columns ,Heat Map Track the access mode of column data . for example ,sales A watch can be “ hot ”, and locations A watch can be “ It's cold ”. For column data ,ADO The algorithm works in the same way as row based data .

The database periodically updates HeatMap Write data into the data dictionary . The database is displayed in the data dictionary view Heat Map data . for example , To get the read and write times of objects in memory , Please check ALL_HEAT_MAP_SEGMENT View .

How to conduct strategy evaluation

IM The policy evaluation use of the column storage policy is different from other ADO The evaluation of the strategy is the same infrastructure . The database automatically evaluates and executes policies during maintenance windows .

Database usage HeatMap Statistics to evaluate strategies , It is stored in the data dictionary . Set up INMEMORY Attributes are mainly metadata operations , Therefore, the impact on performance is minimal .

ADO Use Job Scheduler Perform fill . In-MemoryCoordinator Process（IMCO） Perform fill .

ADO and IM Control of column storage

Use HEAT_MAP Initialization parameters enable Heat Map. adopt SQL and PL / SQL Interface control ADO.

DDL Statement ILM Clause

Creating a memory policy does not require a new SQL sentence , but ILM Clause has new options . The following table describes ADO and IM Column storage SQL Options .

surface 4-3 ADO and IM Column storage ILM Clause

Clause	describe	Example
SET INMEMORY	Set the INMEMORY attribute	ALTER TABLE sh.sales ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH SEGMENT AFTER 30 DAYS OF CREATION;
MODIFY INMEMORY	Modify the compression level of the object	ALTER TABLE sh.customers ILM ADD POLICY MODIFY INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY CRITICAL SEGMENT AFTER 30 DAYS OF CREATION;
NO INMEMORY	Set the NO INMEMORY attribute	ALTER TABLE sh.products ILM ADD POLICY NO INMEMORY SEGMENT AFTER 30 DAYS OF CREATION;

Initialize parameters

The following table describes the relationship between ADO and IM The column stores the relevant initialization parameters .

surface 4-4 ADO and IM The initialization parameters stored in the column

PL/SQLPackages

The following table describes the relationship between ADO and IM The column stores the relevant information PL / SQL software package

surface 4-5 ADO and IM Column storage PL/ SQL Packages

Package	describe
DBMS_HEATMAP	stay Tablespace、Segment、Object、 Extent and Block The level displays detailed Heat Map data .
DBMS_ILM	Use ADO Strategy implementation ILM Strategy .
DBMS_ILM_ADMIN	Customize ADO Strategy execution .

V$ And data dictionary view

The following table describes the relationship between ADO and IM Columns store related views .

surface 4-6 ADO and IM View stored in columns

by IM Column store creation ADO Strategy

You can use ADO Strategy basis Heat Map Statistics settings 、 Modify or delete the of an object INMEMORY Clause .

To create a ADO IM Column storage policy , Please be there. ALTER TABLE Specified in statement ILM ADD POLICY Clause , Followed by one of the following clauses ：

· SET INMEMORY ...SEGMENT

Only when the DML When activity decreases , You want to tag segments with INMEMORY Attribute , This option is useful .

· MODIFY INMEMORY... MEMCOMPRESS ... SEGMENT

When frequently modified , Store uncompressed data or MEMCOMPRESS FOR DML The level is appropriate . Alternative compression levels are better suited for queries . If the activity on the segment changes from most writes to most reads , You can use MODIFY Clause applies different compression methods .

· NO INMEMORY ...SEGMENT

When accessing a segment decreases over time （ It becomes “ cold ”）, And prevent the filling of random access results of this segment. This option is useful .

precondition

In the use of ADOIM Before column storage policy , The following prerequisites must be met ：

· By way of INMEMORY_SIZE Set the initialization parameter to a non-zero value and restart the database , Enable... For the database IM Column store .

· HEAT_MAP The initialization parameter must be set to ON.

HeatMap Provide data access tracking at the segment level , Provide data modification tracking at segment and row levels .

· COMPATIBLE The initialization parameter must be set to 12.2.0 Or higher .

establish ADO Strategy ：

1. stay SQL * Plus or SQLDeveloper in , Log in to the database with the necessary permissions .

2. Use a ILM ADD POLICY ... INMEMORY Clause ALTER TABLE sentence .

Example 4-16 Create expulsion policy

In this example , You create a policy , Specify if you do not visit within three days oe.order_items surface , From IM Evict the table from the column store . ADOIM The column storage policy must be a segment level policy .

ALTER TABLE oe.order_items ILM ADD POLICY

   NO INMEMORY SEGMENT

   AFTER 3 DAYS OF NO ACCESS;

Example 4-17 Use DBMS_ILM perform ILM Strategy Example4-17 Executing an ILM Policy Using DBMS_ILM

You can also manually evaluate and enforce policies . therefore , You can programmatically decide when objects need to be compressed or layered . The following example is performed manually sh.sales Of ADO Mission ：

DECLARE

  v_executonid NUMBER;

BEGIN

  DBMS_ILM.EXECUTE_ILM ( owner           => 'SH',

                         object_name     => 'SALES',

                         execution_mode  => DBMS_ILM.ILM_EXECUTION_OFFLINE,

                         task_id         => v_executionid);

END;

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/202204231136323494.html