all about dbms_xplan info
TRANSCRIPT
![Page 1: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/1.jpg)
The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement
![Page 2: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/2.jpg)
An execution plan includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method.
To execute a SQL statement, Oracle might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to execute a statement is called an execution plan.
![Page 3: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/3.jpg)
Example illustrates the way the execution plan explained
EXPLAIN PLAN FORSELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103 AND e.job_id = j.job_id AND e.department_id = d.department_id;
Here the plan will be explainedThen execute the following to know the Predicate Information
select * from table(dbms_xplan.display(NULL, NULL, 'TYPICAL'));
EXPLAIN PLAN Output-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)||* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)||* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | || 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)||* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |-----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("E"."EMPLOYEE_ID"<103) 5 - access("E"."JOB_ID"="J"."JOB_ID") 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID“)
![Page 4: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/4.jpg)
The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats.
The DBMS_XPLAN package supplies below table functions:
◦ DISPLAY : To format and display the contents of a plan table ◦ DISPLAY_CURSOR : To format and display the contents of the
execution plan of any loaded cursor.
![Page 5: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/5.jpg)
DISPLAY Function: This table function displays the contents of the plan
table.
SyntaxDBMS_XPLAN.DISPLAY
( table_name IN VARCHAR2 DEFAULT
'PLAN_TABLE',statement_id IN VARCHAR2 DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL‘);
![Page 6: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/6.jpg)
![Page 7: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/7.jpg)
Let us consider a piece of query and see the optimizer behavior with either ways of EXPLAIN PLAN and EXECUTION PLAN
Example (a) – Using Explain Plan
SELECT TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1, PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1 AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL (PROJ.TEMPLATE_START_DATE_ACTIVE, TO_DATE ('01-01-1900', 'MM-DD-YYYY') ) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE, TRUNC (SYSDATE) ) AND TASK.PROJECT_ID = PROJ.PROJECT_ID
![Page 8: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/8.jpg)
![Page 9: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/9.jpg)
Example (b) – Displaying a Plan Table Using
(a) DBMS_XPLAN.DISPLAY
Step 1: Execute an explain plan command on a SELECT statement:
EXPLAIN PLAN FOR select……………………[query used above] Step 2: Display the plan using the
DBMS_XPLAN.DISPLAY table function select * from table(dbms_xplan.display(NULL, NULL,
'TYPICAL'))
![Page 10: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/10.jpg)
This query produces the following output:
PLAN_TABLE_OUTPUT
Plan hash value: 3336623817 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 17 | 1156 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 17 | 1156 | 4 (0)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| PA_PROJECTS_ALL | 1 | 42 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 | 1 | | 0 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| PA_TASKS | 17 | 442 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | PA_TASKS_N8 | 17 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL("PROJ"."TEMPLATE_START_DATE_ACTIVE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=TRUNC(SYSDATE@!) AND
NVL("PROJ"."TEMPLATE_END_DATE_ACTIVE",TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@!)) 3 - access("PROJ"."PROJECT_ID"=1001) 5 - access("TASK"."PROJECT_ID"=1001)
![Page 11: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/11.jpg)
DISPLAY_CURSOR Function This table function displays the explain plan of any
cursor loaded in the cursor cache.
Syntax DBMS_XPLAN.DISPLAY_CURSOR ( sql_id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL‘ );
![Page 12: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/12.jpg)
![Page 13: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/13.jpg)
Example (a) – Using Display_Cursor
SELECT /* abc */ TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1, PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1 AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL
(PROJ.TEMPLATE_START_DATE_ACTIVE, TO_DATE ('01-01-1900', 'MM-DD-YYYY') ) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE, TRUNC (SYSDATE) ) AND TASK.PROJECT_ID = PROJ.PROJECT_ID
![Page 14: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/14.jpg)
Step 1:Execute the querySELECT /* abc */ --------------[query used above]
Step 2: Get sql_id and child_number for the preceding statement:
SELECT sql_id, child_number, sql_text, sql_fulltextFROM v$sql where sql_text like '%abc%' Step 3:
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (‘NULL', NULL, 'advanced allstats last')) ;
![Page 15: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/15.jpg)
PLAN_TABLE_OUTPUT
SQL_ID 1hcqg71hwa8cc, child number 0-------------------------------------SELECT /* abc*/ TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1, PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1 AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL (PROJ.TEMPLATE_START_DATE_ACTIVE, TO_DATE ('01-01-1900', 'MM-DD-YYYY') ) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE, TRUNC (SYSDATE) ) AND TASK.PROJECT_ID = PROJ.PROJECT_ID Plan hash value: 3336623817 -------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 4 (100)| || 1 | NESTED LOOPS | | 17 | 1156 | 4 (0)| 00:00:01 ||* 2 | TABLE ACCESS BY INDEX ROWID| PA_PROJECTS_ALL | 1 | 42 | 1 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 | 1 | | 0 (0)| || 4 | TABLE ACCESS BY INDEX ROWID| PA_TASKS | 17 | 442 | 3 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | PA_TASKS_N8 | 17 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / PROJ@SEL$1 3 - SEL$1 / PROJ@SEL$1 4 - SEL$1 / TASK@SEL$1 5 - SEL$1 / TASK@SEL$1
![Page 16: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/16.jpg)
Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.5') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') OPT_PARAM('_index_join_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "PROJ"@"SEL$1" ("PA_PROJECTS_ALL"."PROJECT_ID")) INDEX_RS_ASC(@"SEL$1" "TASK"@"SEL$1" ("PA_TASKS"."PROJECT_ID" "PA_TASKS"."PM_TASK_REFERENCE")) LEADING(@"SEL$1" "PROJ"@"SEL$1" "TASK"@"SEL$1") USE_NL(@"SEL$1" "TASK"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter((NVL("PROJ"."TEMPLATE_START_DATE_ACTIVE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=TRUNC(SYSDATE@!) AND NVL("PROJ"."TEMPLATE_END_DATE_ACTIVE",TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@!))) 3 - access("PROJ"."PROJECT_ID"=1001) 5 - access("TASK"."PROJECT_ID"=1001) Column Projection Information (identified by operation id):----------------------------------------------------------- 1 - "PROJ"."NAME"[VARCHAR2,30], "PROJ"."SEGMENT1"[VARCHAR2,25], "TASK"."TASK_NUMBER"[VARCHAR2,25], "TASK"."TASK_NAME"[VARCHAR2,20] 2 - "PROJ"."NAME"[VARCHAR2,30], "PROJ"."SEGMENT1"[VARCHAR2,25] 3 - "PROJ".ROWID[ROWID,10] 4 - "TASK"."TASK_NUMBER"[VARCHAR2,25], "TASK"."TASK_NAME"[VARCHAR2,20] 5 - "TASK".ROWID[ROWID,10] Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
![Page 17: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/17.jpg)
Example (b) – Using Hint /*+ GATHER_PLAN_STATISTICS */
PLAN_TABLE_OUTPUT
SQL_ID 4azc9hf0kzg5g, child number 0-------------------------------------SELECT /* abc*/ /*+ GATHER_PLAN_STATISTICS */ TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1, PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1 AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL (PROJ.TEMPLATE_START_DATE_ACTIVE, TO_DATE ('01-01-1900', 'MM-DD-YYYY') ) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE, TRUNC (SYSDATE) ) AND TASK.PROJECT_ID = PROJ.PROJECT_ID Plan hash value: 3336623817 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 |00:00:00.01 | 6 || 1 | NESTED LOOPS | | 1 | 7 |00:00:00.01 | 6 ||* 2 | TABLE ACCESS BY INDEX ROWID| PA_PROJECTS_ALL | 1 | 1 |00:00:00.01 | 3 ||* 3 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 | 1 | 1 |00:00:00.01 | 2 || 4 | TABLE ACCESS BY INDEX ROWID| PA_TASKS | 1 | 7 |00:00:00.01 | 3 ||* 5 | INDEX RANGE SCAN | PA_TASKS_N8 | 1 | 7 |00:00:00.01 | 2 |-------------------------------------------------------------------------------------------------
![Page 18: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/18.jpg)
Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / PROJ@SEL$1 3 - SEL$1 / PROJ@SEL$1 4 - SEL$1 / TASK@SEL$1 5 - SEL$1 / TASK@SEL$1 Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.5') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') OPT_PARAM('_index_join_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "PROJ"@"SEL$1" ("PA_PROJECTS_ALL"."PROJECT_ID")) INDEX_RS_ASC(@"SEL$1" "TASK"@"SEL$1" ("PA_TASKS"."PROJECT_ID" "PA_TASKS"."PM_TASK_REFERENCE")) LEADING(@"SEL$1" "PROJ"@"SEL$1" "TASK"@"SEL$1") USE_NL(@"SEL$1" "TASK"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter((NVL("PROJ"."TEMPLATE_START_DATE_ACTIVE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=TRUNC(SYSDATE@!) AND NVL("PROJ"."TEMPLATE_END_DATE_ACTIVE",TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@!))) 3 - access("PROJ"."PROJECT_ID"=1001) 5 - access("TASK"."PROJECT_ID"=1001)
![Page 19: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/19.jpg)
Example (c)
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('d5k3znj9dfaw3', NULL, 'allstats last -rows +predicate')) ;
PLAN_TABLE_OUTPUT
SQL_ID byc2atjb80w2z, child number 0
-------------------------------------
SELECT /* bambo*/ TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1,
PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1
AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL
(PROJ.TEMPLATE_START_DATE_ACTIVE,
TO_DATE ('01-01-1900', 'MM-DD-YYYY')
) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE,
TRUNC (SYSDATE)
) AND TASK.PROJECT_ID = PROJ.PROJECT_ID
Plan hash value: 3336623817
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
|* 2 | TABLE ACCESS BY INDEX ROWID| PA_PROJECTS_ALL |
|* 3 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 |
| 4 | TABLE ACCESS BY INDEX ROWID| PA_TASKS |
|* 5 | INDEX RANGE SCAN | PA_TASKS_N8 |
--------------------------------------------------------
Cont…
![Page 20: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/20.jpg)
Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter((NVL("PROJ"."TEMPLATE_START_DATE_ACTIVE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=TRUNC(SYSDATE@!) AND
NVL("PROJ"."TEMPLATE_END_DATE_ACTIVE",TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@!
))) 3 - access("PROJ"."PROJECT_ID"=1001) 5 - access("TASK"."PROJECT_ID"=1001) Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
![Page 21: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/21.jpg)
Example (c) – Using Hint /*+ GATHER_PLAN_STATISTICS */
PLAN_TABLE_OUTPUT
SQL_ID 7quur9z2hcc8b, child number 0-------------------------------------SELECT /* bambo*/ /*+ GATHER_PLAN_STATISTICS */ TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1, PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1 AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL (PROJ.TEMPLATE_START_DATE_ACTIVE, TO_DATE ('01-01-1900', 'MM-DD-YYYY') ) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE, TRUNC (SYSDATE) ) AND TASK.PROJECT_ID = PROJ.PROJECT_ID Plan hash value: 3336623817 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 |00:00:00.01 | 6 || 1 | NESTED LOOPS | | 1 | 7 |00:00:00.01 | 6 ||* 2 | TABLE ACCESS BY INDEX ROWID| PA_PROJECTS_ALL | 1 | 1 |00:00:00.01 | 3 ||* 3 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 | 1 | 1 |00:00:00.01 | 2 || 4 | TABLE ACCESS BY INDEX ROWID| PA_TASKS | 1 | 7 |00:00:00.01 | 3 ||* 5 | INDEX RANGE SCAN | PA_TASKS_N8 | 1 | 7 |00:00:00.01 | 2 |------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter((NVL("PROJ"."TEMPLATE_START_DATE_ACTIVE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=TRUNC(SYSDATE@!) AND NVL("PROJ"."TEMPLATE_END_DATE_ACTIVE",TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@!))) 3 - access("PROJ"."PROJECT_ID"=1001) 5 - access("TASK"."PROJECT_ID"=1001)
![Page 22: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/22.jpg)
Example (d)
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('d5k3znj9dfaw3', NULL, 'allstats last -rows +alias +outline +predicate')) ;
PLAN_TABLE_OUTPUT
SQL_ID 1fd86xmu1swwt, child number 0 ------------------------------------- SELECT /* saboo */ TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1, PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1 AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL (PROJ.TEMPLATE_START_DATE_ACTIVE, TO_DATE ('01-01-1900', 'MM-DD-YYYY') ) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE, TRUNC (SYSDATE) ) AND TASK.PROJECT_ID = PROJ.PROJECT_ID Plan hash value: 3336623817 -------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | |* 2 | TABLE ACCESS BY INDEX ROWID| PA_PROJECTS_ALL | |* 3 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 | | 4 | TABLE ACCESS BY INDEX ROWID| PA_TASKS | |* 5 | INDEX RANGE SCAN | PA_TASKS_N8 | -------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / PROJ@SEL$1 3 - SEL$1 / PROJ@SEL$1 4 - SEL$1 / TASK@SEL$1 5 - SEL$1 / TASK@SEL$1
Cont….
![Page 23: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/23.jpg)
Outline Data
------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.5') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') OPT_PARAM('_index_join_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "PROJ"@"SEL$1" ("PA_PROJECTS_ALL"."PROJECT_ID")) INDEX_RS_ASC(@"SEL$1" "TASK"@"SEL$1" ("PA_TASKS"."PROJECT_ID" "PA_TASKS"."PM_TASK_REFERENCE")) LEADING(@"SEL$1" "PROJ"@"SEL$1" "TASK"@"SEL$1") USE_NL(@"SEL$1" "TASK"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter((NVL("PROJ"."TEMPLATE_START_DATE_ACTIVE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=TRUNC(SYSDATE@!) AND NVL("PROJ"."TEMPLATE_END_DATE_ACTIVE",TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@! ))) 3 - access("PROJ"."PROJECT_ID"=1001) 5 - access("TASK"."PROJECT_ID"=1001) Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
![Page 24: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/24.jpg)
Example (d) – Using Hint /*+ GATHER_PLAN_STATISTICS */
PLAN_TABLE_OUTPUT
SQL_ID 35d4cm4a53kd2, child number 0-------------------------------------SELECT /* saboo */ /*+ GATHER_PLAN_STATISTICS */ TASK.TASK_NAME, TASK.TASK_NUMBER, PROJ.SEGMENT1, PROJ.NAME FROM PA.PA_PROJECTS_ALL PROJ, PA.PA_TASKS TASK WHERE 1 = 1 AND PROJ.PROJECT_ID = 1001 AND TRUNC (SYSDATE) BETWEEN NVL (PROJ.TEMPLATE_START_DATE_ACTIVE, TO_DATE ('01-01-1900', 'MM-DD-YYYY') ) AND NVL (PROJ.TEMPLATE_END_DATE_ACTIVE, TRUNC (SYSDATE) ) AND TASK.PROJECT_ID = PROJ.PROJECT_ID Plan hash value: 3336623817 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 |00:00:00.01 | 6 || 1 | NESTED LOOPS | | 1 | 7 |00:00:00.01 | 6 ||* 2 | TABLE ACCESS BY INDEX ROWID| PA_PROJECTS_ALL | 1 | 1 |00:00:00.01 | 3 ||* 3 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 | 1 | 1 |00:00:00.01 | 2 || 4 | TABLE ACCESS BY INDEX ROWID| PA_TASKS | 1 | 7 |00:00:00.01 | 3 ||* 5 | INDEX RANGE SCAN | PA_TASKS_N8 | 1 | 7 |00:00:00.01 | 2 |------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / PROJ@SEL$1 3 - SEL$1 / PROJ@SEL$1 4 - SEL$1 / TASK@SEL$1 5 - SEL$1 / TASK@SEL$1
Cont…
![Page 25: All About Dbms_xplan Info](https://reader035.vdocuments.us/reader035/viewer/2022081412/543e8736afaf9f195e8b470a/html5/thumbnails/25.jpg)
Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.5') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') OPT_PARAM('_index_join_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "PROJ"@"SEL$1" ("PA_PROJECTS_ALL"."PROJECT_ID")) INDEX_RS_ASC(@"SEL$1" "TASK"@"SEL$1" ("PA_TASKS"."PROJECT_ID" "PA_TASKS"."PM_TASK_REFERENCE")) LEADING(@"SEL$1" "PROJ"@"SEL$1" "TASK"@"SEL$1") USE_NL(@"SEL$1" "TASK"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter((NVL("PROJ"."TEMPLATE_START_DATE_ACTIVE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=TRUNC(SYSDATE@!) AND NVL("PROJ"."TEMPLATE_END_DATE_ACTIVE",TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@!))) 3 - access("PROJ"."PROJECT_ID"=1001) 5 - access("TASK"."PROJECT_ID"=1001)