HOW TO trace the CBO working out the execution path (event 10053)

Goal
To trace the CBO working out the execution path (event 10053)

Facts


Solution
To start the CBO trace enter the following command:

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

Run the SQL that you wanto trace the CBO optimizer on, e.g.

SELECT *
FROM oe_order_headers_v
WHERE header_id = 999
/

When the query has completed, run the following command to switch the trace off:

ALTER SESSION SET EVENTS '10053 trace name context off';

There appear to 2 levels to the trace:

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

Level 2 is a subset of Level 1 and includes:
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)

but Level 1 is the more detailed of the two; as well as ALL of level 2, it also includes:
Parameters used by the optimizer
Index statistics
标签: 暂无标签
oraunix

写了 199 篇文章,拥有财富 1026,被 339 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

成为第一个吐槽的人

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