• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Tuning of SQL Query

Hi,

I have an SQL Query, it is taking more time to complete. I got TKPROF of that query and not able to find the exact issue there. I mean which table condition is over head for this issue. I have attached the TKPROF also.

Please advice me

Thanks
TKPROF.txt
0
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
2 Solutions
 
Aaron ShiloCommented:
hi

could you please post the execution plan with query cost.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
please check the attachement. i posted with execution plan
0
 
Aaron ShiloCommented:
yes iknow the exec plan for tkprof.

can you please just post the simple on from set autot on.

it has diferent info id ilke to look at.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Suriyaraj_SudalaiappanAuthor Commented:
Hi, I have posted here.
Query-Plan.xls
0
 
Aaron ShiloCommented:
hi

whats bomping the query cost is acceesing
MTL_CATEGORIES_B table using MTL__CATEGORIES_B_N2 index
try and add a condition.
0
 
Franck PachotCommented:
Hi,

Your query took:

-> 1.92 second to parse. Not so bad for a query on 32 tables.
   If the query is run several time, it is not hard parsed each time, so this is not a problem.
   As the plan is rather simple (mostly access by primary key) you may use hints to force that plan (so that optimizer do not check other access plans)

-> 3.27 seconds to send result to the client. That is a lot for only 501 rows. Are the rows so large (including large varchars and/or lob ?) Or is the network long ?
    You nust check that as it takes most of the time.

-> 1.14 seconds of CPU to get 138565 block from buffer cache. I imagine that the first time, when data is not in cache, it is longer.
The order of the joins is not optimal: it gets 3799 rows and then filters out to 501. You can use the ordered hint to access first to tables that return less rows. Probably going to OE_ORDER_LINES_ALL at the end.
The CBO tries to do that, but difficult to have the optimal plan with so many columns.


So my opinion: the response time is not so bad. You can optimize it but with a big effort (hint to try to have a better execution plan).

Is it a query that is run one time (such as a report) ?

Regards,
Franck.
0
 
AkenathonCommented:
I agree, the response time is not that bad given the SQL. A little room for improvement seems to be at (from tkprof output):

   3799 TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=11442 pr=0 pw=0 time=79679 us)
  11308   INDEX RANGE SCAN OE_ORDER_LINES_ALL_N28 (cr=381 pr=0 pw=0 time=1283 us)(object id 15373373)

11K rows are scanned for less than 4K useful. The index is scanned for just one column (note that the EP on the spreadsheet lacks the filter and access predicates). Maybe another index is available, or some other column can be added if the join to this table can be deferred as franckpachot says (however, this table is outer joined to some other tables, limiting the available combinations of join orders/join types).

Can you get an EP from dbms_xplan.display_cursor? Also, which indexes are already in place for OE_ORDER_LINES_ALL? Finally, do you really need all your outer joins to be outer?
0
 
Suriyaraj_SudalaiappanAuthor Commented:
No Comments
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now