6 Replies Latest reply: Oct 19, 2012 6:49 PM by rp0428 RSS

    order by causes query to be much slower (only 1 row returned)

    866564
      DB version is 11.1.0.6.0

      Basically, my question is as stated in the subject line - I have a (somewhat complex) query that only returns 1 row. Without an ORDER BY clause, it is very quick, sub-second. But once I add any kind of order by, the query suddenly takes almsot 30 seconds.

      I see that in the query plan, the ORDER BY causes a few more full table scans, but I have no idea why. How would I go about getting to the bottom of this?

      Thanks
        • 1. Re: order by causes query to be much slower (only 1 row returned)
          Warren Tolentino
          does your query returns only one row all the time? if that is the case you need not to include the order by clause.
          • 2. Re: order by causes query to be much slower (only 1 row returned)
            damorgan
            Never use an ORDER BY unless it is absolutely necessary. Lot of overhead and little benefit.

            But why 11.1.0.6? The 11.1.0.7 patch was released years ago.
            • 3. Re: order by causes query to be much slower (only 1 row returned)
              866564
              No, it's not always going to return only 1 row. The ORDER BY is necessary, unfortunately, for when there are multiple rows.

              In other words, I can tweak the where conditions slightly so that it returns a few more rows, but I see the same effect with the performance. Fast without order by, extremely slow with it.

              Thanks
              • 4. Re: order by causes query to be much slower (only 1 row returned)
                rp0428
                >
                I see that in the query plan, the ORDER BY causes a few more full table scans, but I have no idea why.
                >
                Because if I ask you to sort every book in the library by author's last name you aren't going to use the card catalogue; you are just going to go back to the books shelves and start sorting books. Using the card catalogue is analogous to using an index.

                See the FAQ for how to post a tuning query. You need to post the query, the execution plan, row counts for the tables involved, row counts for the predicates.

                Are the stats on all of the tables and indexes up to date?
                • 5. Re: order by causes query to be much slower (only 1 row returned)
                  866564
                  Ok, before I post all the explain plans, here's a new wrinkle.

                  I have another database server with the exact same schema and data as the problematic database.

                  This other database is actually 11.2.0.1.0. It doesn't have the problem with the slow ORDER BY. It's fast whether it's ordered or not.

                  Looking at all the explain plans, I see that I get destroyed by lack of a VIEW PUSHED PREDICATE in one particular part of the plan. In other words:

                  11.1 server, no ORDER BY ==> VIEW PUSHED PREDICATE ... fast
                  11.1 server, with ORDER BY ==> no predicate push, horrible series of hash joins and FTS resulting in 271M in the "Bytes" column of autotrace output ... sloooow
                  11.2 server, either way ==> VIEW PUSHED PREDICATE ... fast

                  So would it be reasonable to conclude that this really is just some optimizer bug that's related to the oracle version/patchlevel? Would it be advisable to try a predicate push hint?

                  Or could it be related to the sga/pga/etc parameters of the respective servers? They are supposed to be tuned similarly but by no means identically. The server hardware is certainly different.

                  Thanks
                  • 6. Re: order by causes query to be much slower (only 1 row returned)
                    rp0428
                    >
                    So would it be reasonable to conclude that this really is just some optimizer bug that's related to the oracle version/patchlevel?
                    >
                    It wouldn't be reasonable for any of us to conclude anything since, so far, you have chosen NOT to post anything for us to look at that could indicate what the problem might be. Until you do you are pretty much on your own.
                    >
                    Would it be advisable to try a predicate push hint?
                    >
                    It's your database. You don't need permission from any of us to try things.