Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to identify tablescans ???

Re: How to identify tablescans ???

From: Soeren Dalby <dalby_data_at_post3.tele.dk>
Date: Thu, 3 Jun 1999 19:26:41 +0200
Message-ID: <7j6e5r$ggf$2@news.inet.tele.dk>


Thanks for your input. I have generated tracefiles for all sessions on the server and analyzed it with TKPROF with great effect.

markp7832_at_my-deja.com wrote in message <7j0lqt$4ol$1_at_nnrp1.deja.com>...
>To answer the original post, yes, there is a better way. I am a little
>unsure as to if you are trying to fix sql used to support the tool
>itself or the application generated from the tool. In either case you
>can do the following:
>
>1) In one window connect to Oracle using the tool.
>2) In another session use the v$session table to locate the sid and
>serial# for the session
>3) Turn trace on for the session using
>dbms_system.set_sql_trace_in_session
>4) Using window 1 do those activities that are slow
>5) When down or after you have done all activities of interest run the
>trace off and run tkprof on it
>
>Use the explain option of tkprof to generate the plan of every sql
>statement executed by the target session. This is a lot better than
>tracing every session on the system or trying to explain every SQL
>statement in the pool. This method will allow you to look at only
>areas of interest and to use a piecemeal approach as time allows.
>
>
>
>In article <KNl43.2799$8W3.16656_at_news.cwix.com>,
> "Gocha Mchedlishvili" <gocham_at_cwix.com> wrote:
>> 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
>> >>>
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>--
>Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Jun 03 1999 - 12:26:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US