Hi all,
I'm late to the party on this, but I'm trying to understand what the
results of Jonathon's query mean.
I have a database that I suspect has a "too large" buffer cache. Here's
the SGA:
SQL> show sga
Total System Global Area 864323352 bytes
Fixed Size 280344 bytes
Variable Size 167772160 bytes
Database Buffers 687865856 bytes
Redo Buffers 8404992 bytes
Jonathan's query:
SQL> select file#, dbablk, count(*)
2 from x$bh
3 group by
4 file#, dbablk
5 having count(*) > 5
6 ;
FILE# DBABLK COUNT(*)
---------- ---------- ----------
1 422 6
1 443 6
1 9077 10
1 12505 6
1 14951 6
1 15183 6
1 15231 6
1 17267 14
1 17274 9
1 17481 6
1 17483 6
1 17533 6
1 17630 6
4 7 6
5 5432 6
5 5433 6
5 5472 6
5 5473 6
5 5512 6
5 5552 6
5 5553 6
5 5785 6
5 21894 6
5 21898 6
5 63412 6
5 63413 6
5 63414 6
5 63415 6
5 63416 6
5 63417 6
5 63418 6
5 63419 6
5 63420 6
5 63421 6
5 63422 6
5 63423 6
5 63424 6
5 63425 6
5 63426 6
5 63427 6
5 63428 6
5 63429 6
5 63430 6
5 63431 6
5 63432 6
5 63433 6
5 63434 6
5 63435 6
5 63436 6
5 63437 6
5 63438 6
5 70658 6
5 70659 6
5 320502 6
5 354711 6
5 354714 6
5 354721 6
5 354723 6
6 71860 81
6 71864 21
6 71871 73
6 71872 21
6 71874 101
6 71875 8
6 71898 161
6 71905 106
6 71913 23
6 71915 23
6 71922 23
6 71924 26
6 71929 42
6 71932 23
6 71934 21
6 71937 23
6 71939 15
6 71942 23
6 71944 37
6 71946 21
6 71947 23
6 71949 8
6 71951 8
6 71952 24
6 71955 23
6 71957 9
6 71968 8
6 71971 23
6 91747 14
6 91752 23
6 91759 9
6 98815 41
6 121404 7
6 172630 21
6 274077 21
6 274089 25
6 274092 23
6 274094 23
6 274096 8
FILE# DBABLK COUNT(*)
---------- ---------- ----------
6 274098 23
6 274099 21
99 rows selected.
So, the count(*) is the number of copies of that block in the SGA?
Is it desirable to keep this number down (<7)?
If so, why? Doesn't a CR copy of a block have to be done for each query,
assuming they are performed at different points in time?
If the SGA were smaller, I assume that a block would be aged out or the
SGA resulting in a lower count. Is this faster than just reading another
copy into the SGA (and having a higher count)?
Thanks!
- Jerry
:P
-----Original Message-----
Sent: Saturday, January 25, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
Thanks Stephen, but I'm just not ambitious enough
for that today. :)
Jared
On Saturday 25 January 2003 05:20, Stephane Faroult wrote:
> [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: Jared Still
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Cunningham, Gerald
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).
