12c的inmemory option 已经在6月10日发布，预计会在7月份有正式的产品release，即在12.1.0.2中，你就可以看到这个新特性了。
下面我们来简单看看这个新特性的用法和体会一下其厉害之处。
|
1 2 3 4 5 6 7 8 |
SQL> show parameter inmem NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_query string ENABLE inmemory_size big integer 500M |
上面的几个参数和inmemory option有关。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
inmemory_clause_default： 默认空值，表示需要显式的指定某个table才能in memory INMEMORY，表示所有的new table都in memory NO INMEMORY，和空值是一个意思 inmemory_force： default：具有IN MEMORY属性的table，才会被选定以in memory的方式存储。 OFF：即使具有IN MEMORY AREA被配置了，也不会有table以in memory的方式存储。 ON：除非显式的指定NO INMEMORY的属性的table，其他的table都会以in memory方式存储。 inmemory_query： enable，可以进行inmemory_query disable，禁用inmemory_query inmemory_size： 设置inmemory option的内存大小，注，不能动态调整。 |
常用的检查视图：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> desc v$im_segments Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) SEGMENT_NAME NOT NULL VARCHAR2(128) PARTITION_NAME VARCHAR2(128) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME NOT NULL VARCHAR2(30) INMEMORY_SIZE NUMBER BYTES NUMBER BYTES_NOT_POPULATED NUMBER POPULATE_STATUS VARCHAR2(9) INMEMORY_PRIORITY VARCHAR2(8) INMEMORY_DISTRIBUTE VARCHAR2(15) INMEMORY_COMPRESSION VARCHAR2(17) CON_ID NUMBER SQL> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
--注意dba_tables中已经多了几个和inmemory相关的字段，见下面标记<<<的几个 SQL> desc user_tables Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(128) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(128) IOT_NAME VARCHAR2(128) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(40) INSTANCES VARCHAR2(40) CACHE VARCHAR2(20) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) FLASH_CACHE VARCHAR2(7) CELL_FLASH_CACHE VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(128) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) COMPRESS_FOR VARCHAR2(30) DROPPED VARCHAR2(3) READ_ONLY VARCHAR2(3) SEGMENT_CREATED VARCHAR2(3) RESULT_CACHE VARCHAR2(7) CLUSTERING VARCHAR2(3) ACTIVITY_TRACKING VARCHAR2(23) DML_TIMESTAMP VARCHAR2(25) HAS_IDENTITY VARCHAR2(3) CONTAINER_DATA VARCHAR2(3) INMEMORY_PRIORITY VARCHAR2(8) <<< INMEMORY_DISTRIBUTE VARCHAR2(15) <<< INMEMORY_COMPRESSION VARCHAR2(17) <<< SQL> |
我们来测试一下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
--登录PDB，建立测试表 SQL> conn test/test@pdb1 Connected. SQL> create table oracleblog1 as select * from dba_source; Table created. --看看其大小： SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='ORACLEBLOG1'; SUM(BYTES)/1024/1024 -------------------- 63 --将表用cache的方式缓存起来，以便一会与inmemory做对比。 SQL> alter table ORACLEBLOG1 cache; Table altered. --检查dba_tables中，相关inmemory字段的情况。看到结果是空。但cache字段是Y了。 SQL> SELECT table_name, cache, inmemory_priority, 2 inmemory_distribute,inmemory_compression FROM user_tables where table_name='ORACLEBLOG1' 3 / TABLE_NAME CACHE INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS ------------------------------ -------------------- -------- --------------- ----------------- ORACLEBLOG1 Y SQL> --建立另外一个测试表 SQL> create table oracleblog2 as select * from dba_source; Table created. --注意，这里我们将table的属性改成inmemory，但此时还没加载到inmemory中。 SQL> alter table ORACLEBLOG2 inmemory; Table altered. --我们可以通过看im_segments表，发现目前还没有东西进去到inmemory segment中。 SQL> SELECT v.owner, v.segment_name name, 2 v.populate_status status, v.bytes_not_populated FROM v$im_segments v; no rows selected SQL> --检查一下该table的属性： SQL> SELECT table_name, cache, inmemory_priority, 2 inmemory_distribute,inmemory_compression FROM user_tables where table_name='ORACLEBLOG2'; TABLE_NAME CACHE INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS ------------------------------ -------------------- -------- --------------- ----------------- ORACLEBLOG2 N NONE AUTO DISTRIBUTE FOR QUERY SQL> --跑一下下面的语句，使其加载到inmemory中： SELECT /*+ full(t1) noparallel (t1)*/ Count(*) FROM oracleblog1 t1; SELECT /*+ full(t2) noparallel (t2)*/ Count(*) FROM oracleblog2 t2; --检查inmemory segment中，已经有了oracleblog2表。 --注： BYTES_NOT_POPULATED为0，表示整个表都inmemory了。 SQL> SELECT v.owner, v.segment_name name, 2 v.populate_status status, v.bytes_not_populated FROM v$im_segments v; OWNER NAME STATUS BYTES_NOT_POPULATED ------------------------------ ------------------------------ --------- ------------------- TEST ORACLEBLOG2 COMPLETED 0 --同时，我们还能看一下压缩了多少： SQL> SELECT v.owner, v.segment_name, v.bytes orig_size, v.inmemory_size in_mem_size, 2 v.bytes/v.inmemory_size comp_ratio FROM v$im_segments v; OWNER SEGMENT_NAME ORIG_SIZE IN_MEM_SIZE COMP_RATIO ------------------------------ -------------------- ---------- ----------- ---------- TEST ORACLEBLOG2 66060288 33685504 1.96108949 SQL> |
好，我们正式开始对比测试，每个语句跑3次。
（1）用cache的情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
SQL> select /* use cache */ max(LINE) from oracleblog1; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 4099259911 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2176 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| ORACLEBLOG1 | 338K| 1322K| 2176 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 7913 consistent gets 7882 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SQL> SQL> SQL> select /* use cache */ max(LINE) from oracleblog1; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.04 Execution Plan ---------------------------------------------------------- Plan hash value: 4099259911 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2176 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| ORACLEBLOG1 | 338K| 1322K| 2176 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7886 consistent gets 7882 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SQL> SQL> SQL> select /* use cache */ max(LINE) from oracleblog1; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.04 Execution Plan ---------------------------------------------------------- Plan hash value: 4099259911 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2176 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| ORACLEBLOG1 | 338K| 1322K| 2176 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7886 consistent gets 7882 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> |
我们很奇怪的发现，3次运行，时间在4秒左右，consistent gets为7886，始终有physical reads。这是怎么回事？为什么始终有physical reads？
这其实是11g之后的新特性，大表就不经过缓存，直接走direct path read。为了避免该特性影响对比，我们用event将其屏蔽。
|
1 2 3 4 5 6 |
SQL> alter session set events '10949 trace name context forever, level 1'; Session altered. Elapsed: 00:00:00.00 SQL> |
（1.1）屏蔽direct path read后，再次测试用cache的情况
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
SQL> select /* use cache */ max(LINE) from oracleblog1; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.10 Execution Plan ---------------------------------------------------------- Plan hash value: 4099259911 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2176 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| ORACLEBLOG1 | 338K| 1322K| 2176 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 39 recursive calls 0 db block gets 7936 consistent gets 7883 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select /* use cache */ max(LINE) from oracleblog1; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 4099259911 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2176 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| ORACLEBLOG1 | 338K| 1322K| 2176 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7892 consistent gets 0 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> select /* use cache */ max(LINE) from oracleblog1; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 4099259911 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2176 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| ORACLEBLOG1 | 338K| 1322K| 2176 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7892 consistent gets 0 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> |
我们看到基本的运行时间在3秒左右。consistent gets为7892，无physical reads。
好，轮到主角登场，我们来看看。
（2）测试用inmemory option的情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
SQL> select /* use inmemory */ max(LINE) from oracleblog2; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1514125655 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 4 (100)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS INMEMORY FULL| ORACLEBLOG2 | 338K| 1322K| 4 (100)| 00:00:01 | ------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 208 recursive calls 0 db block gets 158 consistent gets 0 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 16 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SQL> select /* use inmemory */ max(LINE) from oracleblog2; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1514125655 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 4 (100)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS INMEMORY FULL| ORACLEBLOG2 | 338K| 1322K| 4 (100)| 00:00:01 | ------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SQL> select /* use inmemory */ max(LINE) from oracleblog2; MAX(LINE) ---------- 11574 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1514125655 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 4 (100)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS INMEMORY FULL| ORACLEBLOG2 | 338K| 1322K| 4 (100)| 00:00:01 | ------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 545 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> |
运行时间几乎是0秒，且consistent gets只有3！没有physical reads。
注意上面执行计划中的 TABLE ACCESS INMEMORY FULL，是显示了使用哦inmemory option。
太强大了，我已经不知道该说什么好了……
==== update 2014-06-18=========================
应木匠同学的要求，再加一次display_cursor和statistics_level=all测试。:)
这次用了4100多万的数据。测试如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 |
SQL> select cache,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,table_name from dba_tables where table_name in ('ORASUP1','ORASUP2'); CACHE INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION TABLE_NAME -------------------- ----------------- ------------------- -------------------- -------------------------------------------------------------------------------- N NONE AUTO DISTRIBUTE FOR QUERY ORASUP1 Y ORASUP2 Executed in 0.624 seconds SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> select /* use cache */ max(line) from orasup1; MAX(LINE) ---------- 11883 Executed in 1.107 seconds SQL> select /* use cache */ max(line) from orasup1; MAX(LINE) ---------- 11883 Executed in 1.311 seconds SQL> select /* use cache */ max(line) from orasup1; MAX(LINE) ---------- 11883 Executed in 1.373 seconds SQL> SQL> SQL> SQL> SQL> select /* use inmemory */ max(line) from orasup2; MAX(LINE) ---------- 11883 Executed in 8.736 seconds SQL> select /* use inmemory */ max(line) from orasup2; MAX(LINE) ---------- 11883 Executed in 10.062 seconds SQL> select /* use inmemory */ max(line) from orasup2; MAX(LINE) ---------- 11883 Executed in 9.906 seconds SQL> SQL> select * from table(dbms_xplan.display_cursor('49ks122w2x163',0,'ADVANCED')); SQL_ID 49ks122w2x163, child number 0 ------------------------------------- select /* use inmemory */ max(line) from orasup1 Plan hash value: 1818600857 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS INMEMORY FULL| ORASUP1 | 324K| 1266K| 1 (100)| 00:00:01 | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / ORASUP1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "ORASUP1"@"SEL$1") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) MAX("LINE")[22] 2 - (rowset=200) "LINE"[NUMBER,22] SQL> SQL> select * from table(dbms_xplan.display_cursor('49ks122w2x163',0,'ALLSTATS LAST')); SQL_ID 49ks122w2x163, child number 0 ------------------------------------- select /* use inmemory */ max(line) from orasup1 Plan hash value: 1818600857 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.25 | 11 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.25 | 11 | | 2 | TABLE ACCESS INMEMORY FULL| ORASUP1 | 1 | 324K| 41M|00:00:00.51 | 11 | ------------------------------------------------------------------------------------------------- SQL> SQL> SQL> SQL> SQL> SQL> select * from table(dbms_xplan.display_cursor('2ahgf7ap509ty',0,'ADVANCED')); SQL_ID 2ahgf7ap509ty, child number 0 ------------------------------------- select /* use cache */ max(line) from orasup2 Plan hash value: 2938134919 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 255K(100)| | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| ORASUP2 | 41M| 158M| 255K (1)| 00:00:10 | ------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / ORASUP2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "ORASUP2"@"SEL$1") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) MAX("LINE")[22] 2 - (rowset=200) "LINE"[NUMBER,22] SQL> SQL> select * from table(dbms_xplan.display_cursor('2ahgf7ap509ty',0,'ALLSTATS LAST')); SQL_ID 2ahgf7ap509ty, child number 0 ------------------------------------- select /* use cache */ max(line) from orasup2 Plan hash value: 2938134919 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:10.43 | 941K| | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:10.43 | 941K| | 2 | TABLE ACCESS FULL| ORASUP2 | 1 | 41M| 41M|00:00:09.34 | 941K| ---------------------------------------------------------------------------------------- |
可以看到bytes还是有变化的。
3条评论
能不能用 RunStats 或者 dbms_xplan.display_cursor + statistics_level = ALL , 再跑一边 ? 看看详细的资源消耗.
两个 execution plan 里面, 都是Bytes 1322K. 鄙人以为 应该有区别.
Thanks,
木匠
你好，关于Oracle12.1.0.2的测试问题
http://www.itpub.net/forum.php?mod=viewthread&tid=1880862&extra=
这里描述很清楚，希望能够得到您的指点
您好 我安装了oracle12c 并且默认创建了一个pdb 名字为pdborcl，但是不知道该如何使用pl/sql developer或者sql developer连接pdb呢？ 如能告知：感激不尽！
yanwushu@163.com