Re: DBA view slow

M Rothwell <ThisIsABadAddress_at_toobad.com> wrote:

>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

According to , in addition to others, Thomas Kyte( who I consider one of, if not the, best sources for Oracle info), the SYS and SYSTEM schemas (especially SYS) should not be analyzed because the recursive Sql Oracle generated over the years was highly optimized for the RULE-based optimizer..Having statistics on a SYS-owned table will cause your database to operate slower than it should.
(paraphrased from "Expert one-on-one Oracle" By Thomas Kyte..Published by Wrox)  

Just my 2c ( actually Thomas Kyte's..) Received on Thu Oct 14 2004 - 13:04:27 CDT