OraFAQ Forum: Performance Tuning » Sorting a trace file using TKPROF

Home » RDBMS Server » Performance Tuning » Sorting a trace file using TKPROF

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sorting a trace file using TKPROF [message #155104]

Thu, 12 January 2006 07:23

guyj
Messages: 31
Registered: September 2005

Member

Hi All,

I’m new to oracle performance tuning. Now I want to optimize a query which is taking more than 1 ½ hours to finish. Can anyone tell me what is the best sort option I should follow when I’m using TKPROF.

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155121 is a reply to message #155104]

Thu, 12 January 2006 08:32

orausern
Messages: 826
Registered: December 2005

Senior Member

You should be familiar with many concepts of tuning..but anyway, first make sure that all objects in the query are having current statistics..if you are responsible for collecting statistics, then , do it by using dbms_stats package.
(e.g. to gather statistics on SCOTT schema, use from sql prompt:
exec dbms_stats(ownname=>'SCOTT',cascade=>TRUE)

Then check whether the query is written correctly..whether there are indexes on the tables that are getting utilized or not..you can post that query here so others can see what it is and look into.

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155123 is a reply to message #155104]

Thu, 12 January 2006 08:36

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

It depends on what you want. If CPU is a problem, you may want to specify "execpu". If disk reads is the problem, you may want to use "exedsk". Etc.

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155130 is a reply to message #155104]

Thu, 12 January 2006 08:57

guyj
Messages: 31
Registered: September 2005

Member

Thanks for the response. i'll send you the first part of the trace file. used fchela as the sort option. i have gathered statistics on that schema as well. Application runs on Oracle 9i database but i'm using oracle 10g TKPROF to convert it.

Attachment: trace file.txt
(Size: 4.83KB, Downloaded 1434 times)

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155134 is a reply to message #155130]

Thu, 12 January 2006 09:19

orausern
Messages: 826
Registered: December 2005

Senior Member

The file shows:

250339431 INDEX FAST FULL SCAN INDX_SL_TXN_INQUIRY_INVOICE_01 (cr=27808560 r=923 w=0 time=2829483025 us)(object id 155266)

so the table SL_TXN_INQUIRY_INVOICE must be very huge table..and the portion of the query : (M.invoice_date >= :b4
AND M.invoice_date < :b3) could be the culprit..just to check this, try remove this part and run the query, this is only to check what I am suspecting is true or not..another thing I noted from your commented code is:
(--AND TO_CHAR(M.invoice_date,'MON') = Wk_tmp_mth
--AND TO_CHAR(M.invoice_date,'YYYY') = Wk_tmp_year) which u have replaced with bind variable , this may mean that you are storing date is varchar2? in that case check whether there is any index on the invoice date and whether the index is used? the chances are that evenif the index is present, it is not used , due to the conversions you are doing..

hth

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155152 is a reply to message #155104]

Thu, 12 January 2006 11:02

guyj
Messages: 31
Registered: September 2005

Member

Hi niravshah,

Thank you very much for the informations. But reason to pass bind variables are, earlier they used to convert date fields into varchar2. to avoid that we convert the value into a date value first and then pass it to the select statement. we don't have an index for the column invoice_date.

once again thanks for the support you're giving

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155339 is a reply to message #155152]

Sat, 14 January 2006 00:41

orausern
Messages: 826
Registered: December 2005

Senior Member

Can u post the table structure of both the tables, the number of columns both tables have, the number of indexes they have and on which columns they are..and the purpose of the query( i mean , a description of output..) so one can try to see if it is possible to come up , with a better query instead of this one..that the query takes a long time is because, it is a very huge table , and you are doing a full scan of it so it has to do lots of work.

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155340 is a reply to message #155339]

Sat, 14 January 2006 00:42

orausern
Messages: 826
Registered: December 2005

Senior Member

pl. read number of 'rows' both tables have instead of number of 'columns' , in my earlier post .

Report message to a moderator

Re: Sorting a trace file using TKPROF [message #155522 is a reply to message #155104]

Mon, 16 January 2006 22:43

guyj
Messages: 31
Registered: September 2005

Member

This query is part of an order schedule report [Inventory System]. This report displays the details about purchased orders, inventory items etc... for a particular time period.
I have attached the details about tables and indexes and also I have created an index for the ‘invoice_date’ column. But no success.

Attachment: tab details.txt
(Size: 2.75KB, Downloaded 1402 times)

Report message to a moderator

Previous Topic:	Getting a list of commonly used views for finding tuning statistics
Next Topic:	V$LibraryCache

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 18:51:50 CDT 2025