oracle优化方法之一

oracle数据库优化有很多种方法，下面介绍中采用session跟踪的方式：

select event "Wait Event", time_waited "Time waited",
       time_waited /(select sum(time_waited) from v$system_event) "%Time waited",
       total_waits "Waits",
       total_waits/(select sum(total_waits) from v$system_event) "%Waited"
  from v$system_event
 order by 3 desc;
 
create table sys_b(
    event  varchar2(64),
    time_waited   number,
    total_waits   number
);

create table sys_e
(
  event varchar2(64),
  time_waited  number,
  total_waits  number
);

insert into sys_b
select event, time_waited, total_waits
  from v$system_event;
 
insert into sys_e
select event, time_waited, total_waits
  from v$system_event;
 
create table sys_dif
as
select e.event,
       e.time_waited - b.time_waited time_waited,
       e.total_waits - b.total_waits total_waits
  from sys_b b, sys_e e
 where b.event = e.event;
 
select event "Wait Event", time_waited "Time Waited",
       time_waited /(select sum(time_waited) from sys_dif) "%Time waited",
       total_waits "Waits",
       total_waits/(select sum(total_waits) from sys_dif) "%waited"
  from sys_dif
 order by 3 desc;
 
select event "Wait Event",
       count(seconds_in_wait) "waited so Far(sec)",
       count(sid) "Num sess Waiting"
  from v$session_wait
 group by event;
 
select sid, event "Wait Event", state "Wait Stat",
       wait_time "W'd So Far(secs)", seconds_in_wait "Time W'd(secs)"
  from v$session_wait
 where event like '&a&'
 order by 5;
 
select *
  from (select address "Stmt Addr",
               disk_reads "Disk Rds",
               buffer_gets "Buff Gets",
               sorts "sorts",
               executions "Runs",
               Loads "Body Loads"
          from v$sqlarea
         where disk_reads >&A
         order by disk_reads)
 where rownum < &B;
 
select cpu.sid "sid", cpu.username "User Name", cpu.value "CPU(sec)",
       reads.value "IO Read(K)", writes.value "IO Write(k)"
  from (select a.sid sid, a.username username, b.name, c.value value, a.serial# serial#
          from v$session a, v$statname b, v$sesstat c
         where a.sid = c.sid
           and b.statistic# = c.statistic#
           and b.name = 'CPU used by this session') cpu,
       (select a.sid, a.username, b.name, c.value value
          from v$session a, v$statname b, v$sesstat c
         where a.sid = c.sid
           and b.STATISTIC# = c.STATISTIC#
           and b.NAME = 'physical reads') reads,
       (select a.sid, a.username, b.name, c.value value
          from v$session a, v$statname b, v$sesstat c
         where a.sid = c.SID
           and b.STATISTIC# = c.STATISTIC#
           and b.NAME ='physical writes') writes
 where cpu.sid = reads.sid
   and reads.sid = writes.sid
   and cpu.username is not null;
  
select sql_text "SQL statement Text"
  from v$sqltext
 where address = &a;
 
--获取跟踪文件
select d.value || '\' || lower(rtrim(i.instance, chr(0))) || '_ora_' || p.spid || '.trc' trace_file_name
  from (select p.spid
          from v$mystat m, v$session s, v$process p
         where m.statistic# = 1
           and s.sid = m.sid
           and p.addr = s.paddr) p,
        (select t.instance
           from v$thread t,
                v$parameter v
          where v.name = 'thread'
            and (v.value = 0 or t.thread# = to_number(v.value))) i,
         (select value from v$parameter
           where name ='user_dump_dest') d
          
--跟踪session
--在使用sql_trace 之前现设置timed_statistics 为true;
--设置max_dump_file_size=unlimited;
alter session set max_dump_file_size = umlimited;
--全局动态启用
alter system set sql_trace = true;

--跟踪当前session
alter session set sql_trace = true;
--关闭当前session
alter session set sql_trace = false;
--跟踪其他session
exec dbms_system.set_sql_trace_in_session(sid, serial#, true);
--关闭其他session跟踪
exec dbms_system.set_sql_trace_in_session(sid, serial#, false);

--10046事件（是oracle提供的内部事件，是对sql_trace的增强）
--10046有是个级别
--Level1:启用标准的sql_trace功能，等价于sql_trace.
--Level4:等价于Level1+绑定值(bind value)
--Level8:等价于Level1+等待事件跟踪。
--Level12:等价于Level1 + Level4 + Level8.

--在全局设置10046
event ='10046 trace name context forever, level 12'

--对当前session设置
alter session set events '10046 trace name context forever';
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';

--对其他用户session设置10046
exec dbms_system.set_ev(sid, serial#, 10046, le, 'username');
--关闭其他用户session设置10046
exec dbms_system.set_ev(sid, serial#, 10046, 0, 'username');

select sid, serial#, username
  from v$session
 where username is not null;
 exec dbms_system.set_ev(144,5,10046,8, 'scott');
 
declare
    event_level    number;
begin
    for event_number in 10000..10999 loop
        sys.dbms_system.read_ev(event_number, event_level);
       
        if(event_level > 0) then
            sys.dbms_output.put_line('Event ' || to_char(event_number) ||
                                     'is set at level ' ||
                                     to_char(event_level));
        end if;
    end loop;
end;

--递归sql， 当发出一条简单的sql命令以后，oracle数据库在后台解析这条命令，并转换为oracle数据库的一系列后台操作。这些后台操作称为递归sql

--10046事件的使用
create table t as select * from dba_objects;

select file_id, block_id, blocks from dba_extents where segment_name ='T'

alter session set events '10046 trace name context forever, level 12';

select count(*) from t;

alter session set events '10046 trace name context off';

--查询等待时间最后累计到v$system_event中
select event, time_waited
  from v$system_event
 where event ='db file scattered read';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20948385/viewspace-622417/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20948385/viewspace-622417/