Re: DBA view slow

M Rothwell <ThisIsABadAddress_at_toobad.com> wrote in message news:<416da527$1_at_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
Received on Thu Oct 14 2004 - 01:26:39 CDT