Re: DBA view slow

ilaletin_at_usa.net (Igor Laletin) wrote in message news:<f9226414.0410132226.59de77e7_at_posting.google.com>...
> 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

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 -- Received on Thu Oct 14 2004 - 08:52:03 CDT