How to Find the Offending SQL from a ORA-600 or ORA-7445 Trace File (文档 ID 154170.1) 转到底部 In this

How to Find the Offending SQL from a ORA-600 or ORA-7445 Trace File (文档 ID 154170.1)

转到底部

---

In this Document Purpose   Troubleshooting Steps   Scope and Application   How to Find the Offending SQL from a Trace File   --- Step 1: Find the SQL   --- Step 2: Find values of the bind variables   Examples   References APPLIES TO: Oracle Database - Enterprise Edition - Version 8.1.7.0 and later Information in this document applies to any platform. ***Checked for relevance on 23-Jan-2014*** PURPOSE This article describes how to find the SQL statement that was active at the time an internal (ORA-600) error or ORA-7445 error was reported. TROUBLESHOOTING STEPS Scope and Application This note is intended as an aid to database administrators when diagnosing the cause of Oracle errors. How to Find the Offending SQL from a Trace File Extracting the SQL statement from a trace file is a two-step process. First you must find the SQL itself, then you must identify the values of any bind variables that the SQL may be referencing. The following text assumes that you have located the trace file associated with the reported error. --- Step 1: Find the SQL Locate the line "Current cursor:" (this should be right at the beginning of the "*** Cursor Dump ***" section). Note the number associated with the current cursor. (See the examples below for more details)  If the current cursor number is 0 then no current SQL is available. Under these conditions, the Call Stack Trace displayed near the top of the trace file gives the best indication of what was happening at the time of the error. Normally, Oracle Support Services will need to see this trace file in order to progress the issue.  If the Current cursor number is non-zero, search the remainder of the trace file for the line beginning with "Cursor n" where n is the cursor number just found. From 10.2 and onwards, search for "Cursor#n". On the next line you should see "cursor name:" which will be followed by the SQL statement.  Note: You will quite often be able to locate the SQL by searching for the line "Current SQL statement for this session:". Normally this is displayed near the beginning of the trace file.  If the SQL statement does not reference any bind variables (ie. ":b1", ":b2", etc.) then Step 2 can be ignored. --- Step 2: Find values of the bind variables If the SQL statement does reference bind variables, then following the SQL statement shown in the cursor, you should in most cases be able to locate a section of text associated with each bind variable. Each section of text will begin "bind x:" where x ranges from 0 to n-1 where n is the number of bind variables used in the SQL statement.  There may be a large memory dump between the SQL statement and the binds - it should just be ignored at this stage.  For each bind variable there are a number of attributes listed. The following are the ones we are interested in here: dty the datatype. Typical values are: 1 VARCHAR2 or NVARCHAR2 2 NUMBER 8 LONG 11 ROWID 12 DATE 23 RAW 24 LONG RAW 96 CHAR 112 CLOB or NCLOB 113 BLOB 114 BFILE mxl the maximum length - ie. the maximum number of bytes occupied by the variable. Eg. dty=2 and mxl=22 denotes a NUMBER(22) column. scl the scale (for NUMBER columns) pre the precision (for NUMBER columns) value the value of the bind variable Mapping the bind variables from the cursor dump with the bind variables in the SQL statement is straight-forward: :b1 ~ bind 0 :b2 ~ bind 1  :b3 ~ bind 2  etc. The binds can also be called :1, :2, etc. or :a1, :a2, etc. the important thing is that the *order* in which they are used in the SQL statement matches the order of the variable dumps. So for a statement like: SELECT * FROM MYTAB WHERE A = :1 AND B = :2 AND C = :3 bind variables should be mapped like: :1 ~ bind 0 :2 ~ bind 1 :3 ~ bind 2 Note that it is possible for the same bind variable to occur multiple times in the SQL statement, but that does not change anything for the mapping, eg. if you have a statement like: SELECT * FROM MYTAB WHERE A = :B1 OR B BETWEEN :B2 AND :B1 the mapping will be: :b1 ~ bind 0 :b2 ~ bind 1 :b1 ~ bind 2    <-- the value for bind 2 should be the same as for bind 0 You should now be able to find the datatype of the bind variable (including length, scale, and precision if applicable) and the value.In some (rare) cases, there is no "value=..." included in the dump, and then the method described in step 2 cannot be used to identify the values of the bind variables. Examples In the following we will work through some examples of how to extract the SQL statement from trace files.  IMPORTANT: Replacing bind variables with literals can result in the optimizer choosing a different query path and thus the problem may not reproduce! --- Example 1: You should now be able to find the datatype of the bind variable (including length, scale, and precision if applicable) and the value. The cursor dump starts with: ******************** Cursor Dump ************************  Current cursor: 2, pgadep: 1  Cursor Dump:  ---------------------------------------- so we are looking for cursor 2: ---------------------------------------- Cursor 2 (20139ad0): CURFETCH curiob: 2013bca4 curflg: 7 curpar: 20139ab0 curusr: 0 curses 587a250c cursor name: select text from view$ where rowid=:1 child pin: 50a5b650, child lock: 50a5a628, parent lock: 50a5a844 xscflg: 20141466, parent handle: 4f348490, xscfl2: 400 nxt: 2.0x0000006c nxt: 1.0x000001d8 Cursor frame allocation dump: frm: -------- Comment -------- Size Seg Off bhp size: 52/560 bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=16 offset=0 bfp=2013e9f4 bln=16 avl=16 flg=05 value=0000138C.0046.0004 The current SQL is: select text from view$ where rowid=:1 and the bind variable translates into: :1 ~ bind 0 - ROWID (dty=11), value = 0000138C.0046.0004 so we can eg. reconstruct the original SQL statement as: SQL> variable a1 varchar2(20) SQL> exec :a1 := '0000138C.0046.0004'; SQL> select text from view$ where rowid=:a1;   Note that we construct the statement using a SQL*Plus bind variable in order to prevent the optimizer from choosing a different plan (not that it would make any difference for this particular example). Example 2:  The cursor dump starts with: ******************** Cursor Dump ************************ Current cursor: 11, pgadep: 1 Cursor Dump: ---------------------------------------- ie. we should look for cursor 11: ---------------------------------------- Cursor 11 (202cb9f0): CURBOUND curiob: 202f8b04 curflg: dd curpar: 0 curusr: 0 curses 30047c7c cursor name: SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :b1 FOR UPDATE child pin: 0, child lock: 300dc9b4, parent lock: 301730b8 xscflg: 1151421, parent handle: 3025b4dc bind 0: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=01 No bind buffers allocated ---------------------------------------- The current SQL statement is then: SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :b1 FOR UPDATE The bind variable :b1 is of type VARCHAR2(32) (dty=1, mxl=32), but no value has been assigned to it at the time of the dump ("No bind buffers allocated"). Example 3: ******************** Cursor Dump ************************ Current cursor: 2, pgadep: 0 Cursor Dump: ---------------------------------------- ... ---------------------------------------- Cursor 2 (20140444): CURNULL curiob: 0 curflg: 44 curpar: 0 curusr: 0 curses 701dc94c ---------------------------------------- In this case there is no SQL being executed at the time of the dump. Example 4: ******************** Cursor Dump ************************ Current cursor: 1, pgadep: 0 pgactx: ccf361c0 ctxcbk: 0 ctxqbc: 0 ctxrws: 0 Cursor Dump: ---------------------------------------- Cursor 1 (400d9478): CURBOUND curiob: 400e43d8 curflg: 4c curpar: 0 curusr: 0 curses d5348f80 cursor name: BEGIN myparser.convert('/tmp','workflow000_2.log',2); END; child pin: d14a4d70, child lock: d1589968, parent lock: d14c64a0 xscflg: 100064, parent handle: d083f1c0, xscfl2: 4040408 nxt: 1.0x000000a8 Cursor frame allocation dump: frm: -------- Comment -------- Size Seg Off ---------------------------------------- No binds included, so the SQL statement is the PL/SQL block: BEGIN myparser.convert('/tmp','workflow000_2.log',2); END;