Fw: sql with SQL_OPCODE=0
- From: Denis <denis.sun@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 1 Dec 2010 04:25:07 -0800 (PST)
It seems the following message did not appear on the list last night. I send it
again. Sorry if duplicated.
I describe our problem here for clarity: When adding or resizing datafiles in a
tablespace hosting lobsegment, under some circusmustance, (for example. many
concurrent sessions doing lob access for insert/update), we observe system
performance degraded greatly immediately after that. ( several such incidents
occurred in 9.2.0.8 with a dedicated ASSM tablespace to the lob, one recent
instance in another 10.2.0.4 db, with MSSM tablespace hosting many objects
including the questioned lob
Anyone else has similar experience?
----- Forwarded Message ----
From: Denis <denis.sun@xxxxxxxxx>
To: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
Cc: Dion Cho <ukja.dion@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Sent: Tue, November 30, 2010 10:00:29 PM
Subject: Re: sql with SQL_OPCODE=0
Thanks Kerry and Dion, you really helped me to understand that sql_id in the
ash
report. As you said, the sql is accessing a table with lob, though I am unable
to get the full sql text by prev_sql_id method as Kerry described in the post.
About the relationship of adding datafile and system slowdown with lob
operation, I did have some observations and experiences as described here,
though don't know the root-cause
http://oracle-study-notes.blogspot.com/2008/06/database-system-wise-slow-related-to.html
This time the data file was also added to a tablespace (but with manul segment
storage managerment) hosting that lob table, also this tablespace hosting many
other objects.
btw in the AWR right after adding datafile and system slow down we have:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: TX - allocate ITL entry 577 1,580 2739 1.1 Configurat
CPU time 1,320 0.9
enq: TX - row lock contention 332 965 2906 0.7 Applicatio
direct path write 487 671 1379 0.5 User I/O
db file sequential read 67,120 517 8 0.4 User I/O
-------------------------------------------------------------
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
1,580 1 354 4.5 1.1 bsyg5yj0a4kk4
Module: JDBC Thin Client
INSERT INTO THE_LOBTABLE (usr_notif_id, usr_id, notification_type, notifica
tion_language, from_address, subject, obj_id, context, content) VALUES (:1, :2,
:3, :4, :5, :6, :7, :8, EMPTY_CLOB())
the top segment for ITL wait is the PK index of THE_LOBTABLE, we increased the
initrans from 10 to 15 in hoping that if someone someday add the datafile
during biz hour. it won't cause same near-outage situation. But I suspect that
the ITL wait is the result of other causes, not the cause itself, as we run
with
same setting just fine for long time.
Any thoughts on relationship between add datafile and lob operations are very
welcomed.
Thanks,
Denis
Other related posts:
