I'm running Oracle EE 11.2 on Solaris 10.
I'm digging into the use of composite limit to limit some of the resources that the report users can use on our OLTP database.
Obviously, my intention is to prevent them from bringing down our OLTP database to its knees with some runaway or heavy reports.
We all know how easy it is for report writers using Crystal Reports or some other tool to develop ad-hoc queries, joining views to views, etc.
So, I figured I could create them a profile, (e.g., reports_profile) and limit their composite_limit.
I checked out a couple of web pages and then it just got more confusing.
Wondering if anyone has done this and figured it out and able to explain it easily.
Any help would be appreciated. I read the docs, but it is still confusing.
It has been a few days since I posted this thread. Is this so difficult that no one uses profiles to manage resources?
I suspect the option of using resource manager is easier, but if I set up services for someone to use a specific service to manage their resources, what is to stop them from using any other service. They can just put it in the tnsnames.ora and then they can use any resource they know is available.
I'm not sure that I understand the question you're asking. That may be why you're not getting any replies. Explaining everything possible about profiles would be a rather broad question that would be hard to accomplish in forums like this. If, having read the documentation, you have specific questions, we can probably help you out. If you need broad overviews, we can point you at documentation. But since we don't know what problem(s) you have with the documentation you've found, it's hard to know what "better" documentation to point you at.
Personally, my default would not be to use a composite limit, I would set either a CPU limit, an I/O limit, or both. Of course, you'd have to figure out what the appropriate value(s) are which would be very organization-specific.
Hi Justin. Sorry if I was unable to make my question clear.
The bottom line is that I would like to know specifically what value I should put for the composite limit to use for my new profile for the folks running reports so as not to bring our OLTP database to its knees during peak times.
The problem is that the composite limit is not something simple like a percentage of total resources. If it were, I could simply set composite limit to 30% of total resources and that would guarantee I would always have at least 70% of the available resources for the OLTP users.
So, researching documents such as:
It says: COMPOSITE_LIMIT Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of
Then, a link to Resource Cost:
It says: (The alter resource cost) statement lets you apply weights to the four resources. Oracle Database then applies the weights to the value of these resources that were specified for a profile to establish a formula for calculating total resource cost. You can limit this cost for a session with the
COMPOSITE_LIMIT parameter of the
Oracle Database calculates the total resource cost by first multiplying the amount of each resource used in the session by the weight of the resource, and then summing the products for all four resources. For any session, this cost is limited by the value of the
COMPOSITE_LIMIT parameter in the user's profile. Both the products and the total cost are expressed in units called service units.
Then, Specify the weight of each resource. The weight that you assign to each resource determines how much the use of that resource contributes to the total resource cost. If you do not assign a weight to a resource, then the weight defaults to 0, and use of the resource subsequently does not contribute to the cost. The weights you assign apply to all subsequent sessions in the database.
So, it all gets confusing determining the service units value to assign to composite limit. (Oracle did not make this easy which I assume is why they came out with resource manager.)
On another site, which I can not find now, it says that in order to come up with the right values for each resource of the composite limit formula, you have to monitor sessions over long periods of time, but it does not give details for what to monitor, or how to apply the values you capture.
Any help would be appreciated, or perhaps suggest how to use resource management instead. My concern with resource management is that if I understand it correctly, it is based on "services", and I don't know how to restrict users to only specific services that I would set up for them.