1 2 3 4 Previous Next 54 Replies Latest reply: Jan 15, 2013 6:29 PM by jgarry Go to original post RSS
      • 15. Re: Performance Issue
        yxes2013
        One question worth asking is, are there other things on the machine outside oracle which suddenly steal the filesystem cache and leave a large number of your "physical reads" actually going to disc rather than coming from memory ?
        The action plan yesterday when everything has nothing to do because the server was hanging....is to reboot the stop the database and reboot the server hoping the the server itself will release
        everything it locks resources. My hunch is It might hit something (data) or part of the hardware that is creating harware locks? Or maybe block, memory, disk, network corruption?

        But even if we restarted the server and run the autoinvoice rma module it was still very slow. So it might me on the data? Because yesterday they run the same program and it is good whole day.


        I asked the users, operators, sysadmin what is the difference being done yesterday and today? and they same none. They said this has been behaving for the last 6 months. but it getting worst lately that they were cancelling transactions worth P5Million a day.
        • 16. Re: Performance Issue
          jgarry
          Not a joke. Did you read the link? It was an illustration of the bind peeking issue.

          For your buffer cache, show us the init parameters from the alert log, they should be displayed after startup messages. Also, check the docs for the V$DB_CACHE_ADVICE view
          • 17. Re: Performance Issue
            yxes2013
            Thanks jq,

            I check the alert log at again i see
            Tue Jan  8 14:58:52 2013
            Errors in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_8774.trc:
            ORA-12570: TNS:packet reader failure
            ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
            Tue Jan  8 15:03:27 2013
            Beginning log switch checkpoint up to RBA [0x21576.2.10], SCN: 0x056e.7527328b
            Thread 1 advanced to log sequence 136566
            This has occured since I increase the redo log and undo_retention just the other day.
            Can you help how to resolve this please.....
            Starting ORACLE instance (normal)
            LICENSE_MAX_SESSION = 0
            LICENSE_SESSIONS_WARNING = 0
            SCN scheme 2
            Using log_archive_dest parameter default value
            LICENSE_MAX_USERS = 0
            SYS auditing is disabled
            Starting up ORACLE RDBMS Version: 9.2.0.6.0.
            System parameters with non-default values:
              _trace_files_public      = TRUE
              processes                = 400
              sessions                 = 800
              timed_statistics         = TRUE
              shared_pool_size         = 301989888
              sga_max_size             = 1688804248
              shared_pool_reserved_size= 30000000
              _shared_pool_reserved_min_alloc= 4100
              java_pool_size           = 67108864
              _kghdsidx_count          = 1
              enqueue_resources        = 32000
              nls_language             = american
              nls_territory            = america
              nls_sort                 = binary
              nls_date_format          = DD-MON-RR
              nls_numeric_characters   = .,
              nls_comp                 = binary
              nls_length_semantics     = BYTE
              control_files            = /u02/oracle/oaproddata/cntrl01.dbf, /u02/oracle/oaproddata/cntrl02.dbf, /u02/oracle/oaproddata/cntrl03.dbf
              db_block_checksum        = TRUE
              db_block_size            = 8192
              db_cache_size            = 301989888
              compatible               = 9.2.0
              log_buffer               = 10485760
              log_checkpoint_interval  = 100000
              log_checkpoint_timeout   = 1200
              db_files                 = 512
              db_file_multiblock_read_count= 8
              log_checkpoints_to_alert = TRUE
              dml_locks                = 10000
              row_locking              = always
              undo_management          = AUTO
              undo_tablespace          = APPS_UNDOTS1
              undo_suppress_errors     = FALSE
              undo_retention           = 3600
              db_block_checking        = FALSE
              max_enabled_roles        = 100
              O7_DICTIONARY_ACCESSIBILITY= FALSE
              session_cached_cursors   = 200
              utl_file_dir             = /usr/tmp, /usr/tmp, /u02/oracle/oaproddb/9.2.0/appsutil/outbound/OAPROD_oel5, /usr/tmp
              job_queue_processes      = 2
              _system_trig_enabled     = TRUE
              cursor_sharing           = EXACT
              parallel_min_servers     = 0
              parallel_max_servers     = 8
              background_dump_dest     = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/bdump
              user_dump_dest           = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump
              max_dump_file_size       = 20480
              core_dump_dest           = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/cdump
              optimizer_features_enable= 9.2.0
              db_name                  = OAPROD
              open_cursors             = 1000
              ifile                    = /u02/oracle/oaproddb/9.2.0/dbs/OAPROD_oel5_ifile.ora
              sql_trace                = FALSE
              _sort_elimination_cost_ratio= 5
              _b_tree_bitmap_plans     = FALSE
              _fast_full_scan_enabled  = FALSE
              optimizer_max_permutations= 2000
              query_rewrite_enabled    = true
              _index_join_enabled      = FALSE
              _sqlexec_progression_cost= 2147483647
              _like_with_bind_as_equality= TRUE
              pga_aggregate_target     = 4294967296
              workarea_size_policy     = AUTO
              aq_tm_processes          = 1
              olap_page_pool_size      = 4194304
            PMON started with pid=2
            DBW0 started with pid=3
            DBW1 started with pid=4
            LGWR started with pid=5
            CKPT started with pid=6
            SMON started with pid=7
            RECO started with pid=8
            CJQ0 started with pid=9
            QMN0 started with pid=10
            Tue Jan  8 11:59:18 2013
            ALTER DATABASE   MOUNT
            Tue Jan  8 11:59:22 2013
            Successful mount of redo thread 1, with mount id 4254113430
            Tue Jan  8 11:59:22 2013
            Database mounted in Exclusive Mode.
            Completed: ALTER DATABASE   MOUNT
            Tue Jan  8 11:59:22 2013
            ALTER DATABASE OPEN
            Tue Jan  8 11:59:23 2013
            Thread 1 opened at log sequence 136562
              Current log# 3 seq# 136562 mem# 0: /u02/oracle/oaproddata/log03a.dbf
              Current log# 3 seq# 136562 mem# 1: /u02/oracle/oaproddata/log03b.dbf
            Successful open of redo thread 1
            Tue Jan  8 11:59:23 2013
            MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
            Tue Jan  8 11:59:23 2013
            SMON: enabling cache recovery
            Tue Jan  8 11:59:23 2013
            Successfully onlined Undo Tablespace 368.
            Tue Jan  8 11:59:23 2013
            SMON: enabling tx recovery
            Tue Jan  8 11:59:23 2013
            Database Characterset is UTF8
            replication_dependency_tracking turned off (no async multimaster replication found)
            Completed: ALTER DATABASE OPEN
            Tue Jan  8 12:02:18 2013
            Beginning log switch checkpoint up to RBA [0x21573.2.10], SCN: 0x056e.7521cc0e
            Thread 1 advanced to log sequence 136563
              Current log# 4 seq# 136563 mem# 0: /u02/oracle/oaproddata/log04a.dbf
              Current log# 4 seq# 136563 mem# 1: /u02/oracle/oaproddata/log04b.dbf
            Tue Jan  8 12:22:33 2013
            Completed checkpoint up to RBA [0x21573.2.10], SCN: 0x056e.7521cc0e
            Tue Jan  8 12:50:04 2013
            Beginning log switch checkpoint up to RBA [0x21574.2.10], SCN: 0x056e.752326a7
            Thread 1 advanced to log sequence 136564
              Current log# 3 seq# 136564 mem# 0: /u02/oracle/oaproddata/log03a.dbf
              Current log# 3 seq# 136564 mem# 1: /u02/oracle/oaproddata/log03b.dbf
            Tue Jan  8 13:10:19 2013
            Completed checkpoint up to RBA [0x21574.2.10], SCN: 0x056e.752326a7
            Tue Jan  8 13:49:16 2013
            Beginning log switch checkpoint up to RBA [0x21575.2.10], SCN: 0x056e.7524a192
            Thread 1 advanced to log sequence 136565
              Current log# 4 seq# 136565 mem# 0: /u02/oracle/oaproddata/log04a.dbf
              Current log# 4 seq# 136565 mem# 1: /u02/oracle/oaproddata/log04b.dbf
            Tue Jan  8 14:10:16 2013
            Completed checkpoint up to RBA [0x21575.2.10], SCN: 0x056e.7524a192
            • 18. Re: Performance Issue
              jgarry
              Please post the lines from your alert log that specify your parameters, after the line that says "System parameters with non-default values:"
              • 19. Re: Performance Issue
                yxes2013
                Hi... posted. thanks.
                • 20. Re: Performance Issue
                  jgarry
                  Do you know how you came up with the values for pga_aggregate_target and db_cache_size? They seem possibly out of whack (too large and too small, respectively), but that might just be me being used to my systems. But I can't help but wonder when those excel programs run, you might just be doing the classic "use so much memory you start swapping" trick.

                  What does linux have to say about memory usage at the slow times? How much physical memory do you have?
                  • 21. Re: Performance Issue
                    yxes2013
                    Physical memory is 12Gb...But my database is 32 bit so it takes only 2Gb max. I am correct? So lots of idle RAM?

                    Can you give me what size do I set. But again what has this to do with the "good" performance at some days?
                    • 22. Re: Performance Issue
                      sb92075
                      yxes2013 wrote:

                      3. Can you increase your SGA to handle more appropriately the volume required by the queries?
                      Is it already 1.7 which is the limit of 32 bit Oracle. which we have.
                      not entirely true. 1.7MB is default maximum size as shipped by Oracle.
                      The Oracle kernel can be relinked after changing a parameter file that increases the SGA to around 2.6MB
                      I did this more than a decade ago when still running V9.2.0.7
                      • 23. Re: Performance Issue
                        yxes2013
                        Can you give me steps to do it please.... Thanks
                        • 24. Re: Performance Issue
                          sb92075
                          yxes2013 wrote:
                          Can you give me steps to do it please.... Thanks
                          I have long forgotten the details.
                          I did it based upon a NOTE available on Metalink; aka My Oracle Support
                          • 25. Re: Performance Issue
                            jgarry
                            yxes2013 wrote:
                            Physical memory is 12Gb...But my database is 32 bit so it takes only 2Gb max. I am correct? So lots of idle RAM?
                            Well, you might go back to the concepts manual and review about SGA and PGA. You stated earlier you set SGA to 1.7G, please do a "show SGA" and show us what it thinks. You are using sga_max_size, and not sga_target, so you may not have the buffers set optimally, and it may be smaller than you think. Tuning is an iterative task.

                            The pga_aggregate_target is just that - a target. Oracle can use more. Much has been written about it, in fact, when I was running 9.2.0.6, Jonathan Lewis was writing a lot about it. Every time I thought I understood it something confused me more, and I eventually gave up and used what worked for me (and decided the docs were a bit off). So google his name and pga_aggregate target and 9i and maybe you'll understand it.

                            >
                            Can you give me what size do I set. But again what has this to do with the "good" performance at some days?
                            This may go along with what Jonathan said about something outside Oracle making it slow. I think what that something is, and I'm speculating here, is some of your programs use lots and lots of PGA, to the point where your system chokes. So the solution would be to make your pga_aggregate_target much smaller. But I wouldn't make any adjustments without gathering evidence, one data point is what linux thinks is being used (top and swapinfo are gross measures I use, I'm not on linux). There are also SGA and PGA advisors, as well as db cache advice. (I may be confused as to what is in what version, see your performance tuning guide. Also, some of the docs are misleading about this stuff, google what Jonathan has had to say. And there is lots of misinformation on the net specifically on this subject.)

                            Since Jonathan has been in this thread, he might have more to say on the subject.

                            You might also check the plans of the programs that are slow. It's possible the excel programs are simply asking for way too much information. I've seen it where such clients blow up (or the user X's out of it, and it keeps running on the server), and they keep submitting more and more.
                            • 26. Re: Performance Issue
                              yxes2013
                              SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jan 9 09:31:27 2013
                              
                              Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
                              
                              
                              Connected to:
                              Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
                              With the Partitioning, OLAP and Oracle Data Mining options
                              JServer Release 9.2.0.6.0 - Production
                              
                              SQL> show sga
                              
                              Total System Global Area 1688804248 bytes
                              Fixed Size                   453528 bytes
                              Variable Size            1375731712 bytes
                              Database Buffers          301989888 bytes
                              Redo Buffers               10629120 bytes
                              • 27. Re: Performance Issue
                                sb92075
                                at OS level you should run top & vmstat when system is totally bogged down under apparent heavy load.

                                The combination of the two can help to identify which system resource (CPU, RAM, I/O) is the bottleneck.

                                COPY & PASTE the results & be sure to enclose in
                                 tags                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                • 28. Re: Performance Issue
                                  jgarry
                                  http://asktom.oracle.com/pls/asktom/f?p=100:11:16924899315320::::P11_QUESTION_ID:365088445659
                                  • 29. Re: Performance Issue
                                    sb92075
                                    SQL to show where time is being spent

                                    http://www.orafaq.com/forum/mv/msg/183304/563118/173420/#msg_563118