Sign in / up

back to article How to tune your Oracle database's performance

It's the call that every Oracle database admin (DBA) dreads: the "too slow" call. Users, or their managers, may argue that online performance is so bad that it stops staff from doing their jobs. Batch jobs such as billing runs may run too slowly. Customers may be walking away from an Oracle-driven ecommerce site because pages …

COMMENTS

House rules Send corrections
This topic is closed for new posts.
  1. Anonymous Coward
    Anonymous Coward

    Tuning Pack

    Please remember that there are licensing issues on using the Performance and Tuning packs, at least on Enterprise ... and they will clobber you if you use them and they find out.

    TOAD optimisation is all well and good unless you modify the SQL query _at all_ ... in which case the optimisation can quite easily be a de-optimisation.

    Make sure you have indexes on major columns, make sure your tables are joined by foreign keys, make sure you "analyze" your tables, especially if you change their structures ... make sure you have a big enough SGA, and loads of RAM ...

    after that, pay for the licenses to use the Performance and Tuning Pack, and turn on the "Enterprise Manager", and you can sit in the dark and watch the nice graphs in "Top Activity" fretting quietly to yourself.

    2 0
    1. roselan
      Reply Icon
      Devil

      Re: Tuning Pack

      There is a shitload of features in oracle. Each version brings it's own layer, and it's own bag of issues. Triggers overhead, buffer groups, result cache latches, sql plan management hidden plans (and defaults), compilation level messing error lines, hprofiler inconsistent results, FAQ (fracking advanced queuing) etc.

      The cost based optimizer first aim, based on histograms, was to address skewed data. But even now it's wonky when data is extremely skewed, or when bind peeking is used. or involves date columns, or etc. It can be argued it's a design issue. As well, oracle can't seem to understand alone that data spread among blocks is critical to throughput.

      And yet sqlplus still doesn't support unicode chars, there is no tool to spread a query on 10 or 100 db and aggregate the result, sql developer is a nightmare. Editions are heavy. And I forget a truck of bothering stuff, or two.

      Then we can discuss spin gets counted as "on cpu" when oracle invoice their thing as "per cpu".

      But the main performance issue is usage. Most databases, sadly, do both oltp and olap. The first require response time, the second throughput. And both requirements are mutually exclusive.

      Funilly enough, the same debate happens at cpu level, and that's why there are things like hsa, phy, BIG.little, denver/kepler (not sure about nvidia), BOC/lic.

      In the database world this result in stuff like storm/hadoop and other multi-tier db configurations. When you know what data *really* needs acid transactions, you can design a database that is *real* fast. Things like cc payments are done through a webservice, and programatically. I have yet to see dmbs_xa used for that kind of stuff, so you lose db "acidity" anyway. But even if you know the overhead of logs, locks, writes and "vertical reads" (group by), there is no way way to implement these design concepts in a traditional rdbms.

      To split workloads with oracle, there is no easy solution. Read only standby database works unless you use mv or temp tables, dblinks unless you have blobs, and don't touch goldengate with a yard stick.

      I didn't have the chance to play with 12c/rac yet, but my guess is that it comes with it's lot of undocumented features (looking at it's architecture chart did frighten me. As much as parallel execute explain plan).

      Point is, it's doesn't work well out of the box, and even when everything is set up and monitored, it's still full of surprises. And most programmers people don't know jack about the sql world, they are lost when they have to open that box and look into it.

      Alternatives don't seem mature (the full immutable/in_memory "newsql" pack) or require such paradigm change that migration (not of data, but minds) is impossible, despise the cost advantage that can soon reach millions, per year. And I'm sure traditional competitors have an even bigger sum of bodies in the closet.

      /rant

      1 0
      1. Anonymous Coward
        Reply Icon
        Anonymous Coward

        Re: Tuning Pack

        RAC is pretty impressive if you have a RAC designed application, or can silo your existing middle tier so different parts of it connect to different nodes in the RAC. If you have connections updating the same data blocks across different nodes the whole thing bogs down as the interconnect gets saturated. Oh, and with RAC sequences have to be cached to get performance (for the same reason) so sequence populated columns are unique but sequences can no longer be used to order events as it is possible for sequence 11 to be used before sequence 1.

        Oracle is, really, a jack of all trades and a master of none.

        0 0
        1. Anonymous Coward
          Reply Icon
          Anonymous Coward

          Re: Tuning Pack

          "RAC is pretty impressive"

          Agree - pretty much the only reason to use Oracle instead of SQL Server these days for most requirements. And even then it's a very limited use case, SQL Server clustering can achieve sub 30 second failovers from point of failure...

          0 0
    2. Anonymous Coward
      Reply Icon
      Anonymous Coward

      Re: Tuning Pack

      We will address some licensing issues in another article in this series.

      0 0
  2. Bakana

    Tuning

    Personally, I've always found that the easiest way to "Tune" Oracle is to Switch to IBM's DB2 Database instead.

    [Snark Off]

    0 0
    1. Anonymous Coward
      Reply Icon
      Anonymous Coward

      Re: Tuning

      Ditto but to Microsoft SQL Server. You don't need an IBM boat anchor then.

      0 0
  3. PowerMan@thinksis

    Best tuning option is Power8

    Best way to tune Oracle is to run it on a Power8 server with IBM FlashSystem 840. Reduce Oracle licenses to 1/4th of any other platform at higher performance than x86 and SPARC eliminating I/O latency with Flash. Further, to the other persons point, Oracle nickel and dimes customers on every feature. DB2 v10.5 with BLU includes many of these features like compression, replication, HA, tuning, their RAC equivalent and more for less money. And, the software license includes the first year of software maintenance unlike Oracle where you pay 22% for year 1 plus the software license cost. If SQL Server also offers similar features and pricing that is great - gives customers more choices. For SMB shops maybe go the SQL Server route. For customers with demanding, enterprise needs there is nothing stronger than a Power8 server.

    0 0
    1. Anonymous Coward
      Reply Icon
      Anonymous Coward

      Re: Best tuning option is Power8

      "For customers with demanding, enterprise needs there is nothing stronger than a Power8 server."

      Except the shock you get when you see the TCO. IBM are a dying boat anchor vendor, so I can't see that too many people are going to want to go down that route. Most companies are trying to move away from Midrange / UNIX to more commodity type Wintel systems.

      For up to 3TB (6TB once 64GB Dimms ship) memory in a single system image, SQL Server / x86 is likely the best way to go for most companies.

      0 0
      1. PowerMan@thinksis
        Reply Icon

        Re: Best tuning option is Power8

        You would be right if this was Power6 days. It is not. With Power7 and now Power8 there are price parity options for TCA and when talking Oracle they dominate when it comes to TCO. Want to see how Oracle on Power is less than x86 ….. Read on! I'll pick a random x86 vendor that will let me get pricing from their website . I use list prices because they are consistent in all comparisons. Discounts can be applied but it doesn’t change the ratio of savings, just how much.

        .

        HP DL380 Gen8 - qty 1

        2 x 6 co @ 2.4 GHz E5-2440 totaling 12 cores

        128 GB Ram

        vSphere Enterprise Plus

        Assume Linux – pick your distro of choice

        3 year support

        No internal HDD - assuming USB boot

        2 x dual port 10 GbE

        2 x dual port Fibre

        All power cores, rail kit, misc

        $25,183 each server List price

        .

        .

        Oracle Licensing cost

        Enterprise Edition - $47,500 per core

        EE maintenance @ 22% per year - $10,450 / co

        RAC - $23,000 per core

        RAC maintenance @ 22% per year - $5,060 / co

        .

        .

        IBM’s latest Power8 server

        S824 Power8 server - qty 1

        (this is the 2 socket model although I just selected it with 1 socket)

        .

        8 x 4.15 Ghz Power8 cores

        256 GB Ram (need more Ram because I’m planning to host more VM’s)

        DVD

        Split backplane

        4 x SSD (building the way I would built it and not just to lower the price which I could do by using HDD)

        2 x dual port 10 Gbe adapters

        2 x dual port Fibre adapters

        AIX v7.1

        PowerVM Enterprise Edition

        3 year 24 x 7 maintenance

        $79,807 server list price

        .

        .

        Now the math and the comparisons!

        .

        Server: HP DL380

        Cost: $25,183

        qty of servers: 2

        Server cost: $50,366

        .

        # of cores in solution: 24 (2 x 12 co servers)

        Oracle Licensing Factor: .5

        # of cores needed for Oracle (actual): 5 (nice to know but doesn’t matter for licensing)

        Total Oracle Licenses required 12 because (24 * .5 = 12)

        .

        Oracle EE Lic cost: $570,000 (12 * $47,500)

        Oracle maint cost (3 yr): $376,200 (12 * $10,450 * 3)

        Oracle RAC Lic cost: $276,000 (12 * $23,000)

        Oracle maint cost (3 yr): $60,720 (12 * $5,060 * 3)

        Total x86 server + Oracle cost over 3 years: $1,333,286 (Add it all up)

        .

        .

        Server: S824

        Cost: $79.807

        qty of servers: 1

        Server cost: $79,807

        # of cores in solution: 8

        Oracle Licensing Factor: 1.0

        # of cores needed for Oracle (actual): 3 (it does matter here)

        Total Oracle Licenses: 3 (either in a dedicated or SPP with proper boundaries)

        Oracle EE Lic cost: $142,500 (3 * $47,500)

        Oracle maint cost (3 yr): $94,050 (3 * $10,450 * 3)

        Oracle RAC Lic cost: $ Not required

        Oracle maint cost (3 yr): NA

        Total Power server + Oracle cost over 3 years: $316,357

        .

        .

        The 3 year total cost of ownership for the x86 solution shown is $1 Million dollars more than a Power8 solution.

        .

        Somebody may question or say it isn’t fair or that it is convenient of me to just use 1 Power8 server whereas I am comparing it to 2 x HP x86 servers. Just in case, here are those numbers. Don’t want somebody to not like me accuse me of making things up ☺.

        .

        Using 2 x Power8 servers instead of 1 to compare:

        .

        Server: S824

        Cost: $79.807

        qty of servers: 2

        Server cost: $159,614

        # of cores in solution: 16 (2 * 8 cores)

        Oracle Licensing Factor: 1.0

        # of cores needed for Oracle (actual): 6 (because each server has 3 co in a Active / Active cluster)

        Total Oracle Licenses: 6

        Oracle EE Lic cost: $285,000 (same math as above)

        Oracle maint cost (3 yr): $188,100

        Oracle RAC Lic cost: $138,000

        Oracle maint cost (3 yr): $91,080

        Total Power server + Oracle cost over 3 years: $861,794

        .

        .

        For those who want everything equal the Power solution is still $470K less than the x86 and everything else I have said remains true.

        0 0
  4. Anonymous Coward
    Anonymous Coward

    Good I/O subsystem is key. Remember to look at 2 metrics. Throughput, which is important for

    full table scans and stats collection and latency which is important when reading an index (index reads are random small I/O requests). We use a pretty cool tool called Shuriken for performance monitoring of our Oracle databases and it exposes all this information (take a look at http://www.pninjas.com)

    0 0

  5. This post has been deleted by its author

This topic is closed for new posts.

Other stories you might like