Oracle database experiences

Oracle database blog

Query investigation, plan statistics and DBMS_XPLAN.DISPLAY_CURSOR

Scope

In some cases, you have a big or a complex SQL query and the result is not what was you have expected: “the query has lost some rows!”.
One solution in order to find which operation has lost some rows is to start this query with one table or subquery and to count the number of rows returned and then to add the different tables/subqueries and to restart this process until you lose some rows.
This process can be time consuming if above all the query takes a long time and is big and/or complex.
We will see that the query optimizer can help us to find the operation responsible of the missing rows.

GATHER_PLAN_STATISTICS

Let’s suppose that we have the following query:

DEV1@pdb1> SELECT t1.col1,
  2         t1.col2,
  3         t1.col3,
  4         t7.col2,
  5         t11.col3,
  6         (t14.col3+t10.col3) sum_t
  7  FROM table1 t1
  8  INNER JOIN table2 t2
  9    ON t2.col1=t1.col1
 10  INNER JOIN table3 t3
 11    ON t3.col1=t2.col1
 12  INNER JOIN table4 t4
 13    ON t4.col1=t3.col1
 14  INNER JOIN table5 t5
 15    ON t5.col1=t4.col1
 16  INNER JOIN table6 t6
 17    ON t6.col1=t5.col1
 18  INNER JOIN table7 t7
 19    ON t7.col1=t6.col1
 20  INNER JOIN table8 t8
 21    ON t8.col1=t7.col1
 22  INNER JOIN table9 t9
 23    ON t9.col1=t8.col1
 24  INNER JOIN table10 t10
 25    ON t10.col1=t9.col1
 26  INNER JOIN table11 t11
 27    ON t11.col1=t10.col1
 28  INNER JOIN table12 t12
 29    ON t12.col1=t11.col1
 30  INNER JOIN table13 t13
 31    ON t13.col1=t12.col1
 32  INNER JOIN table14 t14
 33    ON t14.col1=t13.col1
 34  INNER JOIN table15 t15
 35    ON t15.col1=t14.col1
 36  INNER JOIN table16 t16
 37    ON t16.col1=t15.col1
 38  INNER JOIN table17 t17
 39    ON t17.col1=t16.col1
 40  INNER JOIN
 41   ( SELECT col1
 42     FROM table18
 43    UNION
 44     SELECT col1
 45     FROM table19) tu1
 46    ON tu1.col1 = t17.col1;

no rows selected

The expected result should be around 4000 rows. We will re-run the query with the hint “GATHER_PLAN_STATISTICS” that will force the query optimizer to collect the number of rows for each operation of the execution plan.
Let’s execute again this query with such a hint:

DEV1@pdb1> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         t1.col1,
  3         t1.col2,
  4         t1.col3,
  5         t7.col2,
  6         t11.col3,
  7         (t14.col3+t10.col3) sum_t
  8  FROM table1 t1
  9  INNER JOIN table2 t2
 10    ON t2.col1=t1.col1
 11  INNER JOIN table3 t3
 12    ON t3.col1=t2.col1
 13  INNER JOIN table4 t4
 14    ON t4.col1=t3.col1
 15  INNER JOIN table5 t5
 16    ON t5.col1=t4.col1
 17  INNER JOIN table6 t6
 18    ON t6.col1=t5.col1
 19  INNER JOIN table7 t7
 20    ON t7.col1=t6.col1
 21  INNER JOIN table8 t8
 22    ON t8.col1=t7.col1
 23  INNER JOIN table9 t9
 24    ON t9.col1=t8.col1
 25  INNER JOIN table10 t10
 26    ON t10.col1=t9.col1
 27  INNER JOIN table11 t11
 28    ON t11.col1=t10.col1
 29  INNER JOIN table12 t12
 30    ON t12.col1=t11.col1
 31  INNER JOIN table13 t13
 32    ON t13.col1=t12.col1
 33  INNER JOIN table14 t14
 34    ON t14.col1=t13.col1
 35  INNER JOIN table15 t15
 36    ON t15.col1=t14.col1
 37  INNER JOIN table16 t16
 38    ON t16.col1=t15.col1
 39  INNER JOIN table17 t17
 40    ON t17.col1=t16.col1
 41  INNER JOIN
 42   ( SELECT col1
 43     FROM table18
 44    UNION
 45     SELECT col1
 46     FROM table19) tu1
 47    ON tu1.col1 = t17.col1;

no rows selected

We will then get the sql_id of the previous query in order to use the function DBMS_XPLAN.DISPLAY_CURSOR with the format options ALLSTATS and LAST:

  • ALLSTATS: IOSTATS and MEMSTATS options combined that are input/output and memory statistics,
  • LAST: indicates that we will display the statistics of the last run.

The execution plan is given below:

