oracle v$视图

grant select on v_$session to hr;

select view_name,view_definition from v$fixed_view_definition
where view_name = 'V$SESSION';


select count(*) from v$fixed_table where  name like 'V$%';

select count(*) from v$fixed_table where name like 'GV%';

select * from v$fixed_table;


select * from dba_views;

set long 2000000;
select text from dba_views where view_name = 'DBA_IND_PARTITIONS';

--DBA_视图实际上是从oracle底层数据库的表中来得到的(当然有的也从x$表中得到)，
--下面的程序清单通过访问dba_views来查看组成dba_视图的对象。

--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);
--基本的许可信息
select * from v$license;
--数据库中已安装的产品项
select * from v$option;
--内存分配摘要
select * from v$sga;
--内存分配的细节(v$sysstat)
select * from v$sgastat;

--数据库的命中率
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;
--用v$db_cache_advice视图来帮助改变数据缓存的大小

select size_for_estimate,
       buffers_for_estimate,
       estd_physical_read_factor,
       estd_physical_reads
  from v$db_cache_advice
 where name = 'DEFAULT'
   and block_size =
       (select value from v$parameter where name = 'db_block_size');

-----------------------------
测试数据字典的命中率(v$rowcache),推荐命中率是95%或则更高，如果低于这个百分比，说明可能要增加init.ora参数
shared_pool_size.
select sum(gets),
       sum(getmisses),
       (1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 HitRate
  from v$rowcache;

--测定共享SQL和PL/SQL的命中率(v$librarycache),推荐固定对象的命中率是95%以上,重载命中率为99%.
查询v$librarycache,看看是否重用SQL：
select sum(pins) "Executions",
       sum(pinhits) "Hits",
       ((sum(pinhits) / sum(pins)) * 100) "PinHitRation",
       sum(reloads) "Misses",
       ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "RelHitRatio"
  from v$librarycache;
查询v$sql_bind_capture,看看average binds 是否大于15(issue)
select sql_id, count(*) bind_count
  from v$sql_bind_capture
 where child_number = 0
 group by sql_id
having count(*) > 20
 order by count(*);
查找有问题的SQL并修复它：
select sql_text, users_executing, executions, users_opening, buffer_gets
  from v$sqlarea
 where sql_id = '7cmfnsu39dyuh'
 order by buffer_gets;
查询v$sql_bind_capture,看看average binds 是否大于15(issue)

select avg(bind_count) AVG_NUM_BINDS
  from (select sql_id, count(*) bind_count
          from v$sql_bind_capture
         where child_number = 0
         group by sql_id);
确定需要固定的PL/SQL对象,搜索那些所需空间大于100KB的对象；

select name, sharable_mem
  from v$db_object_cache
 where sharable_mem > 100000
   and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
   and kept = 'NO';

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 > 100000
 order by a.disk_reads desc;

--使用索引
select * from v$object_usage;
在监控任何索引前，这个视图没有任何记录：
alter index ind_hrdt monitoring usage;
select index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
  from v$object_usage;
结束对索引监控
alter index ind_hrdt monitoring usage;

--确定锁问题
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;