Yep, I missed this userenv part totally, even though I posted the source
here myself as well.
Gotta get better eyes from somewhere ;)
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, November 19, 2003 5:10 PM
Tanel,
Raj must be
MG,
AFAIK v$object_usage is ONLY for current user ... you have to hack it to see remaining
data.
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any
Well, it's code is:
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# =
Tanel,
Raj must be talking about userenv('SCHEMAID'). Change that to another user's
user_id as seen in dba_users, you should see that user's object usage:
SQL select * from v$object_usage;
no rows selected
SQL select io.name, t.name,
2 decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
The last statement here is not always true under CBO, at least in 8i. All
unique indexes (no PKs, no FKs) on our 3rd-party ERP tables are segmented,
and the last segment is always company code. Even though there is only one
company code value for all rows in all tables, some queries that do not
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 YouA Joshi [EMAIL PROTECTED] wrote:
Hi,
Is there an easy way to find out if a table or anindex is being used. I mean short of going thru all code or
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
Are you looking to see if statements are using indexes or how often index
blocks are being read?
Daniel Fink
A Joshi wrote:
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
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
Looking to see if any statement has accessed the index in say 30 days. So basically : "how often index blocks are being read". So I can decide to drop unused indexes. T
Thanks Daniel for your help.
Daniel Fink [EMAIL PROTECTED] wrote:
Are you looking to see if statements are using indexes or how
... but the database is 8.1.7 - no monitoring allowed
Mladen Gogala [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/18/2003 12:44 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: ** find whether table
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: ** find whether table or index being accessed
Well, 'ALTER INDEX MONITORING USAGE' should do the trick. Results should
be
in V$OBJECT_USAGE. In connection to that, here is a sweet
:
[EMAIL PROTECTED]Subject: Re: ** find whether table or
index being accessed
.com
This is just an idea, so please test it thoroughly (and then test it again!)
Any and all comments (including "Are you brain-dead, Dan?") are welcome.
How about periodically sampling v$bh for index segment headers? This
assumes that any index access reads the header (true/false?) for the
Daniel,
it
will work but indexes are present in buffer cache also because of updates. The
only possibility is to store each index in questionin separate tablespace
and monitor the i/o. If number of reads will be equal or little bit
greaterthan thenumber of writes than this is a candidate.
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
I was too lazy to look for it on asktom.oracle.com, but here's what I read at the site
a while ago (if you search on index usage or something like that you should find Mr.
Kyte's answer). Tom Kyte has the following suggestions:
a) In Oracle 8.0 and earlier - put an index all by itself in a
% sure.Regards,
Joze
-Original
Message-
From: Daniel Fink [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 18,
2003 10:34 PM
To: Multiple recipients of
list ORACLE-L
Subject: Re: ** find whether
table or index being accessed
This is just an idea, so please test it thoroughly (and then test
Message -
From:
Daniel Fink
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, November 18, 2003 11:34
PM
Subject: Re: ** find whether table or
index being accessed
This is just an idea, so please
test it thoroughly (and then test it again!) Any and all comments
]
cc:
Subject:RE: ** find whether table or index being accessed
I was too lazy to look for it on asktom.oracle.com, but here's what I read at the site a while ago (if you search on index usage or something like that you should find Mr. Kyte's answer). Tom Kyte has the following
recipients of list ORACLE-L
Sent: Tuesday, November 18, 2003 11:34 PM
Subject: Re: ** find whether table or index being accessed
This is just an idea, so please test it thoroughly (and then test it
again!) Any and all comments (including Are you brain-dead, Dan?) are
welcome.
How about
--Boundary-00=_4PUK6RO0
Content-Type: Multipart/Alternative;
boundary=Boundary-00=_4PUK12S0
--Boundary-00=_4PUK12S0
Content-Type: Text/Plain;
charset=shift_jis
Content-Transfer-Encoding: quoted-printable
Joshi,=0D
=0D
Hi,
Is there an easy way to find out if a table or anindex 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
A - For tables, you can turn on auditing. If you are on Oracle9i, there is a
monitoring feature for indexes you can turn on.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, July 31, 2003 12:49 PM
To: Multiple recipients of
24 matches
Mail list logo
