Tuning of SQL Query

Posted on 2011-04-26
Last Modified: 2012-05-11

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

    LVL 15

    Expert Comment

    by:Aaron Shilo

    could you please post the execution plan with query cost.

    Author Comment

    please check the attachement. i posted with execution plan
    LVL 15

    Expert Comment

    by:Aaron Shilo
    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.

    Author Comment

    Hi, I have posted here.
    LVL 15

    Expert Comment

    by:Aaron Shilo

    whats bomping the query cost is acceesing
    MTL_CATEGORIES_B table using MTL__CATEGORIES_B_N2 index
    try and add a condition.
    LVL 15

    Accepted Solution


    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) ?

    LVL 11

    Assisted Solution

    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?

    Author Closing Comment

    No Comments

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Oracle Multiple Union 7 45
    Limit Database Table to a Single Row 6 48
    CREATE TABLE syntax 4 33
    Oracle Database creation fails 5 20
    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now