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 !