Oracle性能分析SQL

原文地址:http://blog.csdn.net/yangshangwei/article/details/52449489


死锁后的解决办法

如果死锁不能自动释放,就需要我们手工的 kill session

生成Kill Session语句

  1. 查看有无死锁对象,如有 kill session

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
  FROM v$session
 WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);12345

如果有,会返回类似与如下的信息:

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_textFROM v$sqltext q, v$session sWHERE 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 获取一个排它锁1
SQL>select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号from v$locked_object l , dba_objects o , 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';

查询绑定变量使用的实际值

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

2.请自行替换sql_id,此时是从awr中查询(sql 被 aged out 出 shared pool)

select instance_number,
         sql_id,
       name,
       datatype_string,
       last_captured,
       value_string  from dba_hist_sqlbind where sql_id = 'fahv8x6ngrb50'
 order by LAST_CAPTURED, POSITION;

监控事例的等待

select event,sum(decode(wait_Time,0,0,1)) "Prev", 
sum(decode(wait_Time,0,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, tablespace_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))                         剩余空间,             sum(nvl(a.bytes,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,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 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,  
       SPACE || 'M' "SUM_SPACE(M)",  
       USED_SPACE || 'M' "USED_SPACE(M)",  
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",  
       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"  
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_TEMP_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 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, 0) / 1024 / 1024, 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.sid,
        s.serial#,
        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.usnORDER  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,b.sid,b.serial#,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, gets,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, gets, 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, gets, misses, immediate_gets, immediate_misses, 
Decode(gets,0,0,misses/gets*100) ratio1, 
Decode(immediate_gets+immediate_misses,0,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_TABLESWHERE 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,
          segtype,
          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,1,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 sWHERE a.owner <> 'SYS'AND s.sid = a.sid 
GROUP BY a.sid,s.terminal,s.programORDER BY count(a.sid) ;

查看数据库库对象

SELECT owner, object_type, status, COUNT(*) count#  FROM all_objects GROUP BY owner, object_type, status;

查看数据库的版本 

SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 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, 2, 3;

根据用户名进行授予的系统级特权

select b.privilege what_granted, b.admin_option, a.username
  from sys.dba_users a, 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,
       a.username  from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee order by 1;

根据被授权人授予的角色

select granted_role ||
       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted,
       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, 'YES', ' (With Admin Option)', null) what_granted  from sys.dba_users a, sys.dba_role_privs b where a.username = b.granteeUNIONselect a.username,
       b.privilege ||
       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted  from sys.dba_users a, sys.dba_sys_privs b where a.username = b.granteeUNIONselect a.username,
       b.table_name || '-' || b.privilege ||
       decode(grantable, 'YES', ' (With Grant Option)', null) what_granted  from sys.dba_users a, 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, 1, 0)) "Waiting Now",       sum(decode(wait_time, 0, 0, 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, 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;

查找P1, P2, P3代表什么–查询 V$EVENT_NAME

select event#, name, parameter1 p1, parameter2 p2, parameter3 p3
  from v$event_name where name in ('buffer busy waits', 'write complete waits');

会话开始后的所有等待数–查询 V$SESSION_EVENT

select sid, event, 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, total_waits, time_waited, 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             215616SQL> 1234567

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

SQL> select dbms_stats.get_stats_history_availability from dual;GET_STATS_HISTORY_AVAILABILITY
-------------------------------------------------------------20-OCT-16 12.04.49.088829000 AM -04:00

什么是AWR信息的保留期?

SQL>  select dbms_stats.get_stats_history_retention from dual;GET_STATS_HISTORY_RETENTION
---------------------------                         31

将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}.trc1

1)instance name 
初始化参数instance_name的小写值。通过v$instance视图的instance_name列可以得到这个值。 
2)process name 
产生跟踪文件进程的名字的小写值。对于专有服务器进程,使用ora,对于共享服务器进程,可以通过v$diapatcher或v$shared_server视图的name列获得。对于并行从属进程,可以通过v$px_process视图server_name列获得,对于其他多数后台进程来说,可以通过v$bgprocess视图的name列获得。 
3)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用户名,
    b.serial#,
    spid 操作系统ID,
    paddr,
    sql_text 正在执行的SQL,
    b.machine 计算机名
FROM v$process a, v$session b, 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 执行的sqlFROM V$SESSION ALEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS          AND A.SQL_HASH_VALUE = B.HASH_VALUEORDER BY b.cpu_time DESC
 v$sql、v$sqlarea 、v$sqltext1

