Re: How to identify tablescans ???

Hi,

To identify statements which do full scan, you can write a procedure, which scans v$sqltext, then runs "explain plan for " then statement, after that you can check options column in plan_table; it should be equal to 'FULL'.

Good luck

Gocha

Soeren Dalby wrote in message <7is518$af9$1_at_news.inet.tele.dk>...
>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 - 20:42:02 CDT