DEV1@pdb1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'dtj16r9xdbf2n',format =>'ALLSTATS LAST'));
SQL_ID  dtj16r9xdbf2n, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        t1.col1,        t1.col2,
    t1.col3,        t7.col2,        t11.col3,
(t14.col3+t10.col3) sum_t FROM table1 t1 INNER JOIN table2 t2   ON
t2.col1=t1.col1 INNER JOIN table3 t3   ON t3.col1=t2.col1 INNER JOIN
table4 t4   ON t4.col1=t3.col1 INNER JOIN table5 t5   ON
t5.col1=t4.col1 INNER JOIN table6 t6   ON t6.col1=t5.col1 INNER JOIN
table7 t7   ON t7.col1=t6.col1 INNER JOIN table8 t8   ON
t8.col1=t7.col1 INNER JOIN table9 t9   ON t9.col1=t8.col1 INNER JOIN
table10 t10   ON t10.col1=t9.col1 INNER JOIN table11 t11   ON
t11.col1=t10.col1 INNER JOIN table12 t12   ON t12.col1=t11.col1 INNER
JOIN table13 t13   ON t13.col1=t12.col1 INNER JOIN table14 t14   ON
t14.col1=t13.col1 INNER JOIN table15 t15   ON t15.col1=t14.col1 INNER
JOIN table16 t16   ON t16.col1=t15.col1 INNER JOIN table17 t17   ON
t17.col1=t16.col1 INNER JOIN  ( SELECT col1    FROM table18   UNION
SELECT col1    FROM table19) tu1   ON tu1.col1 = t17.col1

Plan hash value: 4272326101

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |      1 |        |      0 |00:00:00.04 |     251 |       |       |          |
|*  1 |  HASH JOIN                         |         |      1 |   8192 |      0 |00:00:00.04 |     251 |  2440K|  2440K| 1776K (0)|
|   2 |   VIEW                             |         |      1 |   8192 |   4096 |00:00:00.01 |      24 |       |       |          |
|   3 |    SORT UNIQUE                     |         |      1 |   8192 |   4096 |00:00:00.01 |      24 |   267K|   267K|  237K (0)|
|   4 |     UNION-ALL                      |         |      1 |        |   8192 |00:00:00.01 |      24 |       |       |          |
|   5 |      TABLE ACCESS FULL             | TABLE18 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|   6 |      TABLE ACCESS FULL             | TABLE19 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|*  7 |   HASH JOIN                        |         |      1 |   4096 |      0 |00:00:00.03 |     227 |  2440K|  2440K| 1783K (0)|
|   8 |    TABLE ACCESS FULL               | TABLE17 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|*  9 |    HASH JOIN                       |         |      1 |   4096 |      0 |00:00:00.03 |     215 |  2440K|  2440K| 1725K (0)|
|  10 |     TABLE ACCESS FULL              | TABLE16 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 11 |     HASH JOIN                      |         |      1 |   4096 |      0 |00:00:00.03 |     203 |  2440K|  2440K| 1726K (0)|
|  12 |      TABLE ACCESS FULL             | TABLE15 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 13 |      HASH JOIN                     |         |      1 |   4096 |      0 |00:00:00.03 |     191 |  1969K|  1969K| 1726K (0)|
|  14 |       TABLE ACCESS FULL            | TABLE14 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 15 |       HASH JOIN                    |         |      1 |   4096 |      0 |00:00:00.03 |     179 |  2440K|  2440K| 1726K (0)|
|  16 |        TABLE ACCESS FULL           | TABLE13 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 17 |        HASH JOIN                   |         |      1 |   4096 |      0 |00:00:00.03 |     167 |  2440K|  2440K| 1726K (0)|
|  18 |         TABLE ACCESS FULL          | TABLE12 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 19 |         HASH JOIN                  |         |      1 |   4096 |      0 |00:00:00.02 |     155 |  1483K|  1483K| 1734K (0)|
|  20 |          TABLE ACCESS FULL         | TABLE11 |      1 |   4096 |   4096 |00:00:00.01 |      31 |       |       |          |
|* 21 |          HASH JOIN                 |         |      1 |   4096 |   4096 |00:00:00.03 |     124 |  1969K|  1969K| 1734K (0)|
|  22 |           TABLE ACCESS FULL        | TABLE10 |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 23 |           HASH JOIN                |         |      1 |   4096 |   4096 |00:00:00.03 |     112 |  2440K|  2440K| 1737K (0)|
|  24 |            TABLE ACCESS FULL       | TABLE9  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 25 |            HASH JOIN               |         |      1 |   4096 |   4096 |00:00:00.03 |     100 |  2440K|  2440K| 1733K (0)|
|  26 |             TABLE ACCESS FULL      | TABLE8  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 27 |             HASH JOIN              |         |      1 |   4096 |   4096 |00:00:00.02 |      88 |  1969K|  1969K| 1733K (0)|
|  28 |              TABLE ACCESS FULL     | TABLE7  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 29 |              HASH JOIN             |         |      1 |   4096 |   4096 |00:00:00.02 |      76 |  2440K|  2440K| 1733K (0)|
|  30 |               TABLE ACCESS FULL    | TABLE6  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 31 |               HASH JOIN            |         |      1 |   4096 |   4096 |00:00:00.02 |      64 |  2440K|  2440K| 1737K (0)|
|  32 |                TABLE ACCESS FULL   | TABLE5  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 33 |                HASH JOIN           |         |      1 |   4096 |   4096 |00:00:00.01 |      52 |  2440K|  2440K| 1733K (0)|
|  34 |                 TABLE ACCESS FULL  | TABLE4  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 35 |                 HASH JOIN          |         |      1 |   4096 |   4096 |00:00:00.01 |      40 |  2440K|  2440K| 1733K (0)|
|  36 |                  TABLE ACCESS FULL | TABLE3  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|* 37 |                  HASH JOIN         |         |      1 |   4096 |   4096 |00:00:00.01 |      28 |  2440K|  2440K| 1705K (0)|
|  38 |                   TABLE ACCESS FULL| TABLE2  |      1 |   4096 |   4096 |00:00:00.01 |      12 |       |       |          |
|  39 |                   TABLE ACCESS FULL| TABLE1  |      1 |   4096 |   4096 |00:00:00.01 |      16 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TU1"."COL1"="T17"."COL1")
   7 - access("T17"."COL1"="T16"."COL1")
   9 - access("T16"."COL1"="T15"."COL1")
  11 - access("T15"."COL1"="T14"."COL1")
  13 - access("T14"."COL1"="T13"."COL1")
  15 - access("T13"."COL1"="T12"."COL1")
  17 - access("T12"."COL1"="T11"."COL1")
  19 - access("T11"."COL1"="T10"."COL1")
  21 - access("T10"."COL1"="T9"."COL1")
  23 - access("T9"."COL1"="T8"."COL1")
  25 - access("T8"."COL1"="T7"."COL1")
  27 - access("T7"."COL1"="T6"."COL1")
  29 - access("T6"."COL1"="T5"."COL1")
  31 - access("T5"."COL1"="T4"."COL1")
  33 - access("T4"."COL1"="T3"."COL1")
  35 - access("T3"."COL1"="T2"."COL1")
  37 - access("T2"."COL1"="T1"."COL1")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


