Re: Top SQL question

From: Chuck <skilover_nospam_at_softhome.net>
Date: Thu, 19 May 2005 09:57:40 -0400
Message-ID: <1116510467.480da28c20c5c509fb1eaf62d69d2520@bubbanews>

DA Morgan wrote:

> 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.

Aren't they both 10g features? I'm on 9i (see OP). All I can figure is that somewhere the application is issuing an alter session and changing something that affects the execution plan. Is there a view I can query to see the parameters in effect for a particular session other than my own?

TIA

-- 
To reply by email remove "_nospam"

Received on Thu May 19 2005 - 08:57:40 CDT