查看执行计划

最新推荐文章于 2024-11-17 08:30:00 发布

转载最新推荐文章于 2024-11-17 08:30:00 发布 · 197 阅读

文章标签：

#sql #table #statistics #access #disk #oracle

oracle sql 语句调优及相关技术专栏收录该内容

106 篇文章

订阅专栏

本文详细介绍了如何使用autotrace、explainplanfor和tkprof等工具来生成和优化SQL执行计划，包括如何设置autotrace参数、使用explainplanfor进行计划分析以及通过tkprof工具转换跟踪文件以获取性能数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如何产生执行计划？

1） autotrace

准备PLAN_TABLE，/rdbms/admin/utlxplan.sql /sqlplus/admin/plustrace.sql

grant plustrace to user_name;

用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

举例：SET AUTOT [RACE] OFF

停止AutoTrace

SET AUTOT [RACE] ON

开启AutoTrace，显示AUTOTRACE信息和SQL执行结果

SET AUTOT[RACE] TRACEONLY

开启AutoTrace，仅显示AUTOTRACE信息

SET AUTOT[RACE] ON EXPLAIN

开启AutoTrace，仅显示AUTOTRACE的EXPLAIN信息

SET AUTOT[RACE] ON STATISTICS

开启AutoTrace，仅显示AUTOTRACE的STATISTICS信息

[c-sharp] view plain copy print ?

SQL> set autotrace on;
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace on;SQL> select * from dual;D-XExecution Plan----------------------------------------------------------Plan hash value: 272002086--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        407  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

[c-sharp] view plain copy print ?

SQL> set autotrace traceonly;
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace traceonly;SQL> select * from dual;Execution Plan----------------------------------------------------------Plan hash value: 272002086--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        407  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

[c-sharp] view plain copy print ?

SQL> set autotrace on explain;
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

[c-sharp] view plain copy print ?

SQL> set autotrace on statistics;
SQL> /
D
-
X
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

recursive calls：递归调用统计数据（指由于你需要执行其他SQL语句而必须执行的SQL）；

db block gets：通过update/delete/select for update读的次数；

consistent gets : 通过不带for update的select 读的次数；

physical reads：物理读，从磁盘上读取数据块的数量；

redo size：重做尺寸统计信息显示在执行过程中产生了多少重做数据；

bytes sent via SQL*Net to client：Total number of bytes sent to the client from the foreground processes；

bytes received via SQL*Net from client：Total number of bytes received from the client over Oracle Net；

SQL*Net roundtrips to/from client：Total number of Oracle Net messages sent to and received from the client；

sorts ( memory)：在内存中排序的次数；

sorts(disk)：在磁盘中排序的次数；

rows processed：结果返回的行数。

2） explain plan for

准备PLAN_TABLE，/rdbms/admin/utlxplan.sql

用法：explain plan for set statement= ‘…..’

explain plan for select ….

[c-sharp] view plain copy print ?

SQL> explain plan for SELECT /*+ordered use_hash(d,e) */e.ename,d.deptno
2 from lpx_emp e,lpx_dept d
3 WHERE e.deptno = d.deptno;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2846336464
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 462 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LPX_DEPT | 4 | 52 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
19 rows selected

SQL> explain plan for SELECT /*+ordered use_hash(d,e) */e.ename,d.deptno  2  from lpx_emp e,lpx_dept d   3  WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2846336464-------------------------------------------------------------------------------| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |          |    14 |   462 |     7  (15)| 00:00:01 ||*  1 |  HASH JOIN         |          |    14 |   462 |     7  (15)| 00:00:01 ||   2 |   TABLE ACCESS FULL| LPX_EMP  |    14 |   280 |     3   (0)| 00:00:01 ||   3 |   TABLE ACCESS FULL| LPX_DEPT |     4 |    52 |     3   (0)| 00:00:01 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("E"."DEPTNO"="D"."DEPTNO")Note-----   - dynamic sampling used for this statement 19 rows selected

3） tkprof

oracle在打开跟踪功能后，将被跟踪session中正在执行的SQL的性能状态数据都收集到一个跟踪文件中。这个跟踪文件提供了许多有用的信息，例如一个sql的解析次数、执行次数、fetch次数、物理读次数、逻辑读次数、CPU使用时间等，利用这些信息可以诊断你的sql的问题，从而用来优化你的系统。TKPROF工具对其进行转换。