DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: sunfire performance problem

  1. #1
    Join Date
    Nov 2005
    Posts
    13

    sunfire performance problem

    Hi All,
    first timer here ..
    i have a sunfire 280 with 2 internal disks,
    run oracle 9i on it and get poor poor performance,
    one user logged to oracle and perform loop insert of 100000 rows
    (into not big table) takes couple of minutes (same loop on windows Oracle takes 20sec)
    have two internal disks - I know, not the best for ORacle,
    but still performance seems tooooo slow ...

    I can see io wait of around 50% on the unix machine.
    is there a known problem with internal disks of sunfire & oracle??
    (seen in few placesthat people complaining about same issue )
    thanks
    Sahar

  2. #2
    Join Date
    Apr 2003
    Posts
    353
    Cool down.

    What is your os environment?
    What is your oracle version.
    Anything below 9.2.06 has many bugs related to performance with sun.

    Take a statspack report during perf issue and analyse.

  3. #3
    Join Date
    Nov 2005
    Posts
    13
    hi engigi,
    am cooled down ..
    os : solaris 5.9
    oracle 9.2.0.4
    i did run statspack - and in top5 showed io - disk write.
    there is no performance issue since the only process on that instance are my own tests. - which does insert as described above.

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by Saharn
    hi engigi,
    am cooled down ..
    os : solaris 5.9
    oracle 9.2.0.4
    i did run statspack - and in top5 showed io - disk write.
    there is no performance issue since the only process on that instance are my own tests. - which does insert as described above.
    How have you distributed the file systems among the disks?
    Is there any disk sorts?
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Nov 2005
    Posts
    13

    disk s

    only two disks to the system ....so not much of split ...
    no sorts only one process in oracle (mine) doing insert into
    empty table
    when I run top I see 50% iowait - is this normal?
    no archive, even nologging for the table and tablespace .
    on the pc on the other hand ...just one disk, very simple machine ..
    which works much better.
    thanks

  6. #6
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by Saharn
    when I run top I see 50% iowait - is this normal?
    No!

    can you post the I/O wait statistics from the statspack report?
    "What is past is PROLOGUE"

  7. #7
    Join Date
    Nov 2005
    Posts
    13

    stastpack

    Hi DBasan .
    here are some statpack report:

    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event Waits Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    log buffer space 4,873 315 34.95
    log file parallel write 4,872 261 28.93
    db file parallel write 147 228 25.30
    db file sequential read 6,914 67 7.48
    CPU time 19 2.11

    Event Waits Timeouts Time (s) (ms) /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log buffer space 4,873 0 315 65 541.4
    log file parallel write 4,872 4,872 261 53 541.3
    db file parallel write 147 0 228 1550 16.3
    db file sequential read 6,914 0 67 10 768.2
    control file parallel write 251 0 6 22 27.9
    log file sync 8 2 3 390 0.9
    db file scattered read 135 0 2 14 15.0
    direct path write 11 0 0 23 1.2
    buffer busy waits 2 0 0 36 0.2
    control file sequential read 1,235 0 0 0 137.2
    direct path read 11 0 0 1 1.2
    process startup 1 0 0 14 0.1
    latch free 14 0 0 0 1.6
    LGWR wait for redo copy 50 0 0 0 5.6
    SQL*Net break/reset to clien 4 0 0 0 0.4
    SQL*Net message from client 116 0 2,014 17364 12.9
    wakeup time manager 24 24 700 29187 2.7
    jobq slave wait 22 21 65 2962 2.4
    SQL*Net message to client 115 0 0 0 12.8




    ^LBackground Wait Events for DB: OCT_UNIX Instance: octunix Snaps: 520 -524
    -> ordered by wait time desc, waits desc (idle events last)

    Avg
    Total Wait wait Waits
    Event Waits Timeouts Time (s) (ms) /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log file parallel write 4,873 4,873 261 53 541.4
    db file parallel write 147 0 228 1550 16.3
    control file parallel write 251 0 6 22 27.9
    log buffer space 91 0 2 25 10.1
    direct path write 11 0 0 23 1.2
    direct path read 11 0 0 1 1.2
    control file sequential read 107 0 0 0 11.9
    LGWR wait for redo copy 50 0 0 0 5.6
    db file sequential read 3 0 0 0 0.3
    rdbms ipc message 29,355 7,319 3,234 110 3,261.7
    pmon timer 255 255 745 2923 28.3
    smon timer 2 2 516 ###### 0.2

    notice I have configures BIG redo log so the switch won't be frequent

    thanks
    Sahar

  8. #8
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Check your data buffer and log buffer size and the hit ratio on them when you run the insert.

    run explain plan on the query to check the cost incurred, (take the optimizer setting in account) in windows and solaris should solve you the issue.
    "What is past is PROLOGUE"

  9. #9
    Join Date
    Nov 2005
    Posts
    13

    more info

    1) thhere is no plan - cause there is no query, it does insert in a loop of 100000 - rows .

    2) cache hi ratio is even to high ... see bellow

    I have tried to increase the log_buffer but even 5 mb !
    ( which is big enoug h )
    did not do the trick ....


    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 100.00 Redo NoWait %: 100.00
    Buffer Hit %: 99.43 In-memory Sort %: 100.00
    Library Hit %: 99.99 Soft Parse %: 99.96
    Execute to Parse %: 94.55 Latch Hit %: 99.99
    Parse CPU to Parse Elapsd %: 90.76 % Non-Parse CPU: 76.77

    Shared Pool Statistics Begin End
    ------ ------
    Memory Usage %: 94.28 95.81
    % SQL with executions>1: 80.94 87.31
    % Memory for SQL w/exec>1: 83.41 92.35


    thanks

    Sahar

  10. #10
    Join Date
    Apr 2003
    Posts
    353
    What is your log_buffer parameter value?
    Is your log files separated from other type of files?
    Is your log files configured in faster disk systems?

Posting Permissions

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


Click Here to Expand Forum to Full Width