Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!rip!sjc70.webusenet.com!news.webusenet.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: danielroy10junk@hotmail.com (Daniel Roy) Newsgroups: comp.databases.oracle.server Subject: Re: Question toad: Index utilized for xx% Date: 7 Jul 2003 06:46:42 -0700 Organization: http://groups.google.com/ Lines: 22 Message-ID: <3722db.0307070546.4d03fdd7@posting.google.com> References: NNTP-Posting-Host: 198.96.180.245 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1057585603 23445 127.0.0.1 (7 Jul 2003 13:46:43 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 7 Jul 2003 13:46:43 GMT Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:237057 I unfortunately don't know Toad, but if you happen to be on Oracle 9i, you can "monitor" the indexes' usage ("alter index ... monitoring"). Afterwards, you can see in USER_INDEXES if the indexes were ever used in execution plans. If in Oracle 8i, you might have to rely on using a script (I think ixora has it) to look at the SQL statements currently cached, get their execution plans, and find out from them which indexes are accessed (this is very resource-intensive, careful!). Let me know if you're not clear Daniel > When i get into the TOAD- DBA - Server Statistics then I get > information about the use of indexen. In my case that it was 70% > I know that (sometimes) a full table scan can be fast then using an > index but my question is: > > Where can collect the tables names for which no index was not used !! > > I know that I can retrieve in TOAD - Tools: the statements and the > explanation if the statement used an index or not but can I read this > information in one time for all the tables? > Thanks