10 Replies Latest reply on Jan 14, 2009 2:28 PM by 649606

    R12 on Oracle linux: how to tune the R12?

      Enter the R12. When i open a new page, the system is so bz.
      By the sar : idle is 0
      the top: idle is under 50%.

      Maybe the ways of performancing tuning r Database, JVM and so on .Could pls tell me the details or some new methods to allocate the resources the system provided with reason?


        • 1. Re: R12 on Oracle linux: how to tune the R12?
          What kind of box is this installed on?

          If you are low on memory, you can set the sga_target to 300M, pga_aggregate_target to 200M and bounce the instance.

          When the applications come up, every concurrent manager known to man will be started, so go into System Administrator->Concurrent->Administer, and deactivate all SFM%, Workflow%, MRP%, CRP%, OAM%, RCVTM% managers and any other ones you don't think you need running. You really only have to have Internal Manager, Service Manager, Conflict Resolution Manager, and Standard Manager. Everything else should run in Standard if the specialized manager is not available.

          Stopping the SFM% and Workflow Managers will free up a ton of processing cycles on your machine.
          • 2. Re: R12 on Oracle linux: how to tune the R12?
            mdtaylor: The test result is after lowering the size of the SGA and PGA. i'll try ur suggestions, and could u tell me the details about how to tune the database、core parameters 、form server 、JVM。。。

            thx for ur reply.

            • 3. Re: R12 on Oracle linux: how to tune the R12?
              There is not much else to tune on the database. You should have > 2 GB of RAM.

              For JVM, look at:

              Latest JVM Tuning Recommendations for Apps 11i

              Forms server is servlet mode and there is not much tuning there either. If you install on a single IDE drive, it will be pretty slow compared to RAID or SAN etc.
              • 4. Re: R12 on Oracle linux: how to tune the R12?
                mdtaylor: I think u r right.I'll try the methods provided u hv provided.
                But the fact is : by the "top" of the system ,CPU is idle ,and by the "sar" the idle is keeping zero .If both of them r bz,i can understand it. Maybe my RAM is limited,my using a single IDE,my CPU is too slow..... But now, the things r not like this.
                How to "cut" the IDLE of the CPU to the 'sar of the R12'?

                • 5. Re: R12 on Oracle linux: how to tune the R12?
                  Hard to explain the difference. Maybe make sure you look at a few intervals. Mine is pretty close:

                  Cpu(s): 11.2%us, 7.9%sy, 0.0%ni, 0.0%id, 79.6%wa, 0.7%hi, 0.7%si, 0.0%st

                  Linux (madrid) 03/30/07

                  07:07:28 CPU %user %nice %system %iowait %idle
                  07:07:33 all 17.17 0.00 4.99 77.84 0.00
                  Average: all 17.17 0.00 4.99 77.84 0.00
                  • 6. Re: R12 on Oracle linux: how to tune the R12?
                    thx very much. I'll check it.
                    HI,Everyone,Maybe someone can tell me the difference and how to performe tuning the R12. I just wanna to attribute the system resources with reason.

                    • 7. Re: R12 on Oracle linux: how to tune the R12?
                      Fadi Hasweh
                      one qusiotn plz what is you hardware configuration

                      • 8. Re: R12 on Oracle linux: how to tune the R12?
                        Top Oracle Tuning Tips By Alan Kendall May 2007

                        #1) Set your pga_aggregate_target large enough so that the average disk sort in meg is greater than 200 meg. This way most of the sorts will be done in memory and I/O waits on sorting will not slow down other processing.

                        ---------- ------------ --------------
                        95 14030323 0


                        VALUE NAME
                        ----------------------------------- -----------------------
                        740294656 pga_aggregate_target
                        AUTO workarea_size_policy

                        #2) When you calculate the buffer hit ratio, calculate the average physical reads per hour and try to keep this physical reads per hour less than a million per hour.

                        In the following example, the physical reads per hour are larger than a million so I increased the block buffers by 50%.

                        ---------- ------- ------------- ---------- ----------------
                        CURACAO9 86.37 430521443475 99.16 1766521

                        STARTUP_NAME MEMORY_VALUE
                        ---------------------------------------- ------------
                        db_block_buffers 240000

                        Alter system set db_block_buffers=360000 scope=spfile;

                        #3) Set your log_buffer=14 meg (The 10g default on Unix) because cpu's today can write several meg a second.

                        SYS AS SYSDBA> alter system set log_buffer=163840 scope=spfile;
                        SYS AS SYSDBA> shutdown immediate;
                        SYS AS SYSDBA> startup
                        SYS AS SYSDBA> @insert_a_million_rows
                        Elapsed: 00:00:01.89 (time to insert a million rows is 1.89 seconds.
                        SYS AS SYSDBA> alter system set log_buffer=14680064 scope=spfile;
                        SYS AS SYSDBA> shutdown immediate;
                        SYS AS SYSDBA> startup
                        SYS AS SYSDBA> @insert_a_million_rows
                        Elapsed: 00:00:01.84 (time to insert a million rows is now 2% better.

                        In this case, the 2% benefit was not that much but on some systems where there is heavy write contention, it is not uncommon to get 35% to 50% write benefits.

                        #4) Make sure you are not doing log swaps more than three per hour.

                        SQL> select count(*)/5/24 average_log_swaps_per_hour from v$loghist where first_time > sysdate-5;


                        In this case the average is greater than three so we need to drop log groups and recreate them. This can be done safely online.

                        #5) Run stats pack or awr and identify the top waits in Oracle. Oracle tells us what it is waiting on and we should pay attention to what it is telling us. In the following example the top wait events that statspack would show (I queried sys.v_$system_event directly to get the following report for the waits since startup).

                        Seconds_Wait WAITS_PER_HOUR %_wait EVENT_NAME
                        ------------ -------------- ------ -------------------------
                        45,859,349 22116.2837 79 db file sequential read
                        4,188,633 2020.024 7 library cache load lock
                        3,878,043 1870.23811 6 buffer busy waits
                        1,592,185 767.852326 2 library cache pin
                        748,263 360.860025 1 db file scattered read

                        We see that there is a large amount of waiting on seqential reads. This can be helped by increasing the buffer cache (and archiving historic rows), better indexing, tuning sql, and upgrading the hardware.

                        Also the system is latching on the library cache and v$latch will show this:
                        ECURACAO9 > select name,wait_time from v$latch
                        2 where wait_time > (select 1*avg(wait_time) from v$latch)
                        3 order by wait_time;

                        NAME WAIT_TIME
                        ------------------------------ ----------
                        library cache pin allocation 7484686
                        process allocation 10019421
                        row cache objects 23045242
                        parallel query alloc buffer 33908108
                        library cache load lock 54770430
                        SQL memory manager latch 108655668
                        library cache 139125871
                        session allocation 174105073
                        shared pool 278591232
                        library cache pin 803601839

                        This can be helped by increasing the shared_pool, setting cursor_sharing=similar, add bind variable to code, pinning packages and in this case upgrading Oracle because of a bug in this version.

                        #6) Look at sar and identify if we have mostly a cpu problem, an I/O problem or no problem.

                        a) If a cpu problem, identify the worst cpu hogs by:
                        select cpu_time,sql_text from v$sqlarea order by cpu_time;
                        b) If an I/O problem, identify the worst I/O hogs by:
                        select DISK_READS,sql_text from v$sqlarea order by disk_reads;

                        #7) Look at the logical reads and writes verses the physical reads and writes:
                        EDEVPROD> alter system set statistics_level=TYPICAL scope=both;

                        System altered.

                        EDEVPROD > select sum(value),statistic_name,owner||'.'||object_name
                        2 from v$segment_statistics
                        3 where upper(statistic_name) like '%WRITE%' or
                        4 upper(statistic_name) like '%READ%' or
                        5 upper(statistic_name) like '%LOGICAL%'
                        6 group by statistic_name,owner||'.'||object_name
                        7 order by sum(value);

                        SUM(VALUE) STATISTIC_NAME OWNER||'.'||OBJECT_NAME
                        ---------- ---------------------- -----------------------
                        249477 physical reads GUIDE_PROD.SAVED_FOLDERS
                        251904 logical reads SYS.I_OBJAUTH2
                        286880 logical reads SYS.I_SYSAUTH1
                        301760 logical reads SYS.DUAL
                        307696 logical reads SYS.I_ARGUMENT2
                        348992 logical reads SYSTEM.AQ$_QUEUE_TABLES
                        361168 logical reads GUIDE_PROD.XPKSEARCH_KEYWORDS
                        463728 logical reads SYS.OBJ$
                        639504 logical reads GUIDE_PROD.SRATING_RATINGS
                        668688 logical reads GUIDE_PROD.REPLOG_MIG
                        8333408 logical reads GUIDE_PROD.SAVED_FOLDERS
                        8516864 logical reads GUIDE_PROD.SYS_C004048

                        Logical reads burn up cpu, and can be helped by adding indexes and tuning sql. It is rare but the cpu can be impacted by too many indexes on inserts and that can be helped by removing indexes or removing writes.

                        Physical reads burn up I/O and that can be helped by better partitioning, better indexing, purging historic rows and tuning sql. If a sql burn up a lot of I/O on a small table, it is because the query is not run frequently enough to stay in memory, and you have to cache small lookup tables to avoid the reads with "alter table scott.emp cache;"

                        #8) Look at what is taking up most of the memory and work to tune those reads and writes:

                        EPANAY9 > SELECT COUNT(*)*8192/1024/1024 meg_in_memory,
                        2 o.object_type,o.OBJECT_NAME Object_in_Memory
                        3 FROM DBA_OBJECTS o, V$BH bh
                        4 WHERE o.DATA_OBJECT_ID = bh.OBJD
                        5 GROUP BY o.OBJECT_NAME,o.object_type
                        6 having COUNT(*)*8192/1024/1024>100
                        7 ORDER BY COUNT(*);

                        ------------- ------------------ ----------------------
                        120 INDEX SYS_C004290
                        138 INDEX XPKLOCATIONS
                        148 INDEX XPKENTITIES
                        215 TABLE ENTITIES
                        262 TABLE USERS
                        267 TABLE LOCATIONS
                        1405 TABLE REVIEWS

                        In this case the reviews table needs better indexing or a purge or rows. If the table is partitioned, sometimes recreating the partitions smaller will help.

                        9) Look at when you do the most log swaps, and add the append hint and nologging to create and insert statements in intermediate (work) tables that do not need to be replicated or restored vi the redo logs.

                        EMUSH9 > select to_char(first_time,'MM-DD-RRRR HH24:MI:SS')
                        2 "swaps_last_day"
                        3 from v$loghist where first_time > sysdate-1;

                        05-22-2007 12:47:02
                        05-22-2007 12:58:35
                        05-22-2007 13:11:06
                        05-22-2007 13:22:29
                        05-22-2007 13:33:39
                        05-22-2007 13:44:32
                        05-22-2007 14:12:47
                        05-22-2007 15:05:42
                        05-22-2007 18:19:47
                        05-22-2007 18:31:54
                        05-22-2007 18:43:12
                        05-22-2007 18:54:42
                        05-22-2007 19:08:02
                        05-22-2007 19:19:51
                        05-22-2007 19:37:17
                        05-22-2007 19:49:57
                        05-22-2007 20:02:12
                        05-22-2007 20:14:28
                        05-22-2007 20:25:53
                        05-22-2007 20:39:32
                        05-22-2007 20:54:16
                        05-22-2007 21:12:06
                        05-22-2007 23:05:08
                        05-23-2007 05:32:15
                        05-23-2007 06:25:37
                        05-23-2007 07:40:25
                        05-23-2007 07:51:29
                        05-23-2007 08:04:13
                        05-23-2007 08:17:07
                        05-23-2007 08:29:35
                        05-23-2007 08:40:27
                        05-23-2007 09:07:18
                        05-23-2007 10:51:10
                        05-23-2007 11:13:20

                        34 rows selected.

                        In this database, Between 6 and 8 in the evening a job does a lot of log swaps, also between 7:30 and 9 in the morning. These jobs should be looked at to reduce the redo logs using
                        insert /*+ APPEND */ into owner.intermediate_table nologging select * from some_other_table; and
                        create table owner.intermediate_table nologging as select * from some_other_table;
                        • 9. Re: R12 on Oracle linux: how to tune the R12?
                          Oracle has not used db_block_buffers in an APPS database since 8i. Now all memory pools are automatically tuned when the sga_target initialization parameter is set. At least if db_cache_size is used one can have the db_cache_advice performance views populated.

                          The poster had some under sized machine and wanted to reduce resources allocated because the system was painfully slow when the R12 applications processes were started causing top and sar output to not match, probably because it was taking forever for the commands to return.
                          • 10. Re: R12 on Oracle linux: how to tune the R12?
                            Hi, i just want to tag this in my index list