But for very long lines there is a problem, as PAYLOAD is limited to VARCHAR2(4000)
PAYLOAD | VARCHAR2(4000) |
Displays the trace record payload (contents)
|
Oracle tries to do it's best and presents the one-line in multiple rows. In my 19.7 sandbox it looks like:
This excerpt was created by a SQL which shows exactly the problem:
SELECT parsing_line,
line_number,
payload
FROM
v$diag_trace_file_contents MATCH_RECOGNIZE (
PARTITION BY trace_filename
ORDER BY line_number, timestamp
MEASURES
parse.line_number AS parsing_line
ALL ROWS PER MATCH
PATTERN ( parse lines_ + endline nextline ) DEFINE
parse AS ( payload LIKE 'PARSING IN CURSOR%' ),
lines_ AS ( line_number = prev(line_number) ),
endline AS ( line_number = prev(line_number) + 1 ),
nextline AS ( line_number = prev(line_number) + 1 )
)
WHERE trace_filename = '&trace_file'
/
Here you see the beauty of MATCH_RECOGNIZE: it describes quite clear, what I want to show.
The pattern shown
- Starts with a row payload LIKE 'PARSING IN CURSOR%'
- show all row where it's line_number is equal to previous rows line_number
- the 2 lines afterwards which increases their line_number (in comparison to previous row) again
The wring line number can be seen easily: All the rows with the SQLs PAYLOAD have LINE_NUMBER 57. But in fact 57 is only
PARSING IN CURSOR #140574625525808 len=20604 dep=1 uid=0 oct=3 lid=0 tim=529427750 hv=1387884324 ad='63388040' sqlid='9ygdp6j9bkvt4'
Only the last SQLs fragment is shown as LINE_NUMBER 58.
An additional problem is the missing capability of order these multiple rows properly.
The ORDER BY line_number, timestamp is not sufficient, as all the lines 57 and 58 share the same timestamp. The fixed view x$DBGTFVIEW has a column INDX which is a good candidate for such purpose, but it's not available right now.
I have created a Database Idea for this issue:
extend v$diag_trace_file_contents so multiple chunks of a line can be ordered
extend v$diag_trace_file_contents so multiple chunks of a line can be ordered
For those who read my previous post: if the .trm file is missing, v$diag_trace_file_contents provides one row per line and the payload is truncated to 4000 characters.
Update 2020-06-23: Oracle created Bug 31528203 - V$DIAG_TRACE_FILE_CONTENTS SHOWS WRONG LINE_NUMBER WHEN PAYLOAD > 4000 CHAR for this issue.
"C:\Program Files (x86)\Windows Kits\10\bin\10.0.18362.0\x64\signtool.exe" verify /v orapwdfltr.dll
Verifying: orapwdfltr.dll
Signature Index: 0 (Primary Signature)
Hash of file (sha256): 2A14712107D424FF5577EF5C3D111CF66DB40F6226047ADC4F31389D69F437EB
Signing Certificate Chain:
Issued to: VeriSign Class 3 Public Primary Certification Authority - G5
Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
Expires: Thu Jul 17 01:59:59 2036
SHA1 hash: 4EB6D578499B1CCF5F581EAD56BE3D9B6744A5E5
Issued to: Symantec Class 3 Extended Validation Code Signing CA - G2
Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
Expires: Mon Mar 04 01:59:59 2024
SHA1 hash: 5B8F88C80A73D35F76CD412A9E74E916594DFA67
Issued to: Oracle America Inc.
Issued by: Symantec Class 3 Extended Validation Code Signing CA - G2
Expires: Thu Jan 28 01:59:59 2021
SHA1 hash: 1CB08E9B70B917E64407A4F2665799D58B171F89
The signature is timestamped: Thu Apr 23 03:33:05 2020
Timestamp Verified by:
Issued to: DigiCert Assured ID Root CA
Issued by: DigiCert Assured ID Root CA
Expires: Mon Nov 10 02:00:00 2031
SHA1 hash: 0563B8630D62D75ABBC8AB1E4BDFB5A899B24D43
Issued to: DigiCert SHA2 Assured ID Timestamping CA
Issued by: DigiCert Assured ID Root CA
Expires: Tue Jan 07 14:00:00 2031
SHA1 hash: 3BA63A6E4841355772DEBEF9CDCF4D5AF353A297
Issued to: TIMESTAMP-SHA256-2019-10-15
Issued by: DigiCert SHA2 Assured ID Timestamping CA
Expires: Thu Oct 17 02:00:00 2030
SHA1 hash: 0325BD505EDA96302DC22F4FA01E4C28BE2834C5
SignTool Error: A certificate chain processed, but terminated in a root
certificate which is not trusted by the trust provider.
Number of files successfully Verified: 0
Number of warnings: 0
Number of errors: 1
