Running Statspack in Level 6 for SQL Plan
This is the best way of getting the sql plans of bad performing queries from the production environment for tuning activites. Steps for the statspack report in getting the Sql Plan(S).
1) Run the statspack with level 6
SQL> execute statspack.snap(i_snap_level=>6);
2) After Two consecutive snaps with level 6 - run the spreport and generate the statspack.
3) And after generating the statspack look for the hash values** from the statspack reports top sql session and run the sprepsql.sql*.
For more information please go through the SPDOC.TXT under any Oracle_Home/rdbms/admin folder
*The SQL report sprepsql.sql, displays statistics, the complete SQL text and (if level 6 snapshot has been taken), information on any SQL Plan(s) associated with that statement.
** CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
————— ———— ————– —— ——– ——— ———-
33,420,468 282,084 118.5 71.5 2025.72 4154.73 3131397695
Module: xlsTT_Load@eicsrv2 (TNS V1-V3)
http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=4205