7 Replies Latest reply: Oct 11, 2010 7:19 PM by Taral RSS

    Plan Understanding

    Taral
      Hi All,

      Using 11.2.0.2

      What is Rows (1st) Rows (avg) Rows (max)
      alter index tdesai_dba.t_idx rebuild partition T_1 parallel 4 online
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        2      0.00       0.01          1          1          0           0
      Execute      9     16.26      58.10      33065      34678       5648           0
      Fetch        0      0.00       0.00          0          0          0           0
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total       11     16.26      58.12      33066      34679       5648           0
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: SYS   (recursive depth: 1)
      Number of plan statistics captured: 9
      
      Rows (1st) Rows (avg) Rows (max)  Row Source Operation
      ---------- ---------- ----------  ---------------------------------------------------
               0          0          4  PX COORDINATOR  (cr=1 pr=0 pw=0 time=795005 us)
               0          0          0   PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
               1          0          1    INDEX BUILD NON UNIQUE T_IDX (cr=73 pr=0 pw=456 time=3004795 us)(object id 0)
          838180     348550     990260     SORT CREATE INDEX (cr=0 pr=0 pw=0 time=2757375 us)
          838180     348550     990260      PX RECEIVE  (cr=0 pr=0 pw=0 time=2274457 us cost=3040 size=25095576 card=3136947)
               0          0          0       PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=3040 size=25095576 card=3136947)
               0     349152     796999        PX BLOCK ITERATOR PARTITION: 1 1 (cr=3748 pr=3660 pw=0 time=2059830 us cost=3040 size=25095576 card=3136947)
               0     349152     796999         TABLE ACCESS FULL T PARTITION: 1 1 (cr=3748 pr=3660 pw=0 time=1932330 us cost=3040 size=25095576 card=3136947)
        • 1. Re: Plan Understanding
          Taral
          Any updates
          • 2. Re: Plan Understanding
            Gerwin Hendriksen
            Hi,

            As I remember right the meaning is as follows:
            - Rows (1st): The row source data of the query the first time it was executed, per rowsource.
            - Rows (avg): The row source data averaged over all the occurences of the query execution, per rowsource.
            - Rows (max): The row source data of the maximums, per rowsource.

            ps. It is hard to find a sound and clear document stating the above.

            Regards, Gerwin
            • 3. Re: Plan Understanding
              Taral
              Thanks Gerwin,

              I don't know where to find this information. But will try again to search
              • 4. Re: Plan Understanding
                askraks
                Hi,

                1) how much time it was taking earlier?
                2) Please let me know row source information after doing below steps

                sql>ALTER SESSION DISABLE PARALLEL DDL;
                sql>alter index tdesai_dba.t_idx rebuild partition T_1 online;

                Kind Regards,
                Rakesh Jayappa
                • 5. Re: Plan Understanding
                  Taral
                  Thanks rakesh for responding. I am not concern about performance here. I was just looking for the meaning of that three columns in trace output.
                  • 6. Re: Plan Understanding
                    Charles Hooper
                    Taral wrote:
                    Hi All,

                    Using 11.2.0.2

                    What is Rows (1st) Rows (avg) Rows (max)
                    Taral,

                    As far as I am able to tell Oracle Database 11.2.0.1 and below do not output the "Rows (avg)" and "Rows (max)" columns, while "Rows (1st)" is simply labelled as "Rows" prior to 11.2.0.2. If I were to jump to a conclusion, I would simply state that TKPROF has likely been improved to help highlight execution plan changes, likely due to Oracle Database 11g's default behavior of outputting the execution plan after every execution of a SQL statement, rather than when the cursor is closed as had happened prior to 11.1. Here is a test case setup that you can try to see if my guess is correctly explaining what is happening:
                    CREATE TABLE T1(
                      C1 NUMBER NOT NULL,
                      C2 NUMBER NOT NULL,
                      C3 VARCHAR2(100));
                     
                    CREATE TABLE T2(
                      C1 NUMBER NOT NULL,
                      C2 NUMBER NOT NULL,
                      C3 VARCHAR2(100));
                     
                    INSERT INTO
                      T1
                    SELECT
                      ROWNUM C1,
                      V2.RN C2,
                      RPAD(TO_CHAR(ROWNUM),10,'A') C3
                    FROM
                      (SELECT
                        ROWNUM RN
                      FROM
                        DUAL
                      CONNECT BY
                        LEVEL<=1000) V1,
                      (SELECT
                        ROWNUM RN
                      FROM
                        DUAL
                      CONNECT BY
                        LEVEL<=1000) V2
                    WHERE
                      MOD(V1.RN,1000)>=V2.RN;
                     
                    INSERT INTO
                      T2
                    SELECT
                      *
                    FROM
                      T1;
                     
                    COMMIT;
                     
                    CREATE INDEX IND_T1_C1_C2 ON T1(C1,C2);
                    CREATE INDEX IND_T2_C1_C2 ON T2(C1,C2);
                     
                    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
                    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE)
                    Now the test case script:
                    VARIABLE N1 NUMBER
                     
                    EXEC :N1:=1
                     
                    SET LINESIZE 160
                    SET PAGESIZE 1000
                    SET TRIMSPOOL ON
                     
                    ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_10046_ROW_TEST';
                    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
                     
                    SELECT /*+ GATHER_PLAN_STATISTICS */
                      COUNT(*)
                    FROM
                      T1,
                      T2
                    WHERE
                      T1.C2=T2.C2
                      AND T1.C2= :N1;
                     
                    SELECT
                      *
                    FROM
                      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                     
                    EXEC :N1:=5
                     
                    SELECT /*+ GATHER_PLAN_STATISTICS */
                      COUNT(*)
                    FROM
                      T1,
                      T2
                    WHERE
                      T1.C2=T2.C2
                      AND T1.C2= :N1;
                     
                    SELECT
                      *
                    FROM
                      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                     
                    EXEC :N1:=95
                     
                    SELECT /*+ GATHER_PLAN_STATISTICS */
                      COUNT(*)
                    FROM
                      T1,
                      T2
                    WHERE
                      T1.C2=T2.C2
                      AND T1.C2= :N1;
                     
                    SELECT
                      *
                    FROM
                      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                     
                    EXEC :N1:=995
                     
                    SELECT /*+ GATHER_PLAN_STATISTICS */
                      COUNT(*)
                    FROM
                      T1,
                      T2
                    WHERE
                      T1.C2=T2.C2
                      AND T1.C2= :N1;
                     
                    SELECT
                      *
                    FROM
                      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                     
                    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
                    Here is the output that I obtained with 11.2.0.1:
                    SQL_ID  6z03777fugw57, child number 0
                    -------------------------------------
                    SELECT /*+ GATHER_PLAN_STATISTICS */   COUNT(*) FROM   T1,   T2 WHERE
                    T1.C2=T2.C2   AND T1.C2= :N1
                     
                    Plan hash value: 765688747
                     
                    -------------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
                    -------------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT       |              |      1 |        |      1 |00:00:00.13 |    2780 |      2 |       |       |          |
                    |   1 |  SORT AGGREGATE        |              |      1 |      1 |      1 |00:00:00.13 |    2780 |      2 |       |       |          |
                    |*  2 |   HASH JOIN            |              |      1 |    250K|    998K|00:00:00.14 |    2780 |      2 |  1517K|  1517K|  770K (0)|
                    |*  3 |    INDEX FAST FULL SCAN| IND_T1_C1_C2 |      1 |    500 |    999 |00:00:00.02 |    1390 |      0 |       |       |          |
                    |*  4 |    INDEX FAST FULL SCAN| IND_T2_C1_C2 |      1 |    500 |    999 |00:00:00.02 |    1390 |      2 |       |       |          |
                    -------------------------------------------------------------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       2 - access("T1"."C2"="T2"."C2")
                       3 - filter("T1"."C2"=:N1)
                       4 - filter("T2"."C2"=:N1)
                     
                    ----
                     
                    SQL_ID  6z03777fugw57, child number 0
                    -------------------------------------
                    SELECT /*+ GATHER_PLAN_STATISTICS */   COUNT(*) FROM   T1,   T2 WHERE
                    T1.C2=T2.C2   AND T1.C2= :N1
                     
                    Plan hash value: 765688747
                     
                    ----------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                    ----------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT       |              |      1 |        |      1 |00:00:00.14 |    2780 |       |       |          |
                    |   1 |  SORT AGGREGATE        |              |      1 |      1 |      1 |00:00:00.14 |    2780 |       |       |          |
                    |*  2 |   HASH JOIN            |              |      1 |    250K|    990K|00:00:00.14 |    2780 |  1517K|  1517K|  658K (0)|
                    |*  3 |    INDEX FAST FULL SCAN| IND_T1_C1_C2 |      1 |    500 |    995 |00:00:00.02 |    1390 |       |       |          |
                    |*  4 |    INDEX FAST FULL SCAN| IND_T2_C1_C2 |      1 |    500 |    995 |00:00:00.02 |    1390 |       |       |          |
                    ----------------------------------------------------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       2 - access("T1"."C2"="T2"."C2")
                       3 - filter("T1"."C2"=:N1)
                       4 - filter("T2"."C2"=:N1)
                     
                    ----
                     
                    SQL_ID  6z03777fugw57, child number 0
                    -------------------------------------
                    SELECT /*+ GATHER_PLAN_STATISTICS */   COUNT(*) FROM   T1,   T2 WHERE
                    T1.C2=T2.C2   AND T1.C2= :N1
                     
                    Plan hash value: 765688747
                     
                    ----------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                    ----------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT       |              |      1 |        |      1 |00:00:00.14 |    2780 |       |       |          |
                    |   1 |  SORT AGGREGATE        |              |      1 |      1 |      1 |00:00:00.14 |    2780 |       |       |          |
                    |*  2 |   HASH JOIN            |              |      1 |    250K|    819K|00:00:00.14 |    2780 |  1517K|  1517K|  653K (0)|
                    |*  3 |    INDEX FAST FULL SCAN| IND_T1_C1_C2 |      1 |    500 |    905 |00:00:00.03 |    1390 |       |       |          |
                    |*  4 |    INDEX FAST FULL SCAN| IND_T2_C1_C2 |      1 |    500 |    905 |00:00:00.02 |    1390 |       |       |          |
                    ----------------------------------------------------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       2 - access("T1"."C2"="T2"."C2")
                       3 - filter("T1"."C2"=:N1)
                       4 - filter("T2"."C2"=:N1)
                     
                    ----
                     
                    SQL_ID  6z03777fugw57, child number 0
                    -------------------------------------
                    SELECT /*+ GATHER_PLAN_STATISTICS */   COUNT(*) FROM   T1,   T2 WHERE
                    T1.C2=T2.C2   AND T1.C2= :N1
                     
                    Plan hash value: 765688747
                     
                    ----------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                    ----------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT       |              |      1 |        |      1 |00:00:00.04 |    2780 |       |       |          |
                    |   1 |  SORT AGGREGATE        |              |      1 |      1 |      1 |00:00:00.04 |    2780 |       |       |          |
                    |*  2 |   HASH JOIN            |              |      1 |    250K|     25 |00:00:00.04 |    2780 |  1452K|  1452K|  604K (0)|
                    |*  3 |    INDEX FAST FULL SCAN| IND_T1_C1_C2 |      1 |    500 |      5 |00:00:00.02 |    1390 |       |       |          |
                    |*  4 |    INDEX FAST FULL SCAN| IND_T2_C1_C2 |      1 |    500 |      5 |00:00:00.02 |    1390 |       |       |          |
                    ----------------------------------------------------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       2 - access("T1"."C2"="T2"."C2")
                       3 - filter("T1"."C2"=:N1)
                       4 - filter("T2"."C2"=:N1)
                    When I pass the trace file through TKPROF that is supplied with 11.2.0.1, I see the following:
                    SELECT /*+ GATHER_PLAN_STATISTICS */
                      COUNT(*)
                    FROM
                      T1,
                      T2
                    WHERE
                      T1.C2=T2.C2
                      AND T1.C2= :N1
                     
                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        4      0.00       0.00          0          0          0           0
                    Execute      4      0.00       0.00          0          0          0           0
                    Fetch        8      0.32       0.44          2      11120          0           4
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       16      0.32       0.44          2      11120          0           4
                     
                    Misses in library cache during parse: 1
                    Misses in library cache during execute: 1
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 288  
                     
                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                          1  SORT AGGREGATE (cr=2780 pr=2 pw=0 time=0 us)
                     998001   HASH JOIN  (cr=2780 pr=2 pw=0 time=120958 us cost=865 size=2000000 card=250000)
                        999    INDEX FAST FULL SCAN IND_T1_C1_C2 (cr=1390 pr=0 pw=0 time=18213 us cost=386 size=2000 card=500)(object id 77821)
                        999    INDEX FAST FULL SCAN IND_T2_C1_C2 (cr=1390 pr=2 pw=0 time=17964 us cost=386 size=2000 card=500)(object id 77822)
                    Note in the above that only the Rows column is present, and the execution plan that is displayed is the first one found in the trace file for the SQL statement. If you execute the above test script and process the trace file with TKPROF, do you see something like ths following:
                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
                    ---------- ---------- ----------  ---------------------------------------------------
                             1          1          1  SORT AGGREGATE (cr=2780 pr=2 pw=0 time=0 us)
                        998001     701756     998001   HASH JOIN  (cr=2780 pr=2 pw=0 time=120958 us cost=865 size=2000000 card=250000)
                           999        726        999    INDEX FAST FULL SCAN IND_T1_C1_C2 (cr=1390 pr=0 pw=0 time=18213 us cost=386 size=2000 card=500)(object id 77821)
                           999        726        999    INDEX FAST FULL SCAN IND_T2_C1_C2 (cr=1390 pr=2 pw=0 time=17964 us cost=386 size=2000 card=500)(object id 77822)
                    The second and third columns in the above were manually calculated using the output from DBMS_XPLAN. The second column ("Rows (avg)") was calculated as the average number of rows returned by that line in the execution plan. The third column (" Rows (max)") was calculated as the maximum number of rows returned by that line in the execution plan.

                    Charles Hooper
                    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                    http://hoopercharles.wordpress.com/
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 7. Re: Plan Understanding
                      Taral
                      Yes Charles,

                      Sir You are right. Here are few details

                      http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6020271977738

                      Please, go to last part