Configuration and Troubleshooting Demantra and 12c In Memory Columns and Tables for Faster Read Transactions. Release 12.2.5.1 and Above. Known Issues, Monitoring+! (Doc ID 2126233.1)

 In this Document

Abstract

History

Details

Summary

Configuration

Troubleshooting

---

APPLIES TO:

Oracle Demantra Demand Management - Version 12.2.5.1 and later
Information in this document applies to any platform.

ABSTRACT

Demantra, starting from version 12.2.5.1, is certified to work with Oracle DB 12c. 

 

• This DB version includes the ability to load data into memory to enable faster read transactions from the database; this provides a significant performance gain on some of the Demantra operations, mainly worksheet run time.

HISTORY

Created 13-Apr-2016. 

DETAILS

The following guidelines and suggestions have been compiled based on internal work done by Oracle Demantra Development team, as well as real customer experience with this capability and configuration.

SUMMARY

Configuration

Oracle 12c allows the capability of bringing to memory columns or tables, the recommendation is not to load complete tables to memory, like mdp_matrix and data tables.  But rather load relevant columns from mdp_matrix and the data tables, sales_data as an example, for a DM solution.

It is important to remember that loading data into memory will improve the performance of read transaction, but is less relevant for insert/update transactions.

Therefore while discussing the columns that need to be loaded to memory, we can ignore engine / data loading processes and focus mainly on worksheets and export needs.

Relevant columns for these types of operations can be identified by analyzing the SQLs the application is generating while loading the worksheets used by the users and in the export process.

If your data table is partitioned, it is also recommended to load columns only on relevant partitions, and not just load the full columns to memory from all available partitions.

The suggested options when loading columns to memory are:
InMemory Compress – use the option “For Query Low” – this means less compression and better performance, if you need to compress the data more, you can use the option “For Query High”.
Priority – you should load the columns into memory with priority set to “Critical”

As part of the notes mechanism of Demantra, some mdp_matrix columns are added to the SQL queries, these are columns like IS_PROMOTION, IS_SUPPLY_PLAN, IS_SCENARIO_RESOURCE , IS_T_EP_CTO and more.

• This will need to be fixed in future releases, but currently, for achieving the desired performance gains, these columns will need to be loaded to memory as well. The full list should be obtained by analyzing the queries SQLs.

 

Troubleshooting

1. To monitor the status of IMCS build-up, use the following query:

  

select inst_id, owner||'.'||segment_name segment_name,partition_name,populate_status status
, round(sum(INMEMORY_SIZE)/1024/1024,2) im_size_mb
, round(MAX(BYTES)/1024/1024,2) disk_size_mb
, round(sum(BYTES_NOT_POPULATED)/1024/1024,2) not_mb
, count(*)
, '1:'||round(MAX(BYTES)/sum(INMEMORY_SIZE),1) as compress_ratio
from gv$im_segments
group by inst_id, owner,segment_name,partition_name,populate_status
order by segment_name, inst_id, partition_name;

 

•  Note that only when the status = COMPLETED the actual IMCS load is done.

Other two useful SQL statements that complete the IM segments status are:

  

select 'TABLE' as object_type,owner||'.'||table_name as object_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
from dba_tables
where inmemory is not null
and (inmemory <> 'DISABLED' or inmemory is null)
UNION
select 'PARTITION',table_owner||'.'||table_name||' Part: '||partition_name as table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
from dba_tab_partitions
where inmemory is not null
and (inmemory <> 'DISABLED' or inmemory is null)
order by object_type desc,object_name;

  

SELECT owner,
table_name,
segment_column_id,
column_name,
inmemory_compression
FROM v$im_column_level
WHERE inmemory_compression <> 'NO INMEMORY'
ORDER BY owner,table_name,column_name;

 

  

2. In some versions of 12c we have encountered an ORA-01795: maximum number of expressions in a list is 1000 - error while trying to load into memory data from partitioned table, this is a known DB defect 19670592 : ORA-1795 ON QUERYING AN IN MEMORY TABLE WITH MORE THAN 1000 PARTITIONS , and if you do encounter this problem, there is a need to log a DB SR to get a one off (or get instructions of the bundle patch with this fix).

3. Additional DB patch may be needed to resolve a problem where for few tables and partitions “populate_status” is not getting updated.  This is fixed by defect BUG 18549042 - THE STATUS OUT OF MEMORY IS NOT SHOW IN VIEW V$IM_SEGMENTS.

4. We are seeing cases where although sales_data columns are in memory, the DB is using the table and indexes on sales_data and not the in-memory columns; this is an issue we are still exploring to understand the root cause.