Need a Quick Sure Method to Produce a Formatted Explain Plan? This will help!

Posted by user702295 on Oracle Blogs See other posts from Oracle Blogs or by user702295
Published on Wed, 6 Jun 2012 20:15:00 +0000 Indexed on 2012/06/06 22:44 UTC
Read the original article Hit count: 207

Filed under:

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.

© Oracle Blogs or respective owner

Related posts about /General Performance