Re: database tunning

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!
>
Received on Fri Feb 25 2000 - 00:44:18 CST