Turkbear wrote:
> M Rothwell <ThisIsABadAddress_at_toobad.com> wrote:
>
>
>>Ed Stevens wrote:
>>
>>
>>>On Wed, 13 Oct 2004 14:53:48 -0700, M Rothwell
>>><ThisIsABadAddress_at_toobad.com> wrote:
>>>
>>>
>>>
>>>>Oracle 9.2.0.4 on HP-UX
>>>>
>>>>I have a dev tool that runs a bunch of queries at start-up and when I'm
>>>>monitoring the db.
>>>>
>>>>The following query can take several minutes to return.
>>>>
>>>>SELECT 1
>>>> FROM SYS.DBA_EXTENTS
>>>>WHERE ROWNUM = 1
>>>>
>>>>I tried to do an explain plan on it, but dont have the proper privs.
>>>>The powers that be (corp DBA's) say that you shouldn't run stats on sys
>>>>tables.
>>>>
>>>>I cant change the query, or add a hint because it's embedded in the
>>>>tool. Is there anything I can have the DBA's do to make this query
>>>>perform better. I cant even figure out why the tool is running this query.
>>>>
>>>>Thanks
>>>>
>>>>Michael
>>>
>>>
>>>Just for grins, have you tried executing that query from SQL*Plus? If
>>>the tool runs 'a bunch of queries' how do you know this single query
>>>is the culprit?
>>
>>I captured all the SQL statements that were being run, then I ran each
>>statement in SQL*Plus. This is the only statement that was slow. The
>>others returned data quite quickly.
>>
>>I'll see if I can talk the DBA's into analyzing the table. I have my
>>doubts, but it cant hurt to ask.
>>
>>Michael
>
>
> Not a good idea..System tables should not be analyzed ( see Oracle Docs for reasons)
>
> The overarching question is why in the h**l any application would run such a meaningless query?
>
> ( as an aside, it takes microseconds to run in our 9.2 database)
>
>
Also, what is the consensus on analyzing system tables? Some say dont,
some say do. I looked in tahiti.oracle.com, but couldn't find it in the
brief searches I did. If it's OK, then could someone point to me the
docs that says this, so I can show that to the DBA's.
Michael
Received on Thu Oct 14 2004 - 11:45:36 CDT
