一、shared pool的组成
3块区域:free、library cache、row cache
select * from v$sgastat a where a.NAME='library cache';
select * from v$sgastat a where a.pool='shared pool' and a.NAME='free memory';
select * from v$sgastat a where a.NAME='row cache';
二、硬解析、软解析
硬解析步骤、软解析步骤
讲解shared pool内存块组成结构
ora-4031错误
select count(*) from x$ksmsp;
select count(*) from dba_objects;
select count(*) from x$ksmsp;
alter system flush shared_pool;
软硬解析的具体情况
select name,value from v$sysstat where name like 'parse%'
三、SQL共享,绑定变量
SQL语句组成,动态部分、静态部分
cursor_sharing
delare
vl varchar2(10);
nl int;
begin
nl:=1;
select salary into vl from test where id=n1;
end;
select SQL_ID,sql_text,EXECUTIONS from v$sql where SQL_TEXT like
四、找出没有共享的SQL语句
如何找出不能共享cursor的sql
在v$sql查找执行次数较小的sql语句,观察这些sql语句是否是经常执行的。
select SQL_FULLTEXT from v$sql where EXECTIONS=1 and sql_text like '%from t%';
select SQL_FULLTEXT from v$sql where EXECTIONS=1 order by sql_text;
五、解析命中率
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcache where gets>0;
六、解决4031错误的办法
1、alter system flush shared_pool;
2、共享SQL
3、select * from v$db_object_cache where sharable_mem > 10000
and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type = 'PROCEDURE')
and kept = 'NO';
执行dbms_shared_pool.keep('对象名');
DBMS_SHARED_POOL
@?/rdbms/admin/dbmspool.sql
4、保留区
select REQUEST_MISSES from v$shared_pool_reserved;
5、增加shared pool空间
select COMPONENT.CURRENT_SIZE from v$sga_dynamic_components;
show parameter sga_target;
show parameter sga_max_target;
alter system set shared_pool_size=150M scope=both;
七、查看执行计划
select * from table(dbms_xplan.display_cursor('07hpk6hpb7pp8'));