Table to log User Connection and Disconnection
1010636May 23 2013 — edited May 24 2013Hi Guys,
Can you advice on this case?
I'm running 10.2.0.1.0 Win2003 Server
On our system we have a table that records every login and logoff from our software. But now, as the connections grow up and concurrency too, we facing problems on that table logical.
Here is how we do (this sql appears on AWR as being the top for Elapsed Time.)
UPDATE LOG_RECORD SET TIME_LOGOFF = SYSDATE WHERE SESSIONID = SYS_CONTEXT('USERENV', 'SID') AND TIME_LOGOFF IS NULL
This command does a FTS on the LOG_RECORD table and CPU % is quite high. When we have lot of users login in and off, the system hangs. It freezes, basically. Theres no lock. Just a high numbers of latches of various type.
We recentlly truncated the table to have no rows on it but the response time still the same as the bottleneck appears to be the CPU for the UPDATE Statement.
Is this a good logick?
Any tips on improving that update?
ORacle isnt getting any index.. its because SYS_CONTEXT usage?
Thanks a lot