6.获取执行计划方法
1.explain plan
scott@ORCLPDB01 2023-04-02 19:49:41> explain plan for select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;
Explained.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 19:50:51> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
18 rows selected.
Elapsed: 00:00:00.032.dbms_xplan
2.1.select * from table(dbms_xplan.display);可能不准
scott@ORCLPDB01 2023-04-02 19:49:41> explain plan for select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;
Explained.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 19:50:51> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
18 rows selected.
Elapsed: 00:00:00.032.2.select * from table(dbms_xplan.display(null,null,'advanced'));准确
scott@ORCLPDB01 2023-04-02 19:51:18> select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
7566 JONES RESEARCH
7902 FORD RESEARCH
7876 ADAMS RESEARCH
7369 SMITH RESEARCH
7788 SCOTT RESEARCH
7521 WARD SALES
7844 TURNER SALES
7499 ALLEN SALES
7900 JAMES SALES
7698 BLAKE SALES
7654 MARTIN SALES
14 rows selected.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 19:54:29> select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_MERGE(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
"ENAME"[VARCHAR2,10]
5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DEPT]]></t><s><![CDATA[
SEL$1]]></s></h><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
60 rows selected.2.3. select * from table(dbms_xplan.display_cursor('7fnnxpj6prnu0',0,'advanced'));准确
sys@ORCL 2023-04-02 19:57:39> col SQL_TEXT for a60;
sys@ORCL 2023-04-02 19:57:52> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select empno,ename%'
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
--------------------------------------------------------------------- ------------- ---------- ------------
select empno,ename,dname from emp,dept where emp.deptno = dept.deptno 7fnnxpj6prnu0 1297863488 0
Elapsed: 00:00:00.01
sys@ORCL 2023-04-02 19:57:53> select * from table(dbms_xplan.display_cursor('7fnnxpj6prnu0',0,'advanced'));
PLAN_TABLE_OUTPUT SQL_ID
---------------------------------------------------------------------------------
select empno,ename,dname from emp,dept where emp.deptno = dept.deptno
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
USE_MERGE(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
"ENAME"[VARCHAR2,10]
5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DEPT]]></t><s><![CDATA[
SEL$1]]></s></h><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
65 rows selected.
Elapsed: 00:00:00.172.4.select * from table(dbms_xplan.display_awr('a2dk8bdn0ujx7'));准确
scott@ORCLPDB01 2023-04-02 20:12:33> select count(*) from t1;
COUNT(*)
----------
3
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 20:12:49> conn / as sysdba
Connected.
sys@ORCL 2023-04-02 20:12:58> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID VERSION_COUNT EXECUTIONS
------------- ------------- ----------
select count(*) from t1
5bc0v4my7dvr5 1 1
Elapsed: 00:00:00.02
sys@ORCL 2023-04-02 20:13:00> col SQL_TEXT for a45;
sys@ORCL 2023-04-02 20:13:12> r
1* select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%'
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
--------------------------------------------- ------------- ------------- ----------
select count(*) from t1 5bc0v4my7dvr5 1 1
Elapsed: 00:00:00.02sys@ORCLPDB01 2023-04-02 20:35:30> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.01
sys@ORCLPDB01 2023-04-02 20:35:39> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.41
sys@ORCLPDB01 2023-04-02 20:35:49> col SQL_TEXT for a45;
sys@ORCLPDB01 2023-04-02 20:36:15> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from emp%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
--------------------------------------------- ------------- ------------- ----------
select * from emp a2dk8bdn0ujx7 1 1
Elapsed: 00:00:00.02
sys@ORCLPDB01 2023-04-02 20:36:40> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.15
sys@ORCLPDB01 2023-04-02 20:37:17> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from emp%';
no rows selected
Elapsed: 00:00:00.02
sys@ORCLPDB01 2023-04-02 20:37:51> select * from table(dbms_xplan.display_awr('a2dk8bdn0ujx7'));
no rows selected
Elapsed: 00:00:00.073.sqlplus中的autotrace
数据库中set autotrace有哪些用法
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
说明：
前三个参数{OFF | ON | TRACE[ONLY]} 里必需选择一个，而且只能选择一个
后两个参数[EXP[LAIN]] [STAT[ISTICS]]是可选的，也可以都不选择
TRACE[ONLY]的含意是只显示explain和statistic，不显示SQL的结果集，带TRACE[ONLY]的参数的以下的4,5,6最常用的是第4种
只要带上off，后面的[EXP[LAIN]] [STAT[ISTICS]]就无效了
1:set autotrace on;
显示：记录集+explain+statistics
scott@ORCLPDB01 2023-03-18 13:55:15> set autot on
scott@ORCLPDB01 2023-03-18 13:55:23> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
Statistics
----------------------------------------------------------
66 recursive calls
0 db block gets
124 consistent gets
4 physical reads
0 redo size
964 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
2:set autotrace on explain
显示：记录集+explain
scott@ORCLPDB01 2023-03-18 13:55:38> set autot on exp
scott@ORCLPDB01 2023-03-18 13:57:01> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
3:set autotrace on statistic
显示 ：记录集+statistics
scott@ORCLPDB01 2023-03-18 13:57:04> set autot on stat
scott@ORCLPDB01 2023-03-18 13:57:53> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4:set autotrace traceonly
显示： expain+statistics(不显示数据)
scott@ORCLPDB01 2023-03-18 13:58:49> set autot trace
scott@ORCLPDB01 2023-03-18 13:59:05> select * from emp where empno = 7566;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
391 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5：set autotrace traceonly statistics
显示： statistics
scott@ORCLPDB01 2023-03-18 13:59:47> set autot trace stat
scott@ORCLPDB01 2023-03-18 14:00:06> select * from emp where empno = 7566;
Elapsed: 00:00:00.01
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6：set autotrace traceonly explain
显示： explain
scott@ORCLPDB01 2023-03-18 13:59:45> select * from emp where empno = 7566;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
7:set autotrace off
scott@ORCLPDB01 2023-03-18 14:00:07> set autot off
scott@ORCLPDB01 2023-03-18 14:00:33> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
显示：只显示记录集,这也就是默认的SQL PLUS的窗口效果4.10046事件
4.1.在session中激活10046
alter session set events '10046 trace name context forever,level 12';
---------------------------------------------------------------------------------------
oradebug setmypid;
oradebug event 10046 trace name context forever,level 12;4.2.在session中执行目标sql
select empno,ename,dname from scott.emp,scott.dept where emp.deptno = dept.deptno;4.3.在session中关闭10046事件
alter session set events '10046 trace name context off';
----------------------------------------------------------------
oradebug event 10046 trace name context off;4.4分析trc文件
[oracle@yuanzj.com:/home/oracle]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_36318.trc
output = orcl_ora_36318例子
[oracle@yuanzj.com:/home/oracle]$ sqlplus sys/Oracle_4U@orclpdb01 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 2 21:16:48 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
sys@ORCLPDB01 2023-04-02 21:16:48> oradebug setmypid;
Statement processed.
sys@ORCLPDB01 2023-04-02 21:17:08> oradebug event 10046 trace name context forever,level 12;
Statement processed.
sys@ORCLPDB01 2023-04-02 21:17:12> select empno,ename,dname from scott.emp,scott.dept where emp.deptno = dept.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
7566 JONES RESEARCH
7902 FORD RESEARCH
7876 ADAMS RESEARCH
7369 SMITH RESEARCH
7788 SCOTT RESEARCH
7521 WARD SALES
7844 TURNER SALES
7499 ALLEN SALES
7900 JAMES SALES
7698 BLAKE SALES
7654 MARTIN SALES
14 rows selected.
Elapsed: 00:00:00.01
sys@ORCLPDB01 2023-04-02 21:17:17> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_36318.trc
sys@ORCLPDB01 2023-04-02 21:17:34> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
[oracle@yuanzj.com:/home/oracle]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_36318.trc
output = orcl_ora_36318
TKPROF: Release 19.0.0.0.0 - Development on Sun Apr 2 21:17:56 2023
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
[oracle@yuanzj.com:/home/oracle]$ cat orcl_ora_36318.prf
TKPROF: Release 19.0.0.0.0 - Development on Sun Apr 2 21:17:56 2023
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_36318.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 2618z3prwfyb9 Plan Hash: 844388907
select empno,ename,dname
from
scott.emp,scott.dept where emp.deptno = dept.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 10 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 10 0 14
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 MERGE JOIN (cr=10 pr=0 pw=0 time=145 us starts=1 cost=6 size=364 card=14)
4 4 4 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=67 us starts=1 cost=2 size=52 card=4)
4 4 4 INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=33 us starts=1 cost=1 size=0 card=4)(object id 74863)
14 14 14 SORT JOIN (cr=6 pr=0 pw=0 time=89 us starts=4 cost=4 size=182 card=14)
14 14 14 TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=64 us starts=1 cost=3 size=182 card=14)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
SQL ID: 9babjv8yq8ru3 Plan Hash: 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
SQL*Net message from client 1 16.71 16.71
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 1
Fetch 2 0.00 0.00 0 10 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 10 0 15
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 5 16.71 24.49
Disk file operations I/O 3 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_36318.trc
Trace file compatibility: 12.2.0.0
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
102 lines in trace file.
20 elapsed seconds in trace file.注意：先从最开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行
sys@ORCLPDB01 2023-04-02 22:32:53> select empno,ename,rowid from scott.emp where ename = 'SMITH';
EMPNO ENAME ROWID
---------- ---------- ------------------
7369 SMITH AAASRwAAMAAAACVAAA
Elapsed: 00:00:00.00
sys@ORCLPDB01 2023-04-02 22:33:01> set autot trace
sys@ORCLPDB01 2023-04-02 22:33:09> select empno,ename from scott.emp where rowid = 'AAASRwAAMAAAACVAAA';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1116584662
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 22 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
629 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sys@ORCLPDB01 2023-04-02 22:33:53> select empno,ename from scott.emp where empno = '7369';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
629 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
目标SQL的执行计划在访问表EMP时走的是对表EMP的ROWID扫描，所以执行计划关键字是TABLE ACCESS BY USER ROWID ，这里的ROWID是来源于索引PK_EMP，所以其对应的执行计划关键字是TABLE ACCESS BY INDEX ROWID
5.与B树索引相关的执行计划
索引唯一扫描：index unique scan
scott@ORCLPDB01 2023-04-02 22:44:32> create table employee(gender varchar2(1),employee_id number);
Table created.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 22:45:05> insert into employee values('F','99');
1 row created.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-02 22:46:56> insert into employee values('F','100');
1 row created.
Elapsed: 00:00:00.10
scott@ORCLPDB01 2023-04-02 22:47:02> insert into employee values('M','101');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:09> insert into employee values('M','102');
1 row created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 22:47:12> insert into employee values('M','103');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:15> insert into employee values('M','104');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:17> insert into employee values('M','105');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:22> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:25> create unique index idx_uni_emp on employee(employee_id);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 22:50:13> select * from employee where employee_id = 100;
G EMPLOYEE_ID
- -----------
F 100
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:50:40> set autot trace;
scott@ORCLPDB01 2023-04-02 22:50:52> select * from employee where employee_id = 100;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1887894887
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 15 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_UNI_EMP | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
397 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed索引范围扫描：index range scan
scott@ORCLPDB01 2023-04-02 22:53:11> select * from employee where employee_id = 100;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3410127368
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE | 1 | 15 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_UNI_EMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
635 bytes sent via SQL*Net to client
412 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed索引全扫描：index full scan
scott@ORCLPDB01 2023-04-02 22:53:13> truncate table employee;
Table truncated.
Elapsed: 00:00:00.04
scott@ORCLPDB01 2023-04-02 22:54:00> begin
2 for i in 1..5000 loop
3 insert into employee values('F',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:54:42> begin
2 for i in 5001..10000 loop
3 insert into employee values('M',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:55:45> select gender,count(*) from employee group by gender;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1624656943
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 20000 | 8 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 10000 | 20000 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 10000 | 20000 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
684 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
scott@ORCLPDB01 2023-04-02 22:56:20> set autot off;
scott@ORCLPDB01 2023-04-02 22:56:30> select gender,count(*) from employee group by gender;
G COUNT(*)
- ----------
M 5000
F 5000
Elapsed: 00:00:00.00
22:58:37> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
scott@ORCLPDB01 2023-04-02 22:58:48> set autot trace
scott@ORCLPDB01 2023-04-02 22:59:02> select employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7724 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
scott@ORCLPDB01 2023-04-02 22:59:19> select /*+ index(employee idx_uni_emp) */ employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEE@SEL$1
U - index(employee idx_uni_emp)
Statistics
----------------------------------------------------------
43 recursive calls
0 db block gets
745 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7976 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10000 rows processed索引快速全扫描：index fast full scan
scott@ORCLPDB01 2023-04-02 23:01:08> alter table employee modify(employee_id not null);
Table altered.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 23:03:53> select employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 114952492
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_UNI_EMP | 10000 | 40000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
709 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7724 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
scott@ORCLPDB01 2023-04-02 23:04:07> select /*+ index(employee idx_uni_emp) */ employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3257444309
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 20 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_UNI_EMP | 10000 | 40000 | 20 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
685 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7755 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed索引跳跃式扫描：index skip scan
scott@ORCLPDB01 2023-04-02 23:05:32> create index idx_emp on employee(gender,employee_id);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 23:06:15> select * from employee where employee_id = 101;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1432429342
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_EMP | 1 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID"=101)
filter("EMPLOYEE_ID"=101)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
13 physical reads
0 redo size
632 bytes sent via SQL*Net to client
412 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
