Read explain results from oracle database

explainoracle-12cquery-performance

hi currently i have query like this on oracle database 12

Query Detail because script is too long if input here

the query is too long execute time
from explain results i got this

PLAN_TABLE_OUTPUT
Plan hash value: 3233107407
 
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |     7 |  9093 | 11069   (1)| 00:00:01 |
|   1 |  SORT ORDER BY                          |                           |     7 |  9093 | 11069   (1)| 00:00:01 |
|*  2 |   HASH JOIN RIGHT OUTER                 |                           |     7 |  9093 | 11068   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL                    | TGOSPEMPFAVORITE          |     1 |    80 |     2   (0)| 00:00:01 |
|   4 |    NESTED LOOPS OUTER                   |                           |     7 |  8533 | 11066   (1)| 00:00:01 |
|   5 |     NESTED LOOPS OUTER                  |                           |     4 |  4472 | 11062   (1)| 00:00:01 |
|   6 |      NESTED LOOPS OUTER                 |                           |     4 |  4012 | 11050   (1)| 00:00:01 |
|   7 |       NESTED LOOPS                      |                           |     4 |  3732 | 11046   (1)| 00:00:01 |
|   8 |        NESTED LOOPS                     |                           |     4 |  3664 | 11042   (1)| 00:00:01 |
|*  9 |         HASH JOIN OUTER                 |                           |     4 |  3624 | 11042   (1)| 00:00:01 |
|  10 |          NESTED LOOPS                   |                           |     4 |   416 |    41   (0)| 00:00:01 |
|  11 |           NESTED LOOPS                  |                           |     4 |   416 |    41   (0)| 00:00:01 |
|  12 |            NESTED LOOPS                 |                           |     4 |   272 |    33   (0)| 00:00:01 |
|* 13 |             TABLE ACCESS FULL           | TEODEMPPATH               |     4 |    92 |    29   (0)| 00:00:01 |
|  14 |             TABLE ACCESS BY INDEX ROWID | TEOMEMPPERSONAL           |     1 |    45 |     1   (0)| 00:00:01 |
|* 15 |              INDEX UNIQUE SCAN          | SYS_C0053801              |     1 |       |     0   (0)| 00:00:01 |
|* 16 |            INDEX RANGE SCAN             | IDX_EMPLOYEE_ALL          |     1 |       |     1   (0)| 00:00:01 |
|* 17 |           TABLE ACCESS BY INDEX ROWID   | TEODEMPCOMPANY            |     1 |    36 |     2   (0)| 00:00:01 |
|  18 |          VIEW                           | VIEW_ATTENDANCE           |  1962 |  1536K| 11001   (1)| 00:00:01 |
|  19 |           NESTED LOOPS                  |                           |  1962 |   335K| 11001   (1)| 00:00:01 |
|  20 |            NESTED LOOPS                 |                           | 78880 |   335K| 11001   (1)| 00:00:01 |
|  21 |             TABLE ACCESS FULL           | TTAMSHIFTDAILY            |    58 |  3538 |     4   (0)| 00:00:01 |
|* 22 |             INDEX RANGE SCAN            | IX_TUNED_TTADATTENDANCE_1 |  1360 |       |   123   (2)| 00:00:01 |
|* 23 |            TABLE ACCESS BY INDEX ROWID  | TTADATTENDANCE            |    34 |  3876 |   190   (2)| 00:00:01 |
|* 24 |         INDEX UNIQUE SCAN               | SYS_C0053949              |     1 |    10 |     0   (0)| 00:00:01 |
|  25 |        TABLE ACCESS BY INDEX ROWID      | TCLMUSER                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 26 |         INDEX UNIQUE SCAN               | SYS_C0053882              |     1 |       |     0   (0)| 00:00:01 |
|  27 |       TABLE ACCESS BY INDEX ROWID       | TEOMPOSITION              |     1 |    70 |     1   (0)| 00:00:01 |
|* 28 |        INDEX UNIQUE SCAN                | SYS_C0053959              |     1 |       |     0   (0)| 00:00:01 |
|  29 |      TABLE ACCESS BY INDEX ROWID BATCHED| TTADATTSTATUSDETAIL       |     1 |   115 |     3   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN                  | SYS_C0053316              |     1 |       |     2   (0)| 00:00:01 |
|  31 |     TABLE ACCESS BY INDEX ROWID         | TTAMATTSTATUS             |     1 |   101 |     1   (0)| 00:00:01 |
|* 32 |      INDEX UNIQUE SCAN                  | SYS_C0053743              |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

This is detail from explain

the query is 2 minute execution time
can help me about the explain specially about this part

VIEW                           | VIEW_ATTENDANCE           |  1962 |  1536K| 11001   (1)| 00:00:01 |
|  19 |           NESTED LOOPS                  |                           |  1962 |   335K| 11001   (1)| 00:00:01 |
|  20 |            NESTED LOOPS                 |                           | 78880 |   335K| 11001   (1)| 00:00:01 |
|  21 |             TABLE ACCESS FULL           | TTAMSHIFTDAILY            |    58 |  3538 |     4   (0)| 00:00:01 |
|* 22 |             INDEX RANGE SCAN            | IX_TUNED_TTADATTENDANCE_1 |  1360 |       |   123   (2)| 00:00:01 |
|* 23 |            TABLE ACCESS BY INDEX ROWID  | TTADATTENDANCE            |    34 |  3876 |   190   (2)| 00:00:01 |

that is meaning left join is loop for 78k row data from view_attandance ?

Best Answer

In operation 21, the optimizer estimates 58 rows to be returned from table TTAMSHIFTDAILY.

Using these 58 rows, in operation 22, the database will access index IX_TUNED_TTADATTENDANCE_1 58 times, estimating 1360 rows to be returned. But that is not the total amount of rows it expects to return, because it is a nested loops join. That estimate is about 1 iteration of the loop.

So in operation 20, the optimizer estimates 58 * 1360 = 78880 rows to be returned.

Then the optimizer estimates 1962 rows to be returned from VIEW_ATTENDANCE, because the above is joined to TTADATTENDANCE as well.

But these are just estimates, not actual values. Also, this plan is an adaptive plan (as displayed in the Notes section), so some of the lines are not even displayed in the current output. You can add the format=>'adaptive' parameter to your DBMS_XPLAN call to display the full plan.

Related Question