Need a Quick Sure Method to Produce a Formatted Explain Plan? This will help!
- by user702295
Please use the following on the production machine to get formatted explain
plan and sql trace using the SLOW sql (e.g. 'T_COMB_LIST.COMB_ID = 216') or
any other value that takes longer:
-- Open new session is SQL*Plus */
-- Make sure you are using updated PLAN_TABLE
-- This can be done by dropping it and recreate it by running:
-- SQL> @?/rdbms/admin/utlxplan.sql)
set lines 1000
set pages 1000
spool xplan_1.txt
EXPLAIN PLAN FOR
<<<<Replace this line with exactly the same query you used above. Force hard
parse by modifying the case of a character>>>>
@?/rdbms/admin/utlxplp
spool off
EXIT
--Open a second session is SQL*Plus
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET events '10046 trace name context forever, level 12';
<<<<Replace this line with exactly the same query you used above. Force hard
parse by modifying the case of a character>>>>
select 'verify cursor closed' from dual;
ALTER SYSTEM SET EVENTS '10046 trace name context off';
EXIT
Make sure spooled file is formatted properly and that the 10046 trace has relevant explain plan in it. Please Upload both files (10046 trace is generated in udump).
Need instructions to find udump?
sqlplus "/ as sysdba"
show parameters dump_dest
This will show you bdump, cdump and udump locations.
