DB Time Performance method by Graham Wood Brief introduction to Oracle Perf. Tuning History What is DB Time, datasources of it and method Dark ages :) : Counters-Ratios-Estat/Bstat Renaissance : Counters to Timers-Wait Interface-Statspack More recent methods : Time based methods; Yapp(oraperf.com - instance based)-Method R(hotsos.com - session, 10046 trace) Modern methods(10g) : DB Time Tuning; AWR-ADDM-ASH Performance is always about time, perf. improvement usually means doing things faster. So finding where system is spending yime and reducing it our target. DB Time is time spent in database calls by foreground sessions, includes cpu time-io time-wait time and excludes wait time DB Time = Sum of all DB Time Over All Sessions 10g EM performance tab is based on DB Time, ADDM-AWR-ASH and Server generated alerts are also based on DB Time metric. More users > more calls > DB Time increases (careful can be more than wall clock, all sessions summed up) Longer Transactions > Longer calls > DB Time increases v$active_session_history is a collection of regular samples of active session attributes. ASH samples 1 sec. in memory, 10 sec.s to AWR. EM Top Activity screen is based on ASH. some other views for DB Time : v$sql - v$systime_model - v$sys_classtime_history - v$wait_classtime_history One important adv. : DB Time can be aggregated at multiple levels, not only instance or session based : database/instance/service/module/action/session/user/sql-id/even row source method : discover the problem > identify the scope of the problem > diagnose the problem > identify the scope of the solution(do not chance an instance parameter for just one sql's benefit)