共享池之SQL
--共享池中保留区的信息查询select * from v$shared_pool_reserved
--查询hard parse比率
select a.value as Total,b.value as "Hard Pase",
round(b.value/a.value,2) as Ratio
from v$sysstat a,v$sysstat b
where a.name='parse count(total)' and b.name='parse count(hard)';
--查询哪些SQL语句没有使用绑定变量
select d.plan_hash_value plan_hash_value,
d.execnt execnt,
a.hash_value hash_value,
a.sql_text sql_text
from v$sqltext a,
(select plan_hash_value,hash_value,execnt
from (select c.plan_hash_value,
b.hash_value,
c.execnt,
rank() over(partition by c.plan_hash_value order by
b.hash_value) as hashrank
from v$sql b,
(select count(*) as execnt,plan_hash_value
from v$sql
where plan_hash_value 0
group by plan_hash_value
having count(*)>10
order by count(*) desc) c
where b.plan_hash_value=c.plan_hash_value
group by c.plan_hash_value,b.hash_value,c.execnt)
where hashrank1
order by loads desc;
--使用内存超出了5MB，但并没有被保留在内存内
select owner,name,sharable_mem,kept
from v$db_object_cache
where sharable_mem>51200
and kept='NO'
order by sharable_mem desc;
--利用v$sql查询出使用频率最高的10句sql
select sql_text,executions
from (select sql_text,executions,rank() over(order by executions desc) exec_rank
from v$sql)
where exec_rank100000
order by a.disk_reads desc
--利用v$sql查出查询的SQL语句的address和hash_value
select sql_text,address,hash_value from v$sql
where sql_text like 'SELECT * from EMP %';
--利用查出的address和hash_value，配合v$sql_plan相对应的字段，即可查询出此SQL语句的执行计划
select operation,options,object_name,cost from v$sql_plan
where address='325576612' and hash_value=2234134134
--命中率
select namespace,gets,gethitratio*100 "GET%",
pins,pinhitratio*100 "PIN%",reloads
invalidations
from v$librarycache;
页:
[1]