Oracleパフォーマンスチューニング学習ノート(2)---Oracle SQL TRACEおよびTKPROFで使用
4137 ワード
Oracle SQL TRACE TKPROF
sql trace
1. 、 、
2.cpu
3.
4.
5.
1. dump
show parameter dump;
background_dump_dest: alertlog trace .
user_dump_dest: trace .
max_dump_file_size: .
alter system set max_dump_file_size=500;
alter system set user_dump_dest='';
2. SQL TRACE
A.timed_statistics :( dba)
timed_statistics=true sql trace 、
:
alter system set timed_statistics=true;
:
alter session set timed_statistics=true;
dump
B.sql_trace (dba )
sql_trace=true ,false .
:
alter system set sql_trace=true scope=spfile;
:
alter session set sql_trace=true;
sql_trace
execute rdbms_system.set_sql_trace_in_session(SID,SERIAL#,true);
ps:9 SID,7 SERIAL# true
:
1. dba
sqlplus /nolog
conn /as sysdba
2.
alter session set timed_statistics=true;
dba :
alter session set sql_true=true;
dba ( sys tbs,sysdba ):
select sid,serial#,command,username
from v$session
where username='TBS';
SID:121
SERIAL#:4786
exec dbms_system.set_sql_trace_in_session(121,4786,true);
TBS :
:<oracle-SID>-ora-<PSID>.trc
:<oracle-SID>:oracle SID.
<PSID>: sid.
3.
paddr, paddr spid.
select spid from v$process
where addr = (
select paddr from v$session
where username='TBS'
);
oracle-SID ticket,spid=35572, : ticket-ora-35572.trc.
TKPROF :
TKPROF
tkprof sql trace .
tkprof .trc , . .trc ,
, fchela,
elapsed time fetching (
time_statistics=true), .prf sql
。 sys, no sys
sql , , 。
1.
tkprof inputfile outputfile [optional | parameters ]
:
explain=user/password explain SQL trace
table=schema.table tkprof sql trace
insert=scriptfile scriptfile , tkprof sql
sys=[yes/no] sys sql
print=number sql
record=recordfile recorderfile , sql
sort=sort_option sql trace
sort_option
prscnt
prscpu cpu
prsela
prsdsk
prsqry
prscu
execnt
execpu cpu
exeela
exedsk
exeqry
execu
exerow
exemis
fchcnt
fchcpu cpu
fchela
fchdsk
fchqry
fchcu
fchrow
TKPROF :
tkprof ticket-ora-35572.trc ticket_tbs.prf aggregate=yes sys=no sort=fchela