6.没有对表查询前还是未真正放到In-Memory内存中
SQL> SELECT * FROM V$INMEMORY_AREA; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------- ----------- ---------- -------------------- ---------- 1MB POOL 837812224 0 DONE 3 64KB POOL 201326592 0 DONE 3 SQL> select count(*) from bmw.t; COUNT(*) ---------- 90927 |
7.查询一次后可以看到USED_BYTES已经分配了内存
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------- ----------- ---------- -------------------- ----------
1MB POOL 837812224 4194304 DONE 3
64KB POOL 201326592 131072 DONE 3
8.查看一下执行计划
SQL> set autot trace SQL> SELECT * FROM bmw.t; 90927 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 90927 | 9M| 20 (20)| 00:00:01 | | 1 | TABLE ACCESS INMEMORY FULL| T | 90927 | 9M| 20 (20)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 4944325 bytes sent via SQL*Net to client 67223 bytes received via SQL*Net from client 6063 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 90927 rows processed |
这里可以看到使用了TABLE ACCESS INMEMORY FULL扫描,而且consistent gets为5.可以看到INMEMORY会给查询带来非常高的性能提升。