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—————————————————————————| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————| 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—————————————————————————| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————| 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 gets896 consistent gets 0 physical reads 0 redo size889 bytes sent via SQL*Net to client408 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed3.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’));—————————————————————————————————————-| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |—————————————————————————————————————-| 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: dynami免费主机域名c sampling (level=2)4.dbms_xplan.display_cursor–知道sql_id可以立即得出真是的执行计划，且可以直接得出–没有相关的统计信息（逻辑读等）–无法判断执行了多少次–无法得出表被访问了多少次5qn0b7zft4s04select * 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—————————————————————————| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————| 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.txtTKPROF: Release 19.0.0.0.0 – Development on Wed Mar 11 10:25:48 2020Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Trace file: NGENPR_ora_6661.trcSort options: prsela exeela fchela********************************************************************************count = number of times OCI procedure was executedcpu = cpu time in seconds executingelapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = 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 STATEMENTScall count cpu elapsed disk query current rows——- —— ——– ———- ———- ———- ———- ———-Parse 2 0.00 0.00 0 138 0 0Execute 2 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 0 760 0 3——- —— ——– ———- ———- ———- ———- ———-total 6 0.00 0.00 0 898 0 3Misses in library cache during parse: 1Elapsed 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.24OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows——- —— ——– ———- ———- ———- ———- ———-Parse 3 0.00 0.00 0 0 0 0Execute 3 0.00 0.00 0 0 0 0Fetch 3 0.00 0.00 0 136 0 2——- —— ——– ———- ———- ———- ———- ———-total 9 0.00 0.00 0 136 0 2Misses in library cache during parse: 3Misses 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.trcTrace file compatibility: 12.2.0.0Sort 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/awrsqlrptbegin end snapsql_id六种方法的差异1.如果结果出不来，只能用12.比较简单的方法是1或者23.观察多个执行计划只能用4和64.如果语句复杂，里面涉及到函数等，只能用55.真实的执行计划不能用16.想获取表被访问的次数，只能用3到此，关于“oracle查询执行计划的方法有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注云技术网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: Kafka的核心实践是什么

这篇文章将为大家详细讲解有关Kafka的核心实践是什么，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。　　1.版本升级　　之前大数据平台一直使用的是0.8免费主机域名.3这一Kafka早期版本，而截止到当前，Ka…