Re: V$INMEMORY_AREA. POPULATE_STATUS out of memory

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> 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-l