| 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.
|
|
|
|
| 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.
|
|
|
|
|
|
| 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.
|
|
|
|
| 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
|
|
|
|
| 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
|
|
|
|
| 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.
|
|
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
