This discussion is archived
1 2 Previous Next 15 Replies Latest reply: May 24, 2013 3:48 AM by 1010636 RSS

Table to log User Connection and Disconnection

1010636 Newbie
Currently Being Moderated
Hi 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
  • 1. Re: Table to log User Connection and Disconnection
    sb92075 Guru
    Currently Being Moderated
    1007633 wrote:
    Hi 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?
    since NULL is not indexed "TIME_LOGOFF IS NULL" requires a FTS to validate this clause.
  • 2. Re: Table to log User Connection and Disconnection
    startup Explorer
    Currently Being Moderated
    Hi,

    When you truncated the table,is space got reclaimed , if not, Enable row movement and reclaim the space.
    so can you share us 10046 trace for that statement.

    > alter session set tracefile_identifier='Update_Error'
    > alter session set events '10046 trace name context forever,level 12';
    > update statement.
    > alter session set event '10046 trace name context off;

    The trace file will get generated at udump location.
  • 3. Re: Table to log User Connection and Disconnection
    sybrand_b Guru
    Currently Being Moderated
    I recommend to dump this procedure and to replace it by native AUDIT CONNECT.
    Guaranteed to work always. And supported by Oracle.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: Table to log User Connection and Disconnection
    sb92075 Guru
    Currently Being Moderated
    does application utilize connection pooling?
  • 5. Re: Table to log User Connection and Disconnection
    1010636 Newbie
    Currently Being Moderated
    sb92075

    You’re right. And your comment light me something. I'll insert the login with the Word “NO” so that at the moment of the update we can pass this criteria as well. Oracle now might use índex.

    About connection pooling. We are using totaly default settings of dedicate Server now.

    Any suggestion Will me thankful


    Startup.

    Nice. This is a production Server. So I'll truncate table again at night and reclaim space as you suggest to see the performance improvement tomorrow.

    Sybrand,

    This procedure controls wether a user is logged or not. So it blocks any repeated session and There's a historical search. Its one schema only. I think we cant use AUDICT CONNECT as it audits DB users :(
  • 6. Re: Table to log User Connection and Disconnection
    sulimo Explorer
    Currently Being Moderated
    What indices does table have?
  • 7. Re: Table to log User Connection and Disconnection
    sybrand_b Guru
    Currently Being Moderated
    Sybrand,
    
    This procedure controls wether a user is logged or not. So it blocks any repeated session and There's a historical search. Its one schema only. I think we cant use AUDICT CONNECT as it audits DB users 
    It is sad to see your organisation has, in its reluctance to read Oracle provided documentation, tried to re-invent the wheel already provided by Oracle,for which your organisation pays, and this wheel is broken beyond repair.
    Using PROFILEs you can limit the number of sessions per user, and AUDIT CONNECT will log history and you don't need to use it for all schemas.
    I'm sad you 'think' you can't use it because you still refuse to read documentation.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 8. Re: Table to log User Connection and Disconnection
    1010636 Newbie
    Currently Being Moderated
    Sybrand

    Sorry.

    I may have not been clearly enough.

    We use one schema to connect all users and we control it users inside our system. That means we have 50 users. All of them connect to the Database using just one schema.

    This table needs to recor information at the user level, like name, department and so on.

    I think thats wy we cant control our loggins using Oracle Audit Connect.

    I'm reading the docs anyway after your last post. We know at all isnt a nice model that one we're using. We know. We're working on it.

    Tks
  • 9. Re: Table to log User Connection and Disconnection
    EdStevens Guru
    Currently Being Moderated
    1007633 wrote:
    sb92075

    You’re right. And your comment light me something. I'll insert the login with the Word “NO” so that at the moment of the update we can pass this criteria as well. Oracle now might use índex.

    About connection pooling. We are using totaly default settings of dedicate Server now.

    Any suggestion Will me thankful


    Startup.

    Nice. This is a production Server. So I'll truncate table again at night and reclaim space as you suggest to see the performance improvement tomorrow.

    Sybrand,

    This procedure controls wether a user is logged or not. So it blocks any repeated session and There's a historical search. Its one schema only. I think we cant use AUDICT CONNECT as it audits DB users :(
    Don't "think we can't". Check the AUDIT syntax and see what you can do.

    AUDIT {audit operation clause} {auditing by clause}
    AUDIT {system privilege} {auditing by clause}
    AUDIT CONNECT {auditing by clause}
    AUDIT CONNECT BY user
    SQL> create user fubar identified by fubar;
    
    User created.
    
    SQL> grant create session to fubar;
    
    Grant succeeded.
    
    SQL> audit connect by fubar;
    
    Audit succeeded.
    
    SQL> conn fubar/fubar
    Connected.
    
    SQL> conn / as sysdba
    Connected.
    
    SQL> select username, timestamp, action_name, sys_privilege
      2  from dba_audit_trail
      3  where username='FUBAR';
    
    USERNAME                       TIMESTAMP            ACTION_NAME
    ------------------------------ -------------------- ----------------------------
    SYS_PRIVILEGE
    ----------------------------------------
    FUBAR                          23-may-2013 10:51:07 LOGON
    
    
    FUBAR                          23-may-2013 10:51:15 LOGOFF
    
    
    FUBAR                          23-may-2013 10:50:47 LOGON
    
    
    
    
    
    SQL>
  • 10. Re: Table to log User Connection and Disconnection
    1010636 Newbie
    Currently Being Moderated
    Ed,

    First. I'm big fan of you.

    I'm not explaining well. Sorry again guys

    See:
    Create user USER_PRODUCTION identified by user;

    We all use USER_PRODUCTION to connect. All the 50 users.

    What I do need to record is when user "Mike" from Marketing logs into our system using USER_PRODUCTION at DB level.

    We don't have one schema per user. We have just one schema. That's bad, but is our architecture now. I need to deal with it.

    Actually we can change. Lastly I saw Tom Kyte on World Extreme performance Tour. Such a great day and experience for me. He said: "Don't say you cant'. Say there is no cost benefit changing or your dev or bosses just don't want to".
    In this case, Dev guys and bosses just don't want to change this architecture.

    So, sometimes I face these type of issues when using just one schema for 50 or more users and have to audit at the "personal" level. Thats why we created a table for it.
  • 11. Re: Table to log User Connection and Disconnection
    jgarry Guru
    Currently Being Moderated
    Also review your indices in the light of this: http://richardfoote.wordpress.com/2008/01/23/indexing-nulls-empty-spaces/
  • 12. Re: Table to log User Connection and Disconnection
    JohnWatson Guru
    Currently Being Moderated
    >
    <snip>
    See:
    Create user USER_PRODUCTION identified by user;

    We all use USER_PRODUCTION to connect. All the 50 users.

    What I do need to record is when user "Mike" from Marketing logs into our system using USER_PRODUCTION at DB level.
    Are you sure you can't use standard database auditing, and query dba_audit_session on client_id? If you set a client id in a logon trigger it will not be included in the logon row (because the logon completes before the trigger fires) but it is included in the logoff row:
    orcl>
    orcl> connect scott/tiger
    Connected.
    
    orcl> exec dbms_session.set_identifier('Mike')
    
    PL/SQL procedure successfully completed.
    
    orcl> connect / as sysdba
    Connected.
    
    USERNAME                       CLIENT_ID                                                         SESSIONID
    ------------------------------ ---------------------------------------------------------------- ----------
    TIMESTAMP           LOGOFF_TIME
    ------------------- -------------------
    SCOTT                                                                                              1950850
    2013-05-23 18:00:16
    
    SCOTT                          Mike                                                                1950850
    2013-05-23 18:00:39 2013-05-23 18:00:39
    
    
    orcl>
  • 13. Re: Table to log User Connection and Disconnection
    sb92075 Guru
    Currently Being Moderated
    AUDIT can not be used when Connection Pooling is utilized.
  • 14. Re: Table to log User Connection and Disconnection
    EdStevens Guru
    Currently Being Moderated
    1007633 wrote:
    Ed,

    First. I'm big fan of you.

    I'm not explaining well. Sorry again guys

    See:
    Create user USER_PRODUCTION identified by user;

    We all use USER_PRODUCTION to connect. All the 50 users.

    What I do need to record is when user "Mike" from Marketing logs into our system using USER_PRODUCTION at DB level.

    We don't have one schema per user. We have just one schema. That's bad, but is our architecture now. I need to deal with it.

    Actually we can change. Lastly I saw Tom Kyte on World Extreme performance Tour. Such a great day and experience for me. He said: "Don't say you cant'. Say there is no cost benefit changing or your dev or bosses just don't want to".
    In this case, Dev guys and bosses just don't want to change this architecture.

    So, sometimes I face these type of issues when using just one schema for 50 or more users and have to audit at the "personal" level. Thats why we created a table for it.
    Then you are not looking at everything that is available from standard auditing and just going on your assumptions. And we all know what "assume" spells.
     1  select os_username,
      2  username,
      3  userhost,
      4  terminal,
      5  action_name,
      6  timestamp,
      7  logoff_time
      8  from dba_audit_trail
      9  where username='FUBAR'
     10* order by timestamp
    SQL> /
    
    OS_USERNAM USERNAME   USERHOST        TERMINAL   ACTION_NAM TIMESTAMP
    ---------- ---------- --------------- ---------- ---------- --------------------
    LOGOFF_TIME
    --------------------
    oracle     FUBAR      vbpsdev.vbdomai pts/0      LOGON      23-May-2013 10:50:47
                          n
    
    
    oracle     FUBAR      vbpsdev.vbdomai pts/0      LOGON      23-May-2013 10:51:07
                          n
    
    
    oracle     FUBAR      vbpsdev.vbdomai pts/0      LOGOFF     23-May-2013 10:51:15
    
    OS_USERNAM USERNAME   USERHOST        TERMINAL   ACTION_NAM TIMESTAMP
    ---------- ---------- --------------- ---------- ---------- --------------------
    LOGOFF_TIME
    --------------------
                          n
    23-May-2013 10:51:15
    
    estevens   FUBAR      xxxx\ESTEVENS   ESTEVENS   LOGON      23-May-2013 12:59:31
    
    
    estevens   FUBAR      xxxx\ESTEVENS   ESTEVENS   LOGOFF     23-May-2013 12:59:34
    23-May-2013 12:59:34
    
    
    SQL>
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points