性能调优数据库share pool的调整

--shared pool的组成Library cache Data dictionary cache

show parameter shared_pool_size

desc v$sgastat

select * from v$sgastat;

--free memory的大小，library cache的大小

--oracle的soft parse是非常严格的以下都不可以soft parse

SELECT * FROM employess;

SELECT * FROM Employess;

SELECT *  FROM employess;

desc v$sysstat

col name format a30

select * from v$sysstat;

--parse count(hard)

--查看hard parse的次数

select * form. v$sysstat where name = 'parse count(hard)';

--实验，什么时候会有硬解析

--另一个用户

drop table t;

create table t(id int,name char(10));

insert into t values(0,'**e');

insert into t values(1,'**e');

insert into t values(2,'**e');

commit;

select * from t;

--第一个用户

select * form. v$sysstat where name = 'parse count(hard)';

--另一个用户

select * from t where t.id = 0;

--第一个用户

select * form. v$sysstat where name = 'parse count(hard)';

--另一个用户

select * from t where t.id = 0;

--第一个用户，数据没变

select * form. v$sysstat where name = 'parse count(hard)';

--另一个用户

select * from t where t.id = 1;

--第一个用户,数据变了

select * form. v$sysstat where name = 'parse count(hard)';

show parameter cursor_sharing

--另一个用户

insert into t values(3,'**e');

--第一个用户

--将cursor_sharing改为similar后，会降低soft parse的门槛

alter system set cursor_sharing = 'similar';

show parameter cursor_sharing

select * form. v$sysstat where name = 'parse count(hard)';

--另一个用户

select * from t where t.id < 2;

--第一个用户,数据变了

select * form. v$sysstat where name = 'parse count(hard)';

--另一个用户

select * from t where t.id < 3;

--第一个用户,数据没有变

select * form. v$sysstat where name = 'parse count(hard)';

--默认还是exact，

alter system set cursor_sharing = 'exact';

--实验，hard parse 和soft parse 的效率

drop talbe m;

create table m (x int);

create or replace procedure proc1

as

begin

for i in 1..10000

loop

execute immediate

'insert into m values(:x)' using i;

end loop;

end;

/

create or replace procedure proc2

as

begin

for i in 1..10000

loop

execute immediate

'insert into m values('||i||')';

end loop;

end;

/

--显示执行的时间，这个挺有用

set timing on

exec proc2;

select count(1) from m;

truncate table m;

exec proc1;

select count(1) from m;

--重要的library cache性能表，各个列要好好的看

desc v$librarycache

select * from v$librarycache;

--gets要parse的次数

--gethits，parse中soft parse的次数

--pins，sql语句执行要执行的次数

--pinhits，sql实际执行的次数

--reload，sql需要重载的次数，越少越好

--invalidations，sql失效的次数，object被DLL语句操作以后会出现

select namespace,gets,gethits,pins,pinhits,reloads,invalidations from v$librarycache;

col namespace format a10;

/

--sql area

select *from v$sgastat;

--free memory,如果足够，就不用调整share pool的大小

select * from v$sql;

select * from v$sqlarea;

--执行次数小于5次的sql语句

select sql_text from v$sqlarea where executions < 5 order by upper(sql_text);

--硬解析和执行的次数，如果很接近就是重用的不好

select sql_text,parse_calls,executions from v$sqlarea order by parse_calls;

select * from v$sqltext;

select * from v$db_object_cache;

--share pool内部组成的分配不能控制，由oracle控制，我们只要给总的大小

show parameter shared_pool_size

show parameter open_cursors

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

--命中率越高越好，接近1，越高重用越好 

select gethitratio from v$librarycache where namepase = 'SQL AREA';

--执行的SQL语句

select sql_text,users_executing,executions,loads from v$sqlarea;

--针对具体的语句查询

select * from v$sqltext where sql_text like 'select * from hr,employees where %';

--显示表结构

desc v$librarycache

--此表前四项有用，reload和invalidation越低越好

select namespace,gethits,pinhits,reloads,invalidations from v$librarycache;

col namespace format a15

select namespace,gethits,pinhits,reloads,invalidations from v$librarycache;

--这个最好小于1%，不然要扩展shared_pool

select sum(pins) "executions",sum(reloads) "cache misses",sum(reloads)/sum(pins) from v$librarycache;

--invalidations,增加的例子

select namespace,gets,gethits,pins,pinhits,reloads,invalidations from v$librarycache;

execute dbms_stats.gather_table_stats('HR','EMPLOYEES');

select namespace,gets,gethits,pins,pinhits,reloads,invalidations from v$librarycache;

--share_pool建议值提升的效果

select shared_pool_size_for_estimate as pool_size,estd_lc_size,estd_lc_time_saved from v$shared_pool_advice;

--与v$sql 的plan_hash_value对应，联合查询

select operation,object_owner,cost from v$sql_plan order by hash_value;

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

--以下两个之和可以估算大致的library_cache情况，要在符合下测试

--计算所有非SQL语句的对象所占用的共享内存

select sum(sharable_mem) from v$db_object_cache;

--计算SQL对象所占据的share pool

select sum(sharable_mem) from v$sqlarea where executions >5;

--应急存款的大小，一般是share pool的5%
show parameter shared_pool_reserved_size

--存款的视图

desc v$shared_pool_reserved

--第一个值大，后面的值为0才好

select free_space,REQUESTS,REQUEST_MISSES,REQUEST_FAILURES from v$shared_pool_reserved

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

--查看在cache中的没有被固定的对象

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('PACKAGE_NAME');

--清除share pool中的对象，但是固定的对象不动

alter system flush shared_pool;

--library 中有哪些sql块，47是匿名块的类型

select sql_text from v$sqlarea where command_type=47 and length(sql_text) >500;

v$rowcache

--查看命中率，接近100%才好

col parameter format a21

col pct_succ_gets format 999.9

col updates format 999,999,999

 select PARAMETER,GETS,GETMISSES from v$rowcache;

select parameter

,sum(gets)

,sum(getmisses)

,100*sum(gets-getmisses)/sum(gets) pct_succ_gets

,sum(modifications) updates

from v$rowcache

where gets >0

group by parameter;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15681893/viewspace-710190/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15681893/viewspace-710190/