• 11

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191


File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

We have an Oracle database of roughly 100 GB that runs on top of a Windows Server 2k3R2 VM in ESX in a 1 socket, 1 core configuration. Whenever it has to handle a major query it seems to get "pegged" for the duration of that query (50% - 75% CPU utilization) and in some cases limits functionality of applications that use it. The most typical case is when we run massive reports, the query alone will significantly slow down application performance while the query is being run, as if it is queuing the tasks up. My question is: will "upgrading" the VM to a 1 socket, 2 core configuration significantly improve this "pegging" issue?

      • 1
    • This is easy enough to test, right? Shutdown, modify VM parameters, boot, run reports, benchmark...

That depends. If the CPU utilization is from user, then the answer is most likely "yes". If the CPU utilization is from iowait, then you're wasting your money until you upgrade your storage system. If it's mostly from system, then you probably have a driver or hardware device that is taking up too many interrupt resources (network card, maybe?)

  • 6
Reply Report

I am pretty sure any upgrade in CPU (count) will have performance benefits, as long as you add these to the virtual machine also. Make sure you don't over commit though. That is, say you have 4 virtual machines on a 32 GB server and you assign each virtual machine 25 GB of RAM.

However in my opinion you are limiting yourself with oracle running in a virtual environment.

I would seriously consider running oracle on a dedicated server without the virtualisation layer. Oracle is quite a beast and needs all the resources it can get. Also use a hardware raid10 with at least 8 disks, the more disks the better. That also applies to the ESXi server.

  • 0
Reply Report
      • 1
    • Memory is not an issue - we enforce quotas that cap memory to physical limit, and memory is never "pegged" while the CPU is being pegged - no swapping seems to be occurring at the os OR hypervisor layers

This greatly depends on how much parallelizable is Oracle's work, i.e. on the specific query that is being run. Generally speaking, a DBMS is a highly parallel application, so it should benefit from having more CPUs; but some workloads might not benefit from it at all.

  • -1
Reply Report

Having more than one core for the database engine will help in performance. Oracle has several core processes which all have to compete on CPU time if there is too few of them. Additionally when you introduce capacity consuming user sessions (queries for example) competition gets worse. So, yes, it will help. Oracle is built to have and use a multi-CPU platform. Additionally if you run the application services in the same server, there is no question about it.

  • -1
Reply Report
    • @TomTom This is correct, in general. However, in this case we are told that the DBS size is 100G, there is only one vCPU and significant raise in CPU consumption during the query. These facts indicate that CPU power may be the bottleneck. However, since disk I/O plays a major role in databases, it would also be important to analyze the behavior of the database deeper for example with Statspack. That would also tell if the CPU really is what is needed.

Trending Tags