Oracle性能调优-优化器的原理与执行计划
<h1>执行计划：</h1>
<ul>
<li>Oracle访问数据的方式，数据的处理流程，执行计划的好坏直接影响SQL语句执行的效率。</li>
<li>Oracle的版本越高，优化器的功能越强大，可以只能的选择最佳执行计划</li>
<li>无论优化器多么智能，在某些情况下开发人员能选择出必优化器选择的最优执行计划还要好的执行计划（使用hints???）
<h2>影响执行计划的因素</h2></li>
<li>新的Oracle版本：版本之间优化器评估的比重、考虑的问题不一样导致的</li>
<li>对象有新的统计信息：最直接影响优化的因素之一</li>
<li>初始化参数的改变</li>
<li>数据库的重组</li>
<li>模式的改变</li>
<li>SQL语句的改变</li>
<li>Hints的使用
<strong>读懂执行计划</strong></li>
<li>如何判断哪个表应该为驱动表（最右最上），好的驱动表，返回的行少为选择条件</li>
<li>将表中的数据读入内存的操作可能是并行的、交叉进行的，关键不是看读取的先后顺序，关键看表之间的连接顺序（判断哪个表为驱动表）、使用了何种类型的关联及具体的访问路径（如判断是否利用了索引）</li>
</ul>
<p>示例：
1.从上到下，往右缩进，最右缩进的n行代表了Oracle处理数据的时候先从那n行开始，逐层往外处理;
2.最右行的最上行的表是驱动表（一条sql语句执行的核心，从这个表慢慢往外扩），最右行下面的表就是被驱动表;
3.在执行计划中可以看到以什么样的方式去访问表，比如 TABLE ACCESS (FULL) OF 'B' 表示全表扫描，TABLE ACCESS (BY INDEX ROWID) OF 'A' 表示以索引访问
NESTED LOOPS 嵌套循环
SORT 排序
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=5704b6e1289b9594dbcd8532e9b7d624&amp;file=file.png" alt="" /></p>
<p><strong>表与表之间的3种连接方式</strong></p>
<ul>
<li>Nested loop join 嵌套循坏
Outer table 中的每一行与 inner table 中的相应记录 join,类似一个嵌套，比较高效的一种连接方式。
比对方式：
外部表（驱动表）中的每一行与内部表的所有行进行比对，外部表的行数决定了循环的次数，也决定了执行的效率，所以外部表返回的行越少，则效率越高。
那么一张表返回的行数比较少，另一张表返回的行数比较多的时候，选择嵌套循环是比较好的。</li>
<li>Sort Merge Join(SMJ) 排序合并
将两个表排序，然后再join,适用于大表与大表之间的数据处理。
特点：
比对方式与嵌套循环相似，但是是排序后进行比对，会减少比对次数，提高效率，但是排序会占用成本。
先排序，再比对。oracle自动判断排序后的比对的成本与原始表比对的成本做比较，会选择代价较低的连接方式。</li>
<li>Hash join
散列连接是CBO做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
比对方式：
（1）构建阶段：从两种表中选择一张表构建哈希表
（2）探测阶段：将外部表的每个元组与批处理中的内部元组进行比较，如果满足连接条件，则进行连接。
适用于两张表都是全表扫描并且返回的行数都比较多时效率比较高。</li>
</ul>
<h2>执行计划诊断工具</h2>
<h3>Explain plan 工具</h3>
<p>执行计划信息存放在PLAN_TABLE表，该语句不会正在执行，适合开发阶段使用。
1.创建执行计划：
SQL> Explain plan for
select last_name from hr.employees;
2.从plan_table表中查询执行计划：</p>
<ul>
<li>直接查询plan_table：select plan_table_output from table(DBMS_XPLAN.DISPLAY());</li>
<li>使用 @$ORACLE_HOME/rdbms/admin/utlxpls.sql脚本</li>
<li>使用 @$ORACLE_HOMR/rdbms/admin/utlxplp.sql脚本，显示并行查询信息。
<h3>SQL Trace和TKPRO</h3>
<p>跟踪数据库或者某个会话的SQL执行，产生跟踪文件，然后进行格式化输出。适用于诊断SQL语句执行时占用太多资源问题。
1.打开SQL跟踪：SQL_Trace=true
2.运行应用
3.关闭SQL跟踪：SQL_Trace=false
4.使用TKPROF格式化trace文件
5.输出展示</p>
<h4>开启和关闭SQL Trace</h4>
<p><strong>实例级别：</strong>SQL_TRACE={TRUE|FALSE} 所有的SQL都打开SQL跟踪，占用资源较多，不利于后续分析，不建议
<strong>会话级别：</strong></p></li>
<li>在当前会话中启用跟踪：
SQL>alter session set SQL_TRACE={TRUE|FALSE};
SQL>execute DBMS_SESSION.SET_SQL_TRACE ({TRUE|FALSE})</li>
<li>指定跟踪某个会话：
SQL>execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (session_id,serial_id,{true|false});
<h4>使用TKPROF格式化trace文件</h4>
<p>tkprof tracefile.trc output.txt [options]
产生的跟踪文件在告警日志所在的目录下，使用时最后先删除该目录下的文件（因为该目录下的文件比较多且大部分是没有用的，为了方便查看在开启trace之前可以先将该目录下的文件删除）。
<strong>TKPROF统计信息的几个重要指标</strong></p></li>
<li>Count:执行的次数</li>
<li>CPU：消耗的CPU时间</li>
<li>Elapse：总的耗时</li>
<li>Disk：物理读</li>
<li>Query：以一致性模式从缓存区中获得数据的次数</li>
<li>Current：以当前模式从缓存区获得数据的次数（DML操作）</li>
<li>Rows：处理的行数
>adrci #可直接进入adrci查看跟踪日志文件的路径
show log</li>
</ul>
<h4>SQL*Plus 自动跟踪</h4>
<p>1.创建和授予Plustrace角色权限
SQL>@$ORACLE_HOME/sqlplus/admin/plustrace.sql
SQL>grant plustrace to scott;
2.自动跟踪语法：
set autotrace [off | on | traceonly ]
[ explain | statistics ]
off：关闭跟踪，默认状态
on：打开跟踪，返回查询结果、执行计划、统计信息
Traceonly：只产生执行计划和统计信息，不显示查询结果，适合返回内容比较多
Explain：只显示执行计划
Statistics：只显示统计信息</p>
<h4>V$SQL_PLAN</h4>
<p>对于预先没有跟踪，没有预处理的，执行后想查看其执行计划，可以使用此工具。
1.获得已执行的SQL语句的ID:
SQL>select sql_id,sql_text from v$sql where sql_text like 'select * from employees where%';
2.根基sql_id获得该语句的执行计划：
SQL>select plan_table_output from table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id'))</p>