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: Jared Still <jkstill_at_cybcon.com>
Date: Sat, 25 Jan 2003 13:53:40 -0800
Message-ID: <F001.0053A8F1.20030125135340@fatcity.com>

Thanks Stephen, but I'm just not ambitious enough for that today. :)

Jared

On Saturday 25 January 2003 05:20, Stephane Faroult wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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 - 15:53:40 CST

Original text of this message

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