Oracle获取执行计划的方法（六脉神剑） 一、explai

发表于

「这是我参与11月更文挑战的第16天，活动详情查看：2021最后一次更文挑战」

一、explain plan for

1
2
sql复制代码1、explain plan for &sql_text；
2、select * from table(dbms_xplan.display());
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
sql复制代码SQL> set line222
SQL> set pagesize1000
SQL> explain plan for select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    12 |  1404 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    12 |  1404 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    12 |  1044 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   3 - filter("SAL">1000)

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

_优点：_无需真正执行，方便快捷，与PLSQL工具的F5一样

_缺点：_没有相关统计信息输出；无法判断处理多少行；无法判断表被访问多少次。

二、set autotrace on

1
2
3
sql复制代码1、set autotrace traceonly
2、执行sql
3、set autotrace off
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
sql复制代码SQL> set autotrace -h
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

SQL> set autotrace traceonly
SQL> select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000;

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    12 |  1404 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    12 |  1404 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    12 |  1044 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   3 - filter("SAL">1000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1935  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

_优点：_可以输出相关统计信息；可以通过traceonly来控制执行结果是否输出。

_缺点：_必须等到sql语句执行完毕；无法看到表被访问次数。

三、statistics_level=all

1
2
3
4
5
6
7
8
9
sql复制代码两种方式：
1、alter session set statistics_level=all;
2、执行sql
3、select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

或者执行sql时加上hint 'gather_plan_statistics'

1、select /*+ gather_plan_statistics  */ * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000;
2、select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
sql复制代码SQL> alter session set statistics_level=all;

SQL> show parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      ALL

SQL> SELECT  statistics_name 
  2         ,session_status 
  3         ,system_status 
  4         ,activation_level 
  5         ,session_settable
  6  FROM v$statistics_level
  7  ORDER BY statistics_name;

STATISTICS_NAME                                                  SESSION_ SYSTEM_S ACTIVAT SES
---------------------------------------------------------------- -------- -------- ------- ---
Active Session History                                           ENABLED  ENABLED  TYPICAL NO
Adaptive Thresholds Enabled                                      ENABLED  ENABLED  TYPICAL NO
Automated Maintenance Tasks                                      ENABLED  ENABLED  TYPICAL NO
Bind Data Capture                                                ENABLED  ENABLED  TYPICAL NO
Buffer Cache Advice                                              ENABLED  ENABLED  TYPICAL NO
Global Cache CPU Statistics                                      DISABLED DISABLED ALL     NO
Global Cache Statistics                                          ENABLED  ENABLED  TYPICAL NO
Longops Statistics                                               ENABLED  ENABLED  TYPICAL NO
MTTR Advice                                                      ENABLED  ENABLED  TYPICAL NO
Modification Monitoring                                          ENABLED  ENABLED  TYPICAL NO
PGA Advice                                                       ENABLED  ENABLED  TYPICAL NO
Plan Execution Sampling                                          ENABLED  ENABLED  TYPICAL YES
Plan Execution Statistics                                        ENABLED  DISABLED ALL     YES
SQL Monitoring                                                   ENABLED  ENABLED  TYPICAL YES
Segment Level Statistics                                         ENABLED  ENABLED  TYPICAL NO
Shared Pool Advice                                               ENABLED  ENABLED  TYPICAL NO
Streams Pool Advice                                              ENABLED  ENABLED  TYPICAL NO
Threshold-based Alerts                                           ENABLED  ENABLED  TYPICAL NO
Time Model Events                                                ENABLED  ENABLED  TYPICAL YES
Timed OS Statistics                                              ENABLED  DISABLED ALL     YES
Timed Statistics                                                 ENABLED  ENABLED  TYPICAL YES
Ultrafast Latch Statistics                                       ENABLED  ENABLED  TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up                            ENABLED  ENABLED  TYPICAL NO
V$IOSTAT_* statistics                                            ENABLED  ENABLED  TYPICAL NO

24 rows selected.

SQL> alter session set statistics_level=all;

Session altered.

SQL> select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000;

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- -------------
      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600        300         30         30 SALES          CHICAGO
      7521 WARD       SALESMAN        7698 22-FEB-81                1250        500         30         30 SALES          CHICAGO
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20         20 RESEARCH       DALLAS
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         30 SALES          CHICAGO
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30         30 SALES          CHICAGO
      7782 CLARK      MANAGER         7839 09-JUN-81                2450                    10         10 ACCOUNTING     NEW YORK
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20         20 RESEARCH       DALLAS
      7839 KING       PRESIDENT            17-NOV-81                5000                    10         10 ACCOUNTING     NEW YORK
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500          0         30         30 SALES          CHICAGO
      7876 ADAMS      CLERK           7788 23-MAY-87                1100                    20         20 RESEARCH       DALLAS
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20         20 RESEARCH       DALLAS
      7934 MILLER     CLERK           7782 23-JAN-82                1300                    10         10 ACCOUNTING     NEW YORK

12 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3264d6n6xacac, child number 1
-------------------------------------
select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000

Plan hash value: 615168685

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     12 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     12 |     12 |00:00:00.01 |      15 |  1321K|  1321K|  750K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| EMP  |      1 |     12 |     12 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   3 - filter("SAL">1000)

Note
-----
   - dynamic sampling used for this statement (level=2)


25 rows selected.


--通过hint ’gather_plan_statistics'可以不需要设置ALL
SQL> select /*+ gather_plan_statistics  */ * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000;

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- -------------
      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600        300         30         30 SALES          CHICAGO
      7521 WARD       SALESMAN        7698 22-FEB-81                1250        500         30         30 SALES          CHICAGO
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20         20 RESEARCH       DALLAS
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         30 SALES          CHICAGO
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30         30 SALES          CHICAGO
      7782 CLARK      MANAGER         7839 09-JUN-81                2450                    10         10 ACCOUNTING     NEW YORK
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20         20 RESEARCH       DALLAS
      7839 KING       PRESIDENT            17-NOV-81                5000                    10         10 ACCOUNTING     NEW YORK
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500          0         30         30 SALES          CHICAGO
      7876 ADAMS      CLERK           7788 23-MAY-87                1100                    20         20 RESEARCH       DALLAS
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20         20 RESEARCH       DALLAS
      7934 MILLER     CLERK           7782 23-JAN-82                1300                    10         10 ACCOUNTING     NEW YORK

12 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1kxydxbgh08q2, child number 0
-------------------------------------
select /*+ gather_plan_statistics  */ * from emp t1,dept t2 where
t1.deptno=t2.deptno and sal >1000

Plan hash value: 615168685

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     12 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     12 |     12 |00:00:00.01 |      15 |  1321K|  1321K|  765K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| EMP  |      1 |     12 |     12 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   3 - filter("SAL">1000)

Note
-----
   - dynamic sampling used for this statement (level=2)


26 rows selected.

优点：可以通过STRATS得出表被访问次数；可以通过E-Rows和A-Rows来判断预测行数和实际行数是否一致；可以通过Buffers来获取逻辑读数值。

_缺点：_需要sql语句执行完；必须将执行结果输出；看不出物理读数值。

四、dbms_xplan.display_cursor

1
2
3
4
sql复制代码1、获取sql_id
2、查看AWR和CURSOR中的执行计划
select * from table(dbms_xplan.display_awr('&sqlid'));
select * from table(dbms_xplan.display_cursor('&sqlid'));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
sql复制代码--查看AWR和CURSOR中的执行计划
select * from table(dbms_xplan.display_awr('&sqlid'));
select * from table(dbms_xplan.display_cursor('&sqlid'));

SQL> desc dbms_xplan
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT


SQL> select distinct sql_id,plan_hash_value from v$sql where sql_text like 'select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000%';

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
3264d6n6xacac       615168685

--获取AWR中的执行计划
SQL> select * from table(dbms_xplan.display_awr('3264d6n6xacac'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3264d6n6xacac
--------------------
select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000

Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|   1 |  HASH JOIN         |      |    12 |  1404 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    12 |  1044 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


19 rows selected.

--获取共享池中的执行计划
SQL> select * from table(dbms_xplan.display_cursor('3264d6n6xacac'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3264d6n6xacac, child number 0
-------------------------------------
select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000

Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|*  1 |  HASH JOIN         |      |    12 |  1404 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    12 |  1044 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   3 - filter("SAL">1000)

Note
-----
   - dynamic sampling used for this statement (level=2)


25 rows selected.

_优点：_通过sql_id可以立即获取执行计划，无需执行；可以得到真实执行过的执行计划。

_缺点：_没有输出相关统计信息；无法判断处理行数；无法判断表访问次数。

五、event 10046 trace

1
2
3
4
5
sql复制代码1、设置10046事件
alter session set events '10046 trace name context forever,level 12';
2、tkprof格式化trace文件
tkprof /oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc,/home/oracle/events_10046.txt
3、查看trace文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
sql复制代码SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select * from emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000;

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- -------------
      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600        300         30         30 SALES          CHICAGO
      7521 WARD       SALESMAN        7698 22-FEB-81                1250        500         30         30 SALES          CHICAGO
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20         20 RESEARCH       DALLAS
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         30 SALES          CHICAGO
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30         30 SALES          CHICAGO
      7782 CLARK      MANAGER         7839 09-JUN-81                2450                    10         10 ACCOUNTING     NEW YORK
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20         20 RESEARCH       DALLAS
      7839 KING       PRESIDENT            17-NOV-81                5000                    10         10 ACCOUNTING     NEW YORK
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500          0         30         30 SALES          CHICAGO
      7876 ADAMS      CLERK           7788 23-MAY-87                1100                    20         20 RESEARCH       DALLAS
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20         20 RESEARCH       DALLAS
      7934 MILLER     CLERK           7782 23-JAN-82                1300                    10         10 ACCOUNTING     NEW YORK

12 rows selected.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like 'Default Trace File%';

VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc

--通过tkprof格式化trace文件
SQL> !tkprof /oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc,/home/oracle/events_10046.txt sys=no sort=prsela,exeela,fchela

TKPROF: Release 11.2.0.4.0 - Development on Fri Apr 16 11:29:10 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.



SQL> !cat /home/oracle/events_10046.txt

Trace file: /oracle/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1706.trc
Sort options: prsela  exeela  fchela  
********************************************************************************
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: 3264d6n6xacac Plan Hash: 615168685

select * 
from
 emp t1,dept t2 where t1.deptno=t2.deptno and sal >1000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          2         18          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         15          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          2         33          0          12

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        12         12         12  HASH JOIN  (cr=15 pr=0 pw=0 time=170 us cost=6 size=1404 card=12)
         4          4          4   TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=12 us cost=3 size=120 card=4)
        12         12         12   TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=11 us cost=3 size=1044 card=12)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       34.46         34.46
********************************************************************************

_优点：_可以查看SQL语句对应等待事件；函数调用的SQL将被列出；可以查看处理行数和物理逻辑读；可以看出解析时间和执行时间；可以跟踪整个程序包；

_缺点：_步骤较为繁琐；无法判断表被访问次数；

六、awrsqrpt.sql

1
2
3
4
sql复制代码1、@?/rdbms/admin/awrsqrpt.sql
2、输入begin snap和end snap
3、输入sql_id
4、查看sqlrpt报告
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
sql复制代码
SQL> @?/rdbms/admin/awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1176847559 ORCL11G             1 orcl11g


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: 

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1176847559        1 ORCL11G      orcl11g      orcl11g

Using 1176847559 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl11g      ORCL11G              2 16 Apr 2021 10:05      1
                                  3 16 Apr 2021 11:01      1
                                  4 16 Apr 2021 11:48      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3
Begin Snapshot Id specified: 3

Enter value for end_snap: 4
End   Snapshot Id specified: 4




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 3264d6n6xacac
SQL ID specified:  3264d6n6xacac

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_3_4.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 

Using the report name awrsqlrpt_1_3_4.html

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%