1 2 Previous Next 24 Replies Latest reply: Mar 2, 2005 2:41 AM by William Robertson Go to original post RSS
      • 15. Re: SQL Tuning
        Barbara Boehmer
        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
          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
            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
              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
                Hi all,
                Any solutions/pointers to my query would be highly appreciated!!!!

                Regards,
                Dheeraj Mehra
                • 20. Re: SQL Tuning
                  William Robertson
                  > 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
                    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
                      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
                        Hi all,

                        Any pointers!!!!

                        Regards,
                        Dheeraj Mehra
                        • 24. Re: SQL Tuning
                          William Robertson
                          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