Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DBA view slow

Re: DBA view slow

From: Joel Garry <joel-garry_at_home.com>
Date: 14 Oct 2004 16:56:39 -0700
Message-ID: <91884734.0410141556.31bee40a@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.
>
> 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
Received on Thu Oct 14 2004 - 18:56:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US