oracle 诊断语句

 1 SQL_TRACE

启用当前会话 sql_trace

alter session set sql_trace = true;
sql statements
alter session set sql_trace = off;

启用指定session的sql_trace

select sid,serical#,username from v$session;
exec dbms_system.set_sql_trace_in_session(sid,serial#,true/false)

查看生成的sql_trace文件

 select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
       p.spid || '.trc ' trace_file_name
  from (select p.spid
          from sys.v$mystat m, sys.v$session s, sys.v$process p
         where m.statistic# = 1
           and s.sid = m.sid
           and p.addr = s.paddr) p,
       (select t.instance
          from sys.v$thread t, sys.v$parameter v
         where v.name = 'thread'
           and (v.value = 0 or t.thread# = to_number(v.value))) i,
       (select value from sys.v$parameter where name = 'user_dump_dest') d

需要在sys用户下登陆方可运行！

2查看 空间使用情况 

create or replace procedure show_space(p_segname   in varchar2,
                                       p_owner     in varchar2,
                                       p_type      in varchar2 default 'TABLE',
                                       p_partition in varchar2 default NULL) as
  l_free_blks          number;
  l_total_blocks       number;
  l_total_bytes        number;
  l_unused_bytes       number;
  l_unused_blocks      number;
  l_LastUsedExtFilID   number;
  l_LastUsedExtBlockID number;
  l_last_used_block    number;
  procedure p(p_lable in varchar2, p_number in number) is
  begin
    dbms_output.put_line(rpad(p_lable, 40, '.') || p_number);
  end;
begin
  dbms_space.free_blocks(segment_owner     => p_owner,
                         segment_name      => p_segname,
                         segment_type      => p_type,
                         partition_name    => p_partition,
                         freelist_group_id => 0,
                         free_blks         => l_free_blks);
  dbms_space.unused_space(segment_owner             => p_owner,
                          segment_name              => p_segname,
                          segment_type              => p_type,
                          partition_name            => p_partition,
                          total_blocks              => l_total_blocks,
                          total_bytes               => l_total_bytes,
                          unused_blocks             => l_unused_blocks,
                          unused_bytes              => l_unused_bytes,
                          last_used_extent_file_id  => l_LastUsedExtFilID,
                          last_used_extent_block_id => l_LastUsedExtBlockID,
                          last_used_block           => l_last_used_block);
  p('Free Blocks', l_free_blks);
  p('Total Blocks', l_total_blocks);
  p('Total Bytes', l_total_bytes);
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes', l_unused_bytes);
  p('Last Used Ext FileID', l_LastUsedExtFilID);
  p('Last Used Ext BlockID', l_LastUsedExtBlockID);
  p('Last Used Block', l_last_used_block);
end;

 用法：exec show_space('EMP','SCOTT','TABLE');

查看表空间利用率：方法一、

select b.file_name 物理文件名,
       b.tablespace_name 表空间,
       b.bytes / 1024 / 1024 大小m,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用m,
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
  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.bytes
 order by b.tablespace_name;

方法二、

select a.a1 表空间名称,
       c.c2 类型,
       c.c3 区管理,
       b.b2 / 1024 / 1024 表空间大小m,
       (b.b2 - a.a2) / 1024 / 1024 已使用m,
       substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
  from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
          from dba_free_space
         group by tablespace_name) a,
       (select tablespace_name b1, sum(bytes) b2
          from dba_data_files
         group by tablespace_name) b,
       (select tablespace_name c1, contents c2, extent_management c3
          from dba_tablespaces) c
 where a.a1 = b.b1
   and c.c1 = b.b1;

 3 获取隐藏参数

select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
from sys.x$ksppi x,sys.x$ksppcv y
where x.indx = y.indx and x.ksppinm like '%&par%'

其中x$ksppi全称为Kernel Service Parameter,Parameter Information

x$ksppcv全称为Kernal Service Parameter Current(session)Value

获取V$  视图的定义

select * from v$fixed_view_definition t where t.VIEW_NAME = 'GV$SESSION';

获取特定sechme下object的ddl语句

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
select dbms_metadata.get_ddl('TABLE,VIEW,PROCEDURE,FUNCTION,SYNONYMN,TRIGGER,SEQUENCE...','EMP','SCOTT') FROM DUAL;

4.诊断并解决CPU 100%问题

   top 命令找高利用率的进程，ps -ef | grep 进程号

   查找sql语句

SELECT /*+ ORDERED */
 sql_text
  FROM v$sqltext a
 WHERE (a.hash_value, a.address) IN
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.paddr =
               (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
 ORDER BY piece ASC

在windwos平台可借助于quickslice工具查看oracle线程ID，将其转换为十进制！或者利用 to_number('&spid','xxxx')函数

5诊断并解决cache_buffer latch竞争

首先获取当前持有最热点数据块的latch及buffer信息

select b.addr,
        a.ts#,
        a.dbarfil,
        a.dbablk,
        a.tch,
        b.gets,
        b.misses,
        b.sleeps
   from (select *
           from (select addr, ts#, file#, dbarfile, dbablk, tch, hladdr
                   from x$bh
                  order by tch desc)
          where rownum < 11) a, /*以热点块倒排序，查看前10*/
        (select addr, gets, misses, sleeps
           from v$latch_children
          where name = 'cache buffers chains') b
  where a.hladdr = b.addr

找到这些热点的对象信息

select distinct 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

找到相关的sql语句

select /*+rule*/
 hash_value, sql_text
  from v$sqltext
 where (hash_value, address) in
       (select a.hash_value, a.address
          from v$sqltext a,
               (select distinct 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) c
         where upper(a.sql_text) like '%' || c.segment_name || '%'
           and c.segment_type = 'TABLE')
 order by hash_value, address, piece

 6查看正在运行的sql

SELECT osuser, username, sql_text
  from v$session a, v$sqltext b
 where a.sql_address = b.address
 order by address, piece

7查看oracle 数据缓冲区缓存命中率

select (1 - ((physical.value - direct.value - lobs.value) / logical.value)) * 100 "命中率" 
  from v$sysstat physical,  
       v$sysstat direct,  
       v$sysstat lobs,  
       v$sysstat logical  
 where physical.name = 'physical reads' 
   and direct.name = 'physical reads direct' 
   and lobs.name = 'physical reads direct (lob)' 
   and logical.name = 'session logical reads'; 

对于OLTP，确保命中率大于95%，否则就要增加 data buffer 的大小。

8查看 共享SQL 区命中率

select((sum(pins-reloads))/sum(pins))*100 "Library cache" from v$librarycache;--动态性能表

这个使用率应该在90％以上，否则需要增加共享池的大小。

9查看数据字典缓冲区的使用率

select ((sum(gets-getmisses-usage-fixed))/sum(gets))*100 "Data dictionary cache" from v$rowcache;--动态性能表

这个使用率也应该在90％以上，否则需要增加共享池的大小。
10查看session 级别的等待事件

Select s.username,s.program,s.status,se.event,se.total_waits,se.total_timeouts,se.time_waited,se.average_wait from v$session s,v$session_event se
Where s.sid=se.sid
      And se.event not like 'SQL*Net%'
      And s.status = 'ACTIVE'
      And s.username is not null

如果确定session id，可以查看指定session 的等待事件。如果有对表v$mystat表的访问权限，可以通过以下语句。

select sid from v$mystat t where t.rownum=1;

查看指定进程的等待事件(oracle 9)

SQL> SELECT sid,
       CHR (BITAND (p1,-16777216) / 16777215) ||
       CHR (BITAND (p1, 16711680) / 65535) enq,
       DECODE (CHR (BITAND (p1,-16777216) / 16777215) ||
               CHR (BITAND (p1, 16711680) / 65535),
                 'TX', 'Transaction (RBS)',
                 'TM', 'DML Transaction',
                 'TS', 'Tablespace and Temp Seg',
                 'TT', 'Temporary Table',
                 'ST', 'Space Mgt (e.g., uet$, fet$)',
                 'UL', 'User Defined',
                 CHR (BITAND (p1,-16777216) / 16777215) ||
                 CHR (BITAND (p1, 16711680) / 65535)) enqueue_name,
       DECODE (BITAND (p1, 65535), 1, 'Null', 2, 'Sub-Share',
                 3, 'Sub-Exclusive', 4, 'Share', 5, 'Share/Sub-Exclusive',
                 6, 'Exclusive', 'Other') lock_mode
FROM   v$session_wait
WHERE  sid = ‘96’;

  SID ENQ  ENQUEUE_NAME                   LOCK_MODE
----- ---- ------------------------------ ----------
   96 TX   Transaction (RBS)              Exclusive

oracle 10g

SQL> SELECT event, state, seconds_in_wait siw
     FROM   v$session_wait
     WHERE  sid = ‘143’;

EVENT                               STATE                      SIW
----------------------------------- ------------------- ----------
enq: TX - row lock contention       WAITING                    495