-
Notifications
You must be signed in to change notification settings - Fork 4
/
bug.sql
70 lines (51 loc) · 2.92 KB
/
bug.sql
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- TRACEFILE_IDENTIFIER behavior SR 3-32741539641
/*
Setting TRACEFILE_IDENTIFIER leaves information out of the trace file
When you set TRACEFILE_IDENTIFIER, the Oracle kernel process closes the
existing trace file and opens a new one. The new file doesn't contain all of
the information that it should. Specifically, it will not contain some
"PARSING IN CURSOR" sections that have been written to the first of the two
trace files. Impact is that, when we look at the trace file whose name has
the new tracefile identifier in it, we can't determine the SQL or PL/SQL
associated with all the cursors reported on in the trace. The workaround is
to simply never use TRACEFILE_IDENTIFIER. This has been a problem since at
least Oracle 10g, and it's still reproducible in 23c FREE.
*/
connect system/oracle
alter session set max_dump_file_size=unlimited;
exec dbms_monitor.session_trace_enable
select value trace0 from v$diag_info where name = 'Default Trace File';
select 1;
alter session set tracefile_identifier='1';
select value trace1 from v$diag_info where name = 'Default Trace File';
select 1; -- Must be the same statement as above.
alter session set tracefile_identifier='2';
select value trace2 from v$diag_info where name = 'Default Trace File';
select 1; -- Must be the same statement as above.
exec dbms_monitor.session_trace_disable
/*
Now, look in the two trace files.
In my (cvm) tests, both TRACE1 and TRACE2 contained a 'PARSING IN CURSOR'
section for the 'select 1' cursor. But run the test again. This time, the
behavior is different:
- In TRACE1, you'll see a 'PARSING IN CURSOR' section for the 'select 1' cursor.
- In TRACE2, there is no 'PARSING IN CURSOR' section for the cursor.
It appears that the DBMS is checking a state variable holding the information,
"Have I emitted a 'PARSING IN CURSOR' section for this cursor?" The real
information needs to be "Have I emitted a 'PARSING IN CURSOR' section for this
cursor SINCE OPENING THE CURRENT TRACE FILE?"
The impact of this behavior is:
1. People like the TRACEFILE_IDENTIFIER feature because it makes it easier to
find their trace file in the trace directory. But the trace file created after
a TRACEFILE_IDENTIFIER change is deficient in that it does not contain the SQL
or PL/SQL associated with all the dbcalls in the trace.
3. The easiest workaround is to simply never use TRACEFILE_IDENTIFIER. That is
my recommendation to people. It seems like a nice-to-have feature, but it's so
easy these days to find the name of your trace file in V$DIAG_INFO that we
recommend using that instead of TRACEFILE_IDENTIFIER.
4. A harder workaround (NOBODY will want to do this [or even know how]) is to
always save both the "before" (TRACE1 in this example) and "after" (TRACE2 in
this example) files, and then do sophisticated trace file surgery in an
editor (being extremely careful with the tim values!), to provide the "PARSING
IN CURSOR" information to any user of the "after" trace file.
*/