FilesExpand file tree

 main

/

get_optimizer_trace.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

26 lines (22 loc) · 646 Bytes

 main

/

get_optimizer_trace.sql

Copy path

Top

File metadata and controls

• Code
• Blame

26 lines (22 loc) · 646 Bytes

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

--

-- given a sql_id and child number, generate an optimizer trace

--

set echo off feed off head off

undef sql_id

undef child_number

BEGIN

DBMS_SQLDIAG.DUMP_TRACE(p_sql_id => '&&sql_id',

p_child_number => &&child_number,

p_component => 'Compiler',

p_file_id => 'Optimizer_Trace'

);

END;

/

prompt -- Look for the following trace file on DB Server --;

select '*** '||i.host_name||':'||p.value||'/'||i.instance_name||'_ora_'||pr.spid||'_Optimizer_Trace'||'.trc ***'

from v$instance i

,v$parameter p

,v$process pr

where p.name ='user_dump_dest'

and pr.addr in (select paddr from v$session where sid = userenv('SID'))

;