|
|
看了几篇metalink的文章总算有点收获了
在我上传的trace file中开头是这样一段
*** 2007-03-04 09:10:49.060
*** SESSION ID 86.57476) 2007-03-04 09:10:48.920
LIBRARY OBJECT HANDLE: handle=2ecfcca8
name=INSERT INTO T_ETYLOGFIELD (LOGID, NAME, LOGVALUE, PREVALUE, CHANGETIME) VALUES (:B5, :B4, :B3, :B2, :B1)
[/COLOR]
hash=34912c92 timestamp=02-09-2007 15:24:33
namespace=CRSR flags=RON/KGHP/TIM/OBS/PN0/MED/[50410000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=S latch#=1
lwt=2ECFCCC0[2ECFCCC0,2ECFCCC0] ltm=2ECFCCC8[2ECFCCC8,2ECFCCC8]
pwt=2ECFCCD8[2ECFCCD8,2ECFCCD8] ptm=2ECFCD30[2ECFCD30,2ECFCD30]
ref=2ECFCCB0[2ECFCCB0, 2ECFCCB0] lnd=2ECFCD3C[2ECC00A0,2ECFD12C]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
2d3aec38 2bbd95d8 2bbd95d8 1 N [00]
3b8eec70 2bc4f558 2bc4f558 1 N [00]
2d2479e4 2bbeb0f8 2bbeb0f8 1 N [00]
2d5ab4f0 2bc05038 2bc05038 1 N [00]
2d39ddfc 2bbf3518 2bbf3518 1 N [00]
3b8a2120 2bc1ef78 2bc1ef78 1 N [00]
2d2be10c 2bc4b348 2bc4b348 1 N [00]
2d2d0b50 2bc35618 2bc35618 1 N [00]
3b8eeb98 2bc132b8 2bc132b8 1 N [00]
2d38b6c4 2bc3b478 2bc3b478 1 N [00]
2d5cf910 2bc52488 2bc52488 1 N [00]
2cfab29c 2bc13c28 2bc13c28 1 N [00]
2d4ba2a4 2bc0a528 2bc0a528 1 N [00]
2d46773c 2bbfe868 2bbfe868 1 N [00]
2d3d9550 2bc12948 2bc12948 1 N [00]
2d536750 2bc2a2c8 2bc2a2c8 1 N [00]
3b8fe498 2bc41c48 2bc41c48 1 N [00]
3b8eeac0 2bc2e4d8 2bc2e4d8 1 N [00]
36ef61f8 2bc3fff8 2bc3fff8 1 N [00]
3b8fde68 2bbf8098 2bbf8098 1 N PNC/[04]
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
3b8c71f8 2bbf8098 2bbf8098 3b8fde68 1 S 0001
LIBRARY OBJECT: object=2ec6f18c
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=32768
[/COLOR]
注意红色的部分 可以注意到
2ec6f18c这个 library object 的children object
已经达到32768 超过了最大的 327767
这样就导致了ora-00600的产生 而这个object 应该就是上面那个insert sql
结合metalink上的2篇文章
估计原因就是在同一个sql中采用了绑定变量 但是由于绑定变量定义的最大长度不同 造成了oralce 用原来的sql 的 bind cache 去存储新的绑定变量时无法存储 于是将新的sql进行了硬解析 这样造成了每个相同的sql都存在很多个相同版本在library cache中 仅仅因为他们绑定的变量长度定义的不同 当达到上限32767时就会报ora-00600
联系metalink 上的这2篇文章
Subject: Many Child Cursors create in 10.2.0.3 on Windows and Linux
Doc ID: Note:403616.1 Type: ALERT
Last Revision Date: 26-FEB-2007 Status: PUBLISHED
In this Document
Description
Likelihood of Occurrence
Possible Symptoms
Workaround or Resolution
Patches
Modification History
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3 to 10.2.0.3
Microsoft Windows 2000
Microsoft Windows Server 2003
Linux x86
This problem is introduced by the Oracle Server 10.2.0.3 Patch Set on the 32bit Windows and 32bit Linux platforms only.
The problem also occurs on Windows 10.2.0.2 patch bundle #6, or in any 10.2.0.2 release where a one-off fix for Bug 4752541 has been applied.
Description
Many child cursors may be created for SQL statements using bind variables which have different maximum bind lengths when the same SQL was shared in previous releases. This can lead to shared pool concurrency problems such as latch contention, memory problems such as ORA-4031 or internal errors such as ORA-600 [17059].
Likelihood of Occurrence
In order to encounter this problem the database must:
* Be running 10.2.0.3 Oracle 32bit on Linux or Windows platforms
(or running an earlier release with the fix for Bug 4752541 installed)
* Have SQL issued against it which uses bind variables
Bug 4752541 installed)
* Have SQL issued against it which uses bind variables
* The bind variables can have different maximum lengths specified by the client/s
This is true of many applications and so the chances of hitting this problem are quite high in OLTP type environments. Warehouse environments which use mainly literal SQL are less likely to be impacted. The problem is most obvious for SQL statements using a large number of binds whose lengths can differ.
Possible Symptoms
Symptoms of this problem include:
* High VERSION_COUNT in V$SQL for cursors using bind variables
* V$SQL_SHARED_CURSOR shows that the reason for the mismatch is "BIND_MISMATCH"
(ie: The BIND_MISMATCH column shows as "Y" for the child cursors)
For statements with many binds the lack of sharing can result in problems such as:
* Shared pool / library cache latch contention
* ORA-4031 errors
* In extreme cases ORA-600 [17059] may occur
As an example consider the following SQL / PLSQL code:
create table tst (
a varchar2(10),
b varchar2(10),
c varchar2(10),
d varchar2(10)
);
alter system flush shared_pool;
declare
a0 varchar2(10) := 'x'; a1 varchar2(10) := 'x';
a2 varchar2(10) := 'x'; a3 varchar2(10) := 'x';
b0 varchar2(40) := 'x'; b1 varchar2(40) := 'x';
b2 varchar2(40) := 'x'; b3 varchar2(40) := 'x';
begin
insert into tst values(a0,a1,a2,a3);
insert into tst values(a0,a1,a2,b3);
insert into tst values(a0,a1,b2,a3);
insert into tst values(a0,a1,b2,b3);
insert into tst values(a0,b1,a2,a3);
insert into tst values(a0,b1,a2,b3);
insert into tst values(a0,b1,b2,a3);
insert into tst values(a0,b1,b2,b3);
insert into tst values(b0,a1,a2,a3);
insert into tst values(b0,a1,a2,b3);
insert into tst values(b0,a1,b2,a3);
insert into tst values(b0,a1,b2,b3);
insert into tst values(b0,b1,a2,a3);
insert into tst values(b0,b1,a2,b3);
insert into tst values(b0,b1,b2,a3);
insert into tst values(b0,b1,b2,b3);
end;
/
select sql_text, version_count from v$sqlarea
where sql_text like 'INSERT INTO TST VA%%'
;
In 10.2.0.3 this will create 16 child cursors, one for each insert.
In previous releases it would only create about 5 child cursors as inserts share cursors that have already been built with longer bind lengths. The problem is more severe for differing bind lengths and larger numbers of binds.
和这篇
HIGH VERSION COUNT/INVALIDATION CAUSE BAD PERFORMANCE
*** 06/17/03 08:19 pm ***
TAR:
----
.
PROBLEM:
--------
The application has been in place for the past 12months. For the past month,
it has been opened to more regions which means more users added to the system
(1000-1500). The current sessions are 4000+. The first incident happened on
May 20 where the performance got so bad, users can not sign on (waiting for PE
lock), they have to shutdown abort. It happened yesterday again.
The analyze of system state and library cache dump shows very high version
count upto 480 for a INSERT statement. Most SQL with high version count > 200
are INSERT, UPDATE.
.
This is not MTS/OPS, but use distributed transaction. It has master tables
which been refresh to other 2 sites (read only snapshot).
.
So far, force shutdown happened twice. The instance have been shutdown almost
every Sunday for scheduled maintenance.
.
The wait for latch free and library cache pin just slowly piled up until it
has to be shutdown.
.
The down database cause entire application outage which is very serious to
ct's business. Ct wants to find out the root cause ASAP.
.
DIAGNOSTIC ANALYSIS:
--------------------
Per ct, when invalidation rate goes high (eg 40+/sec), the performance gets
bad.
But from our checking, the SQL with high invalidation count does not necessary
have high version count. There are SQL with very high version count. In the
latest library cache dump (Jun 17), it is upto 480. The statement is INSERT.
and most SQL has quite high CHILDREN number.
.
From our earlier check for a different SQL (eg: INSERT INTO SR_TRANSIMP_REQS
with bind variable) with high version count, the object involved only has 1
public synonym and 1 table. We found the reason for this SQL not sharing are:
1. optimizer_mode different
2. bind type mismatch
3. analyze to collect statistics run
.
Ct's application does have different optimizer_mode set in different module.
We first thought it was due to frequent analyze (every Sunday night) and
no flush shared_pool ever done. But for this incident happened on Jun 17,
Database was shutdown on Sunday, then brought up without open to application,
analyze was run, then flush shared_pool done. No further analyze done. But
within two days, the version count built upto 480. This can not be explained
by above theory.
.
We need to find out what is the cause of high version count and invalidation
count.
.
After the first incident, it was thought related with bug 1349501 and event
32333 level 10 has been set. But problem still occur.
.
WORKAROUND:
-----------
shutdown - not acceptable
flush shared_pool, ct has not tried this yet |
|
IP卡
狗仔卡
发表于 2007-3-5 11:06
收藏
提升卡
置顶卡
变色卡
显身卡
楼主
