This content has been marked as final. Show 9 replies
johnpau2013 wrote:SQL SELECT statements
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.
For performance Tools you can use :
-OEM Enterprise manager
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 ..
we can also use ignite, foglight and OEM. if you understand what these tools will do, you can run appropriate SQLs to get the info you need.
you may look at dbtrends tool here [www.spviewer.com|http://www.spviewer.com]
this tools is used by DBAs for performance tuning and analysis based on AWR or STATSPACK data