This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Mar 2, 2005 12:41 AM by William Robertson RSS

SQL Tuning

438013 Newbie
Currently Being Moderated
Hi everybody,
I have a select query as:

SELECT *
FROM tab1
,tab2
,tab3
,tab4
,tab5
,tab6
,tab7
WHERE tab1.id1 = tab2.id
AND tab1.id2 = tab3.id
AND tab1.id3 = tab4.id
AND ....

Basically, what above SQL is doing is just joining all these tables based on their id's.
I mean there are no complex filter conditions involved.

Out of these tables, tab1 has around 10 milion records where-as other tables are having records not more than 10 thousand.

Few more things:
EXPLAIN PLAN doesn't show full table access for any of these tables. All tables are going by INDEX scan.

I tried using hints like ORDERED, STAR . But still SQL doesn't respond properly.

tab1 is having index on its 12 columns and all 12 columns are part of WHERE clause.

Please help!!!
TIA,

Regards,
Dheeraj Mehra
  • 1. Re: SQL Tuning
    27876 Newbie
    Currently Being Moderated
    But still SQL doesn't respond properly.
    Please explain what you mean by "respond properly"? Do you mean it never returns or it takes a long time (how much) or ...
    EXPLAIN PLAN doesn't show full table access for any of these tables
    Please share the explain plan. Better yet, share the tkprof output from the query execution.
  • 2. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Thanks for the update...

    By "SQL doesn't respond properly", I meant something as follows:

    1) .prf file gives me the following statistics:

    Elapsed time = 118 s
    Disc read = 29800
    Query = 1012343

    For some reasons, I can't share explain plan but as per my understanding, plan doesn't give much room for improvement.

    My basic aim is to reduce above statistics as much as possible.

    TIA,
    Dheeraj Mehra
  • 3. Re: SQL Tuning
    27876 Newbie
    Currently Being Moderated
    For some reasons, I can't share explain plan
    You do realize that you would have to help us by prividing such info that would allow us to help you.

    Anyway, you could as well look into the trace output that would tell you where all this 118 s of time is being spent and then you would have to look into how can you reduce that time.
  • 4. Re: SQL Tuning
    Gabe2 Newbie
    Currently Being Moderated
    Version?
    Are you using the RBO?

    Use the CBO, analyze the tables and you would likely start to see hash joins with full table access ... or other plans rather than nested loops.

    BTW, asking for that many rows is bound to take some time ... doesn't quite look like a real life scenario ... unless???
  • 5. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Thanks Kamal and Gabe for the update...

    Actually, the data size is possible in real time scenario, particularly in case of big customers.

    To answer Gabe's qn, am using CBO only and all the tables were analysed before I tried executing this SQL...

    Well, in EXPLAIN PLAN, I could see a mixture of nested loops and hash join so not sure of Gabe's update that all the tables will start using hash join and full table access stuff...!!!

    Regards,
    Dheeraj Mehra
  • 6. Re: SQL Tuning
    William Robertson Oracle ACE
    Currently Being Moderated
    What percentage of the 10 million row table are you retrieving?

    If it's more than about 15%, indexes probably won't help you and you would want to see a full scan and probably some hash joins.

    However, if one of the smaller tables effectively limits the number of rows, then you might want the optimizer to start with that and use an index + nested loops. For example, what if the large table TAB1 has an optional column which is only populated in 5% of rows, and this is indexed, and this key joins to TAB2, which is relatively small. Then you might want to start with TAB2 and use the index to find rows in TAB1. With up to date stats, the optimizer is pretty good at spotting this type of scenario.

    It sounds as if the execution plan might have changed, since in your first post you said "EXPLAIN PLAN doesn't show full table access for any of these tables. All tables are going by INDEX scan", but later you said "I could see a mixture of nested loops and hash join". Is that the same plan?

    What version of Oracle do you have?

    How are you analyzing the tables (ANALYZE TABLE, DBMS_STATS etc?)
  • 7. Re: SQL Tuning
    Gabe2 Newbie
    Currently Being Moderated
    <quote>I mean there are no complex filter conditions involved.</quote>
    <quote>Actually, the data size is possible in real time scenario, particularly in case of big customers.</quote>

    I didn’t dispute the overall size of the data … I questioned the usefulness of a query joining a 10 million rows table with 6 other tables with no apparent predicate or aggregation (and getting absolutely all columns from all 7 tables) … for OLTP, I can only imagine this being an extract of sorts and, as such, this batch operation is not typically used frequently as part of one’s business operations … tuning a batch extract to shave few seconds may not be worth the effort. Even for a DW, I’m hard pressed to find the usefulness of denormalizing all this data … except for, well, extracting data to another system. But then again, why don’t you say what this mystery SQL is for!

    <quote>Well, in EXPLAIN PLAN, I could see a mixture of nested loops and hash join so not sure of Gabe's update that all the tables will start using hash join and full table access stuff...!!!</quote>

    Where did I say ALL tables would start using hash joins? Given the level of detail you provided, one hypothesis I could think of was RBO and nested loops. Why the big mystery with <quote>can’t share explain plan</quote>? Is there anything tangible you could share … if not then just set fast=true in your parameter file.

    <quote>
    Elapsed time = 118 s
    Disc read = 29800
    Query = 1012343

    My basic aim is to reduce above statistics as much as possible.
    </quote>

    I have a SQL with:

    Elapsed time = 3180 s
    Disc read = 298000
    Query = 101234300

    What do you think of my SQL? … does it respond properly or not?

    Your aim should be to improve the response time to an acceptable but reasonable level.

    I’m happy to help … but it pisses me off when someone dismissively puts words in my mouth.

    Good luck with that.
  • 8. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi William.

    I analysed all the tables before shooting SQL.

    I used following procedure to analyze.

    fnd_stats.gather_table_stats

    Am using 9i version.

    For EXPLAIN PLAN stuff, what I meant was that the tables are getting accessed as :
    TABLE ACCESS BY INDEX ROWID TAB1

    However, the joins between tables shown in EXPLAIN PLAN are a mixture of hash join and Nested Loops.

    Regards,
    Dheeraj Mehra
  • 9. Re: SQL Tuning
    6363 Guru
    Currently Being Moderated
    Why the big mystery with <quote>can’t share explain
    plan</quote>? Is there anything tangible you could
    share … if not then just set fast=true in your
    parameter file.
    That was going to be my suggestion. If that doesn't work try the /*+ tune_query */ hint.
  • 10. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi all,
    Lets not try making the simple SQL appearing complex...

    Why not ideas can be shared in terms of how do we use hints in such cases or whats an ideal PLAN one should observe in such cases ?

    What I am trying to convey is instead of questioning the authencity of SQL, why not our technical Gurus stress more upon how to go abt. tuning such SQL's...

    No offence intended!!!

    With Best Regards,
    Dheeraj Mehra
  • 11. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi all,
    May be trivial thing, but I really couldn't find location of parameter file...

    Can somebody help me out!!!

    Regards,
    Dheeraj Mehra
  • 12. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi all,
    Is there any way thru. which I can use following two hints at the same time, in the same SELECT statement ?

    1) Leading
    2) tune_query

    Tons of thanks in advance!!!

    Regards,
    Dheeraj Mehra
  • 13. Re: SQL Tuning
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Ideally, you don't use hints. You analyze the tables, indexes, and indexed columns to gather statistics so that the cost-based optimizer (CBO) can use those statistics to automatically choose the best execution plan. Although there are various ways, the following is one method to analyze each table:

    ANALYZE TABLE table_name
    COMPUTE STATISTICS
    FOR TABLE
    FOR ALL INDEXES
    FOR ALL INDEXED COLUMNS;

    You should run this for each table, substituting the appropriate table_name. Then see how your query performs. One hint that you might want to use is the first_rows hint. What you have shown us is a query that is so simple that there is no tuning that could improve it, just simple joins of one common column, assuming that you have joined every table and not left out a join producing a cartesian product. In these situations, it is usually what people do not show us that they think is insignificant that is really causing the problem. It might help if you could show us the actual query and execution plan. You could simply set autotrace on, then execute your query, and provide a copy and paste of the results.
  • 14. Re: SQL Tuning
    Gabe2 Newbie
    Currently Being Moderated
    <quote>May be trivial thing, but I really couldn't find location of parameter file</quote>

    Don’t think you got the gist there … though, maybe it is better that way. It may also explain why we couldn’t get some of the things we’ve been asking for (maybe inexperience? … which is very much excusable … we all suffer of that to a certain degree).

    The points are:

    1. 118 seconds may be perfectly reasonable for what you’re doing (which I still don’t know) … if it is not acceptable it may mean your expectation are unreasonable … or indeed there is a problem (which I cannot see based on what you’ve shown)

    2. <quote> whats an ideal PLAN one should observe in such cases</quote> … there is no IDEAL plan for such cases … if there were one, Oracle would’ve burnt it into the optimizer long ago

    3. You should strive to improve the response time (again, if there is a perceived problem)… rather than searching for the ultimate execution plan.

    It may be that the plan you’re trying to get away from could, in fact, be the better option. So, my last suggestion I can make (given the facts you have presented so far) is:

    A. Run your query with no hints or anything to get your 118 seconds in CBO

    B. Just for its sake, run the same query with the RBO (add /*+ rule */) and measure that … it should go into nested loops with index access

    C. May also try to force a mixture of hash and nested loops ( add, let us say, /*+ use_nl(tab1 tab2) use_nl(tab1 tab3) */ and measure that as well

    If you are to post back then do atleast_ the following:

    i. log into sql*plus
    ii. set autotrace traceonly (need to have a plan_table available)
    iii. run your queries
    iv. cut and paste the whole thing

1 2 Previous Next