Oracle - What are the tools provided by oracle to assist performace tuning?
posted by Babu Kunwar
What are the tools provided by oracle to assist performace tuning?
OEM which stands for Oracle Enterprise Manager is a valuable set of tools that
facilitates the comprehensive management of all components of an Oracle
infrastructure, including Oracle database instances, Oracle application
servers, and web servers. If a management agent exists for a third-party
application, then OEM can manage the third-party application in the same
framework as any Oracle-supplied target. OEM is fully web-enabled via Netscape
or Internet Explorer, and as a result any operating system platform that
supports Netscape or IE can be used to launch the OEM console.
AWR stands for Automatic Workload Repository tool which is built in every
Oracle database. It captures snapshots of all key statistics and workloads in
the database at 60-minute intervals by default. We can keep the statistics in
AWR for seven days, after which the oldest statistics can be dropped. Both the
snapshot intervals and the retention period can be adjusted according to
environment. The AWR maintains historical record of how the database is being
used over time? and helps to diagnose and predict problems long before they can
cause a database failure.
SPA stands for SQL Performance Analyzer. It is very similar to Database Replay
but with some important differences. SPA doesn’t record anything. We can
capture the SQL statements from the SQL Tuning Sets or library cache, or we can
write our own SQL statements. Once it gets a number of SQL statements, it
replays them against the database after changing some parameter that we
specify.
TKProf is used to accurately assess the efficiency of the SQL statements during
application run. For best results, we need to use this tool with EXPLAIN PLAN.
We can run the TKPROF program to format the contents of the trace file and
place the output into a readable output file. That is it converts Oracle trace
files into a more readable form.
Statspack is a utility provided by Oracle for performance monitoring and
reporting. Statspack stores the performance statistics permanently in Oracle
tables, which can later be used for reporting and analysis.
More links
Explain the purpose of tuning oracle database.
Could you explain the areas where tuning of database is required?
What are the tools provided by oracle to assist performace tuning? Explain them
in brief
What is cost based optimization? When is it triggered?
Explain the factor that can cause the execution plan of a query to change
What is index monitoring feature in oracle? Explain how to use it for index
optimization............
|
