Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** find whether table or index being accessed

Re: ** find whether table or index being accessed

From: <Brian_P_MacLean_at_eFunds.Com>
Date: Tue, 18 Nov 2003 13:29:26 -0800
Message-ID: <F001.005D7140.20031118132926@fatcity.com>

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;





                                                                                                                                    
                      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:
>

> 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: 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US