12c inmemory load不进内存
版本：12.1.0.2
os：x86_64 linux
一个分区表
DISP_OE_BAG
行数3446684
alter table DISP_OE_BAG inmemory;
想要load进内存，但如果执行了
select /*+full(DISP_OE_BAG)*/ count(*) from DISP_OE_BAG;
是可以正常load进内存的
############################################
问题如下：
但如果再此之前 执行了
select /*+full(DISP_OE_BAG)*/ count(*) from DISP_OE_BAG where rownum<10000;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1572593231
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 119 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | PARTITION RANGE ALL | | 9999 | 119 (0)| 00:00:01 | 1 |1048575|
| 4 | TABLE ACCESS INMEMORY FULL| DISP_OE_BAG | 9999 | 119 (0)| 00:00:01 | 1 |1048575|
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<10000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这样就只有一个分区被load进去
然后这个算正常现象，但当我想把其他都load进去的时候
发现
执行
select /*+full(DISP_OE_BAG)*/ count(*) from DISP_OE_BAG;
Elapsed: 00:00:00.41
Execution Plan
----------------------------------------------------------
Plan hash value: 1996075084
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40503 (1)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE ALL | | 3446K| 40503 (1)| 00:00:02 | 1 |1048575|
| 3 | TABLE ACCESS INMEMORY FULL| DISP_OE_BAG | 3446K| 40503 (1)| 00:00:02 | 1 |1048575|
----------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
143720 consistent gets
0 physical reads
0 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
load不进去
始终是
1* select SEGMENT_NAME,partition_name,INMEMORY_SIZE/1024/1024/1024,BYTES/1024/1024/1024,POPULATE_STATUS from v$im_segments where segment_name='DISP_OE_BAG' and owner='IPWS3'
SQL> /
SEGMENT_NAME PARTITION_NAME INMEMORY_SIZE/1024/1024/1024 BYTES/1024/1024/1024 POPULATE_
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------- -------------------- ---------
DISP_OE_BAG SYS_P317 .005981445 .016601563 COMPLETED
这一个分区
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 8588886016 6690963456 DONE 0
64KB POOL 2130706432 21757952 DONE 0
空间是够的
这是bug么？
单独load一个分区也不行
select /*+full(DISP_OE_BAG)*/ count(*) from DISP_OE_BAG partition (SYS_P484)
SQL> /
COUNT(*)
----------
275688
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1547420784
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3293 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE SINGLE | | 275K| 3293 (1)| 00:00:01 | 9 | 9 |
| 3 | TABLE ACCESS INMEMORY FULL| DISP_OE_BAG | 275K| 3293 (1)| 00:00:01 | 9 | 9 |
----------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11651 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed 早上起来 我来单位一看 又load进去了 不知道是什么时间load进去的 期间也没人操作 是不是后台有个进程 定时load啊
按说应该随着我手动count读到inmemory但是却没有
select SEGMENT_NAME,INMEMORY_SIZE/1024/1024/1024,BYTES/1024/1024/1024,POPULATE_STATUS from v$im_segments where segment_name='DISP_OE_BAG' and owner='IPWS3';
SEGMENT_NAME INMEMORY_SIZE/1024/1024/1024 BYTES/1024/1024/1024 POPULATE_
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------- -------------------- ---------
DISP_OE_BAG .061645508 .1484375 COMPLETED
DISP_OE_BAG .032348633 .078125 COMPLETED
DISP_OE_BAG .038208008 .09375 COMPLETED
DISP_OE_BAG .048950195 .125 COMPLETED
DISP_OE_BAG .005981445 .016601563 COMPLETED
DISP_OE_BAG .053833008 .125 COMPLETED
DISP_OE_BAG .051879883 .131835938 COMPLETED
DISP_OE_BAG .02746582 .0703125 COMPLETED
DISP_OE_BAG .057739258 .140625 COMPLETED
DISP_OE_BAG .042114258 .1015625 COMPLETED
DISP_OE_BAG .033325195 .0859375 COMPLETED
DISP_OE_BAG .012817383 .03515625 COMPLETED
12 rows selected.
测试了一下 ，只有分区表存在这种现象
非分区表没事
页:
[1]