oracle查询执行计划的方法有哪些

这篇文章主要介绍“oracle查询执行计划的方法有哪些”，在日常操作中，相信很多人在oracle查询执行计划的方法有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”oracle查询执行计划的方法有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

1.explain plan for

–无需执行，快捷方便

–没有统计信息，产生的逻辑读，递归等

–无法判断处理了多少行

–无法判断表被访问了多少次

explain plan for select * from t,t1 where t.type=t1.object_name;

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

Plan hash value: 2914261090

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 221 | 244 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 221 | 244 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T | 1 | 142 | 122 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 1 | 79 | 122 (0)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – access(“T”.”TYPE”=”T1″.”OBJECT_NAME”)

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

2.set autotrace on

–有输出统计信息

–必须要等语句执行完之后才有结果–无法看到表被访问了多少次

set autotrace on —set autotrace traceonly 不输出结果–

select * from t,t1 where t.type=t1.object_name;

Execution Plan

———————————————————-

Plan hash value: 2914261090

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 221 | 244 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 221 | 244 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T | 1 | 142 | 122 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 1 | 79 | 122 (0)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – access(“T”.”TYPE”=”T1″.”OBJECT_NAME”)

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

4 recursive calls

0 db block gets

896 consistent gets

0 physical reads

0 redo size

889 bytes sent via SQL*Net to client

408 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

3.statistics_level=all

–可以从Starts看出表被访问多少次，E-Rows，A-Rows预测行数与真实行数，buffer是真实的逻辑读

—语句执行完后才有结果，无法控制不出结果，看不出递归调用和逻辑读

alter session set statistics_level=all;

select * from t,t1 where t.type=t1.object_name;

select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));

—————————————————————————————————————-

—————————————————————————————————————-

| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 896 | | | |

|* 1 | HASH JOIN | | 1 | 1 | 3 |00:00:00.01 | 896 | 1695K| 1695K| 787K (0)|

| 2 | TABLE ACCESS FULL| T | 1 | 1 | 4 |00:00:00.01 | 447 | | | |

| 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 5 |00:00:00.01 | 449 | | | |

—————————————————————————————————————-

Predicate Information (identified by operation id):

—————————————————

1 – access(“T”.”TYPE”=”T1″.”OBJECT_NAME”)

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

4.dbms_xplan.display_cursor

–知道sql_id可以立即得出真是的执行计划，且可以直接得出

–没有相关的统计信息（逻辑读等）–无法判断执行了多少次–无法得出表被访问了多少次

5qn0b7zft4s04

select * from table(dbms_xplan.display_cursor(‘sql_id’))–共享池获取

select * from table(dbms_xplan.display_awr(‘sql_id’))–awr性能视图中获取

select * from table(dbms_xplan.display_cursor(‘5qn0b7zft4s04’))

select * from table(dbms_xplan.display_awr(‘5qn0b7zft4s04’))

Plan hash value: 2914261090

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | | | 244 (100)| |

|* 1 | HASH JOIN | | 1 | 221 | 244 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T | 1 | 142 | 122 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 1 | 79 | 122 (0)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – access(“T”.”TYPE”=”T1″.”OBJECT_NAME”)

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

5.10046 trace跟踪

–可以看出语句的等待事件，可以看出sql中的函数调用，

–可以看出处理的行数以及物理读–解析时间以及执行时间

–方便跟踪整个程序包

alter session set events ‘10046 trace name context forever,level 12’; 开启跟踪

执行语句

alter session set events ‘10046 trace name context off’;

找到文件

tkprof trc文件目标文件 sys=no sort=prsela,exeela,fchela

[oracle@oracle1 ~]$ cat 1.txt

TKPROF: Release 19.0.0.0.0 – Development on Wed Mar 11 10:25:48 2020

Trace file: NGENPR_ora_6661.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

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 2 0.00 0.00 0 138 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 760 0 3

——- —— ——– ———- ———- ———- ———- ———-

total 6 0.00 0.00 0 898 0 3

Misses in library cache during parse: 1

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

—————————————- Waited ———- ————

Disk file operations I/O 2 0.00 0.00

SQL*Net message to client 3 0.00 0.00

SQL*Net message from client 3 8.24 13.24

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 3 0.00 0.00 0 0 0 0

Execute 3 0.00 0.00 0 0 0 0

Fetch 3 0.00 0.00 0 136 0 2

——- —— ——– ———- ———- ———- ———- ———-

total 9 0.00 0.00 0 136 0 2

Misses in library cache during parse: 3

Misses in library cache during execute: 1

2 user SQL statements in session.

3 internal SQL statements in session.

5 SQL statements in session.

********************************************************************************

Trace file: NGENPR_ora_6661.trc

Trace file compatibility: 12.2.0.0

Sort options: prsela exeela fchela

1 session in tracefile.

2 user SQL statements in trace file.

3 internal SQL statements in trace file.

5 SQL statements in trace file.

5 unique SQL statements in trace file.

167 lines in trace file.

5 elapsed seconds in trace file.

6 awrsqlrpt

@?/rdbms/admin/awrsqlrpt

begin end snap

sql_id

六种方法的差异

1.如果结果出不来，只能用1

2.比较简单的方法是1或者2

3.观察多个执行计划只能用4和6

4.如果语句复杂，里面涉及到函数等，只能用5

5.真实的执行计划不能用1

6.想获取表被访问的次数，只能用3

到此，关于“oracle查询执行计划的方法有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注云搜网网站，小编会继续努力为大家带来更多实用的文章！