FAQ
Currently our production/test/development databases all reside on the
same 2 cpu RH4 machine. Our database environment is growing at a steady
pace. Over the last month we have encountered several situations where
a developer doing OLAP development in the dev database has kicked
something off that completely saturates the cpu's and brings our 2
production database to it's knees.


I have been arguing since I got here there we need to isolate dev/test
from prod. I've been told over and over that we won't be acquiring
additional licenses for new hardware (although this may soon change). I
have suggested the possibility of removing a cpu from our production
machine and installing Oracle on another single cpu system so that we
could work with our current licensing agreement.


The developers are understandably worried about taking resources away
from the production databases. I've looked at the db load and host
(through top) and almost always see the cpus as being ~75% idle with the
exception of the end of the month where a majority of our db activity
occurs.


I used to use yapp from miracle to graph db resource usage over time
but I see that there is only an 8i and 9i version. We are on 10gR2.


How is everyone keeping track on general resource usage by databases and
how are they presenting it to their managers?


Thanks,
Chris

CONFIDENTIALITY NOTICE:

This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
communication in error, please notify us immediately by email reply.

Search Discussions

  • Michael McMullen at Sep 28, 2006 at 2:15 pm
    Nice, a company with a market cap of 24.7 billion dollars won't spring for
    another licence. You would almost think that they would have an unlimited
    corporate licence. But it's probably the division you are in, I'm guessing
    it's not revenue earning.

    Mike
  • Dennis Williams at Sep 29, 2006 at 1:21 pm
    Chris,

    I would argue that this is a people problem rather than a technical problem.
    You are correct that this is not an ideal solution. I don't think OLAP
    developers should be creating stuff that drives the system to its knees.
    That is poor development practice. Some day they will release one of
    these bad boys into production. Talk to them. Educate them. If that doesn't
    work, you can put various quotas on them. Especially emphasize that it is
    critical they don't do something dumb during the month-end processing. Send
    out a monthly reminder. Back in the olden mainframe days we all had to share
    one system, and etiquette prevailed.

    I don't know of a tool, but would suspect you could put something
    together using Unix tools such as ps, awk, perl, etc. Then you won't be
    dependent on a vendor, but can perform your own upgrades.

    While you're at it, examine that month end load and see if any tasks
    can be shifted in time to free up more resources.

    Dennis Williams
  • Stephens, Chris at Sep 29, 2006 at 2:11 pm
    The olap stuff is development. I can't come down on them for trying
    stuff out. They need to be able to do that. The problem is that our
    development/test databases are on the same machine and there is no way
    that i know of to limit percentages of server resources to specific
    databases on Red Hat 4. ...everyone (developers here) keeps saying that
    resource manager can do this but that only works within databases.


    I'm looking to gather ammunition for a change in our server
    architecture.


    Chris

    From: Dennis Williams
    Sent: Friday, September 29, 2006 8:21 AM
    To: Stephens, Chris
    Cc: ORACLE-L
    Subject: Re: database resource usage

    Chris,


    I would argue that this is a people problem rather than a technical
    problem. You are correct that this is not an ideal solution. I don't
    think OLAP developers should be creating stuff that drives the system to
    its knees. That is poor development practice. Some day they will release
    one of these bad boys into production. Talk to them. Educate them. If
    that doesn't work, you can put various quotas on them. Especially
    emphasize that it is critical they don't do something dumb during the
    month-end processing. Send out a monthly reminder. Back in the olden
    mainframe days we all had to share one system, and etiquette prevailed.

    I don't know of a tool, but would suspect you could put something
    together using Unix tools such as ps, awk, perl, etc. Then you won't be
    dependent on a vendor, but can perform your own upgrades.

    While you're at it, examine that month end load and see if any
    tasks can be shifted in time to free up more resources.


    Dennis Williams

    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.
  • Dennis Williams at Sep 29, 2006 at 2:23 pm
    Chris,

    Okay, you answered my question while I was typing it. I think your mission
    is to tell management that if they are going to develop a proper data
    warehouse, they should plan to purchase another server.

    I used to work for a tightfisted organization that resented spending
    money for anything. They were planning to share an existing transaction
    application server with their data warehouse. Once they realized how spikey
    the load on a DW is, I never saw them open their pocketbook so fast. They
    had that new server in there in a couple of days and were demanding that I
    get Oracle installed and the database moved over there pronto.

    I don't think most monitoring tools will help you. But you must
    immediately read Tim Gorman's paper: Scaling to Infinity immediately. This
    can be found at: http://www.evdbt.com/papers.htm
    Make sure these guys design this DW right from the ground up.

    Dennis Williams
    On 9/29/06, Stephens, Chris wrote:

    The olap stuff is development. I can't come down on them for trying
    stuff out. They need to be able to do that. The problem is that our
    development/test databases are on the same machine and there is no way that
    i know of to limit percentages of server resources to specific databases on
    Red Hat 4. ...everyone (developers here) keeps saying that resource manager
    can do this but that only works within databases.

    I'm looking to gather ammunition for a change in our server architecture.

    Chris

    ------------------------------
    *From:* Dennis Williams
    *Sent:* Friday, September 29, 2006 8:21 AM
    *To:* Stephens, Chris
    *Cc:* ORACLE-L
    *Subject:* Re: database resource usage


    Chris,

    I would argue that this is a people problem rather than a technical
    problem. You are correct that this is not an ideal solution. I don't think
    OLAP developers should be creating stuff that drives the system to its
    knees. That is poor development practice. Some day they will release one of
    these bad boys into production. Talk to them. Educate them. If that doesn't
    work, you can put various quotas on them. Especially emphasize that it is
    critical they don't do something dumb during the month-end processing. Send
    out a monthly reminder. Back in the olden mainframe days we all had to share
    one system, and etiquette prevailed.
    I don't know of a tool, but would suspect you could put something
    together using Unix tools such as ps, awk, perl, etc. Then you won't be
    dependent on a vendor, but can perform your own upgrades.
    While you're at it, examine that month end load and see if any tasks
    can be shifted in time to free up more resources.

    Dennis Williams

    CONFIDENTIALITY NOTICE:
    This message is intended for the use of the individual or entity to which
    it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the
    reader of this message is not the intended recipient or the employee or
    agent responsible for delivering this message to the intended recipient, you
    are hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.
    --
    http://www.freelists.org/webpage/oracle-l
  • Richard J. Goulet at Sep 29, 2006 at 1:27 pm
    Question & I'll admit to not having deeply read the previous posts on
    this, but have you checked out the resource management capabilities of
    the database? It may not be feasible if the OLAP stuff is using the
    same username as the OLTP user which I would submit is bad practice in
    the first place. And since resource profiles can be switched on the fly
    that may alleviate end of month processing problems as well.




    Dick Goulet, Senior Oracle DBA

    45 Bartlett St Marlborough, Ma 01752, USA
    Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

    RGoulet_at_kanbay.com
    : POWERING TRANSFORMATION



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, September 29, 2006 9:21 AM
    To: chris_stephens_at_admworld.com
    Cc: ORACLE-L
    Subject: Re: database resource usage

    Chris,


    I would argue that this is a people problem rather than a technical
    problem. You are correct that this is not an ideal solution. I don't
    think OLAP developers should be creating stuff that drives the system to
    its knees. That is poor development practice. Some day they will release
    one of these bad boys into production. Talk to them. Educate them. If
    that doesn't work, you can put various quotas on them. Especially
    emphasize that it is critical they don't do something dumb during the
    month-end processing. Send out a monthly reminder. Back in the olden
    mainframe days we all had to share one system, and etiquette prevailed.

    I don't know of a tool, but would suspect you could put something
    together using Unix tools such as ps, awk, perl, etc. Then you won't be
    dependent on a vendor, but can perform your own upgrades.

    While you're at it, examine that month end load and see if any
    tasks can be shifted in time to free up more resources.


    Dennis Williams
  • Stephens, Chris at Sep 29, 2006 at 1:59 pm
    the problem is that the olap stuff is running in one database and the
    oltp stuff on another. ...resource manager only works within databases
    correct?

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Richard J. Goulet
    Sent: Friday, September 29, 2006 8:28 AM
    To: ORACLE-L
    Subject: RE: database resource usage

    Question & I'll admit to not having deeply read the previous posts on
    this, but have you checked out the resource management capabilities of
    the database? It may not be feasible if the OLAP stuff is using the
    same username as the OLTP user which I would submit is bad practice in
    the first place. And since resource profiles can be switched on the fly
    that may alleviate end of month processing problems as well.




    Dick Goulet, Senior Oracle DBA

    45 Bartlett St Marlborough, Ma 01752, USA
    Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

    RGoulet_at_kanbay.com
    : POWERING TRANSFORMATION



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, September 29, 2006 9:21 AM
    To: chris_stephens_at_admworld.com
    Cc: ORACLE-L
    Subject: Re: database resource usage

    Chris,


    I would argue that this is a people problem rather than a technical
    problem. You are correct that this is not an ideal solution. I don't
    think OLAP developers should be creating stuff that drives the system to
    its knees. That is poor development practice. Some day they will release
    one of these bad boys into production. Talk to them. Educate them. If
    that doesn't work, you can put various quotas on them. Especially
    emphasize that it is critical they don't do something dumb during the
    month-end processing. Send out a monthly reminder. Back in the olden
    mainframe days we all had to share one system, and etiquette prevailed.

    I don't know of a tool, but would suspect you could put something
    together using Unix tools such as ps, awk, perl, etc. Then you won't be
    dependent on a vendor, but can perform your own upgrades.

    While you're at it, examine that month end load and see if any
    tasks can be shifted in time to free up more resources.


    Dennis Williams

    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.
  • Dennis Williams at Sep 29, 2006 at 2:15 pm
    Chris,

    One question. When you say you have some OLAP developers, what is the plan?
    Are they just developing some reports, or is your organization developing a
    proper data warehouse? If so, they will want to get a separate server in the
    future.

    Dennis Williams
  • Stephens, Chris at Sep 29, 2006 at 2:24 pm
    full blown olap application. ...yes, i agree. we need another server.


    I am trying to graph out database resource usage and correlate that with
    server resource usage in order to justify this. ...i'm hoping there is
    something like the Miracle yapp stuff that will do that for me. :)

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, September 29, 2006 9:15 AM
    To: rgoulet_at_kanbay.com
    Cc: ORACLE-L
    Subject: Re: database resource usage

    Chris,


    One question. When you say you have some OLAP developers, what is the
    plan? Are they just developing some reports, or is your organization
    developing a proper data warehouse? If so, they will want to get a
    separate server in the future.


    Dennis Williams

    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.
  • James Foronda at Sep 29, 2006 at 2:56 pm
    Chris,
    Currently our production/test/development databases all reside on the
    same 2 cpu RH4 machine. Our database environment is growing at a
    steady pace. Over the last month we have encountered several
    situations where a developer doing OLAP development in the dev
    database has kicked something off that completely saturates the cpu's
    and brings our 2 production database to it's knees.
    I'm looking to gather ammunition for a change in our server
    architecture.
    I know that you are presently using RH4 but let me present you with an
    alternative.

    Solaris 10 has a virtualization feature called Zones. One of the main
    objectives of Zones is *isolation*. Oracle is certified to run on
    Solaris Zones.

    In the situation you are describing above, the 2 databases can be placed
    in two separate Zones. Then, using Solaris resource management (OS
    level, obviously), the resources that a zone can use can be CAPPED. For
    example, you can cap the development zone to use only one CPU if the
    production server is running. That way, it will never hog all the CPU
    of that entire box.

    In all likelihood, Solaris 10 will run on your machine running Redhat
    right now.

    I'm planning to make a few blog posts about Solaris Zones but I have not
    gotten to it yet. In the meantime, there are some pointers here:

    http://jforonda.vintarinian.com/?p=23

    James
  • James Foronda at Sep 29, 2006 at 5:53 pm
    Chris,

    I think I misread your initial post. I thought that you only had 2
    databases. Reading your initial post again, it looks like you have 4
    databases on a single box (2 prods, 1 test, 1 development).

    Solaris Zones can still handle this situation. You can create 4 zones
    and put a database on each zone. CPU resource management will still
    work because CPU resources are defined in fractions of a CPU
    (granularity is 1% of a CPU resource, if I'm not mistaken). So it is
    possible to assign 50% of a CPU to dev, another 50% of a CPU to test and
    the other CPU goes to the 2 production databases.

    The other thing I failed to mention is that if the CPUs are not being
    used, a Zone can use *all* the CPU resources that are available. The
    CPU cap can be looked at as some sort of guarantee of resources. For
    example, if all your databases are busy with the CPUs, each of the Zones
    are guaranteed to have their defined share. If only one Zone is busy,
    it can use the 2 CPUs.

    I hope that clarifies it a little bit.

    James

    James Foronda wrote:
    Chris,
    Currently our production/test/development databases all reside on the
    same 2 cpu RH4 machine. Our database environment is growing at a
    steady pace. Over the last month we have encountered several
    situations where a developer doing OLAP development in the dev
    database has kicked something off that completely saturates the cpu's
    and brings our 2 production database to it's knees.
    I'm looking to gather ammunition for a change in our server
    architecture.
    I know that you are presently using RH4 but let me present you with an
    alternative.

    Solaris 10 has a virtualization feature called Zones. One of the main
    objectives of Zones is *isolation*. Oracle is certified to run on
    Solaris Zones.

    In the situation you are describing above, the 2 databases can be placed
    in two separate Zones. Then, using Solaris resource management (OS
    level, obviously), the resources that a zone can use can be CAPPED. For
    example, you can cap the development zone to use only one CPU if the
    production server is running. That way, it will never hog all the CPU
    of that entire box.

    In all likelihood, Solaris 10 will run on your machine running Redhat
    right now.

    I'm planning to make a few blog posts about Solaris Zones but I have not
    gotten to it yet. In the meantime, there are some pointers here:

    http://jforonda.vintarinian.com/?p=23

    James
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Richard J. Goulet at Sep 29, 2006 at 3:22 pm
    Stephen,


    Have you ever heard of a product called Bez Prophet for Oracle?
    It's marketed by a company called BEZ Systems (
    http://www.bez.com/bez-oracle.htm). Having used the product before for
    just what your doing I can highly recommend it. You'll probably have to
    live with your current setup for about a month during which time the
    Prophet accumulates data on what your databases are doing. Thereafter
    it can predict, with 80 to 90% accuracy, what will happen is a number of
    different scenario's you feed it. Including separating out the OLAP
    instance. It's written by a serious bunch of "propeller heads" who are
    DEEP into system queues, io stats, etc.




    Dick Goulet, Senior Oracle DBA

    45 Bartlett St Marlborough, Ma 01752, USA
    Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

    RGoulet_at_kanbay.com
    : POWERING TRANSFORMATION



    From: Stephens, Chris
    Sent: Friday, September 29, 2006 10:25 AM
    To: oracledba.williams_at_gmail.com; Richard J. Goulet
    Cc: ORACLE-L
    Subject: RE: database resource usage

    full blown olap application. ...yes, i agree. we need another server.


    I am trying to graph out database resource usage and correlate that with
    server resource usage in order to justify this. ...i'm hoping there is
    something like the Miracle yapp stuff that will do that for me. :)

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, September 29, 2006 9:15 AM
    To: rgoulet_at_kanbay.com
    Cc: ORACLE-L
    Subject: Re: database resource usage

    Chris,


    One question. When you say you have some OLAP developers, what is the
    plan? Are they just developing some reports, or is your organization
    developing a proper data warehouse? If so, they will want to get a
    separate server in the future.


    Dennis Williams

    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to
    which it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the
    reader of this message is not the intended recipient or the employee or
    agent responsible for delivering this message to the intended recipient,
    you are hereby notified that any dissemination, distribution or copying
    of this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 28, '06 at 1:41p
activeSep 29, '06 at 5:53p
posts12
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase