Trace/TKPROF
|
AWR/ASH
|
TKPROF: TKPROF is an acronym for the
Transient Kernel PROFile, set via the sql_trace parameter, used to format a
trace file into readable form. As such TKPROF only deals with
diagnostics at the trace file level, as opposed to the system-level
information provided by Oracle AWR.
|
AWR: Oracle's Automatic Workload
Repository is a permanent database of performance tuning information, from
which standard elapsed time AWR report can be run along with hundreds of Oracle
AWR scripts. As such, AWR is a data warehouse for Oracle performance
tuning information that is much broader in scope than TKPROF. Also, unlike
TKPROF, AWR is a separately licensed product, requiring the diagnostic and
tuning pack licenses. ASH and AWR reports are very powerful tools in a
DBA’s arsenal, but they should be used in the right context and for the right
reason.
|
Trace gives us a view of
a single session.
|
AWR gives us a veiw of
the whole database, with all sessions.
|
Trace monitors all activity
for one session for detail analysis of one particular application. It offers
better control for precise evaluations of specific applications.
|
AWR produces averages for
what the entire database is doing over periods of time. It lists system
resource usage and details SQL performance.
|
If we are anticipating a
problem from an individual, or program, we would enable tracing for that
specific activity so that could analyze exactly where it spent its time.
|
AWR have also possibility
to monitor session using active session history (ASH). The ASH content is
like sampling 1% of the same details across every single session. By
capturing ASH we have a historic sample of every 10046 even when we're not
expecting a problem.
|
Steps to tune Database
1.
Generate Oracle AWR and ASH report
2. Compare problem-time AWR and ASH reports report with the reports from the same timeframe, 1 week ago
3. Use oracle performance tuning tools to do this comparison. I recommend to use Enteros UpBeat Performance Explorer-i , it automatically compares multiple performance snapshots and clarifies root causes of the spikes http://www.enteros.com/products_performanceexploreri.htm
4. For complex problems use Oracle 10046 trace and analyze wait events (tkprof waits=yes). For complex cases we should look into using trcsess to consolidate multiple traces into related groups
2. Compare problem-time AWR and ASH reports report with the reports from the same timeframe, 1 week ago
3. Use oracle performance tuning tools to do this comparison. I recommend to use Enteros UpBeat Performance Explorer-i , it automatically compares multiple performance snapshots and clarifies root causes of the spikes http://www.enteros.com/products_performanceexploreri.htm
4. For complex problems use Oracle 10046 trace and analyze wait events (tkprof waits=yes). For complex cases we should look into using trcsess to consolidate multiple traces into related groups
I hope you all have enjoyed reading this article. Comments are welcome....
