Re: How to identify tablescans ???

Oracle keeps a 'dynamic performance' view in memory called v$sqlarea. This keeps track of SQL statement text and their operational statistics. It will not tell you directly which statements use table scans. However, it will tell you something even better. It tells you the buffer gets and the rows returned. Compute the buffer gets per row returned. The SQL statements with the highest buffer gets per row have either no index, or, worse yet, a non-selective index that is used.

Just explain the worst buffer gets per row statements to obtain candidates.

A second stage is to look at all the SQL statements for the tables in these worst SQL statements. You want to be sure that a new index for one query doesn't mess up several other queries.

Another thing to observe: v$sqlarea also keeps track of the number of executions. Bad SQL that is executed only once isn't as bad as poor SQL that is executed often.

And another thing: v$sqlarea is flushed every time you stop the database. All the stats start over.

Soeren Dalby wrote:

> Thanks for your input, but my most important first issue is to IDENTIFY the
> N sql statements out of Y that causes table scan. Doing this manually for
> all statements is not possible.
>
> Gocha Mchedlishvili wrote in message ...
> >Hi,
> >
> >I wouldn't recommend to use any tool for index generation, I thonk it has
> to
> >be done manually, because sometimes too many indexes is as bad as having
> >none.
> >You can use a lot of tool which tell execution plan for specific statement,
> >i.e. SQL navigator.
> >
> >Gocha
> >
> >Soeren Dalby wrote in message <7is1ps$4hk$2_at_news.inet.tele.dk>...
> >>Hi
> >>
> >>I am DBA (not a real DBA, just a programmer with some DB experience) on a
> >>developer site where we use a development tool which uses Oracle "in a
> >>hidden way" meaning that we dont have access to entities such as tables
> and
> >>indexes but just an object hierarchy.
> >>
> >>I have found out that the development tool some times uses SQL searches
> for
> >>which it hasnt build indexes for and thus performs a table scan. So I have
> >>made it my task to identify the sql constructs which causes table scans
> and
> >>get the proper indexes created.
> >>
> >>I have been told that I can enable logging for all connections on the
> >>server, but does this explicitly identify a SQL search as having performed
> >a
> >>tablescan or is there a better way ?? My ideal tool would track all
> >>tablescans and generate the SQL necessary to generate the indexes. I know
> >>that in general, a skilled DBA would consider such a random index creation
> >>dangerous due to the extra workload on updates/creations, but I am sure
> >that
> >>we would gain an enormous performance benefit in our case.
> >>
> >>Any comments to this problem would be appreciated !!
> >>
> >>Thanks in advance
> >>--
> >>
> >>
> >>
> >>Sincerely yours
> >>
> >>Dalby Data
> >>Sneppevej 15, st. th.
> >>2400 Copenhagen NV
> >>
> >>Telephone +45 40 96 00 89
> >>Telefax +45 31 86 09 20
> >>
> >>Mail: dalby_data_at_post3.tele.dk
> >>Site: http://home3.inet.tele.dk/dalbydat
> >>
> >>
> >>
> >
> >
Received on Sun May 30 1999 - 17:20:00 CDT