Re: Top SQL question

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 18 May 2005 22:09:19 -0700
Message-ID: <1116479098.652901@yasure>

Noons wrote:
> DA Morgan wrote:
>

>>>>>TIA
>>>>
>>>>
>>>>Should they be the same? I would hope so but what version of TOAD

>
> and is
>

>>>>it one that Quest has certified against 10gR1?
>>>
>>>
>>>The database is 9.2.0.5. I'm using a 10g OEM to look at the top sql

>
> (as
>

>>>well as a 9i OEM).
>>>
>>>Toad version is 8.0.0.47 which I believe is certified for all

>
> releases
>

>>>of Oracle up to 10g.
>>
>>Then I have no explanation for you other than the possibility that

>
> they
>

>>are using different data dictionary objects from which to formulate
>>their answer ... and one of them, hopefully not both of them, is

>
> wrong.
>
> Check out the "plan_table" versions each uses. They need to be up to
> date for each of the products. And if any of them uses the Oracle
> default one, then that has to match the version in use. And darn it,
> they DO change from version to version!

Which is precisely why Oracle created the DBMS_XPLAN built-in package. Don't use that package and your chances of getting the correct result decrease dramatically.

Also note for the record that building the plan table with UTLXPLAN.SQL is no longer best practice. Instead run CATPLAN.SQL. Stand still very long and Oracle will pass you by.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Thu May 19 2005 - 00:09:19 CDT