Using EXPLAIN, TRACE, and SQL Plan Management (Developer and DBA)
Finding and fixing problem queries has a lot to do with using the tools that are available. Different tools need to be used for different situations. The tools covered in this chapter are Oracle’s provided utilities: SQL TRACE, TKPROF, EXPLAIN PLAN, DBMS_XPLAN, and STORED OUTLINES (previously used, and to convert to what is now used: SQL Plan Baselines/plan stability). These tools were enhanced, including the addition of the DBMS_MONITOR and DBMS_SESSION packages and TRCSESS, in 10g. The SQL tracing options have been centralized and extended using the DBMS_MONITOR package. ...