Set the event to trace the optimizer for a given SQL statement which is identified by an sqlid:
begin execute immediate q'{
alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:}' || dbms_sql_translator.sql_id(
'select * from user_objects'
) ||']''';
end;
/
BTW: the component name SQL_Optimizer (used in the alter session set events statement) is reported by oradebug doc components.
Execute the query:
select * from user_objects;
Turn off tracing:
alter session set events 'trace[rdbms.SQL_Optimizer.*] off';
Look at the trace file via the dynamic performance views:
select
line_number,
payload,
record_type,
component_name,
operation_name,
file_name,
function_name
from
v$diag_trace_file_contents
where
payload is not null and
-- lower(payload) like '%cost%' and
-- not regexp_like(payload, '^_\w* *= ') and
-- function_name = 'dbktWriteTimestampWCdbInfo'
trace_filename = (select regexp_replace(value, '.*[\/]', '') trace_file from v$diag_info where name = 'Default Trace File')
order by
line_number
;
Legend
The legend of an optimzer trace file is found in the tracefile itself:
select
trc.line_number ,
case trc.record_type
when 1 then 'reg'
when 2 then 'free'
when 3 then 'sect beg'
when 4 then 'dump beg'
when 5 then 'buck beg'
when 6 then 'sect end'
when 7 then 'dump end'
when 8 then 'buck end'
end rec_type,
-- trc.record_type "rt",
trc.section_id sect_id,
trc.component_name,
trc.section_name,
trc.file_name,
trc.function_name,
trc.payload,
trc.timestamp
from
v$diag_info inf join
v$diag_trace_file_contents trc on regexp_replace(inf.value, '.*[\\/]', '') = trc.trace_filename
where
inf.name = 'Default Trace File' and
trc.component_name = 'SQL_Costing' and
trc.section_name = 'current_sql_statement' and
trc.function_name = 'kkoTrcPrologue' and
trc.payload like '%Legend%';
The following abbreviations are used by optimizer trace (slightly changed and ordered by me):
AAT
Approximate Aggregate Transformation
AP
adaptive plans
Card
cardinality
CBQT
cost-based query transformation
CBY
connect by
CLUF
clustering factor
CNT
count(col) to count(*) transformation
CPUSPEED
CPU Speed
CVM
complex view merging
DB/K
average number of data blocks per key
DK
distinct keys
dmeth
Distribution method. 1: no partitioning required, 2: value partitioned, 4: right is random (round-robin), 128: left is random (round-robin), 8: broadcast right and partition left, 16: broadcast left and partition right, 32: partition left using partitioning of right, 64: partition right using partitioning of left, 256: run the join in serial, 0: invalid distribution method