【《TOP》讀書筆記】<3> Identifying Performance Problems

1. divide-and-conquer

當troubleshooting時，最有效的方法就是這種分而治之的方法。

將問題分解開來，逐個攻破。

2.10G的v$sess_time_model和v$sys_time_model

為了實現作者希望的divide-and-conquer method，如上這兩個檢視提供了分別從session和system級別分解DB Time的能力。

經過試驗，這兩個檢視只有開啟AWR之後才有資料，即statistics_level要為typical或者all。

另外再偷偷強調一句，使用AWR是需要license的。

作者提供瞭如下SQL：

WITH db_time AS
  (SELECT sid, value
   FROM v$sess_time_model
   WHERE sid = 1986
   AND stat_name = 'DB time')
SELECT stm.stat_name AS statistic,
trunc(stm.value/1000000,3) AS seconds,
trunc(stm.value/tot.value*100,1) AS "%"
FROM v$sess_time_model stm, db_time tot
WHERE stm.sid = tot.sid
AND stm.stat_name <> 'DB time'
AND stm.value > 0
ORDER BY stm.value DESC;

3.如何設定和檢視client info

BEGIN
dbms_session.set_identifier(client_id=>'hao client_id');
dbms_application_info.set_client_info(client_info=>'hao OS');
dbms_application_info.set_module(module_name=>'hao.sql',
action_name=>'hao is testing');
END;
/

SELECT sys_context('userenv','client_identifier') AS client_identifier,
sys_context('userenv','client_info') AS client_info,
sys_context('userenv','module') AS module_name,
sys_context('userenv','action') AS action_name
FROM dual;

SELECT client_identifier,
client_info,
module AS module_name,
action AS action_name
FROM v$session
WHERE sid = sys_context('userenv','sid');

4.各種enable/disable sql trace的方法

############
ALTER SESSION SET events '10046 trace name context forever, level 12';
ALTER SESSION SET events '10046 trace name context off';

############
dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 12, -- level
nm => NULL);

dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 0, -- level
nm => NULL)
############
--Session level
dbms_monitor.session_trace_enable(session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE)

dbms_monitor.session_trace_disable(session_id => 127,
serial_num => 29)

--Client level
dbms_monitor.client_id_trace_enable(client_id => 'hao client',
waits => TRUE,
binds => FALSE)

SQL> SELECT primary_id AS client_id, waits, binds
  2  FROM dba_enabled_traces
  3  WHERE trace_type = 'CLIENT_ID';

CLIENT_ID                                                        WAITS BINDS
---------------------------------------------------------------- ----- -----
hao client                                                       TRUE  FALSE

dbms_monitor.client_id_trace_disable(client_id => 'hao client')

--Component level
dbms_monitor.serv_mod_act_trace_enable(service_name => 'SYS$USERS',
module_name => 'sqlplusxxxx (TNS V1-V3)',
action_name => '',
waits => TRUE,
binds => FALSE,
instance_name => NULL);

SELECT primary_id AS service_name, qualifier_id1 AS module_name,
qualifier_id2 AS action_name, waits, binds
FROM dba_enabled_traces
WHERE trace_type = 'SERVICE_MODULE_ACTION';

dbms_monitor.serv_mod_act_trace_disable(service_name => 'SYS$USERS',
module_name => 'sqlplusxxxx (TNS V1-V3)',
action_name => '',
instance_name => NULL)

--database level
dbms_monitor.database_trace_enable(waits => TRUE,
binds => TRUE,
instance_name => NULL)

SELECT instance_name, waits, binds
FROM dba_enabled_traces
WHERE trace_type = 'DATABASE';

dbms_monitor.database_trace_disable(instance_name => NULL)
############

5.trace file的名字和session的mapping關係

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'
 ORDER BY s.sid;

11G only:

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

6.需找特定trace 檔案片段

例如按session查詢：

trcsess  session="19652.32061" *

trcsess [output=

]  [session=] [clientid=] [service=] [action=] [module=]