Just a categorized list of Oracle performance tools at this stage…
Oracle Enterprise Manager – OEM Tools – OEM 12 and forward
AWR Reports - Historical Windows ASH Reports - Real Time SQL Advisor SQL Profiler - uses active or historical GUI Explain Plan Top Active SQL - under performance tab and then the grandfather of AWR... statspack tkprof
PL/SQL Tools
dbms_profiler PL/SQL builtin package - standard PL/SQL profiler dbms_hprof PL/SQL builtin package - hierarchial profiler dbms_trace PL/SQL builtin package - detailed PL/SQL trace dbms_application_info PL/SQL builtin - application informantion plscope - list identifiers used in the scope of your program and packages - including standard packages
SQLPlus Tools
Explain Plan - create plan table first in schema with $ORACLE_HOME/rdbms/admin/utlxplan.sql V$SESSION_LONG_OPS - View that shows long running operations / SQL oradebug - oradebug help at sqlplus command prompt for options AWR SQL Script - if no access to OEM Diagnostics License - $ORACLE_HOME/rdbms/admin/awrrpt.sql ls $ORACLE_HOME/rdbms/admin/awr*.sql - for a variety awr scripts ls $ORACLE_HOME/rdbms/admin/sp*.sql - for a variety of statspack scripts set autotrace on -- trace will be displayed after each SQL execution alter session set sql_trace = TRUE; You can enable SQL Trace in another session by using the DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure. e.g. EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>&sidIwant, serial#=>&serialIwant, sql_trace=>TRUE);
SQL Developer Tools
Explain Plan - under both Monitor Sessions and Real Time SQL Monitor below PL/SQL Debug - use Menu Item Run-> to set breaks, run, step into etc. Top Active SQL - Menu Item Tools->Monitor Sessions Top Active SQL - Menu Item Tools->Real Time SQL Monitor Menu Item View->DBA
OS Tools
tkprof / trace
References:
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm https://docs.oracle.com/database/121/ADFNS/E41452-07.pdf https://docs.oracle.com/database/121/ADFNS/adfns_plscope.htm#ADFNS02201 http://stevenfeuersteinonplsql.blogspot.com/2015/09/zero-tolerance-for-out-of-scope.html