The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.
Thanks for the question, Alan.
Asked: May 11, 2023 - 9:26 am UTC
Last updated: May 12, 2023 - 2:04 pm UTC
Version: rdbms 12.1-0.2
Viewed 1000+ times
create or replace view dept_emps as select department_id, department_name, first_name, last_name from hr.employees join hr.departments using ( department_id ); explain plan for select * from dept_emps; select * from table ( dbms_xplan.display ); PLAN_TABLE_OUTPUT Plan hash value: 3368388692 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 106 | 7314 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 106 | 7314 | 2 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 107 | 7314 | 2 (0)| 00:00:01 | | 3 | VIEW | index$_join$_002 | 107 | 4173 | 2 (0)| 00:00:01 | |* 4 | HASH JOIN | | | | | | | 5 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 4173 | 1 (0)| 00:00:01 | | 6 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 4173 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(ROWID=ROWID) 7 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID") set serveroutput off alter session set statistics_level = all; select * from dept_emps; select * from dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'); PLAN_TABLE_OUTPUT SQL_ID 0tgnz21xn4mc2, child number 0 ------------------------------------- select * from dept_emps Plan hash value: 3368388692 --------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 120 | | | | | 1 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 120 | | | | | 2 | NESTED LOOPS | | 1 | 107 | 106 |00:00:00.01 | 14 | | | | | 3 | VIEW | index$_join$_002 | 1 | 107 | 106 |00:00:00.01 | 9 | | | | |* 4 | HASH JOIN | | 1 | | 106 |00:00:00.01 | 9 | 1610K| 1610K| 1487K (0)| | 5 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 1 | 107 | 106 |00:00:00.01 | 4 | | | | | 6 | INDEX FAST FULL SCAN | EMP_NAME_IX | 1 | 107 | 107 |00:00:00.01 | 5 | | | | |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 106 | 1 | 106 |00:00:00.01 | 5 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 106 | 1 | 106 |00:00:00.01 | 106 | | | | --------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(ROWID=ROWID) 7 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
Get all the information about database performance in the Database Performance guide.