Wow, this is interesting. Thanks John.
John Higgins wrote in message <3751B98F.F847A8DF_at_deere.com>...
>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 Thu Jun 03 1999 - 12:24:09 CDT
