Hello Andy,
Thank for your response, could you please explain why I get out of
memory, if it should be more than 75% free memory for inmemory store:
I have 8GB for in-memory data ( query from V$INMEMORY_AREA ) and, as I
see by in_memory_size from v$im_segments, there is only ~1800mb are
stored in inmemory.
I don't put all columns to inmemory:
select table_name, decode(INMEMORY_COMPRESSION ,'NO INMEMORY','NO
INMEMORY','INMEMORY') as compress_state,count (*) from V$IM_COLUMN_LEVEL
group by table_name, decode(INMEMORY_COMPRESSION ,'NO INMEMORY','NO
INMEMORY','INMEMORY') order by table_name,compress_state;
TABLE_NAME | COMPRESS_STATE | COUNT(*)
--------------- | --------------- | ----------
T2109 | INMEMORY | 62
T2115 | INMEMORY | 77
T2115 | NO INMEMORY | 507
T2909 | INMEMORY | 3
T2909 | NO INMEMORY | 61
T3558 | INMEMORY | 9
This is not the reason why I have nozero bytes_not_populated in
v$im_segments ?
And how to calculate size of inmemory occupants ?
ps: sorry for my English
On 10/27/2016 05:17 PM, Andy Rivenes wrote:
Hi Anton,
The "OUT OF MEMORY" status in v$im_segments means that you did not have enough space allocated to the IM column store to populate the segments that you enabled for INMEMORY. If you check the BYTES_NOT_POPULATED column in the v$im_segments view you should see a non-zero value for one or more of your segments. Your query will still run and not return an error, but Oracle will only access the rows that have been populated in the IM column store and the rest it will access from the row store (i.e. buffer cache and possibly disk).
Regards,
Andy
On Oct 27, 2016, at 5:22 AM, Anton Bushmelev <djeday84@xxxxxxxxx <mailto:djeday84@xxxxxxxxx>> wrote:
Hello, try to find info about V$INMEMORY_AREA.POPULATE_STATUS and found nothing useful =(
so, what do I have, 4 tables with POPULATE_STATUS=complete
select owner,segment_name,partition_name,
round (inmemory_size/1024/1024) in_mem_mb,
round (bytes/1024/1024) mb,
round (bytes_not_populated/1024/1024) mb_no_im,
populate_status from v$im_segments order by bytes desc;
OWNER | SEGMENT_NAME | PARTITION_NAME | IN_MEM_MB | MB | MB_NO_IM | POPULATE_STATUS
--------------- | ------------------------------ | ------------------------------ | ---------- | ---------- | ---------- | ---------------------------
ARADMIN | *T2115* | | 569 | 9848 | 3930 | *COMPLETED*
ARADMIN | T2109 | | 1057 | 5764 | 4278 | COMPLETED
ARADMIN | T2909 | | 242 | 3276 | 301 | COMPLETED
ARADMIN | T3558 | | 1 | 0 | 0 | COMPLETED
and every time when I run this query, there is out of memory in POPULATE_STATUS column.
select pool,alloc_bytes/1024/1024 alloc_mbytes,POPULATE_STATUS,con_id from V$INMEMORY_AREA;
POOL | ALLOC_MBYTES | POPULATE_STATUS | CON_ID
--------------- | ------------ | -------------------- | ----------
1MB POOL | 8191 | *OUT OF MEMORY * | 0
64KB POOL | 2032 | DONE | 0
Real time monitoring shows that in memory access for my table took more than 120 seconds and it is extremely high =(
Global Information
------------------------------
Status : EXECUTING
* Duration : 115s*
Binds
….
Global Stats
======================================================================
| Elapsed | Cpu | IO | Concurrency | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
======================================================================
| 122 | 21 | 101 | 0.00 | 2M | 181K | 2GB |
======================================================================
SQL Plan Monitoring Details (Plan Hash Value=1160242469)
=======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
=======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | |
| 1 | COUNT STOPKEY | | | | | | 1 | | | | | | | |
| 2 | VIEW | | 1 | 548K | | | 1 | | | | | | | |
| 3 | SORT ORDER BY STOPKEY | | 1 | 548K | 111 | +4 | 1 | 0 | | | 757K | 0.87 | Cpu (1) | |
| -> 4 | FILTER | | | | 111 | +4 | 1 | 1M | | | | | | |
| -> 5 | *TABLE ACCESS INMEMORY FULL* | T2115 | 1 | 548K | 115 | +1 | 1 | 1M | 181K | 2GB | | 99.13 | in memory (1) | 22% |
| | | | | | | | | | | | | | Cpu (4) | |
| | | | | | | | | | | | | | db file scattered read (6) | |
| | | | | | | | | | | | | | *db file sequential read (70) *| |
| | | | | | | | | | | | | | read by other session (33) | |
=======================================================================================================================================================================================
Any advise what to do are welcome !
