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