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