oracle优化方法,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';