ORACLE常用性能监控SQL

-- 感谢CSDN用户:小小工匠 https://blog.csdn.net/yangshangwei/article/details/52449489

/*死锁后的解决办法
如果死锁不能自动释放，就需要我们手工的 kill session
生成Kill Session语句
查看有无死锁对象，如有 kill session
*/
SELECT ‘alter system kill session ‘‘‘ || sid || ‘,‘ || serial# || ‘‘‘;‘ "Deadlock"
  FROM v$session
 WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

--  如果有，会返回类似与如下的信息：
alter system kill session ‘761,876‘;
--  kill session： 执行 alter system kill session ‘761,876’(sid 为 761);
--  注意： 应当注意对于 sid 在 100 以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以 kill

--  查看导致死锁的 SQL
SELECT s.sid,q.sql_text
  FROM v$sqltext q,v$session s
 WHERE q.address = s.sql_address
   AND s.sid = &sid -- 这个&sid 是第一步查询出来的
 ORDER BY piece;

--  执行后，输入对应的sid即可查看对应的sql.
--  查看谁锁了谁
SELECT s1.username || ‘@‘ || s1.machine || ‘ ( SID=‘ || s1.sid ||
       ‘ ) is blocking ‘ || s2.username || ‘@‘ || s2.machine || ‘ ( SID=‘ ||
       s2.sid || ‘ ) ‘ AS blocking_status
  FROM v$lock l1,v$session s1,v$lock l2,v$session s2
 WHERE s1.sid = l1.sid
   AND s2.sid = l2.sid
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l2.id2 = l2.id2;

--  或者
--  推荐这个，因为使用的是 v$locked_object
SELECT LPAD(‘ ‘,DECODE(l.xidusn,0,3,0)) || l.oracle_username User_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#
  FROM v$locked_object l,dba_objects o,v$session s
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
 ORDER BY o.object_id,xidusn DESC;

--  V$LOCKED_OBJECT只能报发生等待的表级锁,不能报发生等待的行级锁。
--  ORA-00054 资源正忙，要求指定 NOWAIT  演示：

select * from emp for update; --通过for update 获取一个排它锁
SQL >
  select object_name as 对象名称,s.serial#,p.spid as 系统进程号
    from v$locked_object l,v$session s,v$process p
   where l.object_id = o.object_id
     and l.session_id = s.sid
     and s.paddr = p.addr;

--  在另外一个会话中执行
ALTER SYSTEM KILL SESSION ‘1411,8865‘;

--  查询绑定变量使用的实际值,SQL还在shared pool中，没有被aged out 替换SQL ID 值即可

select sql_id,name,datatype_string,last_captured,value_string
  from v$sql_bind_capture
 where sql_id = ‘7nqt558g5gmyr‘
 order by LAST_CAPTURED,POSITION;
--  请自行替换sql_id，此时是从awr中查询(sql 被 aged out 出 shared pool)

select instance_number,sql_id,value_string
  from dba_hist_sqlbind
 where sql_id = ‘fahv8x6ngrb50‘
 order by LAST_CAPTURED,POSITION;

--  监控事例的等待
select event,sum(decode(wait_Time,1)) "Prev",1,0)) "Curr",count(*) "Tot"
  from v$session_Wait
 group by event
 order by 4;

--  回滚段的争用情况
select name,waits,gets,waits / gets "Ratio"
  from v$rollstat a,v$rollname b
 where a.usn = b.usn;

--  查看回滚段名称及大小
SELECT segment_name,tablespace_name,r.status,(initial_extent / 1024) initialextent,(next_extent / 1024) nextextent,max_extents,v.curext curextent
  FROM dba_rollback_segs r,v$rollstat v
 WHERE r.segment_id = v.usn(+)
 ORDER BY segment_name;

--  查看控制文件
SELECT NAME FROM v$controlfile;

--  查看日志文件
SELECT MEMBER FROM v$logfile;

--  查看前台正在发出的SQL语句
select user_name,sql_text 　　
  from v$open_cursor 　　
 where sid in (select sid
                 from (select sid,serial#,username,program 　　
                         from v$session 　　
                        where status = ‘ACTIVE‘));

--  数据表占用空间大小情况
select segment_name,bytes,blocks
  from user_segments
 where segment_type = ‘TABLE‘
 ORDER BY bytes DESC,blocks DESC;

--  查看表空间碎片大小
select tablespace_name,round(sqrt(max(blocks) / sum(blocks)) *
             (100 / sqrt(sqrt(count(blocks)))),2) FSFI
  from dba_free_space
 group by tablespace_name
 order by 1;

--  查看表空间占用磁盘情况
select b.file_id 文件ID号,b.tablespace_name 表空间名,b.bytes 字节数,(b.bytes - sum(nvl(a.bytes,0))) 已使用,sum(nvl(a.bytes,0)) 剩余空间,0)) / (b.bytes) * 100 剩余百分比
  from dba_free_space a,dba_data_files b
 where a.file_id = b.file_id
 group by b.tablespace_name,b.file_id,b.bytes
 order by b.file_id;

--  查看表的大小，倒序排列
--  每张表都是作为“段”来存储的，可以通过user_segments视图查看其相应信息。 
--  段（segments）的定义：如果创建一个堆组织表，则该表就是一个段

SELECT SEGMENT_NAME,SUM(BYTES) / 1024 / 1024 MBYTESE
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = ‘TABLE‘
 GROUP BY SEGMENT_NAME
 order by MBYTESE desc;
--  查看表空间物理文件的名称及大小
SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024),0) total_space
  FROM dba_data_files
 ORDER BY tablespace_name;
--  查看Oracle 表空间使用率
SELECT D.TABLESPACE_NAME,SPACE || ‘M‘ "SUM_SPACE(M)",SPACE - NVL(FREE_SPACE,0) || ‘M‘ "USED_SPACE(M)",ROUND((1 - NVL(FREE_SPACE,0) / SPACE) * 100,2) || ‘%‘ "USED_RATE(%)",FREE_SPACE || ‘M‘ "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024),2) SPACE,SUM(BLOCKS) BLOCKS
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间  
SELECT D.TABLESPACE_NAME,USED_SPACE || ‘M‘ "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0) / SPACE * 100,NVL(FREE_SPACE,0) || ‘M‘ "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,SUM(BLOCKS) BLOCKS
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,ROUND(SUM(BYTES_USED) / (1024 * 1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE) / (1024 * 1024),2) FREE_SPACE
          FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 ORDER BY 1;

