Well, it's not a perfect solution but the following query will show you
what is in your buffer pool (v$bh) and match it to each objects total
segment size and give you a report of what is really taking up the buffer
pool and was percent of each object is in memory. It's not a perfect (old
old old) script but the output can be enlightening. If you see indexes
that are large but not much of it is in memory, well, you can draw your own
conclusions. I have been using versions of this script since oracle
7-something but haven't run it in v9. Have fun......
set verify off
set pagesize 36
set linesize 132
set pause off
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off
REM
----------------------------------------------------------------------------
col db_block_size new_value nv_db_block_size noprint
select value db_block_size from v$parameter where name = 'db_block_size';
REM
----------------------------------------------------------------------------
ttitle "V$BH, Ordered by Status and Count"
col status format a10 heading "Block|Status|Types"
col status2 format a25 heading "Block|Status|Description"
col block_status format a32 heading "------Block Status
Details------|Dirty-Temp-Ping-Stale-Direct-New"
col cnt format 99999999 heading "Blocks"
col sga_kbytes format 99999999 heading "Kbytes"
col sga_mbytes format 99999999 heading "Mbytes"
compute sum of cnt on report
compute sum of sga_kbytes on report
compute sum of sga_mbytes on report
break on report
select count(*) cnt,
count(*) * (&nv_db_block_size / 1024) sga_kbytes,
trunc((count(*) * (&nv_db_block_size / 1024)) / 1024) sga_mbytes,
status,
decode(upper(status), 'FREE', 'not currently in use',
'XCUR', 'exclusive',
'SCUR', 'shared current',
'CR', 'consistent read',
'READ', 'being read from disk',
'MREC', 'in media recovery mode',
'IREC', 'in instance recovery mode',
'UNKNOWN') status2,
dirty || ' ' || temp || ' ' || ping || ' ' || stale || '
' || direct || ' ' || new block_status
from v$bh
group by status,
dirty || ' ' || temp || ' ' || ping || ' ' || stale || '
' || direct || ' ' || new
order by 2,1;
clear break
clear compute
REM
----------------------------------------------------------------------------
ttitle off
set pause off
PROMPT
PROMPT Sort BY Selections
prompt For DB Buffer Cache Map
PROMPT ------------------------
PROMPT 1 = Object Owner
PROMPT 2 = Object Name
PROMPT 3 = Object Type
PROMPT 4 = SGA Blocks
PROMPT 5 = SGA KBytes
PROMPT 6 = Segment KBytes
PROMPT 7 = Percent in SGA
ACCEPT USER_INPUT1 NUMBER PROMPT 'Please enter one now:>'
col sort_by_number new_value sort_by_number_value noprint
col sort_by_text new_value sort_by_text_value noprint
select decode(&USER_INPUT1, 1,1, 2,2, 3,3, 4,4, 5,5, 6,7, 7,9, 4)
sort_by_number,
decode(&USER_INPUT1, 1, 'Object Owner',
2,'Object Name',
3,'Object Type',
4,'SGA Blocks',
5,'SGA KBytes',
6,'Segment KBytes',
7,'Percent in SGA',
'SGA Blocks') sort_by_text
from dual;
REM
----------------------------------------------------------------------------
PROMPT
PROMPT Sort ORDER Selections
PROMPT ---------------------
PROMPT 1 = Descending
PROMPT 2 = Ascending
ACCEPT USER_INPUT2 NUMBER PROMPT 'Please enter one now:>'
col order_by_text new_value order_by_text_value noprint
select decode(&USER_INPUT2, 1,'Desc', 2,'Asc', 'Desc') order_by_text
from dual;
REM
----------------------------------------------------------------------------
ACCEPT USER_INPUT3 CHAR PROMPT 'Include SYS objects (Y/N):>'
col include_sys new_value nv_include_sys noprint
select decode(upper('&USER_INPUT3'), 'Y','Y', 'N') include_sys
from dual;
REM
----------------------------------------------------------------------------
set feedback on
create table tmp_v$cache
as select owner#, name, kind, partition_name, count(*) sga_cnt, count(*)
* (&nv_db_block_size / 1024) sga_kb
from v$cache
group by owner#, name, kind, partition_name;
create index tmp_v$cache_idx
on tmp_v$cache(owner#, name, kind, partition_name, sga_cnt, sga_kb);
analyze table tmp_v$cache
compute statistics
for table
for all indexes
for all indexed columns;
create table tmp_dba_users
as select user_id, username
from dba_users;
create index tmp_dba_users_idx
on tmp_dba_users(user_id, username);
analyze table tmp_dba_users
compute statistics
for table
for all indexes
for all indexed columns;
create table tmp_dba_segments
as select owner, segment_name, segment_type, partition_name, blocks *
(&nv_db_block_size / 1024) seg_kb
from dba_segments;
create index tmp_dba_segments_idx
on tmp_dba_segments(owner, segment_name, segment_type, partition_name,
seg_kb);
analyze table tmp_dba_segments
compute statistics
for table
for all indexes
for all indexed columns;
REM
----------------------------------------------------------------------------
PROMPT spooling output to /tmp/tool_db_buffer_map2_&1..lst
PROMPT
PROMPT Working, please wait...
set feedback off termout off
spool /tmp/tool_db_buffer_map2_&1..lst
ttitle 'Sga Usage, Ordered by &sort_by_text_value &order_by_text_value '
col username heading 'Object Owner' format a20
col name heading 'Object Name' format a40
col kind heading 'Object Type'
col sga_cnt heading 'SGA|Blocks' format 9999999
col sga_kb heading 'SGA|KBytes' format 999999
col sga_mb heading 'SGA|MBytes' format 99999
col seg_kb heading 'Segment|KBytes' format 9999999
col seg_mb heading 'Segment|MBytes' format 99999
col pct_load heading 'Pct In|SGA' format 999.999
select /*+ RULE */
B.username
username,
nvl(substr(A.name || decode(A.partition_name,NULL,NULL,'.' ||
A.partition_name
),1,40
), 'FREE BLOCKS'
)
name ,
A.kind
kind ,
A.sga_cnt
sga_cnt ,
A.sga_kb
sga_kb ,
trunc(A.sga_kb / 1024)
sga_mb ,
C.seg_kb
seg_kb ,
trunc(C.seg_kb / 1024)
seg_mb ,
(A.sga_kb / C.seg_kb) * 100
pct_load
from tmp_dba_segments C, tmp_dba_users B, tmp_v$cache A
where A.owner# = B.user_id
and ((B.username != 'SYS' ) or
(B.username = 'SYS' and '&nv_include_sys' = 'Y')
)
and B.username = C.owner
and A.name = C.segment_name
and A.kind = C.segment_type
and nvl(A.partition_name,'NULL') = nvl(C.partition_name,'NULL')
union all
select /*+ RULE */
C.owner
username,
nvl(substr(C.segment_name || decode(C.partition_name,NULL,NULL,'.'
|| C.partition_name
),1,40
), 'FREE BLOCKS'
)
name ,
C.segment_type
kind ,
0
sga_cnt ,
0
sga_kb ,
0
sga_mb ,
C.seg_kb
seg_kb ,
trunc(C.seg_kb / 1024)
seg_mb ,
0
pct_load
from tmp_dba_segments C
where ((C.owner != 'SYS' ) or
(C.owner = 'SYS' and '&nv_include_sys' = 'Y')
)
and (C.owner || C.segment_name || C.segment_type || C.partition_name)
not in
(select B.username || A.name || A.kind || A.partition_name
from tmp_dba_users B, tmp_v$cache A
where A.owner# = B.user_id
and ((B.username != 'SYS' ) or
(B.username = 'SYS' and '&nv_include_sys' = 'Y')
)
)
order by &sort_by_number_value &order_by_text_value, 1,2;
spool off
ttitle off
set feedback on termout on
drop table tmp_dba_segments;
drop table tmp_dba_users;
drop table tmp_v$cache;
[EMAIL PROTECTED]
ys.com To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: Re: ** find whether table or
index being accessed
.com
11/18/2003 01:59
PM
Please respond to
ORACLE-L
... but the database is 8.1.7 - no monitoring allowed
Mladen Gogala
<[EMAIL PROTECTED]> To: Multiple recipients of
Sent by: list ORACLE-L <[EMAIL PROTECTED]>
[EMAIL PROTECTED] cc:
Subject: Re: ** find
whether table or index being accessed
11/18/2003 12:44 PM
Please respond to ORACLE-L
Well, 'ALTER INDEX MONITORING USAGE' should do the trick. Results should be
in V$OBJECT_USAGE. In connection to that, here is a sweet little bug in
oracle 9.2.0.4:
SQL> select name from v$fixed_table where name='V$OBJECT_USAGE';
no rows selected
Don't tell that to oracle, they might even fix it.
On 11/18/2003 02:34:28 PM, DENNIS WILLIAMS wrote:
> A - The only suggestion I've heard is to take the contents of V$SQL,
perform
> EXPLAIN PLAN on all SQL, and try to build a list of indexes that are
used.
> Hardly foolproof. I think Burleson has some scripts in his book Oracle
> High-Performance Tuning With STATSPACK, IIRC.
>
>
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> -----Original Message-----
> Sent: Tuesday, November 18, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
> I had sent this some time back but got no answer for version 8.1.7. For
> table I understand auditing is an option. What about for index? Thank You
>
> A Joshi <[EMAIL PROTECTED]> wrote:
>
> Hi,
> Is there an easy way to find out if a table or an index is being used.
I
> mean short of going thru all code or keeping looking at v$sqlarea. I mean
> even if code is covered there are always ad hoc SQL queries etc. Same for
> other objects like views etc. Is there a place where oracle stores
objects
> accessed and any other related info.
>
> Thanks
>
>
>
>
>
> _____
>
> Do you Yahoo!?
> Yahoo! <http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com>
> SiteBuilder - Free, easy-to-use web site design software
>
>
>
> _____
>
> Do you Yahoo!?
> Protect <http://antispam.yahoo.com/whatsnewfree> your identity with
Yahoo!
> Mail AddressGuard
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> 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).
>
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No
confidentiality or privilege is waived or lost by any mistransmission. If
you receive this message in error, please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify the
sender. You must not, directly or indirectly, use, disclose, distribute,
print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the
right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to
state them to be the views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
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:
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).
