Re: V$INMEMORY_AREA. POPULATE_STATUS out of memory
From: Andy Rivenes <andy.rivenes_at_oracle.com>
Date: Thu, 27 Oct 2016 15:17:49 -0700
Message-Id: <F3D9F2A1-5A36-4D99-A888-3A895E4DDA59_at_oracle.com>
>>> On Oct 27, 2016, at 5:22 AM, Anton Bushmelev <djeday84_at_gmail.com <mailto:djeday84_at_gmail.com>> 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 !
>>>
>>>
>>>
>>
>
Date: Thu, 27 Oct 2016 15:17:49 -0700
Message-Id: <F3D9F2A1-5A36-4D99-A888-3A895E4DDA59_at_oracle.com>
Hi Anton,
Sorry, I didn't look closely enough at your query for v$im_segments. I don't see why your objects haven't populated more than they have, but it looks like your objects take up over 18GB on disk. Have you tried just the T2909 segment for example since that should fit. You could also try a higher compression level than the default of memcompress for query low. You can use the compression advisor (i.e. dbms_compression) to estimate the size that each object will use in the IM column store.
Another thing that you should consider, if you haven't done it already, is to apply the latest database proactive bundle patch (see MOS Note 1937782.1). There have been a lot of enhancements made to Database In-Memory over the last 2+ years. I always recommend that customers start with the latest database proactive bundle patch installed so that they don't wind up hitting known bugs. Note that it has to be a bundle patch, Database In-Memory fixes are not distributed in PSUs or CPUs.
Regards,
Andy
> On Oct 27, 2016, at 8:21 AM, Anton <djeday84_at_gmail.com> wrote: > > 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_at_gmail.com <mailto:djeday84_at_gmail.com>> 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 !
>>>
>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 28 2016 - 00:17:49 CEST