SELECT a.tablespace_name "表空间名",total "表空间大小",free "表空间剩余大小",(total - free) "表空间使用大小",total / (1024 * 1024 * 1024) "表空间大小(G)",free / (1024 * 1024 * 1024) "表空间剩余大小(G)",(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",round((total - free) / total,4) * 100 "使用率 %"
  FROM (SELECT tablespace_name,SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,(SELECT tablespace_name,SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

--  查看Temp 表空间实际使用磁盘大小
  Select f.tablespace_name,d.file_name "Tempfile name",round((f.bytes_free + f.bytes_used) / 1024 / 1024,2) "total MB",round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used,0)) / 1024 / 1024,2) "Free MB",round(nvl(p.bytes_used,0) / 1024 / 1024,2) "Used MB",round((round(nvl(p.bytes_used,2) /
               round((f.bytes_free + f.bytes_used) / 1024 / 1024,2)) * 100,2) as "Used_Rate(%)"
          from SYS.V_$TEMP_SPACE_HEADER f,DBA_TEMP_FILES           d,SYS.V_$TEMP_EXTENT_POOL  p
         where f.tablespace_name(+) = d.tablespace_name
           and f.file_id(+) = d.file_id
           and p.file_id(+) = d.file_id;


--  查看session使用回滚段
SELECT r.name 回滚段名,s.username 用户名,t.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,substr(s.program,1,78) 操作程序
  FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
 WHERE t.addr = s.taddr
   and t.xidusn = r.usn
 ORDER BY t.cr_get,t.phy_io;

--  查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.SID,segtype,blocks * 8 / 1000 "MB",sql_text
  from v$sort_usage sort,v$session sess,v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;
--  Temp表空间上进程的查询
select a.tablespace,b.sid,b.serial#,a.blocks,c.sql_text
  from v$sort_usage a,v$session b,v$sqltext c
 where a.session_addr = b.saddr
   and b.sql_address = c.address
 order by a.tablespace,c.address,c.piece;
--  查看SGA区剩余可用内存
select name,　　　　　　sgasize / 1024 / 1024　　　　　　　　"Allocated(M)",　　　　　　bytes / 1024　　　　　　　　　　　 "**空间(K)",　　　　　　round(bytes / sgasize * 100,2) 　　　"**空间百分比(%)" 　　
  from (select sum(bytes) sgasize from sys.v_$sgastat) s,sys.v_$sgastat f 　　
 where f.name = ‘free memory‘;
--  监控表空间I/O比例
select df.tablespace_name name,df.file_name       "file",f.phyrds           pyr,f.phyblkrd         pbr,f.phywrts          pyw,f.phyblkwrt        pbw
  from v$filestat f,dba_data_files df
 where f.file# = df.file_id
 order by df.tablespace_name;
--  监控SGA命中率
select a.value + b.value "logical_reads",c.value "phys_reads",round(100 * ((a.value + b.value) - c.value) / (a.value + b.value)) "BUFFER HIT RATIO"
  from v$sysstat a,v$sysstat b,v$sysstat c
 where a.statistic# = 38
   and b.statistic# = 39
   and c.statistic# = 40;
--  监控 SGA 中字典缓冲区的命中率
select parameter,Getmisses,getmisses / (gets + getmisses) * 100 "miss ratio",(1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 "Hit ratio"
  from v$rowcache
 where gets + getmisses <> 0
 group by parameter,getmisses;

--  监控 SGA **享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins",sum(reloads) "Total Reloads",sum(reloads) / sum(pins) * 100 libcache
  from v$librarycache;

--  监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name,misses,immediate_gets,immediate_misses,Decode(gets,0,misses / gets * 100) ratio1,Decode(immediate_gets + immediate_misses,0,immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2
  FROM v$latch
 WHERE name IN (‘redo allocation‘,‘redo copy‘);

--  监控内存和硬盘的排序比率，最好使它小于 .10
SELECT name,value
  FROM v$sysstat
 WHERE name IN (‘sorts (memory)‘,‘sorts (disk)‘);

--  监控字典缓冲区
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
  FROM V$ROWCACHE;

--  非系统用户建在SYSTEM表空间中的表
SELECT owner,table_name
  FROM DBA_TABLES
 WHERE tablespace_name in (‘SYSTEM‘,‘USER_DATA‘)
   AND owner NOT IN
       (‘SYSTEM‘,‘SYS‘,‘OUTLN‘,‘ORDSYS‘,‘MDSYS‘,‘SCOTT‘,‘HOSTEAC‘);

--  性能最差的SQL
SELECT *
  FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
          FROM v$sqlarea
         ORDER BY disk_reads DESC)
 WHERE ROWNUM < 100;

--  读磁盘数超100次的sql
select * from sys.v_$sqlarea where disk_reads > 100;

--  查找消耗资源比较的sql语句
Select se.username,se.sid,su.extents,su.blocks * to_number(rtrim(p.value)) as Space,tablespace,sql_text
  from v$sort_usage su,v$parameter p,v$session se,v$sql s
 where p.name = ‘db_block_size‘
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username,se.sid;

--  最频繁执行的sql
select * from sys.v_$sqlarea where executions > 100;

--  查询使用CPU多的用户session
select a.sid,spid,status,substr(a.program,40) prog,a.terminal,osuser,value / 60 / 100 value
  from v$session a,v$process b,v$sesstat c
 where c.statistic# = 12
   and c.sid = a.sid
   and a.paddr = b.addr
 order by value desc;

--  当前每个会话使用的对象数
SELECT a.sid,s.terminal,s.program,count(a.sid)
  FROM V$ACCESS a,V$SESSION s
 WHERE a.owner <> ‘SYS‘
   AND s.sid = a.sid
 GROUP BY a.sid,s.program
 ORDER BY count(a.sid);

--  查看数据库库对象
SELECT owner,object_type,COUNT(*) count#
  FROM all_objects
 GROUP BY owner,status;

--  查看数据库的版本　
SELECT version
  FROM product_component_version
 WHERE substr(product,6) = ‘Oracle‘;

--  查看数据库的创建日期和归档方式
SELECT created,log_mode,log_mode FROM v$database;

--  检查角色和权限设置
--  根据用户名进行授权的对象级特权
select b.owner || ‘.‘ || b.table_name obj,b.privilege what_granted,b.grantable,a.username
  from sys.dba_users a,sys.dba_tab_privs b
 where a.username = b.grantee
 order by 1,2,3;

--  根据被授权人进行授权的对象级特权
Select owner || ‘.‘ || table_name obj,privilege what_granted,grantable,grantee
  from sys.dba_tab_privs
 where not exists (select ‘x‘ from sys.dba_users where username = grantee)
 order by 1,3;

--  根据用户名进行授予的系统级特权
select b.privilege what_granted,b.admin_option,sys.dba_sys_privs b
 where a.username = b.grantee
 order by 1,2;

--  根据被授权人进行授予的系统级特权
select privilege what_granted,admin_option,grantee
  from sys.dba_sys_privs
 where not exists (select ‘x‘ from sys.dba_users where username = grantee)
 order by 1,2;

--  根据用户名授予的角色
select b.granted_role ||
       decode(admin_option,‘YES‘,‘ (With Admin Option)‘,null) what_granted,sys.dba_role_privs b
 where a.username = b.grantee
 order by 1;

--  根据被授权人授予的角色
select granted_role ||
       decode(admin_option,grantee
  from sys.dba_role_privs
 where not exists (select ‘x‘ from sys.dba_users where username = grantee)
 order by 1;

--  用户名及已被授予的相应权限
select a.username,b.granted_role ||
       decode(admin_option,null) what_granted
  from sys.dba_users a,sys.dba_role_privs b
 where a.username = b.grantee
UNION
select a.username,b.privilege ||
       decode(admin_option,sys.dba_sys_privs b
 where a.username = b.grantee
UNION
select a.username,b.table_name || ‘-‘ || b.privilege ||
       decode(grantable,‘ (With Grant Option)‘,sys.dba_tab_privs b
 where a.username = b.grantee
 order by 1;

--  查询用户名及相应的配置文件、默认的表空间和临时表空间
Select username,profile,default_tablespace,temporary_tablespace,created
  from sys.dba_users
 order by username;

--  等待事件V$视图
--  在Oracle 10g中V$SESSION_WAIT中的所有等待事件列现在都在V$SESSION中。因此，确保查询等待信息的 V$SESSION，因为它是一个更快的视图。V$ACTIVE_SESSION_HISTORY (ASH)将许多重要统计数据合并为一个视图或一个报表(ASH报表)。
--  马上该谁等待–查询V$SESSION_WAIT / V$SESSION
select event,sum(decode(wait_time,0)) "Waiting Now",1)) "Previous Waits",count(*) "Total"
  from v$session_wait
 group by event
 order by count(*);

--  马上该谁等待；SPECIFIC Waits–查询V$SESSION_WAIT

SELECT /*+ ordered */
 sid,event,owner,segment_name,segment_type,p1,p2,p3
  FROM v$session_wait sw,dba_extents de
 WHERE de.file_id = sw.p1
   AND sw.p2 between de.block_id and de.block_id + de.blocks - 1
   AND (event = ‘buffer busy waits‘ OR event = ‘write complete waits‘)
   AND p1 IS NOT null
 ORDER BY event,sid;

--  谁在等待 - 最后10 个等待数–查询V$SESSION_WAIT_HISTORY
SELECT /*+ ordered */
 sid,sid;

--  查找P1,P2,P3代表什么–查询 V$EVENT_NAME
select event#,parameter1 p1,parameter2 p2,parameter3 p3
  from v$event_name
 where name in (‘buffer busy waits‘,‘write complete waits‘);

--  会话开始后的所有等待数–查询 V$SESSION_EVENT
select sid,total_waits,time_waited,event_id
  from v$session_event
 where time_waited > 0
 order by time_waited;

--  类的所有会话等待数–查询V$SESSION_WAIT_CLASS
select sid,wait_class,total_waits from v$session_wait_class;

--  系统启动后的所有等待数–查询V$SYSTEM_EVENT
select event,event_id
  from v$system_event
 where time_waited > 0
 order by time_waited;

--  类的系统等待数–查询V$SYSTEM_WAIT_CLASS
select wait_class,total_waits
  from v$system_wait_class
 order by total_waits desc;

--  类的系统等待数–查询V$ACTIVE_SESSION_HISTORY
–In the query below,the highest count session is leader in non - idle wait events.

  select session_id,count(1)
    from v$active_session_history
   group by session_id
   order by 2;
–In the query below,find the SQL for the leader in non - idle wait events.

  select c.sql_id,a.sql_text
    from v$sql a,(select sql_id,count(1)
            from v$active_session_history b
           where sql_id is not null
           group by sql_id
           order by 2 desc) c
   where rownum <= 5
   order by rownum;

--  自动工作量仓库(AWR) 的基本信息
--  自动工作量仓库(AWR)在默认情况下，仓库用小时填充，保留期是7天。 
--  AWR使用多少空间

SQL >
  Select occupant_name,occupant_desc,space_usage_kbytes
    from v$sysaux_occupants
   where occupant_name like ‘%AWR%‘;

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES
----------------- ---------------------------------- ------------------
SM / AWR Server Manageability - Automatic Workload Repository 215616

SQL >

--  系统上最原始的AWR信息是什么？

SQL >
  select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
-------------------------------------------------------------
-OCT - 16 12.04.49.088829000 AM - 04 :00

--  什么是AWR信息的保留期？

SQL >
  select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------

--  将AWR信息的保留期更改为15天？

SQL > EXEC dbms_stats.alter_stats_history_retention(15);

--  PL/SQL 过程已成功完成。
--  获取生成的trace文件
--  开启SQL跟踪后，会生成一个trace文件，通过初始化参数user_dump_dest配置其所在目录，该参数的值可以通过下面方法获取到：

select name,value
  from v$parameter
 where name = ‘user_dump_dest‘
--  trace文件的名字是独立于版本和平台的，在大部分常见的平台下，命名结构如下：

 {instance name}_{process name}_{process id}.trc ）instance name

--  初始化参数instance_name的小写值。通过v$instance视图的instance_name列可以得到这个值。 
 ）process name

--  产生跟踪文件进程的名字的小写值。对于专有服务器进程，使用ora，对于共享服务器进程，可以通过v$diapatcher或v$shared_server视图的name列获得。对于并行从属进程，可以通过v$px_process视图server_name列获得，对于其他多数后台进程来说，可以通过v$bgprocess视图的name列获得。 
 ）process id

--  操作系统层面的进程标记。这个值可以通过v$process视图的spid列获取。

--  根据这些信息，可以通过下面的方式获取trace文件名：

  select s.SID,s.SERVER,lower(case
                 when s.SERVER in (‘DEDICATED‘,‘SHARED‘) then
                  i.INSTANCE_NAME || ‘_‘ ||
                  nvl(pp.SERVER_NAME,nvl(ss.NAME,‘ora‘)) || ‘_‘ || p.SPID ||
                  ‘.trc‘
                 else
                  null
               end) as trace_file_name
          from v$instance      i,v$session       s,v$process       p,v$px_process    pp,v$shared_server ss
         where s.PADDR = p.ADDR
           and s.SID = pp.SID(+)
           and s.PADDR = ss.PADDR(+)
           and s.TYPE = ‘USER‘
           and s.SID = ‘your sid‘
         order by s.SID;


--  将上面的’your sid’替换为你的session的sid就可以查出指定session生成的trace文件的名字，session的sid在v$session视图中得到，或者直接查询当前session的sid：

select userenv(‘sid‘) from dual;

--  或者
select sid from v$mystat a where rownum = 1;

--  将路径（user_dump_dest）和文件名结合在一起，我们就得到了trace文件的完整路径。

--  而在Oracel 11g中，查询当前会话生成的trace文件则非常简单：

select value
  from v$diag_info
 where name = ‘Default Trace File‘

--  查询Oracle正在执行的sql语句及执行该语句的用户
  SELECT b.sid      oracleID,b.username 登录Oracle用户名,spid       操作系统ID,paddr,sql_text   正在执行的SQL,b.machine  计算机名
          FROM v$process a,v$sqlarea c
         WHERE a.addr = b.paddr
           AND b.sql_hash_value = c.hash_value;


--  或者

select a.username,a.sid,b.SQL_TEXT,b.SQL_FULLTEXT
  from v$session a,v$sqlarea b
 where a.sql_address = b.address;

--  查询Oracle执行过的sql语句及执行该语句的用户
--  执行过的
select a.USERNAME        登录Oracle用户名,a.MACHINE         计算机名,SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b,v$session a
 where a.sql_hash_value = b.hash_value
   and b.FIRST_LOAD_TIME between ‘2016-11-01/09:24:47‘ and
       ‘2016-11-31/09:24:47‘
 order by b.FIRST_LOAD_TIME desc;

--  查看正在执行sql的发起者的发放程序
SELECT OSUSER     电脑登录身份,PROGRAM    发起请求的程序,USERNAME   登录系统的用户名,SCHEMANAME,B.Cpu_Time 花费cpu的时间,STATUS,B.SQL_TEXT 执行的sql
  FROM V$SESSION A
  LEFT JOIN V$SQL B
    ON A.SQL_ADDRESS = B.ADDRESS
   AND A.SQL_HASH_VALUE = B.HASH_VALUE
 ORDER BY b.cpu_time DESC;

--   v$sql、v$sqlarea 、v$sqltext

--  这三哥视图都可以用于查询共享池中已经解析过的SQL语句及其相关信息。
--  V$SQL中列出了共享SQL区中所有语句的信息，它不包含GROUP BY字句，并且为每一条SQL语句中单独存放一条记录；
--  V$SQLAREA中一条记录显示了一条共享SQL区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL语句的统计信息；
--  V$SQLTEXT包含了库缓存中所有共享游标对应的SQL语句。它将SQL语句分片显示。

--  查出oracle当前的被锁对象
SELECT l.session_id      sid,l.locked_mode     锁模式,l.oracle_username 登录用户,l.os_user_name    登录机器用户名,s.machine         机器名,s.terminal        终端用户名,o.object_name     被锁对象名,s.logon_time      登录数据库时间
  FROM v$locked_object l,all_objects o,v$session s
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
 ORDER BY sid,s.serial#;

--  kill掉当前的锁对象可以为
alter system kill session ‘sid， s.serial#‘;

--  查看占io较大的正在运行的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,se.sql_address,st.event,st. p1text,si.physical_reads,si.block_changes
  FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
 WHERE st.sid = se.sid
   AND st. sid = si.sid
   AND se.PADDR = pr.ADDR
   AND se.sid > 6
   AND st. wait_time = 0
   AND st.event NOT LIKE ‘%SQL%‘
 ORDER BY physical_reads DESC;

--  查询碎片程度高（实际使用率小于30%）的表，也就是可以收缩的表
--  条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了
--  算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满
--  AVG_ROW_LEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’2222211’7位，AVG_ROW_LEN显示依然为513

SELECT TABLE_NAME,(BLOCKS * 8192 / 1024 / 1024) "理论大小M",(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) "实际大小M",round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
             (BLOCKS * 8192 / 1024 / 1024),3) * 100 || ‘%‘ "实际使用率%"
  FROM USER_TABLES
 where blocks > 100
   and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
       (BLOCKS * 8192 / 1024 / 1024) < 0.3

 order by (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
          (BLOCKS * 8192 / 1024 / 1024) desc;

--  查询索引碎片的比例（索引删除行数除以索引总行数的百分比>30%即认为索引碎片大），也就是需要重建的索引

select name,del_lf_rows,lf_rows,round(del_lf_rows / decode(lf_rows,lf_rows) * 100,0) || ‘%‘ frag_pct
  from index_stats
 where round(del_lf_rows / decode(lf_rows,0) > 30;

--  集群因子clustering_factor高的表
--  集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,round(nvl(ind.clustering_factor,1) /
             decode(tab.num_rows,1,tab.num_rows),3) * 100 || ‘%‘ "集群因子接近行数"
  from user_tables tab,user_indexes ind
 where tab.table_name = ind.table_name
   and tab.blocks > 100
   and nvl(ind.clustering_factor,1) /
       decode(tab.num_rows,tab.num_rows) between 0.35 and 3;

--  或

select tab.owner,tab.table_name,3) * 100 || ‘%‘ "集群因子接近行数"

  from dba_tables tab,dba_indexes ind
 where tab.table_name = ind.table_name
   and tab.owner
      
       not in (‘SYS‘,‘SYSTEM‘,‘WMSYS‘,‘DBSNMP‘,‘CTXSYS‘,‘XDB‘,‘ORDDATA‘,‘SYSMAN‘,‘CATALOG‘,‘APEX_030200‘,‘MDSYS‘,‘OLAPSYS‘,‘EXFSYS‘)
      
   and tab.blocks > 100
      
   and nvl(ind.clustering_factor,tab.num_rows) between 0.35 and 3;

--  根据sid查spid或根据spid查sid
select s.sid,s.LOGON_TIME,s.machine,p.spid,p.terminal
  from v$session s,v$process p
 where s.paddr = p.addr
   and s.sid = XX
    or p.spid = YY;

--  根据sid查看具体的sql语句
select username,sql_text,machine,osuser
  from v$session a,v$sqltext_with_newlines b
 where DECODE(a.sql_hash_value,prev_hash_value,sql_hash_value) =
       b.hash_value
   and a.sid = &sid
 order by piece;

--  根据spid查询具体的sql语句
select ss.SID,ss.SERIAL#,ss.LOGON_TIME,ss.action,sa.SQL_FULLTEXT,ss.machine,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.STATUS,ss.OSUSER,ss.last_call_et
  from v$process pr,v$session ss,v$sqlarea sa
 where ss.status = ‘ACTIVE‘
   and ss.username is not null
   and pr.ADDR = ss.PADDR
   and ss.SQL_ADDRESS = sa.ADDRESS
   and ss.SQL_HASH_VALUE = sa.HASH_VALUE
   and pr.spid = XX
      
      --  查看历史session_id的SQL来自哪个IP(当然这是个误解，都是历史的了，怎么可能还查到spid，其实查看trace文件名就可以知道spid,trace文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DW_ora_17751.trc中17751就是spid，里面有这样的内容Incident 115 created,dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具体的sql语句)
      
      --  DB_ora_29349.trc中出现如下
       * SESSION ID :(5057.12807) 2016 - 10 - 26 14 :45 :52.726
--  通过表V$ACTIVE_SESSION_HISTORY来查，如下

  select a.sql_id,a.machine,a.*
    from V$ACTIVE_SESSION_HISTORY a
   where a.session_id = 5057
     and a.SESSION_SERIAL# = 12807
  
  --  查询上面的machine的IP是多少
  
    select s.sid,p.terminal
            from v$session s,v$process p
           where s.paddr = p.addr
             and s.machine = ‘localhost‘;



/*通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可
          
          [[email?protected] trace]$ netstat -anp |grep 17630
          
          tcp      210      0 192.168.64.228:11095        192.168.21.16:1521          ESTABLISHED 17630/oracleDB
          
          tcp        0      0 ::ffff:192.168.64.228:1521  ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB
          出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器
          */

--  查询DML死锁会话sid（对象锁被释放的等待者），及引起死锁的堵塞者会话blocking_session（对象加锁者）

select sid,blocking_session,LOGON_TIME,seconds_in_wait,state,BLOCKING_SESSION_STATUS
  from v$session
 where event like ‘enq%‘
   and state = ‘WAITING‘
   and BLOCKING_SESSION_STATUS = ‘VALID‘

 BLOCKING_SESSION :Session identifier of the blocking session. This
 column is valid only if BLOCKING_SESSION_STATUS has the value VALID;

--  可以在v$session.LOGON_TIME上看到引起死锁的堵塞者会话比等待者要早

--  如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session ‘sid,serial#’要到RAC对应的实例上去执行

--  或如下也可以

select

 (select username from v$session where sid = a.sid) blocker,a.id1,a.id2,‘ is blocking ‘ "IS BLOCKING",(select username from v$session where sid = b.sid) blockee,b.sid
  from v$lock a,v$lock b
 where a.block = 1
   and b.request > 0
   and a.id1 = b.id1
   and a.id2 = b.id2;

--  查询DDL锁的sql
--  查询x开头的动态性能视图，只能用sys用户

SELECT sid,p1raw,wait_time
  FROM sys.v_$session_wait
 WHERE event like ‘library cache %‘ p1raw结果为’0000000453992440’
  SELECT s.sid,kglpnmod "Mode",kglpnreq "Req",s.LOGON_TIME
          FROM x$kglpn p,v$session s
         WHERE p.kglpnuse = s.saddr
           AND kglpnhdl = ‘0000000453992440‘;


--  查询锁住的DDL对象
select d.session_id,s.SERIAL#,d.name
  from dba_ddl_locks d,v$session s
 where d.owner = ‘CC‘
   and d.SESSION_ID = s.sid

--  查询当前正在执行的sql
  SELECT s.sid,s.username,v$sql.sql_id,sql_text
          FROM v$process,v$sql
         WHERE addr = paddr
           and s.sql_id = v$sql.sql_id
           AND sql_hash_value = hash_value;


--  查询正在执行的SCHEDULER_JOB
select owner,job_name,sid,b.SERIAL#,b.username,spid
  from ALL_SCHEDULER_RUNNING_JOBS,v$process
 where session_id = sid
   and paddr = addr;

--  查询正在执行的dbms_job
select job,spid
  from DBA_JOBS_RUNNING a,v$process
 where a.sid = b.sid
   and paddr = addr;

--  查询一个会话session、process平均消耗多少内存，查看下面avg_used_M值
select round(sum(pga_used_mem) / 1024 / 1024,0) total_used_M,round(sum(pga_used_mem) / count(1) / 1024 / 1024,0) avg_used_M,round(sum(pga_alloc_mem) / 1024 / 1024,0) total_alloc_M,round(sum(pga_alloc_mem) / count(1) / 1024 / 1024,0) avg_alloc_M
  from v$process;

--  TOP 10 执行次数排序
select *
  from (select executions,PARSING_USER_ID,sql_text
        
          from v$sql,dba_users
         where user_id = PARSING_USER_ID
         order by executions desc)
 where rownum <= 5;

--  TOP 10 物理读排序（消耗IO排序，即最差性能SQL、低效SQL排序）
select *
  from (select DISK_READS,ELAPSED_TIME / 1000000,dba_users
         where user_id = PARSING_USER_ID
         order by DISK_READS desc)
 where rownum <= 5;

--  （不要使用DISK_READS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）

--  TOP 10 逻辑读排序（消耗内存排序）
select *
  from (select BUFFER_GETS,sql_text
          from v$sql,dba_users
         where user_id = PARSING_USER_ID
         order by BUFFER_GETS desc)
 where rownum <= 5;

--  （不要使用BUFFER_GETS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）
--  TOP 10 CPU排序(单位秒=cpu_time/1000000)
select *

  from (select CPU_TIME / 1000000,dba_users
         where user_id = PARSING_USER_ID
         order by CPU_TIME / 1000000 desc)
 where rownum <= 5;
--  （不要使用CPU_TIME/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）

--  查询等待事件
select event,1)) "之前等待次数",0)) "正在等待次数",count(*)
  from v$session_wait
 group by event
 order by 4 desc;

