5 Replies Latest reply: Nov 18, 2012 3:33 AM by Nikolay Savvinov RSS

    differences b/w 2 explain plans

    905632
      11gR2

      What is difference for below explain plans in order of execution,
      Please find the query and query plan of both below:

      FIRST
      PLAN_TABLE_OUTPUT
      
      SQL_ID  agf3cck4j02zs, child number 0
      -------------------------------------
      select /*+GATHER_PLAN_STATISTICS*/ * from ( select * from employees e 
      where exists (select /*no_unnest*/1 from departments where 
      manager_id=e.manager_id) )
       
      Plan hash value: 61425051
       
      -------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
      -------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT     |             |      1 |        |     44 |00:00:00.01 |      15 |       |       |          |
      |*  1 |  HASH JOIN RIGHT SEMI|             |      1 |     65 |     44 |00:00:00.01 |      15 |  1066K|  1066K| 1063K (0)|
      |*  2 |   TABLE ACCESS FULL  | DEPARTMENTS |      1 |     11 |     11 |00:00:00.01 |       7 |       |       |          |
      |   3 |   TABLE ACCESS FULL  | EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |       8 |       |       |          |
      -------------------------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         1 - access("MANAGER_ID"="E"."MANAGER_ID")
         2 - filter("MANAGER_ID" IS NOT NULL)
      SECOND
       PLAN_TABLE_OUTPUT
      
      SQL_ID  4x6rmw43x68xg, child number 0
      -------------------------------------
      select /*+GATHER_PLAN_STATISTICS*/ * from ( select * from employees e 
      where exists (select /*+no_unnest*/1 from departments where 
      manager_id=e.manager_id) )
       
      Plan hash value: 2318085533
       
      --------------------------------------------------------------------------------------------
      | Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
      --------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |             |      1 |        |     44 |00:00:00.01 |     141 |
      |*  1 |  FILTER            |             |      1 |        |     44 |00:00:00.01 |     141 |
      |   2 |   TABLE ACCESS FULL| EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |       8 |
      |*  3 |   TABLE ACCESS FULL| DEPARTMENTS |     19 |      2 |      8 |00:00:00.01 |     133 |
      --------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         1 - filter( IS NOT NULL)
         3 - filter("MANAGER_ID"=:B1)
      Question 1:
      As we know semi joins are joins that stop when the first hit is encountered. They are used many times for "existence" checks (-- copied text from asktom.com)

      Then why there is no filter predicate like filter("MANAGER_ID"=:B1) not there in first query, which is there in 2nd query?
      Why STARTS column is 1 in step 2 , if SEMI join have happened then it should have more in number?

      Question 2:
      STARTS column at step 3 is 19, for every record in employees table 19 times department table was been referred, this is actually a SEMI join behavior in nested join semi scenario.
      So does 2nd query plan is similar to NESTED LOOPS SEMI ?

      Question 3:
      Can you differentiate how things are processed exactly (rows coming from dept and emp tables, filters, joins etc..) in both the plans?

      Thanks in advance,
      SAM
        • 1. Re: differences b/w 2 explain plans
          Hoek
          On a first glance:
          The first query contains a comment (/*no_unnest*/) instead of a hint, like your second query (/*+no_unnest*/).                                                                                                                                                                                                                                                                   
          • 2. Re: differences b/w 2 explain plans
            905632
            I purposefully commented that hint, my plan was to run same query with and without hint
            • 3. Re: differences b/w 2 explain plans
              Nikolay Savvinov
              Hi,
              Question 1:
              As we know semi joins are joins that stop when the first hit is encountered. They are used many times for "existence" checks (-- copied text from asktom.com)

              Then why there is no filter predicate like filter("MANAGER_ID"=:B1) not there in first query, which is there in 2nd query?
              It appears so that your questions stem mainly from your confusion about the FILTER operation. It's not a join, it's not application of a filter predicate -- rather, in this context, it's simply a way of denoting an unntested correlated subquery. That's why there is MANAGER_ID = :B1 predicate: it means that for each row in EMPLOYEES table, something like select /*+no_unnest*/1 from departments where  manager_id=:B1 and rownum=1, where :B1 is the value of e.manager_id for that particular row.

              In plan 1, the subquery is unnested, i.e. replaced with a join (more accurately, a semijoin). Naturally, instead of of a correlated predicate in this case you have a join predicate ("MANAGER_ID"="E"."MANAGER_ID")
              Why STARTS column is 1 in step 2 , if SEMI join have happened then it should have more in number?
              It's not just any semi join. It's a hash semi join. With hash joins, both rowsources only need to be read once. First, DEPARTMENTS table is scanned and transformed into some kind of an in memory hash table with MANAGER_ID as the hash key. Then, EMPLOYEES table is scanned, and per each row, the hashing function is applied to MANAGER_ID to quickly calculate the address of the matching row in the "in memory hash table".

              >
              Question 2:
              STARTS column at step 3 is 19, for every record in employees table 19 times department table was been referred, this is actually a SEMI join behavior in nested join semi scenario.
              So does 2nd query plan is similar to NESTED LOOPS SEMI ?
              There are 19 starts because there is an unnested subquery. Basically, for each of 19 values of EMPLOYEES.MANAGER_ID a select from DEPARTMENTS table was performed, involving a full table scan. It's not NESTED LOOPS SEMI, it's FILTER.
              Question 3:
              Can you differentiate how things are processed exactly (rows coming from dept and emp tables, filters, joins etc..) in both the plans?
              Hopefully, you should get a basic picture from my explanations above. If anything is still unclear, feel free to ask.

              Best regards,
              Nikolay
              • 4. Re: differences b/w 2 explain plans
                905632
                Hopefully, you should get a basic picture from my explanations above. If anything is still unclear, feel free to ask.
                Hats off :)

                Nikolay, Thank you so much for answering.

                It appears so that your questions stem mainly from your confusion about the FILTER operation.
                I was wondering why FILTER operation and NESTED LOOPS SEMI are doing same, like iterating for every record in EMP table to DEPT table. That's y i got doubt whether they are doing same technically but showing difference only in plan.
                PLAN_TABLE_OUTPUT
                
                SQL_ID  1w1b875ktvtt1, child number 0
                -------------------------------------
                select /*+GATHER_PLAN_STATISTICS*/ * from ( select * from employees e  
                where exists (select /*+nl_sj*/1 from departments where  
                manager_id=e.manager_id) )
                 
                Plan hash value: 2307166682
                 
                -----------------------------------------------------------------------------------------------------
                | Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
                -----------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |             |      1 |        |     44 |00:00:00.02 |     141 |     12 |
                |   1 |  NESTED LOOPS SEMI |             |      1 |     65 |     44 |00:00:00.02 |     141 |     12 |
                |   2 |   TABLE ACCESS FULL| EMPLOYEES   |      1 |    107 |    107 |00:00:00.02 |       8 |      6 |
                |*  3 |   TABLE ACCESS FULL| DEPARTMENTS |     19 |      7 |      8 |00:00:00.01 |     133 |      6 |
                -----------------------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                 
                   3 - filter(("MANAGER_ID" IS NOT NULL AND "MANAGER_ID"="E"."MANAGER_ID"))
                Please correct me whether i am rite or wrong....

                As we know SELECT is the only statement, which will be used to read data from the table, whether it was human or SYS.

                From the above explain plan we can say that step 3 filtering will happen only by SELECT statement like ..
                SELECT MANAGER_ID FROM DEPARTMENTS WHERE MANAGER_ID IS NOT NULL AND MANAGER_ID = E.MANAGER_ID
                is nothing but
                SELECT MANAGER_ID FROM DEPARTMENTS WHERE MANAGER_ID IS NOT NULL AND MANAGER_ID = :B1 -- considering soft-parse because this will be used 19 times, as we know
                is nothing but
                SELECT MANAGER_ID FROM DEPARTMENTS WHERE MANAGER_ID IS NOT NULL AND MANAGER_ID = :B1 AND ROWNUM=1 --considering SEMI functionality
                It's a similar one like what you said ...
                That's why there is MANAGER_ID = :B1 predicate: it means that for each row in EMPLOYEES table, something like select /*+no_unnest*/1 from departments where manager_id=:B1 and rownum=1, where :B1 is the value of e.manager_id for that particular row.
                so both are almost same ??
                • 5. Re: differences b/w 2 explain plans
                  Nikolay Savvinov
                  Hi,

                  >
                  I was wondering why FILTER operation and NESTED LOOPS SEMI are doing same, like iterating for every record in EMP table to DEPT table. That's y i got doubt whether they are doing same technically but showing difference only in plan.
                  PLAN_TABLE_OUTPUT
                  
                  SQL_ID  1w1b875ktvtt1, child number 0
                  -------------------------------------
                  select /*+GATHER_PLAN_STATISTICS*/ * from ( select * from employees e  
                  where exists (select /*+nl_sj*/1 from departments where  
                  manager_id=e.manager_id) )
                  
                  Plan hash value: 2307166682
                  
                  -----------------------------------------------------------------------------------------------------
                  | Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
                  -----------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT   |             |      1 |        |     44 |00:00:00.02 |     141 |     12 |
                  |   1 |  NESTED LOOPS SEMI |             |      1 |     65 |     44 |00:00:00.02 |     141 |     12 |
                  |   2 |   TABLE ACCESS FULL| EMPLOYEES   |      1 |    107 |    107 |00:00:00.02 |       8 |      6 |
                  |*  3 |   TABLE ACCESS FULL| DEPARTMENTS |     19 |      7 |      8 |00:00:00.01 |     133 |      6 |
                  -----------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                  3 - filter(("MANAGER_ID" IS NOT NULL AND "MANAGER_ID"="E"."MANAGER_ID"))
                  Please correct me whether i am rite or wrong....
                  ...
                  so both are almost same ??
                  yes the are almost the same, "almost" being the key word. :)

                  A FILTER operation means that rows from one input are kept or discarded based on the results from the other input. A NESTED LOOP means that per each row in one input (the outer rowsource) an data access operation on the other input (the inner rowsource) is performed. In the simplest case (like the one above) they are quite similar.

                  Best regards,
                  Nikolay