13 rows selected

对于经常访问的表,索引等其它对象，可以将表固定在keep buffer cache中,但需要先设置db_keep_cache_size

SQL> alter system set db_keep_cache_size=1M;

System altered

SQL> show parameter keep;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_keep string

control_file_record_keep_time integer 7

db_keep_cache_size big integer 12M

SQL> select buffer_pool from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';

BUFFER_POOL

-----------

DEFAULT

SQL> conn hr/hr

Connected.

SQL> alter table employees storage (buffer_pool keep);

Table altered.

SQL> select buffer_pool from user_tables t where t.table_name='EMPLOYEES';

BUFFER_POOL

---------------------

KEEP

可以将用户常用的代码固定在库缓存中，用dbmspool.sql脚本提供dbms_share_pool包

SQL> @/u01/app/oracle/rdbms/admin/dbmspool.sql;

Package created.

Grant succeeded.

View created.

Package body created

SQL> grant execute on dbms_shared_pool to hr;

Grant succeeded.

SQL> conn hr/hr

Connected.

SQL> exec sys.dbms_shared_pool.keep('HR.COMP_TAX');

PL/SQL procedure successfully completed.

若要创建非标块的的表空间，则需要设置非标块的default buffer cache；若将非标块的default buffer cache设置为0，则新建的非标块表空间无法创建表

SQL> create tablespace t04308_4k datafile '/u01/app/oradata/t04308_4k01.dbf' size 10M blocksize 4096;

create tablespace t04308_4k datafile '/u01/app/oradata/t04308_4k01.dbf' size 10M blocksize 4096

*

ERROR at line 1:

ORA-29339: tablespace block size 4096 does not match configured block sizes

SQL> alter system set db_4k_cache_size=1M;

System altered.

SQL> show parameter db_4k_cache_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_4k_cache_size big integer 12M

SQL> create tablespace t04308_4k datafile '/u01/app/oradata/t04308_4k01.dbf' size 10M blocksize 4096;

Tablespace created.

SQL> alter system set db_4k_cache_size=0;

System altered

SQL> create table hr.t043_test (a number) tablespace t04308_4k;

create table hr.t043_test (a number) tablespace t04308_4k

*

ERROR at line 1:

ORA-00379: no free buffers available in buffer pool DEFAULT for block size 4K

三：手动配置SGA参数

若要将assm功能关闭，将sga_target设置为0即可

SQL> alter system set sga_target=0;

System altered.

SQL> show parameter db_cache_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_cache_size big integer 356M

四：配置自动PGA内存管理

PGA主要存放私有SQL区，游标参考点和活跃集，排序和哈希桶等，由pga_aggregate_target参数设定pga的大小

SQL> show parameter pga_aggre;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target big integer 186M

可以通过设置sort_area_size和workarea_size_policy参数将排序操作固定在内存中

SQL> show parameter sort_area_size;

NAME TYPE VALUE