--  查询当前正在消耗temp空间的sql语句
Select distinct se.username,su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as space_G,su.tablespace,sql_text
  from V$TEMPSEG_USAGE su,v$sql s
 where p.name = ‘db_block_size‘
   and su.session_addr = se.saddr
   and su.sqlhash = s.hash_value
   and su.sqladdr = s.address;

--  查询需要使用绑定变量的sql,10G以后推荐第二种（任何一条执行过的语句不管执行了几次在V$SQL中都只有一条记录，V$SQL中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select * from t1.test在sye、system下执行则V$SQL只有一条记录(谁先执行则PARSING_SCHEMA_NAME显示谁)。如在sys和system都执行select * from test则V$SQL中有两条记录，两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录，说明V$SQL对应的sql语句必须一模一样，如果alter system flush shared_pool（主站慎用）后再执行一样的语句，发现语句在V$SQL中的SQL_ID和HASH_VALUE与之前的一样，说明SQL_ID和HASH_VALUE应该是oracle自己的一套算法来的，只是根据sql语句内容来进行转换，sql语句不变则SQL_ID和HASH_VALUE也不变。）

--  第一种

select *
  from (select count(*),substr(sql_text,40)
          from v$sql
         group by sql_id,40)
        having count(*) > 10
         order by count(*) desc)
 where rownum < 10;

