Oracle-L: Re: Slow database, too MANY buffers???

From: <Jared.Still_at_radisys.com>
Date: Fri, 24 Jan 2003 14:24:39 -0800
Message-ID: <F001.0053A2A7.20030124142439@fatcity.com>

Yes, the only problem is that doing a join with the dba_extents query makes this run rather long.

Jared

"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:
INET: Jared.Still_at_radisys.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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Thomas Day
INET: tday6_at_csc.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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Jared.Still_at_radisys.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