| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** find whether table or index being accessed
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
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
dirty || ' ' || temp || ' ' || ping || ' ' || stale || '
' || direct || ' ' || new
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
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 noprintselect 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
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
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
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
where ((C.owner != 'SYS' ) or
(C.owner = 'SYS' and '&nv_include_sys' = 'Y')
)
(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')
)
)
spool off
ttitle off
set feedback on termout on
drop table tmp_dba_segments;
drop table tmp_dba_users;
drop table tmp_v$cache;
Jared.Still_at_radis
ys.com To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
ml-errors_at_fatcity 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
<mladen_at_wangtrading.com> To: Multiple recipients of
Sent by: list ORACLE-L <ORACLE-L_at_fatcity.com>
ml-errors_at_fatcity.com 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. > dwilliams_at_lifetouch.com > > -----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 <ajoshi977_at_yahoo.com> wrote: >
> 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 storesobjects
> 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 withYahoo!
> Mail AddressGuard > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > INET: DWILLIAMS_at_LIFETOUCH.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). >
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: mladen_at_wangtrading.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: INET: Brian_P_MacLean_at_eFunds.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 Tue Nov 18 2003 - 15:29:26 CST
 |
 |