Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | 7FS02, 7FS02PS
Document Number | TMOTI003
Overview
dbms_xplan.display_cursor is a function that retrieves plans registered in the Physical Plan Cache using the SQL_ID value.
This document explains how to use this function to check SQL execution plans (Plans) registered in the Physical Plan Cache.
Method
Note
Caution
1. Query SQL_ID and CHILD_NUMBER
SQL> select sql_id, CHILD_NUMBER, SQL_TEXT from V$SQLTEXT where sql_text like '%{part of the SQL statement to check}%';
2. Retrieve execution plan using the queried SQL_ID and CHILD_NUMBER
SQL> spool xplan.txt
SQL> select * from table(dbms_xplan.display_cursor('SQL_ID',CHILD_NUMBER,'ALL');
SQL> spool off
--Example
SQL> select * from table(dbms_xplan.display_cursor('84wtrjkhtuk86',44584,'ALL'));
3. Check the result file (xplan.txt)
| Item | Description |
|---|---|
| SQL ID | Queried SQL ID |
| CHILD NUMBER | Queried CHILD NUMBER |
| EXECUTIONS | Number of executions |
| LOADED AT | Time loaded into cache |
| TOT ELAPSED TIME | Total elapsed time |
| AVG ELAPSED TIME | Average elapsed time |
COLUMN_VALUE ------------------------------------- SQL ID : 84wtrjkhtuk86 CHILD NUMBER : 44584 HASH VALUE : 2711439622 PLAN HASH VALUE: 1515504815 EXECUTIONS : 3 FETCHES : 574 LOADED AT : 2025/03/12 15:05:58 TOT ELAPSED TIME: 00:07:03.7238 AVG ELAPSED TIME: 00:02:21.2413
| Item | Description |
|---|---|
| Operation | Type of operation performed |
| Name | Object name (table, index name) |
| Cost (%CPU) | Estimated cost value (CPU%) |
| Rows | Number of rows actually processed |
| Elaps. Time | Elapsed time per step |
| CR Gets | Number of Consistent Reads |
| Used Mem | Amount of memory used |
| Temp Read / Temp Write | Number of temporary space read/write blocks |
| Reads | Number of physical disk block reads |
COLUMN_VALUE ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | CR Gets | Starts | Used Mem | Temp. Read | Temp. Write| Reads | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | HASH JOIN (REVERSE LEFT OUTER) | | 261M (.01)| 55511 | 14102 |00:00:00.0460 | 112 | 1 | 1438K | 0 | 0 | 0 | | 2 | GROUP BY (SORT) | | 261M (.01)| 30145 | 12502 |00:00:00.0356 | 0 | 1 | 4687K | 0 | 0 | 0 | | 3 | HASH JOIN (SEMI) | | 261M (.01)| 30145 | 29627 |00:00:00.2338 | 0 | 1 | 5248K | 0 | 0 | 0 | | 4 | UNION ALL | | 7419 (.05)| 30939 | 34201 |00:00:00.0040 | 0 | 1 | 0K | 0 | 0 | 0 | | 5 | TABLE ACCESS (ROWID) | EMPLOY_TRAINEE_TOUR | 4364 (.02)| 22022 | 26910 |00:00:00.0132 | 815 | 1 | 0K | 0 | 0 | 0 | | 6 | INDEX (SKIP SCAN) | EMPLOY_TRAINEE_TOUR_1 | 217 (0)| 22022 | 26910 |00:00:00.1525 | 274 | 1 | 0K | 0 | 0 | 233 | | 7 | INDEX JOIN | | 3055 (.1)| 8916 | 7291 |00:00:00.0022 | 0 | 1 | 0K | 0 | 0 | 0 | | 8 | TABLE ACCESS (ROWID) | INVOLVED_COM_CHECK | 489 (0)| 2480 | 4624 |00:00:00.0026 | 212 | 1 | 0K | 0 | 0 | 0 | | 9 | INDEX (SKIP SCAN) | INVOLVED_COM_CHECK_PK | 38 (0)| 2480 | 4624 |00:00:00.0003 | 57 | 1 | 0K | 0 | 0 | 0 | | 10 | TABLE ACCESS (ROWID) | INVOLVED_COM_CHK_QT | 5 (0)| 1 | 7291 |00:00:00.0609 | 8857 | 4624 | 0K | 0 | 0 | 0 | | 11 | INDEX (RANGE SCAN) | INVOLVED_COM_CHK_QT_H | 4 (0)| 1 | 247721 |00:00:00.0360 | 8951 | 4624 | 0K | 0 | 0 | 0 | | 12 | FILTER | | 261M (.01)| 1100739 | 746694 |00:00:36.4823 | 12M | 1 | 0K | 0 | 0 | 0 | | 13 | TABLE ACCESS (FULL) | EMPLOY_TRAINEE_HIS |16939 (0)| 1100739 | 1123563 |00:00:00.1265 | 39213 | 1 | 0K | 0 | 0 | 0 | | 14 | CACHE | | 237 (0)| 0 | 1123563 |00:00:00.1504 | 0 | 1123K | 0K | 0 | 0 | 0 | | 15 | TABLE ACCESS (ROWID) | EMPLOY_TRAINEE_HIS | 237 (0)| 1 | 375089 |00:00:01.4285 | 643K | 1115K | 0K | 0 | 0 | 0 | | 16 | HASH JOIN (ANTI) | | 236 (0)| 1 | 646954 |00:00:01.1923 | 0 | 1115K | 144K | 0 | 0 | 0 | | 17 | HASH JOIN | | 6 (0)| 1 | 1124029 |00:00:03.6817 | 0 | 1115K | 78K | 0 | 0 | 0 | | 18 | INDEX (RANGE SCAN) | EMPLOY_TRAIN_HIS_3 | 3 (0)| 54 | 2194366 |00:00:04.8821 | 3400K | 1115K | 0K | 0 | 0 | 0 | | 19 | INDEX (RANGE SCAN) | EMPLOY_TRAIN_HIS_5 | 3 (0)| 54 | 1196691 |00:00:04.7531 | 3388K | 1115K | 0K | 0 | 0 | 0 | | 20 | TABLE ACCESS (ROWID) | EMPLOY_RE_HIS | 230 (0)| 223 | 473668 |00:00:02.7967 | 995K | 1115K | 0K | 0 | 0 | 0 | | 21 | INDEX (RANGE SCAN) | EMPLOY_RE_HIS | 6 (0)| 223 | 995327 |00:00:06.0843 | 4493K | 1115K | 0K | 0 | 0 | 0 | | 22 | HASH JOIN (REVERSE LEFT OUTER) | | 390K (.53)| 55511 | 14102 |00:00:00.0331 | 0 | 1 | 1453K | 0 | 0 | 0 | | 23 | TABLE ACCESS (FULL) | EMPLOY_TRAIN_ENT_EDU_HIS | 655 (.61)| 8879 | 13228 |00:00:00.0115 | 1505 | 1 | 0K | 0 | 0 | 0 | | 24 | INDEX JOIN (LEFT OUTER) | | 389K (.38)| 26658 | 14102 |00:00:00.0354 | 0 | 1 | 0K | 0 | 0 | 0 | | 25 | INDEX JOIN (LEFT OUTER) | | 386K (.29)| 6946 | 14102 |00:00:21.5471 | 335K | 1 | 0K | 0 | 0 | 0 | | 26 | HASH JOIN (LEFT OUTER) | | 358K (.27)| 6946 | 14102 |00:00:00.0265 | 0 | 1 | 9323K | 0 | 0 | 0 | | 27 | INDEX JOIN (LEFT OUTER) | | 280K (.25)| 6946 | 14102 |00:00:00.0153 | 0 | 1 | 0K | 0 | 0 | 0 | | 28 | WINDOW (SIMPLE) | | 259K (.25)| 6946 | 14102 |00:00:00.0182 | 0 | 1 | 8374K | 0 | 0 | 0 | | 29 | ORDER BY (SORT) | | 259K (.24)| 6946 | 14102 |00:00:00.0383 | 0 | 1 | 9206K | 0 | 0 | 0 | | 30 | WINDOW (SIMPLE) | | 137K (.44)| 6946 | 14102 |00:00:00.0218 | 0 | 1 | 264K | 0 | 0 | 0 | ...(omitted)....