--共享池中保留区的信息查询

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 hashrank<=3) d

where a.hash_value=d.hash_value

order by d.execnt desc,a.hash_value,a.piece;


--调整共享池大小参考视图

select * from v$shared_pool_advice


--查询保留区的统计信息

select * from V$shared_Pool_Reserved


--确认shared_pool_reserved_size的空间情况

select round(free_space/1024/1024,1) "FREE SPACE MB",

       request_misses,

       request_failures

from v$shared_pool_reserved


--确认shared_pool_reserved_size的命中率(以上结果应该都要小于1%)，否则应该加大shared_pool_reserved_size

select (request_misses/(requests+0.0001))*100 "REQUEST MISSES RATIO",

       (request_failures/(requests+0.0001))*100 "REQUEST FAILURES RATIO"

from v$shared_pool_reserved


--对象在库缓存中消耗的内存

select substr(owner,1,10) owner,

       substr(type,1,12) type,

       substr(name,1,20) name,

       executions,

       sharable_mem "MEMORY USED",

       substr(kept||' ',1,4) "KEEP"

from v$db_object_cache

where type in('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')

order by executions desc;


--查询对象被加载的次数，以及是否被保留到内存内

select owner,name sharable_mem,kept,loads

from v$db_object_cache

where loads>1

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_rank<=10


--利用v$sql查询出最耗费磁盘读的前10句SQL

select disk_reads,sql_text 

from (select sql_text,disk_reads,dense_rank() over(order by disk_reads desc) disk_reads_rank

       from v$sql)

where disk_reads_rank<=10


--利用v$sql查询出最耗费内存的前10句逻辑读SQL

select buffer_gets,sql_text

from (select sql_text,buffer_gets,dense_rank() over(order by buffer_gets desc) buffer_gets_rank

       from v$sql)

where buffer_gets_rank<=10


--利用v$sqlarea查出消耗资源最多的sql的hash_value

select hash_value,executions,buffer_gets,disk_reads,parse_calls

from v$sqlarea

where buffer_gets>10000000 or disk_reads>1000000

order by buffer_gets+100*disk_reads desc

--得到hash_value后，再利用v$sqltext查询出实际的sql语句

select address,hash_value,piece,sql_text

from v$sqltext

where hash_value='2450129223'

order by piece;


--利用v$sqlarea查询出磁盘读次数超过100000的查询语句，也就是最耗资源的SQL

select b.username "USER NAME",

       a.disk_reads "DISK READ",

       a.executions EXECUTIONS,

       a.disk_reads/decode(a.executions,0,1,a.executions) "READ_EXEC%",

       a.sql_text "SQL TEXT"

from v$sqlarea a,dba_users b

where a.parsing_user_id=b.user_id

and a.disk_reads>100000

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;