Re: How to identify tablescans ???

In Oracle performance tuning, developers and DBAs often employ the EXPLAIN PLAN command to determine the execution path of SQL statements. What they typically look for when using EXPLAIN PLAN is the word FULL that appears in the OPERATIONS columns in conjunction with the TABLE ACCESS operation. This signifies that the query performs a full table scan to retrieve the data. If this query is common, it generally makes sense to create an index on the table.

SQL TRACE is an Oracle tool that extends the functionality provided by EXPLAIN PLAN by giving statistical information about the SQL statements executed in a session that has tracing enabled. This utility is run for the entire user session using the ALTER SESSION SET SQL_TRACE=TRUE. Tracing a sesion is especially helpful for analyzing the full operation of an application or batch process containing multiple transactions, where it is unclear which part of an application or batch process is encountering performance issues.

NOTE: Before you use the EXOLAIN PLAN statement, you will need to run a script from SQL*Plus that creates the PLAN_TABLE in your schema. SQL> @%oracle_home%\rdbmsxx\admin\utlxplan.sql

Just my 2 cents!
wozi_at_dhc.net

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 Mon May 31 1999 - 00:17:17 CDT