There is also Bug 4057920 which is fixed in the final CPU for 22.214.171.124 - it may not be related as it doesn't sound exactly the same but worth a look maybe - especially if the latch name is the one mentioned in the bug.
sb92075 ha scritto:
The USED_UBLK / USED_UREC are indicators of the rollback segment activity.
So a transaction is activity on the rollback segment.
Oracle9i Enterprise Edition Release 126.96.36.199.0 - 64bit Production
PL/SQL Release 188.8.131.52.0 - Production
CORE 184.108.40.206.0 Production
TNS for IBM/AIX RISC System/6000: Version 220.127.116.11.0 - Production
NLSRTL Version 18.104.22.168.0 - Production
if USED_UBLK & USER_UREC are increasing it means that DML is occurring.
if USED_UBLK & USER_UREC are decreasing it means that ROLLBACK is occurring.
if USED_UBLK & USER_UREC stay the same it means no changes are occurring.
So how does this relate to the batch application bug which is causing you all this heartburn?
"So how does this relate to the batch application bug which is causing you all this heartburn?"
Because the transaction locks the objects and other sessions are in the wait for "enqueue" event.
I absolutely need to close the transaction hanging.
"if USED_UBLK & USER_UREC stay the same it means no changes are occurring."
Okay, no changes are occuring...Why the transaction is not resolved??? The client is dead, the WAS is stopped...How long should I wait???
Why Oracle does not resolve the transaction and I need to restart the DB???
Again, is it possible that the problem, for example, the rollback segment is not properly sized?
I think it is likely to be a bug. I've had similar problems (and it always seems to be with jdbc thin connections). For me though killing the os process (and only the os process) seemed to clear it up - that doesn't seem to be happening for you.
Is there any chance of a db upgrade, or jdbc driver update?
we have said the problem at the Oracle and the Oracle suggested to upgrade to version 11g.
We will update the Oracle version in February, but we can not restart database every day for six months...Only for 1 transaction that hangs!!!
Tomorrow we will evaluate if the latch is on a particular block because we have an online table with more than 1,500,000,000 of rows and this table is not partitioned.
We have more than 12GB for the DB_CACHE_SIZE, but maybe there are problems in the shared pool...
Is it possible that the cause is complex procedures or tables that are too large or too many concurrent DML?
Why we have only one transaction per day with this problem??? The procedure call is always the same and this stored is called hundreds of times during the day!!!
This problem is a real mystery.
It's possible you are running up against inherent problems with larger SGA's in O9 when trying to update a hot block. See
if you haven't already. Back in the day some people would advocate messing with _spin_count, but that is perhaps a cure worse than the disease. Others would say to use a keep or recycle pool, and mess with DB_BLOCK_LRU_LATCHES (if it seems more general than a particular hot block). Still other people would advocate changing blocksizes, resulting in horrible flame wars. Ask Tom Hot blocks
Don't laugh, that stuff happens all the time. One person decides to go off to get some coffee, leaving a select for update open... I've even seen an inquiry-only program do that, so people don't even realize they are causing TX waits to stack up. Those kinds of things often become obvious with an OEM type of program to watch waits. Then someone kills the client program and the process goes on its merry way forever.
Well, you can laugh at them talking about AWR, but just substitute statspack and it is sensible. Those tools are more system wide, but there may be something specific or cumulative across processes that victimizes the one that you see, or its parent.
I'm laughing because the problem is not pending transactions to enqueue!!!, but 1 transaction hanging unresolved with the kill/disconnect on Oracle or Unix even when the WebSpheare is stopped!!!
The "for update" on hold because the client is dead can cause the restart of a production DB???? I do not think so.
My problem isn't the transactions in enqueue because the objects are locked by "for update" uncommited, but 1 transaction that not resolved in any way by Oracle.
OK, that's funny.
If you look at the definitions of gv$transaction and gv$transaction_enqueue, you'll see the first looks at x$ktcxb, and the latter joins that to some other x$ tables with some bit twiddling. x$ktcxb is a mapping of the transaction table in memory - so it needs to be controlled by something. In your original post, you asked about a latch wait. Later, you mentioned objects are locked by this, so you can't just laugh and laugh and ignore it. So yes, it is likely a bug with x$ktcxb maintenance. Yay!
Maybe try this: Killing an Oracle process from inside Oracle | Tanel Poder
It is a violation of your support agreement if ANY of that information you just posted came from the SOR you created or from Oracle's response to that SOR.
Users can NOT make public information contained in official SORs. That information is ONLY available to Oracle users who have paid for support.
If any of that information is from an SOR edit your last reply and remove that information IMMEDIATELY or you will be violating your support agreement.