Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DB Maintenance and Monitoring Advice?

Re: DB Maintenance and Monitoring Advice?

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Sun, 29 Jun 2003 11:16:26 +1000
Message-ID: <3efe3e56$0$30568$afc38c87@news.optusnet.com.au>


"April Nine" <april94111_at_yahoo.com> wrote in message news:cdf76ce3.0306281525.46ca75d9_at_posting.google.com...

>
> Current State Info:
> How much disk space is Oracle currently using?

Once in a while type of thing. Unless AUTOEXTEND is on, in which case it should be monitored regularly.

> How much RAM is Oracle currently using?

Hmmm, not really needed. Check paging and swap size as part of your regular Unix stuff.

> How much CPU is Oracle currently using?

Not needed. Check run queue length, part of Unix.

> What is the log-switch rate?

Should always be very low, unless you have a very atypical database. Better handle this one as an alarm rather than a monitor. Just in case someone goes production with a default install.

>
> Growth Rate Info:
> What is the disk space growth rate?

Shouldn't be needed for Oracle unless AUTOEXTEND is on.

> What is the RAM use growth rate?

Do it as part of your normal Unix mon.

> What is the CPU use growth rate?

As above.

> What is the log switch growth rate?
>

Assuming correctly sized logs, it won't change dramatically. Better handle it as an alarm: set a value that shouldn't be passed and use "blink" fonts when it does.

> Recent Activity Info:
> Who has been logging into the DB recently?

What the heck for? Application function, not DBA. Mostly...

> What applications have been connecting to the DB recently?

As above.

> What DB objects have been created recently?

Production? There shouldn't be any or only temp stuff. Which should be in its own TS so it doesn't upset everything else. Maybe that's something to monitor?

> What heavy hitter SQL commands have been hitting the DB recently?

You'd need to define classes of "heavy hitter". CPU? Parsing? Cache? Disk? All can be monitored, but you can't just do it blanket.

> How do the heavy hitter SQL commands compare to historical heavy
> hitter SQL commands?

That is an intersting proposition. Subjected to the same constraints. Not something I'd like to be monitoring heavily, but could be useful in some circumstances. Like when developers tell you "nothing has changed" and your performance has suddenly gone down the drain?

> What do the SGA structures currently look like?
> (How large are they? What is in them?)

Not really needed as on-going monitoring, I'd say. Check value only. Move it to your "Current Status" section.

> How do the current SGA structures compare to historical SGA
> structures?

As above. 9i manages most of the SGA dinamically, based on a start max value. Don't dwell too much on this, it's one of the areas I suspect Oracle will make "out of bounds" for 10i and later.

>
> Also I'm interested in 'Maintenance'.
> I'd like to compile a list of Maintenance tasks a DBA typically does.

No. Don't even try to do maintenance with a monitoring tool. The number of situations and possible alternatives is just too high. All you need is for ONE of the actions to be the wrong one and you killed the usefulness of any tool. Leave it to the DBA. You need human intervention here, unless you are prepared to do some serious heuristics and applied AI. AND take the responsibility for "wrong" actions...

> So far I can only think of two Maintenance tasks:
>
> -Run a periodic backup (once a day or whatever).

Yes, that can be automated.

> -Do I have stale indexes which need to be rebuilt?

Hmm, I don't think so. But I'll let others chip-in on this one.

Just a few ideas more:

Do some work in isolating the file systems/devices that the db uses, then monitor disk queue lengths on it.

You should monitor the alert log and the free space inside the DB. And the TEMP use. And table scans.

If db has multiple buffer pools, then monitor usage. Another one is to monitor periodically which objects are mostly in cache. That helps anyone that has to implement multiple buffer pools. Only on demand, this one.

There is also some good work to be done for db stats. This is where you can apply some historical stuff in a useful way. There is little point in re-examining stats for objects that seldom change. On the other hand, objects that change a lot might benefit from having stats "set", rather than "calculated". And don't forget the new resource profiles in 9i. That can benefit from some specific monitoring and automation. Still early days IMHO for this one, but it may be the way to go.

Use a database instance to keep your historical stuff: it makes it incomparably easier to further enhance, as opposed to flat files.

Be prepared to configure your monitoring based on what the database has installed and configured. The Oracle RDBMS nowadays is a monster in size and applications. You don't want to be monitoring for datawarehouse if the target database is really OLTP. That sort of thing. You must have a set of monitoring criteria dependent on final use of the database and what is installed. Then your tool can be useful, instead of being one of the umpteen "me-toos" out there in the market.

--
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
Received on Sat Jun 28 2003 - 20:16:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US