Q:

I have a client that has a data warehouse with Sun servers, Hitachi storage and an Oracle database. It seems that there may be bottleneck problems, but not on the Oracle side. Do you have any guidelines, check lists or recommendations to research this problem?

A:

Sid Adelman’s Answer: These are some of the questions you will want to consider:

  • How do you know Oracle is not involved in the problem?
  • What are you measuring?
  • How are you measuring?
  • Is someone responsible for monitoring performance?
  • What are the factors that tell you about the potential bottleneck?
  • Is the bottleneck on the ETL side or the query side or both?
  • What’s happening to response time?
  • Do you have any standard queries whose optimal response time is known? What’s happening to those response times?
  • What is your CPU utilization?
  • What is your disk utilization?
  • How are the disks configured? Is it an optimal configuration?
  • What’s the workload? What types of queries are being run?
  • Is anything else running on the server?
  • Is the network adequate?

Chuck Kelley’s Answer: I don’t know how you can say that it is not Oracle, since Oracle is dependent on the hardware. However, I would first find out what the real problem is. Is it CPU saturation? Disk I/O saturation at one or more drives? What about the controller between the Hitachi and Sun Servers? When you have figured this out, you need to find the cause. My bet is something within Oracle that is causing. Not because Oracle is bad but because something has to be causing the problems. If it is CPU, is the SGA too large? If it is disks, then are there too many sequential scans or is there a "paging" in the SGA (pins or hits). Regardless of whether the problem is a data warehouse or an operational system, the procedures will be the same.

Clay Rehm’s Answer: Before you assume that your existing hardware or software is the problem, have you looked at outside influences such as the network or other applications? The bandwidth of your existing network could be the problem – you may be competing with other resources and applications. I would include support personnel from other areas to see what ideas they can provide.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access