This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Mar 2, 2005 12:41 AM by William Robertson Go to original post RSS
  • 15. Re: SQL Tuning
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Although you did not show it, you mentioned that you have twelve columns used in your where clause. It is possible that the where conditions could be written better. We cannot tell without seeing them.
  • 16. Re: SQL Tuning
    27876 Newbie
    Currently Being Moderated
    If you have some unknown reservations about sharing of the explain plan, you could at least post other portions of your tkprof trace output including the timings and the statistics/wait events.

    Also, would it hurt you if you at least posted here as to how many rows you expect to come out of each of the steps in the plan and how many total rows returned?
  • 17. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi everybody,
    Thanks for all the updates till now.

    Well, as per different suggested approaches, I tried 'leading' hint so that smallest table gets read first.

    Now,following are tha statistics:

    Elapsed: 5.46 s
    Disk: 441
    Query: 105852

    So, Ideally speaking, the statistics have remarkably improved.

    However, can anybody suggest me how do I ensure that a particular table gets read last ?

    I have tried ORDERED hint also giving the requisite table as the last one in FROM clause but I don't find this table (which I have specified last in the FROM clause) getting read last ?

    *********************************************************
    One more scenario, suppose I have a table having 6 columns:
    col1,col2,col3,col4,col5,col6

    Now, two indexes exist:

    INDEX1: col1,col2,col3,col4,col5,col6

    INDEX2: col2,col3,col4,col5,col6,col1

    (column sequence differs)

    Now, under what scenarios, INDEX1/INDEX2 indexes will be used ?

    Will, it depend upon the sequencing of WHERE clause (I doubt !!!)

    Any pointers will be highly appreciated!!! and once again thanks for the useful updates till now....

    Regards,
    Dheeraj Mehra
  • 18. Re: SQL Tuning
    27876 Newbie
    Currently Being Moderated
    I have tried ORDERED hint also giving the requisite table as the last one in FROM clause but I don't find this table (which I have specified last in the FROM clause) getting read last ?
    <quote source="documentation">
    ORDERED
    The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

    If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.
    </quote>

  • 19. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi all,
    Any solutions/pointers to my query would be highly appreciated!!!!

    Regards,
    Dheeraj Mehra
  • 20. Re: SQL Tuning
    William Robertson Oracle ACE
    Currently Being Moderated
    > So, Ideally speaking, the statistics have remarkably improved.
    Are you saying the query is now faster? In which case is the problem solved?

    > Any solutions/pointers to my query would be highly appreciated!!!!
    As would any answers to the questions you have been asked. Like the one about what percentage of rows from TAB1 you will be accessing, or the selectivity of the joins, or what happened when you tried the suggestions posted by Gabe or Barbara.

    Also bearing in mind that none of us has ever heard of fnd_stats.gather_table_stats.
  • 21. Re: SQL Tuning
    3096 Newbie
    Currently Being Moderated
    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 ? <<
    You want general advice, read the documentation -- you want specific advice then post the table structures and the explain plan.
  • 22. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi everybody,
    Well, as per the new statistics I posted earlier, query has improved in terms of elapsed time, query read and disk read.

    I tried /*+ LEADING(t2) */ hint on the table, t2, having least no. of rows and put t1 table, having largest no. of rows, as the last table in the FROM clause.

    Ya, from the biggest table,t1, SQL usually reads 10-15% of the total rows.

    Folks can go to the following link to gather more information abt. FND_STATS (May be an internal package!!!)

    http://repo.solutionbeacon.net/40WaysToSpeedUpYourUpgradeAndDecreaseDowntime.doc


    Usually, I prefer getting the generalised concept and then try implementing in my particular SQL....Same, am trying out here also.

    Now, atleast, I hope, I can expect few pointers!!!!

    Regards,
    Dheeraj Mehra
  • 23. Re: SQL Tuning
    438013 Newbie
    Currently Being Moderated
    Hi all,

    Any pointers!!!!

    Regards,
    Dheeraj Mehra
  • 24. Re: SQL Tuning
    William Robertson Oracle ACE
    Currently Being Moderated
    You've had two pages of general advice and quite a few specific suggestions and tests, many of which you haven't tried yet, and we still don't know your query, the Explain Plan output, the tkprof output, the table and index definitions, or very much about your data.

    If you are going to experiment with optimizer hints one at a time, bear in mind that this board only seems to handle about 120 posts per thread.

    If there is some specific thing you are stuck on, please post details.

    How to post questions
1 2 Previous Next