Monitoring and tuning database performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Si - 30 Dec 2005 15:35 GMT

I am writing some coursework for students that describes the tools used to
monitor and tune database performance in Oracle. I have experience of doing
this in MS SQL Server, but not in Oracle.

What tools do you guys use in order to achieve this in Oracle? For example
how do I find out the most common transactions? How do I monitor hardware
resource usage?

I am using Windows XP and Oracle 10g.

Reply to this Message

IANAL_VISTA - 30 Dec 2005 16:22 GMT

> I am writing some coursework for students that describes the tools
> used to monitor and tune database performance in Oracle. I have
> experience of doing this in MS SQL Server, but not in Oracle.
>
> What tools do you guys use in order to achieve this in Oracle?

SQL is the only/best choice.

> Forexample how do I find out the most common transactions?

SQL is the only/best choice.
Quantify "common".
Most executes?
Most LIO?
Most PIO?
http://tahiti.oracle.com
contains the whole Documentation set.
RTFM - specifically REFERENCE manual & in particular the "V$*" views.

> How do I monitor hardware resource usage?

What does this have to do with Oracle?

Reply to this Message

Joel Garry - 31 Dec 2005 02:01 GMT

Why are you writing coursework for students on a subject about which
you know nothing? I'm somehow both amused and offended.

The answer is so long and involved books have been written about it.
You might even google this group to find some of them.

If you weren't so completely clueless, I'd say start with the
performance guide in the docs. But I have to say, start with the
concepts manual. By the time you understand it, your course will be
long gone.

Sorry to be so negative, I'm usually an advocate of being nice to
newbies, but there is a huge backstory of people having to unlearn
other databases, and myth propagation, here. MS SQL and WINDOWS GUI's
advocates in particular have a reputation of vast and misleading
oversimplification of the issues involved. Some people have even gone
to the extreme of saying "why do you care what the most common
transactions are? Wouldn't you care more what transactions affect your
business the most?"

Grid Control has some pretty pictures, if that's what you want. If you
have metalink access, see
https://metalink.oracle.com/metalink/plsql/f?p=130:3:3296175892382194830::::p3_d
atabase_id,p3_docid,p3_show_header,p3_show_help,p3_black_frame,p3_font:NOT,13170
4.1,1,1,1,helvetica
for some scripts, some of which may also propagate myths.

Some people downplay Niemec's performance tuning book, but I think it
has some interesting insights. Milsap, Kyte and Lewis' books are
generally perceived to be the best (with good reason, IMO). Don't hurt
your back picking them up. You might want to start with Lawson's book
to pick up some administration basics.

Definitely avoid downloading any "free java tool" crap.

There are some commercial products that have their fans, for some
reason the few obvious flaws get magnified out of proportion, perhaps
because they cause an inordinate amount of needless work.

jg

Signature

@home.com is bogus.
http://catless.ncl.ac.uk/Risks/24.13.html#subj3

Reply to this Message