SQL调优和诊断之何时使用何工具？

・企业版（Enterprise Edition ）
・企业版（Enterprise Edition ） + Diagnostics Pack
・企业版（Enterprise Edition ） + Tuning Pack（include Diagnostics Pack）
・企业版（Enterprise Edition ）以外的版本，如标准版（Standard Edition），个人版（Personal Edition ）等

1.通过某些条件和方法可以重现的问题
2.不可以重现，需要实时分析的问题
3.不可以重现，需要事后分析的问题

AWR、ADDM、ASH及其相关的视图、表和程序包等
Performance monitoring and diagnostics (database and host)
Automatic Workload Repository (AWR)★
AWR Warehouse★
Automatic Database Diagnostic Monitor (ADDM)★
Compare Period ADDM★
Real Time ADDM★
Active Session History (ASH)★
ASH analytics★
Performance Hub
Exadata Cell Grid Administration
Exadata Cell Grid Performance
Exadata Cell Group Health Overview page
Exadata Resource Utilization
Blackouts
Notifications
Metric and Alert/Event history
User-Defined Metrics and Metric Extensions
Management Connectors
Dynamic metric baselines and Adaptive metric thresholds
Monitoring templates and Template Collections
Replay Compare Period Report
Supporting functionality to perform per stream bottleneck detection and per component top wait event analysis

Licensed Command-Line APIs：
程序包（Package）:
DBMS_WORKLOAD_REPOSITORY package
DBMS_ADDM package
DBMS_ADVISOR package (if advisor_name=ADDM or task_name=ADDM prefix)

功能方法（Function）:
DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT function

视图（Views）:
V$ACTIVE_SESSION_HISTORY(X$ASH)
DBA_STREAMS_TP_PATH_BOTTLENECK
DBA_ADDM_*
MGMT$*

数据（Data）:
DBA_STREAMS_TP_COMPONENT_STAT (where STATISTIC_UNIT = 'PERCENT')
DBA_HIST_* ( except DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, DBA_HIST_UNDOSTAT)
DBA_ADVISOR_* (when ADVISOR_NAME =ADDM or TASK_NAME =ADDM*)

报告（Reports）:
在/rdbms/admin/ 路径下的这些脚本
awrrpt.sql, awrrpti.sql, awrgrpt.sql, awrgrpti.sql, awrgdrpt.sql, awrgdrpi.sql, addmrpt.sql, addmrpti.sql, ashrpt.sql, ashrpti.sql, awrddrpt.sql, awrddrpi.sql, awrsqrpi.sql, awrsqrpt.sql, awrextr.sql, awrload.sql, awrinfo.sql, spawrio.sql, spawrrac.sql.

SQL Access Advisor
SQL Tuning Advisor
Oracle Database In-Memory Advisor
Automatic SQL Tuning
SQL Tuning Sets
SQL Profiles
Real-time SQL and PL/SQL Monitoring
Real-time Database Operations Monitoring
Reorganize objects

Licensed Command-Line APIs:
程序包（Package）:
DBMS_ADVISOR package (if advisor_name=SQL Tuning Advisor or SQL Access Advisor)
DBMS_SQLTUNE

视图（Views）:
V$SQL_MONITOR
V$SQL_PLAN_MONITOR

报告（Reports）:
在/rdbms/admin/ 路径下的这些脚本
sqltrpt.sql

Database Licensing Information User Manual 12c
>Oracle Diagnostics Pack
>Oracle Tuning Pack

・EXPLAIN PLAN
・Autotrace (SQL*PLUS)
・动态视图
・AWR SQL Report
・StatsPack SQL Report
・DBMS_XPLAN
・SQLTXPLAIN(SQLT)
・SQL Tuning Health-Check Script (SQLHC)
・SQL TRACE（10046）
・OPTIMIZER TRACE（10053 ）
・Real-Time SQL Monitoring
・DBMS_SQLDIAG

・动态视图 （dynamic performance views）
V$SQL
V$SQL_SHARED_CURSOR
V$SQL_*

和数据库整体相关时：
V$SESSION
V$PROCESS
V$BGPROCESS
V$LOCK
V$LOCKED_OBJECT
V$SESSION_WAIT
V$LATCH
V$LATCHHOLDER
V$ROWCACHE

・Real-Time SQL Monitoring (11g)
・SQLTXPLAIN(SQLT)
>XTRACT mode
・SQL Tuning Health-Check Script (SQLHC)
・10053：DBMS_SQLDIAG.DUMP_TRACE

・StatsPack Report
・StatsPack SQL Report
・AWR Report
・ADDM
・ASH
・AWR SQL Report
・Historical SQL Monitoring (12c)