Oracle Troubleshooting: How to read Oracle ERRORSTACK output?!

I have written the first article to the troubleshooting section of my new website tech.E2SN.com:

It’s about a very valuable Oracle troubleshooting tool -> ERRORSTACK trace.

I cover 4 frequently asked questions there:

  1. Reading the current executing SQL statement text from errorstack trace
  2. Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace
  3. Reading the current bind variable values from errostack trace
  4. Identifying how much private (UGA) memory a cursor is using

You can read it here:

By the way, if you like my new website, feel free to link to it !!! ;-)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Cool stuff, Oracle and tagged , , , . Bookmark the permalink.

14 Responses to Oracle Troubleshooting: How to read Oracle ERRORSTACK output?!

  1. Hi Tanel,

    great article. Just a small typo: it´s v$sql_bind_capture instead of v$sql_plan_capture.

    Looking forward to reading more great stuff on e2sn…

    Regards,
    Martin

  2. Tanel Poder says:

    @Martin Decker
    Hi Martin,

    Thanks, the typo is fixed now.

  3. Chris Neumueller says:

    Hi Tanel!

    Thanks for a very interesting read. I found another typo: x$klgob should be replaced with x$kglob.

    Regards,
    Chris

  4. Maxx says:

    Hi Tanel, Do you have a RSS feed setup for the new website ??

  5. Tanel Poder says:

    @Maxx
    Hi Maxx,

    I’m still working on the best solution, I need to test out few things before I can publish this. Basically people need to join the site with their google account… stay tuned!:)

  6. Giridhar Kodakalla says:

    Are there any occassions where the “Current SQL Statement for this Session” in the trace file will be different than whatever is being found from the PL/SQL code (if the sql is from a pl/sql program) which you demonstrated in “Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace” by querying DBA_SOURCE?

    If yes, would you please let us know.

    Thanks,
    Giridhar Kodakalla.

  7. Tanel Poder says:

    @Giridhar Kodakalla
    Well, if the SQL you fire from your PL/SQL causes some other SQL to fire recursively (triggers, auditing, recursive data dictionary queries) then you may see other SQL pop up there if you take the errorstack dump when the recursive SQL happens to be executing.

  8. Dave Hays says:

    Tanel,
    I am a great fan of your posts, this is the first time I’ve communicated with you directly – I have what a simple question -I am trying to reproduce the
    examples in

    (http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output)

    in which you are able to obtain bind information using oradebug dump errorstack 3

    I am following the progression in your article, but I’m unable to reproduce the same when I try it at home. What I always see instead of “value=” in my trace dumps is:

    No bind buffers allocated

    Can you shed some light on this ? I’m not expecting that I need to tinker with the hidden parameter _optim_peek_user_binds (believe thats the name).

    I thank you greatly in advance for any feedback you can provide –

    -dave
    Dave Hays
    Caterpillar, Inc.

  9. Tanel Poder says:

    @Dave Hays
    Can you post the full testcase here, e.g. which SQL are you running and when exactly are you running the dump command.

  10. Dave Hays says:

    @Tanel Poder
    sure –

    I have a table called BINDTEST :
    SQL> desc bindtest;
    Name Null? Type
    —————————————– ——– —————————-
    V1 NUMBER

    SQL> select * from bindtest;

    V1
    ———-
    10

    SQL>

    The simplest test I could think of was an anonymous block of PL/SQL with the following delete statement:

    script name delete.sql:

    variable B1 number;
    exec :B1 := 10;

    BEGIN
    delete bindtest where v1 = :B1;
    end;
    /

    And here’s the test:
    1)
    I setospid to my current session:
    Terminal 1:
    SQL> !ps -ef | grep 32191
    haysdj 467 32191 0 14:43 pts/3 00:00:00 grep 32191
    haysdj 32191 21417 0 14:40 pts/3 00:00:00 sqlplus
    oracle 32220 32191 0 14:40 ? 00:00:00 oraclez1tr (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

    Terminal 2:
    SQL> oradebug setospid 32220
    Oracle pid: 27, Unix process pid: 32220, image: oracle@arloraft01.corp.cat.com ( TNS V1-V3)
    SQL>

    2)
    I run my simple delete script:

    Terminal1:

    SQL> @delete

    PL/SQL procedure successfully completed.

    PL/SQL procedure successfully completed.

    SQL>

    3)
    I dump errorstack (level 3)
    Terminal 2
    SQL> oradebug dump errorstack 3
    Statement processed.
    SQL>

    My trace output is as follows:

    Unix process pid: 32220, image: oracle@arloraft01.corp.cat.com (TNS V1-V3)

    *** 2011-05-20 14:44:25.821
    *** ACTION NAME:() 2011-05-20 14:44:25.810
    *** MODULE NAME:(SQL*Plus) 2011-05-20 14:44:25.810
    *** SERVICE NAME:(SYS$USERS) 2011-05-20 14:44:25.810
    *** SESSION ID:(305.915) 2011-05-20 14:44:25.810
    Received ORADEBUG command ‘dump errorstack 3′ from process Unix process pid: 364, image:
    *** 2011-05-20 14:44:25.821
    ksedmp: internal or fatal error
    Current SQL statement for this session:
    BEGIN
    delete bindtest where v1 = :B1;
    end;




    Cursor#1(0x2b9b42321768) state=BOUND curiob=0x2b9b4233ce40
    curflg=cd fl2=0 par=(nil) ses=0x8f64ed70
    sqltxt(0x883fe0f0)=DELETE BINDTEST WHERE V1 = :B1
    hash=564318d4cda9b6ad1a4c07f3319b5fd0
    parent=0x8779afc8 maxchild=01 plk=0x8c35b8e8 ppn=n
    cursor instantiation=0x2b9b4233ce40 used=1305920648
    child#0(0x883fdec8) pcs=0x8779abd8
    clk=0x8c2d9c90 ci=0x83f9d2e0 pn=(nil) ctx=0x840ba8a8
    kgsccflg=0 llk[0x2b9b4233ce48,0x2b9b4233ce48] idx=0
    xscflg=80110436 fl2=15000000 fl3=208218c fl4=0
    Bind bytecodes
    Opcode = 6 Bind Rpi Scalar Sql In(may be out) Nocopy NoSkip
    Offsi = 48, Offsi = 0
    kkscoacd
    Bind#0
    oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
    oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
    No bind buffers allocated



  11. Tanel Poder says:

    This is likely because you’re not running the query anymore. Run something which takes longer time so you could run the oradebug command when the query is still running.

    What I use for demos is:

    SELECT COUNT(*) FROM some_table a, sometable b, sometable c WHERE a.some_col != :bind1 and b.some_col != :bind2

    Btw, you don’t need PL/SQL anonymous block to use bind variables…

  12. Dave Hays says:

    OH my goodness, I totally missed the point :)
    also, correct, I think I took my test as far as to begin using the pl/sql block just in attempt to obtain the desired result.
    Tanel, thank you very much!

    -dave

  13. Pingback: Where is the sql_id of active session « Coskan’s Approach to Oracle

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>