Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    May 2008
    Posts
    20

    Unanswered: memory requirement for Oracle

    Hi,

    I have Oracle installed on Solaris 10 with the veritas setup on a T2000 Machine with 8GB of memory.

    I just wanted information as to achieve 500 CPs rate of insertion into the Oracle is 8GB memory sufficient? Because I am not able to achieve the rate.Will increasing the memory help us.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Because I am not able to achieve the rate
    What is the bottleneck which prevents sustaining desired rate?

    Until you precisely know where time is being spent, you can only guess at possible solutions.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2008
    Posts
    20
    Hi,

    I find lot of TX waiting which is one of the possible reasons.

    How can avoid these waits to get the performance.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I find lot of TX waiting which is one of the possible reasons.
    Waiting on which object(s)?

    let me guess....
    the table being INSERTed has a column which gets populated by a sequence & this column is indexed.

    Am I correct?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2008
    Posts
    28
    hi vaishalik,

    You need to focus on the subsystem that limits the throughput: disk.

    At low level, are you using disk arrays, SAN or internal disks?
    what RAID level are you using?
    How much disk cache have you configured (here you may use some memory)?

    At high level, check file multiplexing:
    don't put redo log files on heavy load filesystems
    distribute datafiles evenly
    is your DB in archive mode?

    Hope this helps..

  6. #6
    Join Date
    May 2008
    Posts
    20
    Hi anacedent,

    You had asked

    >I find lot of TX waiting which is one of the possible reasons.
    Waiting on which object(s)?

    Can u please let me know what you meant by the object it is waiting on ? Please give me some example.

    We have not indexed the field which is populated by a sequence.

    But we have 4 unique key constraints whic hare indexed.

    I got the Lock stats during the hang scenario I got the below stats:

    Lock Mode
    ---- ----
    SQ 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6
    Last edited by vaishalik; 05-15-08 at 06:15.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
    id1, id2, lmode, request, type
    FROM V$LOCK
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    ORDER BY id1, request
    /
    SQL above will report which sessionsIDs & OBJECT_IDs involved with WAITs

    from http://www.psoug.org/reference/locks.html

    "'SQ','Sequence Enqueue',"

    I don't know how to reconcile your statement that your problem does not include a sequence when Oracle reports Sequence Enqueue
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    May 2008
    Posts
    20
    Hi,

    Regarding the veritas setup please find my answers below:

    At low level, are you using disk arrays, SAN or internal disks? --- Internal disks

    what RAID level are you using? ---- RAID 0

    How much disk cache have you configured (here you may use some memory)? -- We are not using disk cache.....The option is FALSE when we do the set up.

    Will the above configurations affect the performance.

  9. #9
    Join Date
    May 2008
    Posts
    20
    Hi anacedent,

    We have a key which is populated by the sequence number but we have not indexed that field.We have other fields which are indexed.

    When the sessions get hanged I am not able to take the ouput of the command which u had sent.

    But I used the script below and got the output as below:

    SCRIPT USED:

    * ENQUEUES9i.sql

    -- *************************************************
    -- Copyright ) 2005 by Rampant TechPress
    -- This script is free for non-commercial purposes
    -- with no warranties. Use at your own risk.
    --
    -- To license this script for a commercial purpose,
    -- contact info@rampant.cc
    -- *************************************************

    rem
    rem ENQUEUES9i.SQL
    rem Mike Ault
    rem
    ttitle 'Enqueues Report'
    spool enqueues
    prompt Enqueues
    col name format a25
    col lock format a4 heading 'Lock'
    col gets format 9,999,999 heading 'Gets'
    col waits format 9,999,999 heading 'Waits'
    col Mode format a4

    SELECT *
    FROM v$sysstat

    WHERE class=4
    ;

    SELECT chr(bitand(p1,-16777216)/16777215)||
    chr(bitand(p1, 16711680)/65535) "Lock",
    to_char( bitand(p1, 65535) ) "Mode"
    FROM v$session_wait
    WHERE event = 'enqueue'
    /

    Prompt Enqueue Stats

    select * from v$enqueue_stat where cum_wait_time>0
    order by cum_wait_time desc
    /
    spool off
    ttitle off
    Code Depot Username = reader, Password = arsenal
    Enqueues

    OUTPUT:


    SQL> @s.sql
    Enqueues

    Fri May 16 page 1
    Enqueues Report

    STATISTIC# NAME CLASS VALUE
    ---------- ------------------------- ---------- ----------
    22 enqueue timeouts 4 31
    23 enqueue waits 4 231609
    24 enqueue deadlocks 4 0
    25 enqueue requests 4 1032635
    26 enqueue conversions 4 65
    27 enqueue releases 4 1032541

    6 rows selected.


    Fri May 16 page 1
    Enqueues Report

    Lock Mode
    ---- ----
    CF 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6

    6 rows selected.

    Enqueue Stats

    Fri May 16 page 1
    Enqueues Report

    INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
    ---------- -- ---------- ----------- ---------- ----------- -------------
    1 SQ 320670 225011 320665 0 4804538
    1 CF 27631 30 27601 29 332381
    1 TX 242484 786 242520 0 2995
    1 FB 15843 4316 15836 0 2832
    1 HW 9604 1456 9602 2 1909
    1 US 4334 4 4334 0 8

    6 rows selected.



    Can you please let us know how can be avoid these waits to achieve the required performance rate.

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You need to provide some idea of what your app is doing.

    1. Do you have a number of processes doing inserts?
    2. What is the insert statement. Is it called from a procedure or is from an app server. If it is from an app server do you use batch insert?
    3. How have you created the sequence. Did you specify a CACHE value on the sequence which can make a big difference in some cases.

    Even given all of this information only you can find out if you can achieve 500CPS (whatever that is) as there are just too many unknowns for anyone else.

    Alan

  11. #11
    Join Date
    May 2008
    Posts
    20
    Hi Alan,

    Please find my answers below:

    1. Do you have a number of processes doing inserts?

    --- I have 16 sessions which are inserting into the database in paralle.

    2. What is the insert statement. Is it called from a procedure or is from an app server. If it is from an app server do you use batch insert?

    ---No we are not using any procedure nor app server for insertion.We are not using batch insert.

    --We bind the values to the insert statement and each session executed the insert statement one a time. But we commit the data only after says 4 seconds or 'x' number of records of inserted in the table

    3. How have you created the sequence. Did you specify a CACHE value on the sequence which can make a big difference in some cases.

    -- We have created the sequence but not specified any cache.I can try it now. One question like how much cache should I specify for 16 sessions writing in parallel to the table.

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) If you have 16 processes in parallel inserting you have a lot of scope for contention, at the hot end of the table and potentially the indexes aswell.
    Do you really need 16 processes? You may find a lower number actually increases throughput.

    2) If its not an app server or a database procedure what is doing the inserts?
    I would strongly advise use of batch inserts as throughput increases by a number of magnitudes when you use it. One of our java apps had throughput increase by a factor of 10!

    3) You should specify a large value for CACHE but you will have to experiment. Try a 100 initially and then try increasing it to see how much it helps.

    In order of things to do try 3. as a quick win but definitely try the other two. Also where is your source data coming from, another database or is a text file?

    Alan

  13. #13
    Join Date
    May 2008
    Posts
    20
    Hi Alan,

    We are getting the data from other application.
    AFter receiving the data we bind the values to the insert statement and then insert into the database.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >When the sessions get hanged I am not able to take the ouput of the command which u had sent.

    I have NO idea what this statement means.

    Too bad for all concerned that you don't simply use CUT & PASTE rather than trying to describe what you think you are doing & how Oracle responds.

    You should read the STICKY post at top to learn how to use <code tags>.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    May 2008
    Posts
    20
    Hi anaceden,

    You had asked me to check the command

    SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
    id1, id2, lmode, request, type
    FROM V$LOCK
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    ORDER BY id1, request
    /

    which provides information as which sessionsIDs & OBJECT_IDs involved with WAITs. But when my sessions are waiting for the lock I am not able to get the output of the above command.Even the execution of the above command is hanged. So I executed the script "ENQUEUES9i.sql" which gave me the below information


    Enqueues

    Fri May 16 page 1
    Enqueues Report

    STATISTIC# NAME CLASS VALUE
    ---------- ------------------------- ---------- ----------
    22 enqueue timeouts 4 31
    23 enqueue waits 4 231609
    24 enqueue deadlocks 4 0
    25 enqueue requests 4 1032635
    26 enqueue conversions 4 65
    27 enqueue releases 4 1032541

    6 rows selected.


    Fri May 16 page 1
    Enqueues Report

    Lock Mode
    ---- ----
    CF 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6
    SQ 6

    6 rows selected.

    Enqueue Stats

    Fri May 16 page 1
    Enqueues Report

    INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
    ---------- -- ---------- ----------- ---------- ----------- -------------
    1 SQ 320670 225011 320665 0 4804538
    1 CF 27631 30 27601 29 332381
    1 TX 242484 786 242520 0 2995
    1 FB 15843 4316 15836 0 2832
    1 HW 9604 1456 9602 2 1909
    1 US 4334 4 4334 0 8

    6 rows selected.


    To avoid the lock SQ I have recreated the sequence with noorder and have even set the cache to 100.

    But why do we see CF contention and how can we avoid the same?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •