9 Replies Latest reply: Feb 23, 2012 3:28 AM by orawiss RSS

    performance issue

    902663
      Hello,

      we have 2 environments ; RAC 4 nodes database, 11.2.0.2.5 under redhat linux 5.5 and RAC 2 nodes database, 11.2.0.2.5 under redhat linux 5.5.

      Developers are comlaining that an insert statement error (primary key already esists) is returned after 15 seconds in the 4 nodes RAC where it is only returns 1 second in the RAC 2 nodes.

      they are testing exactly the same insert statement, and same version of the application.

      I would like to know ; How to troubleshoot the problem?

      thank you,
      Diego
        • 1. Re: performance issue
          P.Forstmann
          This situation can be explained by following scenario where 2 differents sessions insert the same primary key in the same table:
          -- session 1:
          insert into t values(1);
          -- session 1 does something else without running COMMIT or ROLLBACK
          
          -- session 2
          insert into t values(1);
          -- session 2 waits until session 1 runs COMMIT or ROLLBACK.
          You can try to use DBMS_MONITOR to trace SQL statements (see examples in http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php) to check wait events.
          • 2. Re: performance issue
            902663
            Hello,

            Any other solution please, How can I compare the sqlnet connection delay ? The problem is the application opens quicky a session , run the insert and close it quickly ... no time to identify the session id, also the application cannot be modified , written en java ...
            • 3. Re: performance issue
              sb92075
              does application utilize connection pooling?
              • 4. Re: performance issue
                902663
                sb92075 wrote:
                does application utilize connection pooling?
                yes.
                • 5. Re: performance issue
                  P.Forstmann
                  You can try to create a logon trigger to enable trace provided that the Oracle user account executing the trigger has been granted EXECUTE privilege on DBMS_MONITOR.

                  Something like:
                  create or replace trigger tt
                  after logon
                  on database
                  begin
                  if (user = '<connecting user>') then
                   dbms_session.session_trace_enable(waits => true, binds => true);
                  end if;
                  end tt;
                  /
                  show errors
                  exit
                  WARNING: if there are errors in this trigger this can prevent almost any new connection to database !!!

                  Note also that your application seems to suffer from n°1 mistake according to Top Ten Mistakes Found in Oracle Systems http://docs.oracle.com/cd/E11882_01/server.112/e16638/technique.htm#i11221 :

                  >
                  1. Bad connection management

                  The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and is totally unscalable.
                  >

                  Edited by: P. Forstmann on 21 févr. 2012 19:04
                  • 6. Re: performance issue
                    902663
                    hello,

                    What about I am having hundreds of connections for the same user, this will create a lot of trace files :(
                    • 7. Re: performance issue
                      P.Forstmann
                      You can try to disable the trigger after 1 or 2 minutes or try to filter sessions to be traced by using V$SESSION columns if you know which sessions are more likely to get the issue.
                      • 8. Re: performance issue
                        902663
                        Hello,

                        I did the folowing;

                        1- Took an AWR snapshot.
                        2- Asked the developers to run the queries from the application
                        3- Took another AWR snapshot.

                        After that I have generated an AWR report but I don't see application queries listed in the report. I suppose the AWR report only shows the top 30 sql listed.
                        How can I change that so I have all the queries listed or at least appliaction queries, I think I need to modify some paramaters in the database right? My idea is to have all application listed and then see the queries response time (=elapsed time).

                        Another question, wondering if there is response time for each query capture somewhere in AWR dba_hist tables; which table and which column should I look for?

                        Thank you,
                        Diego
                        • 9. Re: performance issue
                          orawiss
                          hi,

                          You can use:
                          BEGIN
                          
                          DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
                             topnsql   => 'MAXIMUM'
                          );
                          END;
                          /
                          references: http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/d_workload_repos.htm#BACEHJJI
                          topnsql
                          If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.
                          If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.