--  第二种                    
--  count(1)>10表示类语句运行了10次以上

select sql_id,FORCE_MATCHING_SIGNATURE,sql_text
  from v$SQL
 where FORCE_MATCHING_SIGNATURE in
       (select /*+ unnest */
         FORCE_MATCHING_SIGNATURE
          from v$sql
         where FORCE_MATCHING_SIGNATURE > 0
           and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
         group by FORCE_MATCHING_SIGNATURE
        having count(1) > 10);

--  查看数据文件可用百分比
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,ROUND(b.bytes / 1024 / 1024 / 1024,2) || ‘G‘ "文件总容量",ROUND((b.bytes - sum(nvl(a.bytes,0))) / 1024 / 1024 / 1024,2) || ‘G‘ "文件已用容量",ROUND(sum(nvl(a.bytes,0)) / 1024 / 1024 / 1024,2) || ‘G‘ "文件可用容量",0)) / (b.bytes),2) * 100 || ‘%‘ "文件可用百分比"
  from dba_free_space a,dba_data_files b
 where a.file_id = b.file_id
 group by b.tablespace_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE
 order by b.tablespace_name;

--  查看数据文件可用百分比（文件自增长的情况下）
select b.file_id,ROUND(b.MAXBYTES / 1024 / 1024 / 1024,2) || ‘G‘ "文件最大可用总容量",ROUND(((b.MAXBYTES / 1024 / 1024 / 1024) -
             ((b.bytes - sum(nvl(a.bytes,0))) / 1024 / 1024 / 1024)) /
             (b.MAXBYTES / 1024 / 1024 / 1024),2) * 100 || ‘%‘ "文件可用百分比"
  from dba_free_space a,dba_data_files b
 where a.file_id = b.file_id
   and b.file_id > 4
 group by b.tablespace_name,b.MAXBYTES
 order by b.tablespace_name;

