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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Slow database, too MANY buffers???

Re: Slow database, too MANY buffers???

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Sat, 25 Jan 2003 05:20:49 -0800
Message-ID: <F001.0053A643.20030125052049@fatcity.com>


Jared.Still_at_radisys.com wrote:
>
> Yes, the only problem is that doing a join with the dba_extents
> query makes this run rather long.
>
> Jared

Because the join is done BEFORE the HAVING filtering. I would push the HAVING to an online view, and join on the output. And use sys.uet$, sys.seg$, sys.obj$ and sys.user$ rather than dba_extents.  

> "Thomas Day" <tday6_at_csc.com>
> Sent by: root_at_fatcity.com
> 01/24/2003 10:39 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: Slow database, too MANY buffers???
>
> So this is what you're looking for?
>
> col segment_name format a30
> col segment_type format a10
> select segment_name, segment_type, count(*)
> from dba_extents, x$bh
> where file_id = file# and dbablk between block_id and
> block_id + blocks - 1
> group by segment_name, segment_type
> HAVING count(*) > 5
> ORDER BY 3
> /
>
>
> Jared.Still
> @radisys.com To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: root cc:
> Subject: Re: Slow database,
> too MANY buffers???
>
> 01/24/2003 12:34
> PM
> Please respond
> to ORACLE-L
>
>
>
> I just use this script that I originally used for finding
> which object corrupt blocks are in.
>
> Serves well for this as well.
>
> Note that file_id is the incorrect column to
> use on a database with > 1022 data files.
>
> I think you need to use relative_fno in that case.
>
> Jared
>
> -- ora_1578.sql
> -- use args from ORA-1578 errors to find
> -- file and segment generating the error
>
> col cfileid new_value ufileid noprint
> col cblockid new_value ublockid noprint
>
> prompt File ID:
> set term off feed off
> select '&1' cfileid from dual;
>
> set feed on term on
> prompt Block ID:
> set term off feed off
> select '&2' cblockid from dual;
> set feed on term on
>
> --define ufileid=8
> --define ublockid=129601
>
> select file_name "FILE WITH CORRUPT BLOCK"
> from dba_data_files
> where file_id = &ufileid
> /
> col segment_name format a30
> col segment_type format a15
>
> select segment_name, segment_type
> from dba_extents
> where file_id = &ufileid and &ublockid between block_id and
> block_id + blocks - 1
> /
>
> undef 1 2
>
> "Thomas Day" <tday6_at_csc.com>
> Sent by: root_at_fatcity.com
> 01/24/2003 07:54 AM
> Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: Slow database, too MANY buffers???
>
> We'll I don't want to show my ignorance but I'll never learn if I don't
> ask. How do you get from DBABLK to PK_MATERIAL_ORDER_POOL?
>
> Jared.Still
> @radisys.com To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: root cc:
> Subject: Re: Slow database,
> too MANY buffers???
>
> 01/23/2003 04:09
> PM
> Please respond
> to ORACLE-L
>
> Well, I'm close.
>
> I just ran this on the DEV database for an app that is in the 'upgrade'
> process.
>
> FILE# DBABLK COUNT(*)
> ---------- ---------- ----------
> 10 38968 6
> 11 22753 6
> 11 40180 6
> 11 74893 6
> 16 104388 6
> 16 104511 66
>
> 6 rows selected.
>
> Which resolves to index PK_MATERIAL_ORDER_POOL.
>
> Looks like further investigation is in order.
>
> Jared
>
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sat Jan 25 2003 - 07:20:49 CST

Original text of this message

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