8 Replies Latest reply: Jul 11, 2012 12:09 AM by onkar.nath RSS

    Plan hash value for two queries!

    onkar.nath
      Hi,
      DB : Oracle 11g (11.2.0.3.0)
      OS: RHEL 5

      I have two question:

      1. Can two queries have same plan hash value? I mean I have below two queries:

      SELECT /+ NO_MERGE */ MIN(payor.next_review_date)*
      * FROM payor*
      * WHERE payor.review_complete = 0*
      * AND payor.closing_date IS NULL*
      * AND payor.patient_key = 10;*

      and

      SELECT  MIN(payor.next_review_date)
      * FROM payor*
      * WHERE payor.review_complete = 0*
      * AND payor.closing_date IS NULL*
      * AND payor.patient_key = 10;*

      When I tried to review the execution plan for both queries, the plan hash value remain same. Does it mean that execution plan for both queries are same? If yes, then how Oracle understands or changes the execution plan based on hint. If no then what plan hash value represents?

      2. If the execution plan with hint and without hint is same except for a given query except no.of rows and bytes. Does it mean that query with less rows and bytes scanned is better?

      Thanks in advance
      -Onkar
        • 1. Re: Plan hash value for two queries!
          Antonio Navarro
          1- Yes is possible same plan hash value, differents are marked by child number

          2- Different number or rows is equal a different queries !!!

          HTH
          Antonio NAVARRO
          • 2. Re: Plan hash value for two queries!
            onkar.nath
            thanks for the reply Antonio.

            1. Let me rephrase my understanding based on your answer: plan hash value can be same for different query and if there is difference in the execution plan then oracle will generate child hash number. Is that correct? But one question: if plan hash value is same then how Oracle decides which hash plan belongs to which query? Could you please elaborate it a bit more.

            2. I did not got your second point. Let say I have one query SELECT /*+ ORDERED */ EMPNO,ENAME,DNAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO
            and SELECT EMPNO,ENAME,DNAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO and execution plan for both of these queries are same except for the no of rows and bytes scanned are different then which query is better? I mean if rows scanned in case of query with hint is say 10 and no of rows scanned in case of query without hint is 100 then which one is better i.e. the one with hint or without hint?

            Thanks,
            Onkar
            • 3. Re: Plan hash value for two queries!
              Karan
              They will be simply distinguished with their child numbers so the plan hash value can surely be same ... you should see why a seperate child cursor was created means the reason for a mismatch in select sql_text,sql_id from v$sql where hash_value = '3535363636'; select * from v$sql_shared_cursor where sql_id = 'sgfsd42342'; Above your statements look similar and for plan hash value calculation anyway object_id is not used.. its the text which matters
              • 4. Re: Plan hash value for two queries!
                Nikolay Savvinov
                Hi Onkar,

                1) same plan hash value = same plan (unless you have a hash collision, but chances of that are very very low).
                2) if the plan is the same with or without hint then it means that the hint is not making any difference

                Best regards,
                Nikolay
                • 5. Re: Plan hash value for two queries!
                  onkar.nath
                  that means no of rows and bytes scanned in execution plan have no significance in terms of performance of the query and they are listed for informative or documentation puprose?
                  • 6. Re: Plan hash value for two queries!
                    Nikolay Savvinov
                    Hi,

                    there are two different things. One is EXPLAIN PLAN, which is how the optimizer thinks the query will be executed. It contains some estimates of cost, cardinalities etc. There is also EXECUTION PLAN. It also contains all this information regarding the optimizer estimates, but on the top of that, it also contains information about actual I/O incurred, actual cardinalities, actual timings etc.

                    So if a hint is changing optimizer estimates, but the plan stays the same, then impact on query's performance is zero.

                    If the actual numbers are changing, this is probably also irrelevant to the hint (e.g. you can have less physical reads because more blocks are found in the buffer cache the second time you're running the query, or you less work because you don't have to parse the statement etc.).

                    Actually, most of optimizer hints don't affect optimizer estimates; rather, they try to get the optimizer to use a certain access method or a certain join order etc. regardless of the cost. So you must be talking about such hints as cardinality, dynamic_sampling etc. If that's not the case -- please clarify, because this means that something wrong is going on here (e.g. an INDEX hint may work or it may fail to work, but if it fails, optimizer estimates shouldn't change).

                    Best regards,
                    Nikolay
                    • 7. Re: Plan hash value for two queries!
                      rp0428
                      >
                      1. Can two queries have same plan hash value?
                      >
                      Yes - as already answered - but it isn't just due to hints.

                      Here is example code I use to show that Oracle does NOT simply use a text version of the query you submit and compute a hash from it.
                      These two queries look radically different but have the same hash value; to Oracle they are not different at all.
                      set serveroutput on
                      set autotrace traceonly
                      select * from DEPT A, EMP B
                      where A.DEPTNO=B.DEPTNO
                       
                      SQL> select * from DEPT A, EMP B
                        2  where A.DEPTNO=B.DEPTNO
                        3  / 
                       
                      14 rows selected.
                       
                       
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 844388907
                       
                      select * from
                      (select * from DEPT) A, (select * from EMP) B
                      WHERE A.DEPTNO = B.DEPTNO
                       
                      SQL> select * from
                        2  (select * from DEPT) A, (select * from EMP) B
                        3  WHERE A.DEPTNO = B.DEPTNO
                        4  / 
                       
                      14 rows selected.
                       
                       
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 844388907
                      • 8. Re: Plan hash value for two queries!
                        onkar.nath
                        Thanks a lot everybody for the answers/explanation. My question has been answered well and I have got the information what I was basically looking for. Thanks once again.