这三哥视图都可以用于查询共享池中已经解析过的SQL语句及其相关信息。

  • V$SQL中列出了共享SQL区中所有语句的信息,它不包含GROUP BY字句,并且为每一条SQL语句中单独存放一条记录;

  • V$SQLAREA中一条记录显示了一条共享SQL区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL语句的统计信息;

  • V$SQLTEXT包含了库缓存中所有共享游标对应的SQL语句。它将SQL语句分片显示。


查出oracle当前的被锁对象

SELECT l.session_id sid,
    s.serial#,
    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.sidORDER 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)但是实际数据都是’1111111’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, 0, 1, lf_rows) * 100, 0) || '%' frag_pct  from index_stats where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 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,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"from user_tables tab, user_indexes ind where tab.table_name=ind.table_nameand tab.blocks>100and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.ownernot in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS')and tab.blocks>100and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

根据sid查spid或根据spid查sid

select s.sid, s.serial#, 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, 0, 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,
       pr.SPID,
       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, s.serial#, s.LOGON_TIME, s.machine, p.spid, p.terminal  from v$session s, v$process p where s.paddr = p.addr   and s.machine = 'localhost'

通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可

[oracle@dwdb trace]$ netstat -anp |grep 17630tcp      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,
       sql_id,
       status,       event,
       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.sid,

         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, event, p1raw, seconds_in_wait, wait_time  FROM sys.v_$session_wait WHERE event like 'library cache %'

p1raw结果为’0000000453992440’

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIMEFROM x$kglpn p, v$session sWHERE p.kglpnuse=s.saddrAND kglpnhdl='0000000453992440';

结果为671 0 3 2011-11-1 12:00:00

  525 2 0 2011-11-4 12:00:00

查询锁住的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.serial#,
       s.username,
       spid,
       v$sql.sql_id,
       machine,
       s.terminal,
       s.program,
       sql_text

  FROM v$process, v$session s, 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$session b, v$process where session_id = sid   and paddr = addr

查询正在执行的dbms_job

select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,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,username,PARSING_USER_ID,sql_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,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text  

   from v$sql,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,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,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,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text  

   from v$sql,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,       sum(decode(wait_time, 0, 0, 1)) "之前等待次数",       sum(decode(wait_time, 0, 1, 0)) "正在等待次数",       count(*)  from v$session_wait group by event order by 4 desc

查询当前正在消耗temp空间的sql语句

Select distinct se.username,

         se.sid,

         su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,

         su.tablespace,

         sql_text    from V$TEMPSEG_USAGE su, v$parameter p, v$session se, 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(*),sql_id, substr(sql_text,1,40)from v$sqlgroup by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10

第二种

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

select sql_id, FORCE_MATCHING_SIGNATURE, sql_textfrom v$SQLwhere FORCE_MATCHING_SIGNATURE in(select /*+ unnest */

FORCE_MATCHING_SIGNATUREfrom v$sqlwhere FORCE_MATCHING_SIGNATURE > 0and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATUREgroup by FORCE_MATCHING_SIGNATUREhaving 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' "文件可用容量",

ROUND(sum(nvl(a.bytes,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_name,b.file_id,b.bytes,b.AUTOEXTENSIBLEorder by b.tablespace_name;

查看数据文件可用百分比(文件自增长的情况下)

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,

ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G'  "文件最大可用总容量",

ROUND((b.bytes-sum(nvl(a.bytes,0)))/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>4group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTESorder 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) bWHERE a.tablespace_name = b.tablespace_nameorder 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,

           (SELECT tablespace_name, 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, status, 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,

       (SELECT tablespace_name,               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) cWHERE  a.tablespace_name = b.tablespace_nameand a.tablespace_name =c.Tablespace_Nameorder 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;ps auxw|grep SPID

查看行迁移或行链接的表

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 b, 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', VALUE, 0)) +
       (SUM(DECODE(NAME, 'consistent gets', VALUE, 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.7987987Library 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.278741381305912345
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, 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;

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

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

查找磁盘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 fromv$log_history where first_time > sysdate - 3 order by first_time,minutes;

select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# fromv$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', 1, 0)) H00,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22,SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 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, state, seq#, seconds_in_wait, 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_typeFROM dba_extents e,

(SELECT *FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tchFROM x$bhORDER BY tch DESC)WHERE ROWNUM < 11) bWHERE e.relative_fno = b.dbarfilAND e.block_id <= b.dbablkAND e.block_id + e.blocks > b.dbablk;

导出AWR报告的SQL语句

select * from dba_hist_snapshot1
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))1
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,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,0,'advanced'));

含顺序的

select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));

不过要先创建xplan包,再执行

SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;

SQL> grant execute on sys.xplan to public;