Re: database tunning

Hi.

 Don't forget to set TIMED_STATISTICS = TRUE in init.ora.

 HTH. Michael.

In article <ixpt4.2295$x3.3815_at_wormhole.dimensional.com>,   "Bill Coulam" <bcoulam_at_DELETECAPSusa.net> wrote:
> Man that's a huge question. If you have access to it, I'd refer you
first to
> your Oracle documentation, then to Guy Harrison's book "Oracle High
> Performance Tuning" (I think that's the title). Check www.amazon.com
or
> www.fatbrain.com. It has a pretty good chapter on tkprof, explain
plan, and
> autotrace.
>
> A quick test for you:
> As someone with rights to the v$parameter DBA view, issue the
following
> (user beware, I'm doing this from home and no syntax has been
checked):
>
> select value from v$parameter where name = 'user_dump_dest';
>
> This should tell you where Oracle trace files are going. Make note of
it.
>
> Make sure the tables you will be querying have been analyzed.
> Now from the same or some other user, issue the following statement
to turn
> tracing on for your session :
> alter session set sql_trace = true;
>
> Now issue a simple or complex SQL statement that you're interested in
> tracing. Like:
> select a,b,c,d,e,f,g, from <mytables or dynamic views> where t1.a =
t2.b and
> t2.date between date1 and date2, etc, etc...;
>
> Oracle has just collected all sorts of information regarding your
query,
> what path it took to get the info, how long each step took, how many
rows
> were accessed at each explain plan step, etc.
>
> Now you must end that session. Usually the easiest way to do that is
to exit
> SQL*Plus. Now go to that directory you noted earlier and if you have
the
> right privileges to read the file, the last file in the directory (a
*.trc
> file) will be yours. Say it is 9999.trc.
>
> Issue the following at the command line (depends on your version of
tkprof):
>
> tkprof 9999.trc mytracefilename.txt explain=myuser/mypassword_at_mydbsid
sys=no
>
> tkprof will run and take the unintelligible .trc file and turn it into
> something you can read. Again go to the Guy Harrison book or the
Oracle
> documentation to figure out what it all means.
>
> Having said all that, the autotrace feature in SQL*Plus is much
easier to
> use and you can even suppress rows returned just to see the
statistics and
> explain plan. However, it does not show the number of rows processed
at each
> step. ONLY tkprof does that. And that is a very valuable thing to see
when
> tuning a very difficult SQL statement which is inefficient at a non-
obvious
> step.
>
> best of luck,
> - bill c.
> "mac" <mayamdNOmaSPAM_at_yahoo.com.invalid> wrote in message
> news:0ab659d8.38fba3da_at_usw-ex0103-020.remarq.com...
> > Hi
> >
> > I am facing problem in database tunning.Can u tell me what
> > is sqltrace?.How it work?.
> >
> > what is tkprof?.How it work?.I know explain plan vary well?.
> >
> > Please reply on my mayamd_at_yahoo.com id
> >
> > Thanks
> >
> >
> >
> >
> > * Sent from RemarQ http://www.remarq.com The Internet's Discussion
Network
> *
> > The fastest and easiest way to search and participate in Usenet -
Free!
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 25 2000 - 09:20:06 CST