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.
>
> 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

Might be a trick for the tool to "preload" dba_extents into the SGA buffers for subsequent performance.

Ask the DBA's to figure out what is causing it to be so slow. Latch waits? I/O waits? Too small NEXT on some table? See the OEM performance monitor. Large numbers of deleted extents? Select count from the table, is that quick, large? Do you have ALL_ROWS set? Do you see some cpu maxed out during the query? How big are buffers? Does OEM think stats have been run on the table? How many thousands of tables do you have?

jg

--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/23.56.html