[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/24/2003 10:39 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> > 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" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/24/2003 07:54 AM > Please respond to ORACLE-L > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