--  查看表空间可用百分比
select b.tablespace_name,a.total,b.free,round((b.free / a.total) * 100) "% Free"
  from (select tablespace_name,sum(bytes / (1024 * 1024)) total
          from dba_data_files
         group by tablespace_name) a,(select tablespace_name,round(sum(bytes / (1024 * 1024))) free
          from dba_free_space
         group by tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
 order by "% Free";

--  查看临时表空间使用率（临时文件是AUTOEXTENSIBLE的情况下可能空闲率是0）
SELECT temp_used.tablespace_name,total,used,total - used as "Free",round(nvl(total - used,0) * 100 / total,3) "Free percent"
  FROM (SELECT tablespace_name,SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name

--  查询undo表空间使用情况
  select tablespace_name,sum(bytes) / 1024 / 1024 M
          from dba_undo_extents
         group by tablespace_name,status;


--  查看ASM磁盘组使用率
select name,round(total_mb / 1024) "总容量",round(free_mb / 1024) "空闲空间",round((free_mb / total_mb) * 100) "可用空间比例"
  from gv$asm_diskgroup;

--  统计每个用户使用表空间率
SELECT c.owner "用户",a.tablespace_name "表空间名",total / 1024 / 1024 "表空间大小M",free / 1024 / 1024 "表空间剩余大小M",(total - free) / 1024 / 1024 "表空间使用大小M",Round((total - free) / total,4) * 100 "表空间总计使用率   %",c.schemas_use / 1024 / 1024 "用户使用表空间大小M",round((schemas_use) / total,4) * 100 "用户使用表空间率  %"
  FROM (SELECT tablespace_name,Sum(bytes) free
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name) a,Sum(bytes) total
          FROM DBA_DATA_FILES
         GROUP BY tablespace_name) b,(Select owner,Tablespace_Name,Sum(bytes) schemas_use
          From Dba_Segments
         Group By owner,Tablespace_Name) c
 WHERE a.tablespace_name = b.tablespace_name
   and a.tablespace_name = c.Tablespace_Name
 order by "用户","表空间名";

--  查看闪回区快速恢复区空间使用率
select sum(percent_space_used) || ‘%‘ "已使用空间比例"
  from V$RECOVERY_AREA_USAGE
       
       --  或
       
         select round(100 * (a.space_used / space_limit),2) || ‘%‘ "已使用空间比例",a.*
           from v$recovery_file_dest a;


--  查看僵死进程，分两种（一种是会话不在的，另一种是会话标记为killed的但是会话还在的）

--  alter system kill session一执行则session即标记为KILLED，但是如果会话产生的数据量大则这个kill可能会比较久，在这个过程中session标记为KILLED但是这个会话还在V$session中，则V$session.paddr还在，所以可以匹配到V$process.addr，所以process进程还在；当kill过程执行完毕，则这个会话即不在V$session中

--  会话不在的

select *
  from v$process
 where addr not in (select paddr from v$session)
   and pid not in (1,17,18);

--  会话还在的，但是会话标记为killed

select *
  from v$process
 where addr in (select paddr from v$session where status = ‘KILLED‘) --  再根据上述结果中的SPID通过如下命令可以查看到process的启动时间
 ps auxw | head - 1;

--  查看行迁移或行链接的表
select *
  From dba_tables
 where nvl(chain_cnt,0) <> 0 chain_cnt ：Number of rows in the table that are
 chained from one data block to another
    or that have migrated to a new block,requiring a link to
 preserve the old rowid. This column is updated only after you
 analyze the table;

--  数据缓冲区命中率（百分比小于90就要加大db_cache_size）

--  查询V$SYSSTAT视图可以查看从内存中读取数据的频率。它提供了数据库中设置的数据块缓存区的命中率。这个信息可以帮助您判断系统何时需要更多的数据缓存(DB_CACHE_SIZE)，或者系统的状态何时调整得不佳(二者均将导致较低的命中率)。

--  通常情况下，您应当确保读数据的命中率保持在95%以上。将系统的命中率从98%提高到99%，可能意味着性能提高了100%(取决于引起磁盘读操作的语句)。

SELECT a.VALUE + b.VALUE logical_reads,c.VALUE phys_reads,round(100 * (1 - c.value / (a.value + b.value)),2) || ‘%‘ hit_ratio
  FROM v$sysstat a,v$sysstat c
 WHERE a.NAME = ‘db block gets‘
   AND b.NAME = ‘consistent gets‘
   AND c.NAME = ‘physical reads‘;

--  或

SELECT DB_BLOCK_GETS + CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads,round(100 *
             (1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))),2) || ‘%‘ "Hit Ratio"
  FROM V$BUFFER_POOL_STATISTICS
 WHERE NAME = ‘DEFAULT‘;

