有用なv$ビュースクリプト

1、        
    ：
select * from v$version;
     ：
Select * from v$database;

2、            (AWR)     
AWR       ？ 
Select occupant_name, occupant_desc, space_usage_kbytes
  from v$sysaux_occupants
 where occupant_name like '%AWR%';

        AWR      ？ 
select dbms_stats.get_stats_history_availability from dual;

    AWR       ？ 
select dbms_stats.get_stats_history_retention from dual;

  AWR           15  ？ 
EXEC dbms_stats.alter_stats_history_retention(15);

3、       
select * from v$license;


4、           
select * from v$option;

5、      
select * from   v$sga;

       
select * from v$sgastat;

6、V$PARAMETER   init.ora    
select * from v$parameter;

7、        
select 1 - (sum(decode(name, 'physical reads', value, 0)) /
       (sum(decode(name, 'db block gets', value, 0)) +
       (sum(decode(name, 'consistent gets', value, 0))))) "Read Hit Ratio"
  from v$sysstat;


          
select sum(gets),
       sum(getmisses),
       (1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 HitRate
  from v$rowcache;

   SQL  PL/SQL    
select sum(pins) "Executions",
       sum(pinhits) "Hits",
       ((sum(pinhits) / sum(pins)) * 100) "PinHitRatio",
       sum(reloads) "Misses",
       ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "RelHitRatio"
  from v$librarycache;


  V$SQLAREA        
select b.username username,
       a.disk_reads reads,
       a.executions exec,
       a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,
       a.command_type,
       a.sql_text Statement
  from v$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
   and a.disk_reads > 10000
 order by a.disk_reads desc;

                
select a.sid, a.username, s.sql_text
  from v$session a, v$sqltext s
 where a.sql_address = s.address
   and a.sql_hash_value = s.hash_value
 order by a.username, a.sid, s.piece;


                    
select a.username,
       b.block_gets,
       b.consistent_gets,
       b.physical_reads,
       b.block_changes,
       b.consistent_changes
  from v$session a, v$sess_io b
 where a.sid = b.sid
 order by a.username;


           
select a.sid, a.username, b.owner, b.object, b.type
  from v$session a, v$access b
 where a.sid = b.sid;


      
select /*+ ordered */
 b.username, b.serial#, d.id1, a.sql_text
  from v$lock d, v$session b, v$sqltext a
 where b.lockwait = d.kaddr
   and a.address = b.sql_address
   and a.hash_value = b.sql_hash_value;

select /*+ ordered */
 a.serial#, a.sid, a.username, b.id1, c.sql_text
  from v$lock b, v$session a, v$sqltext c
 where b.id1 in (select /*+ ordered */
                 distinct e.id1
                   from v$lock e, v$session d
                  where d.lockwait = e.kaddr)
   and a.sid = b.sid
   and c.hash_value = a.sql_hash_value
   and b.request = 0;

select /*+ ordered */
 username,
 v$lock.sid,
 trunc(id1 / power(2, 16)) rbs,
 bitand(id1, to_number('ffff', 'xxxx')) + 0 slot,
 id2 seq,
 lmode,
 request
  from v$lock, v$session
 where v$lock.type = 'TX'
   and v$lock.sid = v$session.sid;


        
select   username, sid, serial#, program, terminal 
from     v$session;

alter system kill session '11,18';