I want some inputs from DBA's.
Do they use any other third party tools for there performance tuning for there Oracle database apart from Oracle OEM ? If YES what tools are they.
Thanks in advance.
If you know what you are trying to tune, there is a fair bit of tuning information available using the reports under the DBA module in SQL Developer. Good place to start is the AWR reports for the time window you want to monitor for tuning.
There are several tools, it depends what do you want to tune,
SQL Tuning ,
Disk I/O Tuning,-
AWR, ADDM, ASH, Explain Plan, TKProf, extended TKProf, V$ views, OS level tools, Ask for system administrator help
1. Statspack - -> License Not Required
2. AWR (Automatic Workload Repository) - - > Extra License Required for this apart from Oracle EE
ADDM, ASH etc
3. Explain Plan & TKprof - - > In development stages, if you use this for complicated queries then most performance issues we can eliminate in production database
4. Oracle EM - -> License Not Required
5. OS Level Tools - sar, vmstat, netstat, TOP etc we can use from OS level (Depending on OS)
Without any additional licensing required for AWR/ADDM/ASH, I usually use following Oracle tools (no third party tool):
1. at instance level : Statspack (see <ORACLE_HOME>/rdbms/admin/spdoc.txt) still available in 11.2 although not documented any more in official Oracle documentation
2. at session level: SQL trace and TKPROF (see http://www.oracle-base.com/articles/10g/sql-trace-10046-trcsess-and-tkprof-10g.php)
3. at statement level:
- SQL*Plus autotrace http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037226
- DBMS_XPLAN package http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#ARPLS7012 especially because:
It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.
Edited by: P. Forstmann on 17 nov. 2012 10:12
see http://dboptimizer.com/2011/01/12/best-oracle-peformance-tools/ for some ideas.
Personally I really like ASH Viewer and orasrp when tuning&diagnostic pack are not available, and of course statspack on the side ..