1 2 Previous Next 15 Replies Latest reply: Apr 14, 2010 4:06 PM by 649635 RSS

    Performance of database runnig on RAC 10g with two Solaris nodes

    443102
      Hello,

      I have performance problems one a database run on RAC installed on two Solaris nodes (node1 and node2).
      ASM is used with two disk groups (DG1, DG2):
      - database files and redo logs are on DG1
      - flash recovery area (FRA) files and archive log files are on DG2

      SQL> alter system set db_recovery_file_dest='+DG2' scope=both;
      SQL> alter system set log_archive_dest_10='location=use_db_recovery_file_dest';
      SQL> alter database archivelog;
      SQL> alter database enable block change tracking using file '+DG2';
      SQL> alter database flashback on;

      Need to increase performance. Which way do you recommend?

      I am thinking to move some files (FRA or redo logs) on an NFS file system mounted from network on the two RAC nodes. But I don't know if this is possible.

      Thanks alot.
        • 1. Re: Performance of database runnig on RAC 10g with two Solaris nodes
          sb92075
          Need to increase performance. Which way do you recommend?
          Fix what is slow.
          I am thinking to move some files (FRA or redo logs) on an NFS file system mounted from network on the two RAC nodes.
          Why? What metrics do you have showing this is where slowness occurs?

          Ready, Fire, Aim school of tuning?
          • 2. Re: Performance of database runnig on RAC 10g with two Solaris nodes
            660111
            Please provide an AWR report to us, so we can better analyze the performance problem.
            Get an AWR report of the period where you get the worst performance.
            • 3. Re: Performance of database runnig on RAC 10g with two Solaris nodes
              443102
              Hello,

              Hardware available:
              - 2 Solaris servers with Oracle RAC 10g installed
              - 1 NAS configured with RAID 5 (DG1 and DG2 disk groups of the ASM are stored on the NAS).
              - archived log mode and flashback are enabled, DG2 is used
              - DG1 is used to store database files and redo logs
              During tests I saw that there is a high Waiting time on IO operation on both nodes of the RAC.
              This is the reason I am thinking to store the redo logs and/or flashback area and/or archived redo logs on another hardware - the only one I have available is a NFS volume which is not really slow.

              I am attaching links to AWR logs from RAC node1 and node2 during performance tests:
              http://beri.110mb.com/awrrpt_1_4796_4798.html
              http://beri.110mb.com/awrrpt_2_4796_4798.html

              Thank you, Alex
              • 4. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                Jonathan Lewis
                berindei@gmail.com wrote:
                Hello,

                During tests I saw that there is a high Waiting time on IO operation on both nodes of the RAC.
                This is the reason I am thinking to store the redo logs and/or flashback area and/or archived redo logs on another hardware - the only one I have available is a NFS volume which is not really slow.

                I am attaching links to AWR logs from RAC node1 and node2 during performance tests:
                http://beri.110mb.com/awrrpt_1_4796_4798.html
                http://beri.110mb.com/awrrpt_2_4796_4798.html
                Alex,

                A couple of points about the AWR reports you've published:

                Elapsed time 120 minutes, DB time 140 minutes -
                Top 5 CPU Time 23,195 seconds (and you have 8 CPUs, for a total of 2 * 3600 * 8 CPU seconds available).
                BUSY_TIME - 475,589 c/s

                The machine is not under load, and the database is not busy - so we have to assume that you are worried about some specific task rather than "the system". That being the case, using event 10046 at level 8 and examining the trace file and tkprof output is a more appropriate approach.

                However, there are some interesting clues in the AWR:
                After CPU, your top 5 on node 1 shows:
                gc buffer busy
                log file sync (avg 5 m/s)
                flashback writer
                db file sequential read (avg 3 m/s)

                and on node 2 shows
                gc buffer busy
                TX locks
                flashback log writer
                log file sync (avg 3 m/s)

                The I/O times don't really seem to be too bad (3 m/s reads is pretty fast, and must be getting some benefit from a cache somewhere). I'll ignore the TX waits (data conflict for the moment).

                The significant thing is the RAC-related issue: You're running RAC, and the nodes constantly want to update blocks which are currently being updated by the other node - that's the direct interpretation of "gc buffer busy"; and as a side effect of that conflict you will be running up excess CPU (latch spinning, LMS processes, etc.) at the same time.

                So look for SQL suffering a lot of Cluster Wait time, and Segments which send a lot of blocks back and fore across the interconnect and suffer GC buffer busy. I can't easily copy from the AWR HTML form to this page, but checking:

                SQL ordered by Cluster Wait Time I see near the top (node 1):
                DELETE FROM JMS_TRANSACTIONS WHERE TXID = :1
                INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(:1, :2, :3, :4, :5)

                And in the
                Segments by Global Cache Buffer Busy
                Table JMS_TRANSACTIONS
                Indexes: JMS_MESSAGES_DESTINATION_IB, JMS_MESSAGES_TXID_TXOP_I,

                At first sight it looks like you should be worrying about an application that should be connecting to just one instance - perhaps by defining an Oracle service that runs on just one instance. Alternatively, is there some way of identifying the incoming data and separating it into two distinct sets so that the two instances can process two sets of data independently.

                If you don't mind me copying the files up to my blog some time next week I think it might be a nice example for my +*[Analyzing Statspack|http://jonathanlewis.wordpress.com/statspack-examples/]*+ series.

                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                http://www.jlcomp.demon.co.uk

                To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                fixed format
                .
                
                "Science is more than a body of knowledge; it is a way of thinking" 
                Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                • 5. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                  baskar.l
                  Lewis,

                  Looking forward for your post on analyzing...

                  thanks,
                  baskar.l
                  • 6. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                    701909
                    Need to increase performance. Which way do you recommend?
                    Find the top Oracle tuning book and read it carefully:

                    http://www.lmgtfy.com/?q=top+oracle+tuning+book

                    There are free Oracle tuning for RAC articles on the web but nothing is as good as a book:

                    http://www.lmgtfy.com/?q=oracle+tuning+rac
                    • 7. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                      Timur Akhmadeev
                      berindei@gmail.com wrote:
                      Need to increase performance. Which way do you recommend?
                      Standard way (SQL trace) is the best. Since you are using JDBC-based application (deployed on a 2-node JBoss cluster, as I understand) it would require application instrumentation.

                      One thing to consider: you are using 10g DB and JDBC XA driver version less than 10g. It is clear because there are calls to JAVA_XA package, which is used by XA driver to support Oracle participation in distributed transactions. This package is not used by JDBC driver version >= 10g. All the work associated with distributed transaction management is done via native protocol and is faster than in previous drivers. So, you should think about moving to newer JDBC driver too.
                      And listen to Jonathan, of course :)
                      • 8. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                        Jonathan Lewis
                        Absorbine Jr. wrote:

                        Find the top Oracle tuning book and read it carefully:
                        http://www.lmgtfy.com/?q=top+oracle+tuning+book

                        There are free Oracle tuning for RAC articles on the web but nothing is as good as a book:
                        http://www.lmgtfy.com/?q=oracle+tuning+rac
                        Dear Mr. Burleson,

                        If you want people to visit your websites I'm fairly sure that no-one would object to you listing a few of them after your name - provided your answers included some specific information that was relevant to the thread.

                        However, if you continue your attempts to use OTN as a tool for boosting the Google ranking of your websites, I shall start reviewing some of the articles listed by your suggested search patterns.


                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk
                        • 9. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                          443102
                          Hello Jonathan,

                          Please use everything you need from the Analyzing. This might also raise new ideas :).

                          Thank you for the smart tech answer,
                          Alex
                          • 10. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                            Jonathan Lewis
                            I took another look at the two AWR reports this morning - and there's a possible quick fix for a relatively small amount of your time. You've got a few hundred seconds lost on RVWR waiting for flashback buffers - it may be possible to address this by increasing the size of the flashback buffer (which is current 15MB), but the "legal" way of doing that is to increase the size of the log buffer (which you normally don't set in 10g).

                            If you can restart the database, setting the log_buffer to 16MB or 32MB may allow you to get rid of the RVWR time.

                            Regards
                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk


                            "Science is more than a body of knowledge; it is a way of thinking"
                            Carl Sagan
                            • 11. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                              Jonathan Lewis
                              Jonathan Lewis wrote:

                              If you can restart the database, setting the log_buffer to 16MB or 32MB may allow you to get rid of the RVWR time.
                              A quick follow-on from that.
                              Since you're running RAC, the private redo thread mechanism will have been disabled. I'd be interested to see how many shared redo threads your two instances were using. If you can connect as SYS could you post the results for:
                              select 
                                   inst_id, strand_size_kcrfa, count(*) 
                              from 
                                   x$kcrfstrand 
                              group by 
                                   inst_id, strand_size_kcrfa
                              ;
                              It's worth checking before and after making the change to log_buffer.


                              Regards
                              Jonathan Lewis
                              http://jonathanlewis.wordpress.com
                              http://www.jlcomp.demon.co.uk


                              "Science is more than a body of knowledge; it is a way of thinking"
                              Carl Sagan
                              • 12. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                                443102
                                Hello Jonathan,

                                Here is the result of the SELECT from x$kcrfstrand:
                                INST_ID     STRAND_SIZE_KCRFA     COUNT(*)
                                +1     2097152          2+

                                Here is the log_buffer (checked both instances):
                                SQL> show parameter log_buffer
                                NAME                                 TYPE        VALUE
                                ------------------------------------ ----------- ------------------------------
                                log_buffer                           integer     4194304

                                Will change the log_buffer size tomorrow and let you know.
                                • 13. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                                  598168
                                  hi,
                                  what version of database are you running
                                  i.e.
                                  Standard or enterprise Edition?

                                  regards
                                  • 14. Re: Performance of database runnig on RAC 10g with two Solaris nodes
                                    443102
                                    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
                                    With the Partitioning, Real Application Clusters, OLAP and Data Mining options
                                    1 2 Previous Next