This discussion is archived
10 Replies Latest reply: Jan 14, 2009 6:28 AM by 649606 RSS

R12 on Oracle linux: how to tune the R12?

568906 Newbie
Currently Being Moderated
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?

regards

maratsafin
  • 1. Re: R12 on Oracle linux: how to tune the R12?
    mdtaylor Oracle ACE
    Currently Being Moderated
    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?
    568906 Newbie
    Currently Being Moderated
    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.

    maratsafin
  • 3. Re: R12 on Oracle linux: how to tune the R12?
    mdtaylor Oracle ACE
    Currently Being Moderated
    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
    http://blogs.oracle.com/schan/2007/03/01#a1258

    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?
    568906 Newbie
    Currently Being Moderated
    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'?

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

    top:
    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

    sar:
    Linux 2.6.18.8-0.1-default (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?
    568906 Newbie
    Currently Being Moderated
    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.

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

    fadi
    http://oracle-magic.blogspot.com
  • 8. Re: R12 on Oracle linux: how to tune the R12?
    580451 Newbie
    Currently Being Moderated
    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.

    DISK_SORTS MEMORY_SORTS PCT_DISK_SORTS
    ---------- ------------ --------------
    95 14030323 0

    AVERAGE_DISK_SORT_IN_MEG
    ------------------------
    241.61179

    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%.

    INSTANCE_N UPDAYS CONSISTENT BLKHIT PHYSRDS_PER_HOUR
    ---------- ------- ------------- ---------- ----------------
    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;

    AVERAGE_LOG_SWAPS_PER_HOUR
    --------------------------
    5.48333333

    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(*);

    MEG_IN_MEMORY OBJECT_TYPE OBJECT_IN_MEMORY
    ------------- ------------------ ----------------------
    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;

    swaps_last_day
    -------------------
    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?
    mdtaylor Oracle ACE
    Currently Being Moderated
    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?
    649606 Newbie
    Currently Being Moderated
    Hi, i just want to tag this in my index list