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

M Rothwell <ThisIsABadAddress@toobad.com> wrote in message news:<416da527$1@usenet01.boi.hp.com>...
> 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.

Yeah, used to be the case before 9.2. For your version it's OK to
analise data dictionary. Your query will definetely run faster with
cbo (I had the same problem). So while I didn't notice any problems
with analysed data dictionary. If want play it safe you may want to
analise just the base tables for dba_extents.

You must have lotsa extents, either big application or segments with
badly sized extents.

Cheers,
Igor

> 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

On Wed, 13 Oct 2004 14:53:48 -0700, M Rothwell
<ThisIsABadAddress@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?

ilaletin@usa.net (Igor Laletin) wrote in message news:<f9226414.0410132226.59de77e7@posting.google. com>...
> M Rothwell <ThisIsABadAddress@toobad.com> wrote in message news:<416da527$1@usenet01.boi.hp.com>...
> > 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.
>
> Yeah, used to be the case before 9.2. For your version it's OK to
> analise data dictionary. Your query will definetely run faster with
> cbo (I had the same problem). So while I didn't notice any problems
> with analysed data dictionary. If want play it safe you may want to
> analise just the base tables for dba_extents.
>
> You must have lotsa extents, either big application or segments with
> badly sized extents.
>
> Cheers,
> Igor
>
> > 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

Igor, if you use Locally Managed Tablespaces then to answer queries
against the dba_extents, dba_free_space, and dba_segments views Oracle
has to access the bitmaps that control extent space allocation for
each and every data file in LMT tablespaces. This can add
considerable time to queries against these views compared to the same
queries ran against dicitonary managed space. But LMT tablesaces
provide much more efficient extent allocation due to insert activity
so the possible increased DBA query cost is more than made up in
system performance benefit.

Bad/old statistics on the SYS owned base tables as alreay mentioned is
another possbile cause.

HTH -- Mark D Powell --

Ed Stevens wrote:

> On Wed, 13 Oct 2004 14:53:48 -0700, M Rothwell
> <ThisIsABadAddress@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

M Rothwell <ThisIsABadAddress@toobad.com> wrote:

>Ed Stevens wrote:
>
>> On Wed, 13 Oct 2004 14:53:48 -0700, M Rothwell
>> <ThisIsABadAddress@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)

Turkbear wrote:

> M Rothwell <ThisIsABadAddress@toobad.com> wrote:
>
>
>>Ed Stevens wrote:
>>
>>
>>>On Wed, 13 Oct 2004 14:53:48 -0700, M Rothwell
>>><ThisIsABadAddress@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)
>
>

The problem is, that it takes 2 to 4 minutes to run on my box. I can
return millions of records in a hurry, but not this query. I dont know
why the app runs that query, but it does, and I cant change that.

Turkbear wrote:

> M Rothwell <ThisIsABadAddress@toobad.com> wrote:
>
>
>>Ed Stevens wrote:
>>
>>
>>>On Wed, 13 Oct 2004 14:53:48 -0700, M Rothwell
>>><ThisIsABadAddress@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

M Rothwell <ThisIsABadAddress@toobad.com> wrote:

>Turkbear wrote:
>
>> M Rothwell <ThisIsABadAddress@toobad.com> wrote:
>>
>>
>>>Ed Stevens wrote:
>>>
>>>
>>>>On Wed, 13 Oct 2004 14:53:48 -0700, M Rothwell
>>>><ThisIsABadAddress@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..)

Turkbear wrote:
> 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)
>
>
IIRC isn't this for <= 8i, though? I thought that in 9i it was sometimes
done (but only on recommendation from Support?), and in 10g it is the
default?

disclaimer, I've never seen any sys/system schemas analyzed.