--  或
SELECT 1 - (SUM(DECODE(NAME,‘physical reads‘,VALUE,0)) /
       (SUM(DECODE(NAME,‘db block gets‘,0)) +
       (SUM(DECODE(NAME,‘consistent gets‘,0))))) "Read Hit Ratio"
  FROM v$sysstat;

--  或者
--  在Oracle 10g中，也可以直接获得V$SYSMETRIC中的 AWR 信息:
select metric_name,value
  from v$sysmetric
 where metric_name = ‘Buffer Cache Hit Ratio‘;

--  测定数据字典的命中率(V$ROWCACHE)
--  可以使用V$ROWCACHE视图来发现对数据字典的调用是否有效地利用了通过init.ora参数SHARED_POOL_SIZE分配的内存缓存.
--  如果字典的命中率不高，系统的综合性能将大受影响。推荐的命中率是95%或者更高。如果命中率低于这个百分比，说明可能需要增加init.ora参数SHARED_POOL_SIZE。
--  但要记住，在V$SGASTAT视图中看到的共享池包括多个部分，而这里仅仅就是其中之一。注意：在大幅度使用公共同名的环境中，字典命中率可能难以超过75%，即使共享池的尺寸很大。这是因为Oracle必须经常检查不存在的对象是否依旧存在。

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

