CPU Utilization Breakdown by DB

[Jim Mallett]

I need to be able to report back to Mgmt some system utilization numbers. I already have my sar reports in place, which is great, but need to also report back CPU utilization by database (Oracle).
Using Accounting, all db instances are bundled into one "oracle" line, so I can't see the breakdown by individual database. I understand why this is happening, but does anybody know how to make it report by oracle"dbname" rather than just oracle using Accounting?

Or....what do other people use if they need to break down CPU utilization by database? I'd love to be able to do this by day, week, month; but realistically just getting it broken out in real-time would be helpful.

Thanks...
Jim

[RAC_1]

Run different databases under diff names. Now under /var/opt/perf/param, put your application such as follows.

application - database1
user = database_user1
file = oracle*,*ora_*

Same for other databases.

Restart scopeux.
There might be way to do that in accounting also, but there also I think you will have to different on the basis of user id.

[TwoProc]

Jim, if you implement PRM it has a little bit of help - you display the ps command with information re the PRM group. I think there are other extension for it too...

[harry d brown jr]

Do you have glance/measureware and perfview ??

if not, well I'd say it's time to tell management that you need it to perform the tasks they have assigned you.

live free or die
harry d brown jr

[Jim Mallett]

I probably should have filled in some of the other blanks:

OS: HP-UX 11i

I do have GlancePlus and PRM at my disposal. PRM is not configured yet, part of this exercise was meant to be a precursor to configuring it.

Thx...Jim

[RAC_1]

I think, even if you configure PRM, you will come to the point when you will have to different databases on the basis of uid.

Not sure about this.

[TwoProc]

Jim, I believe the easiest answer here is to have each database owned by a different Oracle user. You can still administer them all via your "dba" group ID. Migration of a database to a new user is pretty straightforward (considering) and would give you great data as to what's going on in each database.

[Rita C Workman]

Well Hello Jim !

RAC mentions one option, presuming you have MeasureWare, you could breakout some things using the ~ /perf/parm file. If there is some uniqueness to each oracle, try and close in on what you can.

For that day/week/month thing, well your friend and mine, PW, developed a super fantastic webpage that pulls in MWA data that creates great graphs. We put it up on our systems and Mgmt doesn't call asking about performance anymore! This with perfview-and any fluxes in performance-and we can prove who/what/when & where.
....a picture says a thousand words....

[ shame they wait listed his presentation for this last year... ]

Rgrds,
Rita

[Peyman Javaheri · Accepted solution]

Hi Jim,

This is very simple:

- Run each oracle instance with unique process template names like ora_dbwr_TESTDB (and so on all processes of that instance)

- in /var/opt/perf/parm: seperate each DB with its name:
application = TESTDB
file = *TESTDB

- and the end of the /var/opt/perf/parm, put a line like:
application = other_oracle
file = ora*

regards,
peyman;

[Jim Mallett]

As far as the real time data is concerned the /var/opt/perf/parm file is working like a charm. Just as described above.

I'm messing with the /etc/prmconf file now and seeing some good results from 'prmanalyze -s prmid'. Just as I can with parm, I can group processes to form database groups.

Now it's just a matter of piecing it all together (ie: setting up cron for the accounting jobs- ckpacct, runacct, etc) and running reports against some long term data.

I'll keep this open till I finish this next week in case anybody has any refreshing thoughts....but thank you to everybody that has contributed so far. Hope all is well Rita.

Jim

[Stephen Serbe]

Ms. Rita,
Your Web page solution (or PW's) sounds interesting... where can I get a view? Is this a share-ware pkg or an OVO product (Service Reporter, etc.)?

Thanks!
Stephen

[Hein van den Heuvel]

Hey Jim,

How about asking Oracle instead of the OS.
Get some procedures in place as a framework:
- activate statspack on every instance.
- times_statistics is probably not need, just counters
- run a statspack snap after startup, befor shutdown.
- run a statspack snap once a week? day? hour?
- optionally roll up statspacks through queries or exports.

You may be able to use CPU time from the statspack, but why not take an easier number. Notably I would suggest BUFFERED GETS. Optionally toss in PHYSICAL IO COUNTS.
Now pro-rate the aggregate OS collected CPU cost for all oracles by buffered_gets (or executes, or usercalls, or sqlnetpackages, whatever tickles your imagination).

Statspack may be over the top. It may suffice to just run your own simple queries against V$SYSTAT. Check out the ORACLE DATABASE REFERENCE MANUAL. Appendix C: "Statistics Description". The table indicated whether timed_statistics is needed or not for a given counter.


Or how about looking at some externally visible attribute of Oracle. Admittedly only one thing comes to mind: The redo/archive log volume. You can probably tell how many bytes were written. This will be proportional to the database modification work (insert, update, delete) and may or might not be proportional to all the work. A read-mostly DB would not see its full share of system resource usage reflected, but that coudl be our little secret :-).

Just thinking out aloud,
Hein.

[Stephen Serbe]

Good thoughts! I was looking at a "mgmt" level view... especially a web view for managers. I'm using MW extracts to Oracle RDBMS for a relational repository that I can coorelate with other data (configuration and change). Always looking for a way (tools) to improve the process.

The trouble with our times is that the future is not what it used to be.
--poet Paul Valery

[Tim D Fulford]

Hi

I use MeasureWare and run Informix. With multiple instances of Informix what I do is split up the oninits by soft linking them to a "dummy" name. eg oninit-inst1 or oninit-inst2. This means in your parm file you can list all the application as
application = ifx-inst1
file = oninit-inst1
application = ifx-inst2
file = oninit-inst2
etc...

I also collect usage data from each Informix instance (select * from syspfofile); I'm sure Oracle has similar tables.

I find that this allows me to corolate usage spike, with say reductions in caching ot High IO etc. It is worth attcacking the problems from as many angles as possible.

Regards

Tim