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
      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
          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
            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
              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
                does application utilize connection pooling?
                • 5. Re: Table to log User Connection and Disconnection
                  1010636
                  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
                    What indices does table have?
                    • 7. Re: Table to log User Connection and Disconnection
                      sybrand_b
                      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
                        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
                          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
                            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
                              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
                                >
                                <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
                                  AUDIT can not be used when Connection Pooling is utilized.
                                  • 14. Re: Table to log User Connection and Disconnection
                                    EdStevens
                                    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