SUM(GETS) SUM(GETMISSES) HITRATE
---------- -------------- ----------
35555492 186408 99.4784608

--  在Oracle 10g中，也可以直接获得V$SYSMETRIC中的AWR信息:

SQL >
  select metric_name,value
    from v$sysmetric
   where metric_name = ‘Library Cache Hit Ratio‘;

METRIC_NAME VALUE
---------------------------------------------------------------- ----------
Library Cache Hit Ratio 98.7987987 Library Cache Hit Ratio 100

--  测定共享SQL和PL/SQL的命中率(V$LIBRARYCACHE)

--  访问V$LIBRARYCACHE视图可以显示实际使用的语句(SQL和PL/SQL)访问内存的情况。如果init.ora的参数SHARED_POOL_SIZE设置得太小，内存中就没有足够的空间来存储所有的语句。固定命中率通常应该是95%或更高，而重载的次数不应该超过1%。查询V$SQL_BIND_CAPTURE视图，看看每个SQL绑定是否太高，是否需要CURSOR_SHARING。

SQL >
  select sum(pinhits) / sum(pins) * 100 from v$librarycache;

SUM(PINHITS) / SUM(PINS) * 100
--------------------------
98.2787413813059 SQL >
  select sum(pinhits - reloads) / sum(pins) * 100 from v$librarycache;

SUM(PINHITS - RELOADS) / SUM(PINS)
------------------------------
98.0597157838185

--  最好的方式：
  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$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(*);