90 rows selected.
  • “E-Rows”: represents the estimated number of rows,
  • “A-Rows”: represents the actual number of rows.

We will focus on the “A-Rows” column in blue on the below figure:

Query_investigation_01

We will analyse step by step the execution plan to find the operation that has lost the rows!

Investigation

  • Step 1: The “SELECT STATEMENT” operation (Id=0) gives 0 row. It is the result of a “HASH JOIN” operation (Id=1, in orange in the above figure) between the “VIEW” operations (Id=2,3,4,5,6) and a “HASH JOIN” operation (Id=7). The “VIEW” operation returns 4096 rows whereas the “HASH JOIN” operation returns 0 row. We will continue the investigation on the previous operation,
  • Step 2: The “HASH JOIN” operation (Id=7, in orange in the above figure) is the result of a “TABLE ACCESS FULL” operation (Id=8) and a “HASH JOIN” operation (Id=9). The “TABLE ACCESS FULL” operation returns 4096 rows whereas the “HASH JOIN” operation returns 0 row. We will continue the investigation on the previous operation,
  • Step 7: The “HASH JOIN” operation (Id=17) is the result of a “TABLE ACCESS FULL” operation (Id=18) and a “HASH JOIN” operation (Id=19). The “TABLE ACCESS FULL” operation returns 4096 rows whereas the “HASH JOIN” operation returns 0 row. We will continue the investigation on the previous operation,
  • Step 8:The “HASH JOIN” operation (Id=19, in orange in the above figure) is the result of a “TABLE ACCESS FULL” operation (Id=20, in red in the above figure) and a “HASH JOIN” operation (Id=21, in red in the above figure). The “TABLE ACCESS FULL” operation returns 4096 rows like the “HASH JOIN” operation.

Conclusion

The previous investigation means that the operation performed on (Id=19) loses the rows. In the section “Predicate Information” of the execution plan, we can see that this operation is “access(“T11″.”COL1″=”T10″.”COL1″)” which indicates that the join between Table11 and Table10 on COL1 column returned 0 row. We might then wonder why this join does not work as expected.

Remark:

  • For IOSTASTS format, the plan statistics can be also collected if the parameter statistics_level is set to ALL.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: