Home » RDBMS Server » Performance Tuning » Sorting a trace file using TKPROF
Sorting a trace file using TKPROF [message #155104] Thu, 12 January 2006 07:23 Go to next message
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.
Re: Sorting a trace file using TKPROF [message #155121 is a reply to message #155104] Thu, 12 January 2006 08:32 Go to previous messageGo to next message
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.

Re: Sorting a trace file using TKPROF [message #155123 is a reply to message #155104] Thu, 12 January 2006 08:36 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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.
Re: Sorting a trace file using TKPROF [message #155130 is a reply to message #155104] Thu, 12 January 2006 08:57 Go to previous messageGo to next message
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.

Re: Sorting a trace file using TKPROF [message #155134 is a reply to message #155130] Thu, 12 January 2006 09:19 Go to previous messageGo to next message
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
Re: Sorting a trace file using TKPROF [message #155152 is a reply to message #155104] Thu, 12 January 2006 11:02 Go to previous messageGo to next message
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
Re: Sorting a trace file using TKPROF [message #155339 is a reply to message #155152] Sat, 14 January 2006 00:41 Go to previous messageGo to next message
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.
Re: Sorting a trace file using TKPROF [message #155340 is a reply to message #155339] Sat, 14 January 2006 00:42 Go to previous messageGo to next message
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 .
Re: Sorting a trace file using TKPROF [message #155522 is a reply to message #155104] Mon, 16 January 2006 22:43 Go to previous message
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.

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


Current Time: Thu Mar 28 07:23:59 CDT 2024