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:
- Reading the current executing SQL statement text from errorstack trace
- Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace
- Reading the current bind variable values from errostack trace
- 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 !!! ;-)
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
@Martin Decker
Hi Martin,
Thanks, the typo is fixed now.
Hi Tanel!
Thanks for a very interesting read. I found another typo: x$klgob should be replaced with x$kglob.
Regards,
Chris
@Chris Neumueller
Hi Chris, thanks, typo fixed!
Hi Tanel, Do you have a RSS feed setup for the new website ??
@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!:)
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.
@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.
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.
@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.
@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
…
…
…
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…
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