Oracle 在12c版本中引入了内存与列式压缩选件In-Memory，In-Memory可以极大提升查询的性能。
这里通过一个对比可以看到同样的查询中能提高多少性能。
1.先给In-Memory分配内存
SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 percent optimizer_inmemory_aware boolean TRUE |
2.INMEMORY_SIZE定义了in-memory的大小
SQL> alter system set inmemory_size=1000m scope=spfile;
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
修改完需要重启一下
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 5016387584 bytes Fixed Size 3721128 bytes Variable Size 1056966744 bytes Database Buffers 3724541952 bytes Redo Buffers 13053952 bytes In-Memory Area 218103808 bytes Database mounted. Database opened. |
3.建立一张测试表
SQL> create table bmw.t as select * from dba_objects; Table created. SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from dba_tables where table_name='T'; TABLE_NAME INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION ---------- ------------------------ --------------------------------------------- --------------------------------------------------- T 可以看到并未真正的分配USED_BYTES SQL> l 1* SELECT * FROM V$INMEMORY_AREA SQL> / POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------- ----------- ---------- -------------------- ---------- 1MB POOL 837812224 0 DONE 3 64KB POOL 201326592 0 DONE 3 |
4.看一下未使用In-Memory时的查询
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| 416 (1)| 00:00:01 | | 1 | TABLE ACCESS FULL| T | 90927 | 9M| 416 (1)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 7487 consistent gets 1525 physical reads 0 redo size 12128303 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 FULL扫描，consistent gets也达到了7487。
5.将表放到In-Memory中
SQL> set autot off SQL> alter table bmw.t inmemory; Table altered. SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from dba_tables where table_name='T'; TABLE_NAME INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION ---------- ------------------------ --------------------------------------------- --------------------------------------------------- T NONE AUTO FOR QUERY LOW |
