yenkat_muratee_at_yahoo.com wrote:
> What is the difference of utlxplan.sql vs catplan.sql in term of
> advantage and disadvantage between these 2?
>
> yenkat...
>
> On May 4, 11:38 am, DA Morgan <damor..._at_psoug.org> wrote:
>> Michel Cadot wrote:
>>> "Ben" <bal..._at_comcast.net> a écrit dans le message de news: 1178295834.160371.234..._at_c35g2000hsg.googlegroups.com...
>>> | 9.2.0.5 EntEd AIX5L
>>> |
>>> | What do I have to do to turn on the predicates display for
>>> | xplan.display? I've been using Sql Developer a little here recently
>>> | and notice that when you do an explain plan in there it is supposed to
>>> | show the predicate information related to each step. Well it isn't
>>> | working. I haven't been able to get it to work using xplan.display
>>> | either though. I'd would guess that it's a setting maybe a parameter
>>> | that I don't have selected but I'm not sure. My compatible parameter
>>> | is set to 8.1.0 but the opt_feat_enable is set to 9.2.0 Here's a
>>> | sample of what I get.
>>> |
>>> | SQL> explain plan set statement_id = 'abc'
>>> | 2 for
>>> | 3 SELECT syedoc,
>>> | 4 sydoco
>>> | 5 FROM proddta.f47011
>>> | 6 WHERE sypnid = 'DOC'
>>> | 7 AND syedsp = 'Y'
>>> | 8 AND sycrmd = ' '
>>> | 9 ORDER BY syedoc;
>>> |
>>> | Explained.
>>> |
>>> | SQL> SELECT * FROM
>>> | TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
>>> |
>>> | PLAN_TABLE_OUTPUT
>>> | -------------------------------------------------------------------------------
>>> |
>>> | ----------------------------------------------------------------------------
>>> || Id | Operation | Name | Rows | Bytes |
>>> | Cost |
>>> | ----------------------------------------------------------------------------
>>> || 0 | SELECT STATEMENT | | 9 | 279
>>> || 14 |
>>> || 1 | SORT ORDER BY | | 9 | 279
>>> || 14 |
>>> || 2 | TABLE ACCESS BY INDEX ROWID| F47011 | 9 | 279
>>> || 12 |
>>> || 3 | INDEX RANGE SCAN | F47011_1 | 28 |
>>> || 1 |
>>> | ----------------------------------------------------------------------------
>>> |
>>> | Note: cpu costing is off, PLAN_TABLE' is old version
>>> |
>>> | 11 rows selected.
>>> |
>>> Drop your OLD plan_table and recreate it with your current version script
>>> ($ORACLE_HOME/rdbms/admin/utlxplan.sql)
>>> Regards
>>> Michel Cadot
>> I don't have a copy of 9i installed in the lab but my first instinct
>> was "no no no."
>>
>> In 10g utlxplan.sql is deprecated. You want to run catplan.sql.
>> Is it there in 9i?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
Please do not top post. Scroll to the bottom to reply.
CATPLAN creates a global temporary table named plan_table$ that offers
numerous advantages. Among them:
- DELETE doesn't wipe out other user's plans
- TRUNCATE doesn't wipe out other user's plans
- No need to empty the table after each session
- Less overhead on the system
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri May 04 2007 - 20:43:54 CDT
