This discussion is archived
9 Replies Latest reply: Feb 6, 2013 3:16 AM by Rob_J RSS

Oracle Performance Issue

div Newbie
Currently Being Moderated
Oracle : 11.2 EE.
OS : RHEL 5.x
Storage : VNX SAN.

We have been trying to determine a performance issue for our ETL process before we go live.
We are using external tables with data pump for extraction and load.

On server 1 (Dual Core QA)
Our etl process runs 15% faster.

On server 2 ( Quad Core Production)

Our test includes creating a database in production and running the etl process. We moved the Lun containing the datafiles from prod to test and using the same init.ora files, we brought up the database on server1. We find that server1 performs much faster than on server2.
We even switched out the HBA's from one server to the other, to remove any connectivity issue with the hardware. Even with that, we are still at a point where server2 performs slower than server 1.

We have narrowed down to the following from the AWR report (don't know how to post the whole report). It seems the reads are much faster on server 1 than server 2.

IOStat by Function summary
Server 1
Function    NameReads: Data   Reqs per sec   Data per sec   Writes: Data   Reqs per sec   Data per sec   Waits: Count   Avg Tm(ms)
Data Pump   1.1T              759.83         162.614        118.6G         236.56         17.1264        7063.3K        0.10


Server 2
Function    NameReads: Data   Reqs per sec   Data per sec   Writes: Data   Reqs per sec   Data per sec   Waits: Count   Avg Tm(ms)
Data Pump   1.1T              475.83         101/852        118.6G         141.41         10.7269        7065.1K        0.38
I know there is not much information here, but hopefully someone might have experienced this in the past and has a solution.
Any reply will be appreciated.

Thanks
Div.

Edited by: div on Feb 4, 2013 7:25 AM
  • 1. Re: Oracle Performance Issue
    rahulras Explorer
    Currently Being Moderated
    What are the SGA sizes on both servers?
    select pool, sum(bytes)/(1024 * 1024) size_mb from v$sgastat group by pool ;
  • 2. Re: Oracle Performance Issue
    damorgan Oracle ACE Director
    Currently Being Moderated
    Please read the FAQ and learn how to use
     tags to format listings and make them readable. That also will address how to post an AWR report though if you post an entire AWR report you can expect to be ignored. Post only relevant sections.
    
    After you format your listing perhaps it will be readable.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 3. Re: Oracle Performance Issue
    div Newbie
    Currently Being Moderated
    SGA : 4GB.
    PGA : 2Gb.

    We are toggling the database on 2 servers, with the same init.ora file.

    This is the extract from slower server2.

    SQL> select pool, sum(bytes)/(1024 * 1024) size_mb from v$sgastat group by pool ;

    POOL SIZE_MB
    ------------ ----------
    3213.70019
    java pool 16
    streams pool 16
    shared pool 768
    large pool 64



    Div.
  • 4. Re: Oracle Performance Issue
    823085 Newbie
    Currently Being Moderated
    Sounds like disk io issue, but can't really sure, recommanded you check V$IOSTAT_FILE and V$IOSTAT_NETWORK on both servers, compare to see if any significate differences.
  • 5. Re: Oracle Performance Issue
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Is filesystemio_options set to the same value on both systems?

    Cheers,
    Harry
  • 6. Re: Oracle Performance Issue
    div Newbie
    Currently Being Moderated
    Yes , all parameters are the same, we do not change anything and hence the complexity.
    Steps involved (overview).
    Shutdown database on server 1,
    Detach Lun on server 1
    Attach Lun on server 2
    Copy init.ora to server 2.
    Startup pfile=xx.ora
  • 7. Re: Oracle Performance Issue
    Rob_J Journeyer
    Currently Being Moderated
    What's the speed of each core on both servers?

    Also, post the key parts of the AWR from each run (server 1 and server 2) so we can see if there is anything of interest.

    Have a read of the [url https://forums.oracle.com/forums/help.jspa]FAQs so you can see how to use the code tag to make it easier to read.

    Edited by: Rob_J on Feb 4, 2013 4:08 PM
  • 8. Re: Oracle Performance Issue
    div Newbie
    Currently Being Moderated
    Server 1:
    CPU1 Intel(R) Xeon(R) CPU X5650 @ 2.67GHz Model 44 Stepping 2 2666 MHz Present 6
    CPU2 Intel(R) Xeon(R) CPU X5650 @ 2.67GHz Model 44 Stepping 2 2666 MHz Present 6

    Server 2
    CPU1 Intel(R) Xeon(R) CPU E5-4607 0 @ 2.20GHz Model 45 Stepping 7 2200 MHz Present 6
    CPU2 Intel(R) Xeon(R) CPU E5-4607 0 @ 2.20GHz Model 45 Stepping 7 2200 MHz Present 6
    CPU3 Intel(R) Xeon(R) CPU E5-4607 0 @ 2.20GHz Model 45 Stepping 7 2200 MHz Present 6
    CPU4 Intel(R) Xeon(R) CPU E5-4607 0 @ 2.20GHz Model 45 Stepping 7 2200 MHz Present 6

    Trying to get you more info on the awr.

    Thanks
    Div
  • 9. Re: Oracle Performance Issue
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    I've seen this before, where going from a faster CPU speed to a slower one, albeit it more of them, causes single threaded processes to run slower. You don't have as much power for that single threaded process to use. Even if it wasn't maxing out the CPU before, it might be now on that single thread.

    It's not definitely the answer but if everyhting else is the same, and I mean identical, then this has to be the only change and therefore the culprit. In this case you need to parallelise out the process.

    Cheers,
    Rob

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points