--  确定需要固定的PL/SQL对象的可用空间均成为许多零散的片段，而没有足够大的连续空间，这是共享池中的普遍现象。消除共享池错误的关键是理解哪些对象会引起问题。一旦知道了会引起潜在问题的PL/SQL对象，就可以在数据库启动时固定这个代码(这时共享池是完全连续的)。

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

--  通过V$SQLAREA查找有问题的查询V$SQLAREA视图提供了一种识别有潜在问题或者需要优化的SQL语句的方法，从而可通过减少磁盘的访问来优化数据库的综合性能。

select b.username username,a.disk_reads reads,a.executions exec,a.disk_reads / decode(a.executions,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;

--  检查用户的当前操作及其使用的资源

--  将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句。这在有些时候是极为有用的，例如DBA希望查看某一个给定的时间点上系统究竟执行了哪些操作。

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,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;

--  查找磁盘I/O问题
--  视图V$DATAFILE、V$FILESTAT和V$DBA_DATA_FILES提供了数据库中所有数据文件和磁盘的文件I/O活动信息。理想情况下，物理的读和写应当平均分布。如果没有合理的配置系统，其综合性能就会受到影响。

select a.file#,a.name,a.status,a.bytes,b.phyrds,b.phywrts
  from v$datafile a,v$filestat b
 where a.file# = b.file#;

--  查询归档日志切换频率
select sequence#,to_char(first_time,‘yyyymmdd_hh24:mi:ss‘)
       
       firsttime,round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,2) minutes
  from v$log_history
 where first_time > sysdate - 3
 order by first_time,minutes;

--  或

select sequence#,‘yyyy-mm-dd hh24:mi:ss‘) First_time,First_change#,switch_change#
  from v$loghist
 where first_time > sysdate - 3
 order by 1;

--  或

SELECT TO_CHAR(first_time,‘MM/DD‘) DAY,SUM(DECODE(TO_CHAR(first_time,‘HH24‘),‘00‘,0)) H00,‘01‘,0)) H01,‘02‘,0)) H02,‘03‘,0)) H03,‘04‘,0)) H04,‘05‘,0)) H05,‘06‘,0)) H06,‘07‘,0)) H07,‘08‘,0)) H08,‘09‘,0)) H09,‘10‘,0)) H10,‘11‘,0)) H11,‘12‘,0)) H12,‘13‘,0)) H13,‘14‘,0)) H14,‘15‘,0)) H15,‘16‘,0)) H16,‘17‘,0)) H17,‘18‘,0)) H18,‘19‘,0)) H19,‘20‘,0)) H20,‘21‘,0)) H21,‘22‘,0)) H22,‘23‘,0)) H23,COUNT(*) TOTAL
  FROM (SELECT ROWNUM RN,FIRST_TIME
          FROM V$LOG_HISTORY
         WHERE first_time > sysdate - 18
           and FIRST_TIME > ADD_MONTHS(SYSDATE,-1)
         ORDER BY FIRST_TIME)
 GROUP BY TO_CHAR(first_time,‘MM/DD‘)
 ORDER BY MIN(RN);

--  查询lgwr进程写日志时每执行一次lgwr需要多少秒，在state是waiting的情况下，某个等待编号seq#下，seconds_in_wait达多少秒，就是lgwr进程写一次IO需要多少秒
select event,seq#,program
  from v$session
 where program like ‘%LGWR%‘
   and state = ‘WAITING‘

--  查询没有索引的表
  Select table_name
          from user_tables
         where table_name not in (select table_name from user_indexes)
          Select table_name
                  from user_tables
                 where table_name not in
                       (select table_name from user_ind_columns);



--  查询7天的db time（db time=db cpu+io time+wait time不包含空闲等待）
WITH sysstat AS

 (select sn.begin_interval_time begin_interval_time,sn.end_interval_time end_interval_time,ss.stat_name stat_name,ss.value e_value,lag(ss.value,1) over(order by ss.snap_id) b_value
    from dba_hist_sysstat ss,dba_hist_snapshot sn
   where trunc(sn.begin_interval_time) >= sysdate - 7
     and ss.snap_id = sn.snap_id
     and ss.dbid = sn.dbid
     and ss.instance_number = sn.instance_number
     and ss.dbid = (select dbid from v$database)
     and ss.instance_number = (select instance_number from v$instance)
     and ss.stat_name = ‘DB time‘)
select to_char(BEGIN_INTERVAL_TIME,‘mm-dd hh24:mi‘) ||
       to_char(END_INTERVAL_TIME,‘ hh24:mi‘) date_time,stat_name,round((e_value - nvl(b_value,0)) /
             (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +
             extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +
             extract(minute from(end_interval_time - begin_interval_time)) * 60 +
             extract(second from(end_interval_time - begin_interval_time))),0) per_sec
  from sysstat
 where (e_value - nvl(b_value,0)) > 0
   and nvl(b_value,0) > 0;

--  查询产生热块较多的对象
--  x$bh .tch(Touch)表示访问次数越高，热点快竞争问题就存在

SELECT e.owner,e.segment_name,e.segment_type
  FROM dba_extents e,(SELECT *
          FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch
                  FROM x$bh
                 ORDER BY tch DESC)
         WHERE ROWNUM < 11) b
 WHERE e.relative_fno = b.dbarfil
   AND e.block_id <= b.dbablk
   AND e.block_id + e.blocks > b.dbablk;

--  导出AWR报告的SQL语句
select *
  from dba_hist_snapshot
        select *
          from table(dbms_workload_repository.awr_report_html(DBID,INSTANCE_NUMBER,startsnapid,endsnapid))
                select *
                  from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID,endsnapid,DBID,endsnapid));



--  查询某个SQL的执行计划
select a.hash_value,a.*
  from v$sql a
 where sql_id = ‘0n4qfzbqfsjm3‘
  select *
          from table(dbms_xplan.display_cursor(v$sql.hash_value,‘advanced‘));


--  含顺序的
select * from table(xplan.display_cursor(‘v$sql.sql_id‘,‘advanced‘));

--  不过要先创建xplan包，再执行
SQL > CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
SQL > grant execute on sys.xplan to public;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!