11 Replies Latest reply: Jun 26, 2013 1:37 AM by EBSDBA RSS

    Performance Issue with sql query

    EBSDBA

      Hi,

       

      My db is 10.2.0.5 with RAC on ASM, Cluster ware version 10.2.0.5.

       

       

      With bsoa table as

       

       

      SQL> desc bsoa;
      Name                                      Null?    Type
      ----------------------------------------- -------- ----------------------------

      ID                                        NOT NULL NUMBER
      LOGIN_TIME                                         DATE
      LOGOUT_TIME                                        DATE
      SUCCESSFUL_IND                                     VARCHAR2(1)
      WORK_STATION_NAME                                  VARCHAR2(80)
      OS_USER                                            VARCHAR2(30)
      USER_NAME                                 NOT NULL VARCHAR2(30)
      FORM_ID                                            NUMBER
      AUDIT_TRAIL_NO                                     NUMBER
      CREATED_BY                                         VARCHAR2(30)
      CREATION_DATE                                      DATE
      LAST_UPDATED_BY                                    VARCHAR2(30)
      LAST_UPDATE_DATE                                   DATE
      SITE_NO                                            NUMBER
      SESSION_ID                                         NUMBER(8)

       

       

      The query

       

      UPDATE BSOA SET LOGOUT_TIME =SYSDATE WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = SESSION_ID

       

      Is taking a lot of time to execute and in AWR reports also it is on top in

       

      1. SQL Order by elapsed time

      2. SQL order by reads

      3. SQL order by gets

       

      So i am trying a way to solve the performance issue as the application is slow specially during login and logout time.

       

      I understand that the function in the where condition cause to do FTS, but i can not think what other parts to look at.

       

      Also:

      SQL> SELECT COUNT(1) FROM BSOA;

       

        COUNT(1)

      ----------

         7800373

       

       

      The explain plan for  "UPDATE BSOA SET LOGOUT_TIME =SYSDATE WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = SESSION_ID" is

       

      {code}

      PLAN_TABLE_OUTPUT

      Plan hash value: 1184960901

       

      -----------------------------------------------------------------------------------------

      | Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

      -----------------------------------------------------------------------------------------

      |   0 | UPDATE STATEMENT   |                    |     1 |    26 | 18748   (3)| 00:03:45 |

      |   1 |  UPDATE            | BSOA |       |       |            |          |

      |*  2 |   TABLE ACCESS FULL| BSOA |     1 |    26 | 18748   (3)| 00:03:45 |

      -----------------------------------------------------------------------------------------

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

         2 - filter("SESSION_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','SESSIONID')))

       

      {code}

        • 1. Re: Performance Issue with sql query
          DK2010

          Hi,

           

          What about the Table Stat.. it gather recently. what about create index on SESSION_ID column.

          • 2. Re: Performance Issue with sql query
            EBSDBA

            Hi,

             

            Yes the table stats were gathered last week. I am thinking to create index on SESSIONID, But the usage of function in Where condition will supress the index.

             

             

            Thanks

            • 3. Re: Performance Issue with sql query
              sybrand_b

              Incorrect.

               

              An index will not be used if the column is being used in a function on the left hand side of an expression

              like

               

              <function>(<indexed column>) = constant

               

              You don't have this situation.

               

              ---------------

              Sybrand Bakker

              Senior Oracle DBA

              • 4. Re: Performance Issue with sql query
                EBSDBA

                Oh, Thank you.

                 

                So i better create a index on sessionid then ?

                 

                Thanks    

                • 5. Re: Performance Issue with sql query
                  DK2010

                  Yes, You can May it will improve the performance

                  • 6. Re: Performance Issue with sql query
                    Martin Preiss

                    if sessionid has a sufficient selectivity and the resulting clustering factor of the index is not too bad (i.e. the data for each sessioid is not spread accross too many data blocks) then the CBO will probably decide to use the index for the update.

                     

                    Regards

                     

                    Martin

                    • 7. Re: Performance Issue with sql query
                      EBSDBA

                      Hi,

                       

                      There are also triggers before update and AUDITS on this table.

                       

                      CREATE OR REPLACE TRIGGER B2.TRIGGER1
                      BEFORE UPDATE
                      ON B2.BSOA  REFERENCING OLD AS OLD NEW AS NEW
                      FOR EACH ROW
                      :NEW.LAST_UPDATED_BY   := USER    ;
                      :NEW.LAST_UPDATE_DATE  := SYSDATE ;
                      END;
                      /

                       

                       

                      CREATE OR REPLACE TRIGGER B2.TRIGGER2
                      BEFORE INSERT
                      ON B2.BSOA  REFERENCING OLD AS OLD NEW AS NEW
                      FOR EACH ROW
                      :NEW.CREATED_BY        := USER ;
                      :NEW.CREATION_DATE     := SYSDATE ;
                      :NEW.LAST_UPDATED_BY   := USER    ;
                      :NEW.LAST_UPDATE_DATE  := SYSDATE ;
                      END;
                      /

                       

                      And also there is an audit on this table

                      AUDIT UPDATE ON B2.BSOA BY ACCESS WHENEVER SUCCESSFUL;
                      AUDIT UPDATE ON B2.BSOA BY ACCESS WHENEVER NOT SUCCESSFUL;


                      And the sessionid column in BSOA has height balanced histogram.

                       

                       

                      When i create an index i get the following error. As i am on 10g I can't use DDL_LOCK_TIMEOUT . I may have to wait for next down time.

                       

                       

                      SQL> CREATE INDEX B2.BSOA_SESSID_I ON B2.BSOA(SESSION_ID) TABLESPACE B2 COMPUTE STATISTICS;

                      CREATE INDEX B2.BSOA_SESSID_I ON B2.BSOA(SESSION_ID) TABLESPACE B2 COMPUTE STATISTICS

                                                                          *

                      ERROR at line 1:

                      ORA-00054: resource busy and acquire with NOWAIT specified

                       

                      Thanks

                       


                      • 8. Re: Performance Issue with sql query
                        DK2010

                        Hi,

                         

                        have you tried with online option, and you can remove the clause COMPUTE STATISTICS has been deprecated in 10g onward. ref: CREATE INDEX

                         

                        CREATE INDEX B2.BSOA_SESSID_I ON B2.BSOA(SESSION_ID) TABLESPACE B2 online;


                        HTH

                        • 9. Re: Performance Issue with sql query
                          Jonathan Lewis

                          A couple of thoughts - on top of suggestions from others.

                           

                          a) It looks as if you're trying to do a home-made audit of session logon/logoff - which that's available as a built-on if you set audit_trail=db and then audit create session.

                           

                          b) It looks like your method adds in a lot of overhead because the table and triggers have been generated in a semi-automatic way

                           

                          c) With the index in place, you may still have problems with your code since the call to sys_context('userenv','sessionid') returns the "audsid" for a session, and the audsid for SYS is always 4294967295 - this may lead to performance problems, and wrong results on any report you generate: audsid | Oracle Scratchpad The presence of a histogram on the sessionid column makes this seem particularly likely to be true.

                           

                          Regards

                          Jonathan Lewis

                           


                          • 10. Re: Performance Issue with sql query
                            EBSDBA

                            Hi Jonathan,

                             

                            Thanks for your reply. Appreciate it

                             

                            a).

                             

                            Yes, We have a custom hr application which was developed by some company and now that company doesn't exist.

                             

                            Yes, They are doing home made audit of session on login/logoff and storing previous old values before changing. Although we are using audit_trail with db,extended. I do not understand why they are auditing again.

                             

                            And the worst thing is AUD$ table is over 40 GB.

                             

                            I joined this company few days back and stunned to see the application and its performance .

                             

                            b).

                            So, what''s the best thing about Triggers. should i do some modifications on them?

                             

                            c). The application calls to sys_context('userenv','sessionid') using some other database user.

                             

                            Thanks

                            • 11. Re: Performance Issue with sql query
                              Hemant K Chitale

                              Does this table ever get purged ?  What is the oldest data in it ?

                               

                              Can you purge "old"  (you'll have to determine a date cut-off that defines "old") rows and rebuild the table ?

                               

                              What is the point of the BEFORE UPDATE trigger ?  What updates are executed against this table.  Such a table should only have INSERT at logon and UPDATE at logout.  (do you want the BEFORE UPDATE trigger to fire at every logout ?)

                               

                              Does AUD$ ever get purged ?  What is the oldest data in it ?

                               

                               

                              Hemant K Chitale