RE: Identification of tables NOT being used in the System.
Title: RE: Identification of tables NOT being used in the System.
There was a note previously on a similar theme which I have posted below.
The dba_tab_modifications does not show when a table has been selected from which could be a problem for reference data type table which can easily be overlooked.
+ Previous notes
Tony is thinking along the same lines I was. Go ahead and capture the
outlines and then querying the DD where the indexes aren't found in the
OL$HINTS.HINT_TEXT column could tell you what indices haven't been used.
Something like:
select i.owner, i.table_name, i.index_name
from dba_indexes i, outln.OL$HINTS h
where index_name not like ('%'||hint_text||'%')
and owner not in ('SYS','SYSTEM','PORTAL30','DES6I')
Just add the schema's to exclude. Only as good as the code coverage from
your collection timeframe but still seems like a pretty solid approach.
Anyone tried this? I've kicked it around but have never actually tried it in
anything other than a test environment.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
-Original Message-
Sent: Monday, February 04, 2002 4:31 PM
To: Multiple recipients of list ORACLE-L
Another option you have if your Oracle version is high enough is to use
Stored Outlines. Enable automatic generation of stored outlines for a full
processing cycle as defined by the application (full month, Qtr, etc.) Then
extract all of the indexes used during that cycle from OL$HINTS.HINT_TEXT
(i.e.. WHERE HINT_TEXT LIKE 'INDEX%'). If all of your application code has
been traversed in the cycle then this list will be pretty darn close to real
usage. Even if you can't wait until year-end processing, you can eliminate
the bulk of code to be mined for embedded SQL and focus on those once-a-year
programs.
HTH
Tony Aponte
-Original Message-
Sent: Monday, February 04, 2002 12:55 PM
To: Multiple recipients of list ORACLE-L
Hi
I there any view which can tell us which indexes are not in use?
Thx
-Seema
-Original Message-
From: Jesse, Rich [mailto:[EMAIL PROTECTED]]
Sent: 15 March 2002 21:49
To: Multiple recipients of list ORACLE-L
Subject: RE: Identification of tables NOT being used in the System.
ALTER TABLE tablename MONITORING;
Every three hours or so, the SYS.DBA_TAB_MODIFICATIONS view gets updated
with the tables UPDATEs, DELETEs, and INSERTs, as well as wether or not the
table has been TRUNCATEd since the last time it was DBMS_STATS'd. The view
also gets updated on a SHUTDOWN, except for SHUTDOWN ABORT. I believe that
if there's no activity on the table you set for MONITORING, that there will
not be a row for it in this view.
And if you use CBO, you'll want to save the rows from DBA_TAB_MODIFICATIONS
to your own table before using DBMS_STATS. It will zero the counters in the
view for the tables it's run against.
Also, there's very little overhead, at least according to Oracle.
HTH! You might want to look this up in Metalink or the Oracle docs, too.
Enjoy! :)
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
Sent: Friday, March 15, 2002 3:08 PM
To: Multiple recipients of list ORACLE-L
In our production database environment, I have a list of about 1000 tables
,for which we want to find if these tables are being used by anyone. How it
can be done. One of the ideas is that we start database auditing on these
tables for a considerable period of time say one month. Then for those
tables for which there is nothing in database audit, we assume that tables
are not being used. For this option I would like to know if we put auditing
on these 1000 tables, how much extra burden it is add onto the system (CPU,
Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00.
If there are some other alternatives, please let me know.
Thanks.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
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).
=
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by
RE: Identification of tables NOT being used in the System.
ALTER TABLE tablename MONITORING; Every three hours or so, the SYS.DBA_TAB_MODIFICATIONS view gets updated with the tables UPDATEs, DELETEs, and INSERTs, as well as wether or not the table has been TRUNCATEd since the last time it was DBMS_STATS'd. The view also gets updated on a SHUTDOWN, except for SHUTDOWN ABORT. I believe that if there's no activity on the table you set for MONITORING, that there will not be a row for it in this view. And if you use CBO, you'll want to save the rows from DBA_TAB_MODIFICATIONS to your own table before using DBMS_STATS. It will zero the counters in the view for the tables it's run against. Also, there's very little overhead, at least according to Oracle. HTH! You might want to look this up in Metalink or the Oracle docs, too. Enjoy! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Friday, March 15, 2002 3:08 PM To: Multiple recipients of list ORACLE-L In our production database environment, I have a list of about 1000 tables ,for which we want to find if these tables are being used by anyone. How it can be done. One of the ideas is that we start database auditing on these tables for a considerable period of time say one month. Then for those tables for which there is nothing in database audit, we assume that tables are not being used. For this option I would like to know if we put auditing on these 1000 tables, how much extra burden it is add onto the system (CPU, Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00. If there are some other alternatives, please let me know. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Identification of tables NOT being used in the System.
There is the vil way ... just drop the table and see who screams :) Alec -Original Message- Dharminder Sent: Friday, March 15, 2002 1:08 PM To: Multiple recipients of list ORACLE-L In our production database environment, I have a list of about 1000 tables ,for which we want to find if these tables are being used by anyone. How it can be done. One of the ideas is that we start database auditing on these tables for a considerable period of time say one month. Then for those tables for which there is nothing in database audit, we assume that tables are not being used. For this option I would like to know if we put auditing on these 1000 tables, how much extra burden it is add onto the system (CPU, Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00. If there are some other alternatives, please let me know. Thanks. Dharminder Kumar FONT SIZE = 1** ** This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient is unauthorized and may be illegal. Unless otherwise stated, opinions expressed in this e-mail are those of the author and are not endorsed by the author's employer./FONT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kumar, Dharminder INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Alec Macdonell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Identification of tables NOT being used in the System.
Dharminder, Instead of the overhead of auditing, how about periodic queries of V$SQL_Text for SQL statements that reference those tables. It should be pretty easy to come up with occurrences and counts for each table, though statements using bind variables could cause low counts for the tables referenced by them. Heck, you might flush the Shared Pool occasionally and requery V$SQL_Text just to see which tables are the hottest - most frequently referenced. That may not be a good idea on a Production DB, though - depends on your application load. If you bounce your database regularly (for cold backups, for instance), do it right before shutdown. It's just a quick thought...which I probably absorbed from someone else's posting on this very valuable list. ;-) If so, then my thanks to the originator. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Dharminder Sent: Friday, March 15, 2002 3:08 PM To: Multiple recipients of list ORACLE-L In our production database environment, I have a list of about 1000 tables ,for which we want to find if these tables are being used by anyone. How it can be done. One of the ideas is that we start database auditing on these tables for a considerable period of time say one month. Then for those tables for which there is nothing in database audit, we assume that tables are not being used. For this option I would like to know if we put auditing on these 1000 tables, how much extra burden it is add onto the system (CPU, Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00. If there are some other alternatives, please let me know. Thanks. Dharminder Kumar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
