如何从INMEMORY分区收集数据? [英] How to collect data from INMEMORY partition?

问题描述

我正在调整Oracle 12c DB并尝试将某些表加载到INMEMORY分区.因此，我用CRITICAL PRIORITY更改了2个表.但是，当我尝试从内存中的表中SELECT某些数据时，它将从常规补丁中收集数据.我已经从Autotrace获得了它.

I'm tuning Oracle 12c DB and trying to load some tables to INMEMORY partition. So, I'm altered 2 tables with CRITICAL PRIORITY. But, when I'm trying to SELECT some data from table in memory, it collects from general patition. I've got it from Autotrace.

我做错了什么?

推荐答案

IMCO(内存协调器)进程唤醒

The IMCO(Inmemory Coordinator) process wakes up every two minutes and checks to see if any population tasks needs to be completed. So, querying a table immediately after enabling it for inmemory doesn't ensure that query to be served from inmemory.

在打开数据库后或在第一次扫描(查询)表之后，将对象按优先级列表填充到IM列存储中.

Objects are populated into the IM column store either in a prioritized list immediately after the database is opened or after the tables are scanned (queried) for the first time.

例如，如果我们为内存启用表tab1，tab2，tab3:

For example, if we enable table tab1, tab2, tab3 for inmemory:

alter table tab1 inmemory priority medium;
alter table tab3 inmemory priority high;
alter table tab2 inmemory priority critical;

在以下情况下，这些表会进入内存:

These tables get into inmemory when:

1. IMCO流程提取这些表并将其加载到内存区域 (按照优先级从高到低的顺序:tab2，tab3和tab1)
2. 如果我们在IMCO处理开始之前(每2分钟)对任何表(例如:select * from tab1)执行选择查询

1. IMCO process picks up these tables and loads them into inmemory area (in the order of priority higest to lowest: tab2, tab3 and tab1)
2. If we perform select query on any of the tables (example: select * from tab1) before the IMCO process kicks in (in every 2 mins)

要了解表/分区是否已完全加载到内存中，可以查询v$im_segments视图，如下所示:

To know if a table/partition is loaded completely into inmemory you can query v$im_segments view like following:

select owner, segment_name, partition_name, segment_type, bytes, 
bytes_not_populated, populate_status from v$im_segments;

因此，回答您的问题:

1. 通过查询v$im_segments
确保将表加载到内存中
2. 如果未加载表，请对其执行选择查询以使其成为 加载到内存区
3. 获取有关选择查询的查询计划 表格中，它应将INMEMORY显示为计划的一部分

1. Ensure the table is loaded into inmemory by querying v$im_segments
2. If the table is not loaded perform select query on it to make it loaded into inmemory area
3. Get query plan for the select query on the table, it should show INMEMORY as part of the plan

选中此白皮书以获取更多详细信息.

Check this whitepaper for more details.

这篇关于如何从INMEMORY分区收集数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！