查看当前分配的大小: SQL> select component,current_size from v$sga_dynamic_components where component='KEEP buffer cache'; COMPONENT CURRENT_SIZE ---------------------------------------------------------------- ------------ KEEP buffer cache 54525952 SQL> select name,current_size from v$buffer_pool; NAME CURRENT_SIZE -------------------- ------------ KEEP 52 DEFAULT 84 修改当前keep分配的大小 SQL> alter system set db_keep_cache_size=30M scope=both; 再查询: SQL> select component,current_size from v$sga_dynamic_components where component='KEEP buffer cache'; SQL> select name,current_size from v$buffer_pool; ................................................. recycle pool: SQL> select component,current_size from v$sga_dynamic_components where component='RECYCLE buffer cache'; COMPONENT CURRENT_SIZE ---------------------------------------------------------------- ------------ RECYCLE buffer cache 0 SQL> select name,current_size from v$buffer_pool; NAME CURRENT_SIZE -------------------- ------------ KEEP 56 DEFAULT 80 SQL> alter system set db_recycle_cache_size=10M scope=both; alter system db_recycle_cache_size=10M scope=both SQL> select name,current_size from v$buffer_pool; NAME CURRENT_SIZE -------------------- ------------ KEEP 56 RECYCLE 12 DEFAULT 68 ........................ shared_pool: 查看 shared _pool 参数默认值 SQL> show parameter shared_pool_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_pool_size big integer 0 查看当前运行期间分配给shared_pool的大小 SQL> select pool,sum(bytes) from v$sgastat group by pool; POOL SUM(BYTES) ------------ ---------- 142606188 java pool 4194304 shared pool 58724948 large pool 4194304 Library cache:(SQL语句缓冲) SQL> select sum(sharable_mem) from v$db_object_cache; SUM(SHARABLE_MEM) ----------------- 7416202 实例引证: SQL> set timing on SQL> alter system flush shared_pool; Elapsed: 00:00:00.13 SQL> select count(*) from dba_objects; COUNT(*) ---------- 50332 Elapsed: 00:00:00.19 查看保存在library cache中保存的已分析的SQL语句: SQL> select * from v$sqltext where sql_text like '%dba_object%'; ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE -------- ---------- ------------- ------------ ---------- SQL_TEXT ---------------------------------------------------------------- 3F952FD0 2935929963 g4pkmrqrgxg3b 3 0 select count(*) from dba_objects 3FA3D868 2816052306 024c6akmxm32k 3 0 select * from v$sqltext where sql_text like '%dba_object%' Elapsed: 00:00:00.09 SQL> c/*/sql_text 1* select sql_text from v$sqltext where sql_text like '%dba_object%' SQL> / SQL_TEXT ---------------------------------------------------------------- select count(*) from dba_objects select * from v$sqltext where sql_text like '%dba_object%' select sql_text from v$sqltext where sql_text like '%dba_object% Elapsed: 00:00:00.06 Data Dictionary cache(数据字典缓冲): SQL> select sum(sharable_mem) from v$sqlarea; SUM(SHARABLE_MEM) ----------------- 3620010 Elapsed: 00:00:00.03 select * from dict;查看数据字典 select object_name,object_type,created from dba_objects where object_name in ('SC','PROC1'); select username from dba_users; 测试Data dictionary cache的作用 set autot on stat select count(*) from dba_source; 查看recursive calls次数 select count(*) from dba_source; 再查recursive calls次数 数据字典中表的分类: SQL> select count(*) from dict where table_name like'DBA%'; dba_*:当前数据库中所有数据库对象的描述. COUNT(*) ---------- 515 SQL> select count(*) from dict where table_name like'ALL%'; all_*当前用户能够访问的DB对象的描述 COUNT(*) ---------- 272 SQL> select count(*) from dict where table_name like'USER%'; user_*当前用户所拥有的DB对象的描述 COUNT(*) ---------- 285