解读 Oracle 12c 自适应执行计划一例
从Oracle 12c R1版本开始,Oracle的查询优化器能在以下状况下使用自适应执行计划。
- 从Nested Loop循环切换到Hash Join连接,反之亦然。
- 为并行执行的SQL语句从散列向广播切换分配方法。
示例:
在样例Schema HR中执行下面的SQL语句:
SELECT
/*+ GATHER_PLAN_STATISTICS */
e.first_name,
e.last_name,
e.salary,
d.department_name
FROM employees e,
departments d
WHERE e.department_id = d.department_id
AND d.department_name IN ('Marketing','Sales');
SELECT
/*+ GATHER_PLAN_STATISTICS */
e.first_name,
e.last_name,
e.salary,
d.department_name
FROM employees e,
departments d
WHERE e.department_id = d.department_id
AND d.department_name IN ('Marketing','Sales');
然后使用
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));
查看执行计划,