Oracle ErrorStack 使用和阅读具体解释
打开相应的trace后。你会发现这类trace文件一般都是以“ksedmp:internal or fatal error"开头,"kesdmp"意味着Kernel Service
Error Dump,这一行以下的内容就是errorstack记录的错误堆栈!
Errorstack对于诊断一个session似乎Hang住(可是在v$session_wait里面并未出现合理的wait event)或者是比正常时消耗很多其它资源时。获取当前session运行sql、详细的变量值等等信息,从而帮助你找到问题根源!
- 0 仅转储错误堆栈
- 1 转储错误堆栈和函数调用堆栈
- 2 Level 1 + ProcessState
- 3 Level 2 + Context area (一般我们诊断问题,都是使用这个级别的跟踪。)
- 实例级别:alter system set events='984 trace name errorstack forever,level 3' scope=spfile;
- 会话级别: alter session set events='984 trace name errorstack forever,level 3';
- oradebug: 1、oradebug
setospid xxxx; 2、oradebug dump errorstack 3 --当前session正在执行的语句
- 从Errorstack跟踪文件中发现当前正在运行SQL文本。
- 从Errorstack跟踪文件中发现当前正在运行PL/SQL包和PL/SQL source code line number。
- 从Errorstack跟踪文件中发现当前bind variable value。
- 从Errorstack跟踪文件中发现一个cursor正在使用多少private memory(UGA)。
这一部分的内容主要參考tanelpoder大牛的博客。
Oracle .... - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME ..
System name: Linux
Node name: 192oracle.cn100.com
Release: ...el6.x86_64
SMP Fri Feb 00:: UTC
Machine: x86_64
Instance name: cn100
Redo thread mounted
Oracle process
Unix process pid: , image: oracle@192oracle.cn100.com (TNS V1-V3)
::.
.) ::.
::.
::.
::.
::.
dbkedDefDump(): Starting a non,
mask=0x0)
----- Error Stack Dump -----
ORA: value larger than specified precision allowed for this column
----- Current SQL Statement for this session (sql_id=b8n03s73k7d39) -----
--能够看到,当前SQL就在这一行以下
INSERT INTO DH_T VALUES (:B2 ,:B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x1075fcd10 procedure DBMON.P_DH1
0xfcfaebe8 procedure DBMON.P_DH2
0x10e7d6420 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst() call kgdsdst() 000000000 ? 000000000 ?
?
7FFF332CCFD8 ? 000000000 ?
......为了排版,兴许省略......
Errorstack跟踪文件和前面一样,例如以下
Oracle .... - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME ..
System name: Linux
Node name: 192oracle.cn100.com
Release: ...el6.x86_64
SMP Fri Feb 00:: UTC
Machine: x86_64
Instance name: cn100
Redo thread mounted
Oracle process
Unix process pid: , image: oracle@192oracle.cn100.com (TNS V1-V3)
::.
.) ::.
::.
::.
::.
::.
dbkedDefDump(): Starting a non,
mask=0x0)
----- Error Stack Dump -----
ORA: value larger than specified precision allowed for this column
----- Current SQL Statement for this session (sql_id=b8n03s73k7d39) -----
--能够看到,当前SQL就在这一行以下
INSERT INTO DH_T VALUES (:B2 ,:B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x1075fcd10 6 procedure DBMON.P_DH1
0xfcfaebe8 7 procedure DBMON.P_DH2
0x10e7d6420 1 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (?
means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst() call kgdsdst() 000000000 ?
000000000 ?
?
7FFF332CCFD8 ? 000000000 ?
......为了排版,兴许省略......
这部分告诉你发生错误时Oracle在运行详细哪个PL/SQL过程(包or函数)以及errorstack跟踪过程中的详细哪个调用发生错误。这对我们诊断问题很有帮助
- object handle
- line number
- object name
procedure DBMON.P_DH2
anonymous
block
- 一个会话可能以某种方式变的很消耗CPU,而且会话的wait等待时间没有不论什么意义.
- 你须要调查什么SQL正在被运行,而且你须要查看SQL带有的绑定变量
- SQL的运行计划是正常的,可是性能却很低下
- 能够如果当某些表或者行源变的大的时候,存在数据倾斜。CBO没有计算出正确的运行计划。
errorstack跟踪文件里中包括CURSORDUMP,也就包括我们想要得到的bind variable value。
Oracle .... - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME ..
System name: Linux
Node name: 192oracle.cn100.com
Release: ...el6.x86_64
SMP Fri Feb 00:: UTC
Machine: x86_64
Instance name: cn100
Redo thread mounted
Oracle process
Unix process pid: , image: oracle@192oracle.cn100.com (TNS V1-V3)
::.
.) ::.
::.
::.
::.
::.
dbkedDefDump(): Starting a non,
mask=0x0)
----- Error Stack Dump -----
ORA: value larger than specified precision allowed for this column
----- Current SQL Statement for this session (sql_id=b8n03s73k7d39) -----
--能够看到,当前SQL就在这一行以下
INSERT INTO DH_T VALUES (:B2 ,:B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x1075fcd10 6 procedure DBMON.P_DH1
0xfcfaebe8 7 procedure DBMON.P_DH2
0x10e7d6420 1 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (?
means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst() call kgdsdst() 000000000 ? 000000000 ?
?
7FFF332CCFD8 ? 000000000 ?
......为了排版,兴许省略......
, pgadep
(, , , )
SYNTAX PARSE BOUND
Cached frame pages(total, free):
, ), , ), , ), , )
----- Current Cursor -----
xsc=0x7f5227898580 ctx=0xf92d7aa8 pgactx=0xf92d7aa8 ctxcbk=0xf92d74f8 ctxqbc=(nil)
ctxrws=0x10293a4c0
----- Explain Plan Dump -----
----- Compact Format (Stream) -----
Dumping stream
----------------------------------
0000:
......i.............
: ..
----- Plan Table -----
============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time
|
--------------------------------------------+-----------------------------------+
| | | |
| | LOAD TABLE CONVENTIONAL | | | | | |
--------------------------------------------+-----------------------------------+
Content of other_xml column
===========================
db_version : ...
parse_schema : DBMON
plan_hash :
plan_hash_2 :
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted ..
parallel_execution_enabled = true
parallel_query_forced_dop
- Current cursor: 2,这个告诉我们在这个会话的UGA打开的cursor中,当前正在运行Cursor# 6 游标
- the pgadep:1变量告诉我们PGA depth。也就是这个查询运行的递归的深度
curflg par=(nil) ses=0x129a8edc0
----- Dump Cursor sql_id=b8n03s73k7d39
xsc=0x7f5227898580 cur=0x7f5227951aa0 -----
LibraryHandle: Address LoadLockMode Status=VALD
ObjectName: Name=INSERT INTO DH_T VALUES (:B2 ,:B1 )
FullHashValue=38f6bac85b76f427b45003c1c723b469 Namespace=SQL AREA(00) Type=CURSOR(00)
Identifier OwnerIdn
Statistics: InvalidationCount ExecutionCount LoadCount ActiveLocks TotalLockCount TotalPinCount
Counters: BrokenCount RevocablePointer KeepDependency KeepHandle BucketInUse HandleInUse
Concurrency: DependencyMutex, , , ) Mutex, , , )
Flags]
WaitersLists:
Lock=f68ce968[f68ce968,f68ce968]
Pin=f68ce978[f68ce948,f68ce948]
::
LibraryObject: Address Flags=EXS[0000]
Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=f6b88bc8 Reference=f6b88668 Handle=fab7b4c0
Children:
Child: childNum='0'
LibraryHandle: Address LockMode Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount ExecutionCount LoadCount ActiveLocks TotalLockCount TotalPinCount
Counters: BrokenCount RevocablePointer KeepDependency KeepHandle BucketInUse HandleInUse
Concurrency: DependencyMutex, , , ) Mutex, , , )
Flags]
WaitersLists:
Lock=fab7b550[fab7b550,fab7b550]
Pin=fab7b560[fab7b530,fab7b530]
LibraryObject: Address Flags=EXS[0000]
Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: # Change=NONE
Heap=f6a0b38 Pointer=f6569c08 Extent=f6569aa0 Flags=I/-/P/A/-/-
FreedLocation Alloc. . LoadTime
Block: # Change=NONE
Heap=f6b88438 Pointer=f92d7aa8 Extent=f92d6e48 Flags=I/-/P/A/-/E
FreedLocation Alloc. . LoadTime
NamespaceDump:
Child :: Heap6 ::
NamespaceDump:
Parent plk=y
ppn cld hd=0xfab7b4c0
par=0xf6b87e00
Mutex 0xf6b88308(, ) idn
ct hsh unp hvl=f6b88c60
nhv ses=(nil)
hep ld ob=0xf6569b20 ptr=0xf92d7aa8
fex=0xf92d6e48
exec_id
child#(0xfab7b4c0) pcs=0xf6b88308
clk=0x10d6111e0 ci=0xf6569c08 pn=0xfdf4c890 ctx=0xf92d7aa8
kgsccflg llk[0x7f5227898588,0x7f5227898588] idx=6a
xscflg
----- Bind Byte Code (IN) -----
Opcode Bind Rpi Scalar Sql In(may be out)
Nocopy NoSkip
Offsi , Offsi
Opcode Bind Rpi Scalar Sql In(may be out)
Nocopy NoSkip
Offsi , Offsi
----- Bind Info (kkscoacd) -----
Bind#0
oacdty mxl() mxlc=00 mal=00
scl=00 pre=00
oacflg fl2 frm
kxsbbbfp avl flg
value=1234335
Bind#1
oacdty mxl() mxlc=00 mal=00
scl=00 pre=00
oacflg fl2 frm csi siz
kxsbbbfp avl flg
value="oradh"
Frames pfr 0x7f5227897c18 siz efr 0x7f5227897b38 siz
Cursor frame dump
enxt: .0x00000550 enxt: .0x00000040 enxt: .0x000007a0
pnxt: .0x00000030
kxscphp inu nps
kxscbhp inu nps
kxscwhp inu nps
- 在errorstack跟踪文件里发现Cursor#2,它的state=BOUND。意味着绑定变量值已经被赋予的
- SQL文本是我们在前面已经查找出来的。我们能够看到两个绑定变量值
- Bind variale numbering是从0開始。所以假设我们想搜索第一个绑定值。须要搜索Bind#0
例如以下
Cursor frame dump
enxt: .0x00000550 enxt: .0x00000040 enxt: .0x000007a0
pnxt: .0x00000030
kxscphp inu nps
kxscbhp inu nps
kxscwhp inu nps
Cursor#1游标部分:
Cursor frame dump
enxt: .0x000005c0 enxt: .0x00000fd8 enxt: .0x00000fa0 enxt: .0x00000658
enxt: .0x00000228 enxt: .0x00000fd8 enxt: .0x00000fa0 enxt: .0x00000248
enxt: .0x00000fa0 enxt: .0x00000410 enxt: .0x00000480 enxt: .0x00000f70
pnxt: .0x00000030
kxscphp inu nps
kxscbhp inu nps
create or replace procedure p_dh1 as
v_id ;
v_name varchar2() :='oradh';
begin
--just for errorstack test
insert into dh_t values (v_id,v_name);
commit;
end;
/
create or replace procedure p_dh2 as
v_cnt number;
begin
----just for errorstack test
select count(*) into v_cnt from dh_t;
dbms_output.put_line('the dh_t count is '||v_cnt);
p_dh1;
end;
/
Jul ::
Copyright (, , Oracle. All rights reserved.
Connected to:
Oracle .... - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
pagesize
SQL> col tracefile format a100
));
SPID TRACEFILE
---------------
--------------------------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc
SQL> alter session set events='1438
trace name errorstack forever,level 3';
Session altered.
SQL> exec p_dh2;
BEGIN p_dh2; END;
*
ERROR :
ORA: value larger than specified precision allowed for this column
ORA:
ORA:
ORA:
[oracle@192oracle ~]$ ls -ltr /u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc
oracle
oinstall Jul : /u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc
最新文章
- jsp项目部署
- 在VBA中使用Windows API
- 【javascript 面试笔试】1、几道笔试题
- Oracle中的数据分页
- MFCC可视化
- 2016-07-15: Window定时器使用
- CSS Hack(转)
- Jquery EasyUI的添加,修改,删除,查询等基本操作介绍
- C#连接数据库的四种方法(转)
- Java加密技术
- Leetcode 073 Set Matrix Zeroes
- animation-timing-function的steps详解
- [Mysql]备份同库中一张表的历史记录 insert into ..select
- Web应用增加struts2支持
- 【译】第十一篇 SQL Server安全审核
- RPC与实践(thrift在windows的编译/安装--c++版)
- 《重回耶路撒冷——犹太人的三千年》(Return to Jerusalem)读后感
- cto职责
- 初探VBScript
- android安全检测工具,梆梆安全 - 防止反编译|APP安全加固|应用加固|盗版监测
热门文章
- C# Soap调WebService
- HDU 4421 Bit Magic(2-sat)
- Java实现XSS防御
- 【HDU-5246】超级赛亚ACMer(贪心)
- eslint — js书写规范
- layui动态渲染生成select的option值
- python 分支结构
- The Super Powers UVA 11752 分析分析 求无符号长整形以内的数满足至少可以用两种不同的次方来表示。比如64 = 2^6 = 8^2; 一个数的1次方不算数。
- ptmalloc、tcmalloc和jemalloc
- POJ2947 DAZE [Gauss]