所以，做了下面这个实验，看是否能够通过将图层或索引设置为Keep Pool，保持在内存中，提高图层的查询效率。
实验环境： 虚拟机（2核/3G内存）， 数据库Oracle 11.2.0.1，ArcSDE 10.0，一个包含大约35万个多边形要素的Shp图层
步骤一：环境准备
在虚拟机里安装完成Oracle和ArcSDE后，连接到Oracle，首先查看并修改相关的参数 查看SGA中Buffer cache 大小及其它信息SQL> select * from v$sgainfo; NAME BYTES RESIZEABLE -------------------------------- ---------- ---------- Fixed SGA Size 2180544 No Redo Buffers 6549504 No Buffer Cache Size 310378496 Yes Shared Pool Size 226492416 Yes Large Pool Size 4194304 Yes Java Pool Size 4194304 Yes Streams Pool Size 4194304 Yes Shared IO Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 855982080 No Startup overhead in Shared Pool 71303168 No Free SGA Memory Available 297795584 12 rows selected
查询KeepPool大小 SQL> show parameter db_keep_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_keep_cache_size big integer 0
默认为0，现手动修改大小 SQL> alter system set db_keep_cache_size=250M scope=both sid='orcl'; System altered
再查看KeepPool SQL> show parameter db_keep_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_keep_cache_size big integer 252M
步骤二：图层导入
将图层导入ArcSDE中，并拷贝两份，分别命名为：sde_ori sde_index_mem sde_all_mem，各表情况如下| 图层名 | 空间索引名 | 索引表名 |
| sde_ori | A2_IX1 | s2_idx$ |
| sde_index_mem | A4_IX1 | s4_idx$ |
| sde_all_mem | A5_IX1 | s5_idx$ |
sde_ori表保持原状 将sde_index_mem图层的索引表均调入内存，alter table s4_idx$ storage(buffer_pool keep); 将sde_all_mem图层的主表及索引表均调入内存，alter table sde_all_mem storage(buffer_pool keep);alter table s5_idx$ storage(buffer_pool keep);
步骤三：空间查询实验
1.首次查询
清空缓存： alter system flush shared_pool; alter system flush buffer_cache;对这三个表进行一次空间查询，构造一个长方形，坐标为（90，30）（93，30）（93，33）（90，33），选择图层中与之相交的元素，返回ID 首先是原表sde_ori: SQL> select dataid from sde_ori where sde.st_intersects(shape,sde.st_geometry('POLYGON((90 30,93 30,93 33,90 33,90 30))',2))=1;
已用时间: 00: 00: 09.23
统计信息 ---------------------------------------------------------- 17522 recursive calls 150 db block gets 12924 consistent gets 2110 physical reads 0 redo size 42237 bytes sent via SQL*Net to client 1235 bytes received via SQL*Net from client 67 SQL*Net roundtrips to/from client 501 sorts (memory) 0 sorts (disk) 984 rows processed
然后是将索引放在内存中的sde_index_mem: SQL> select dataid from sde_index_mem where sde.st_intersects(shape,sde.st_geometry('POLYGON((90 30,93 30,93 33,90 33,90 30))',2))=1;
已选择984行。
已用时间: 00: 00: 08.18
统计信息 ---------------------------------------------------------- 2832 recursive calls 150 db block gets 5024 consistent gets 1665 physical reads 0 redo size 42237 bytes sent via SQL*Net to client 1235 bytes received via SQL*Net from client 67 SQL*Net roundtrips to/from client 18 sorts (memory) 0 sorts (disk) 984 rows processed
最后是主表及索引表均放入内存的sde_all_mem: SQL> select dataid from sde_all_mem where sde.st_intersects(shape,sde.st_geometry('POLYGON((90 30,93 30,93 33,90 33,90 30))',2))=1;
已选择984行。
已用时间: 00: 00: 06.98
统计信息 ---------------------------------------------------------- 3650 recursive calls 150 db block gets 5242 consistent gets 1612 physical reads 0 redo size 42237 bytes sent via SQ