How to identify tablescans ???

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 - 13:54:29 CDT