6 Replies Latest reply on Apr 10, 2017 6:50 PM by Jonathan Lewis

    Questions about hint

    1255076

      Hi,

      question about hint in 11.2.0.4

      A developer write a query in which use this hint:

       

           /*+ ordered first_rows(1) parallel */

       

      In the plan there are 2 tables in FTS.

      I don't know why, but is a nosense the use of first_rows and parallel with FTS?

       

      Thank you

        • 1. Re: Questions about hint
          Jonathan Lewis

          1255076 wrote:

           

          question about hint in 11.2.0.4

          A developer write a query in which use this hint:

           

          /*+ ordered first_rows(1) parallel */

           

          In the plan there are 2 tables in FTS.

          I don't know why, but is a nosense the use of first_rows and parallel with FTS?

           

           

           

          It's a common misconception that parallel and first_rows(n) are incompatible with each other, but although the cases where it's useful are probably fairly rare it is possible that the combination will produce the best plan.

           

          One thing that ought to be corrected in this hinting is the ordered: if hinting is needed at all your developers should get into the habit of using a carefully designed leading() hint instead.

           

          Regards

          Jonathan Lewis

          • 2. Re: Questions about hint
            Mustafa KALAYCI

            Hi,

             

            I'm totally agree with Jonathan, in addition if there is only 2 table, I don't thing that ordered and leading usage is necessary. Oracle would be finding best execution plan.

            • 3. Re: Questions about hint
              John Thorton

              1255076 wrote:

               

              Hi,

              question about hint in 11.2.0.4

              A developer write a query in which use this hint:

               

              /*+ ordered first_rows(1) parallel */

               

              In the plan there are 2 tables in FTS.

              I don't know why, but is a nosense the use of first_rows and parallel with FTS?

               

              Thank you

              SQL hints should NOT be deployed into Production.

              • 4. Re: Questions about hint
                Evgeni Gelfand

                You should be aware that parallel hint specified there incorrectly and such query would take all available parallel slaves and use it on some random table specified in from, which is I think you don't want.

                In general I would ban use hint's by developers. Especially in this case when developer seems don't understand what is hints for and don't care to read documentation.

                 

                 

                Evgeni

                • 5. Re: Questions about hint
                  Jonathan Lewis

                  The parallel hint is specified correctly.

                  The query won't (necessarily) take all parallel query slaves

                  The optimizer won't pick "some random table" for parallel access.

                   

                  With this set of hints the optimizer will do the usual sort of arithmetic to decide what degree of parallelism should be used on which tables to return the first row at the lowest cost possible.

                   

                  Regards

                  Jonathan Lewis

                   

                   

                  P.S.  In general I would also suggest to developers that they not use hints unless really necessary, and then take specific advice for each query where they think they need to do it.

                  • 6. Re: Questions about hint
                    Jonathan Lewis

                    1255076,

                     

                    I've published a little note demonstrating that first_rows(1) and parallel can be used together and will have an effect that shows the optimizer has obeyed both hints: http://jonathanlewis.wordpress.com/2017/04/10/parallel-first_rows/

                     

                    My example doesn't result in a tablescan for the parallel first_rows(1) path, but it is perfectly feasible for the optimizer to decide that a path including a parallel hash join with tablescans IS the fastest way to get the first row from the required result set, it all depends on the data, the query, the indexing, and the resources available.

                     

                    Regards

                    Jonathan Lewis