DBA Monitoring tools + fragmentation

[sonik]

Does anybody know of any reasonably priced DBA monitoring tools that offer you general DB performance monitoring views of a DB i.e. Cahch hits/misses, I/O, CPU etc. Plus tablespace de-fragmentation, tablespace sizing tools to. I know there is enterprise manager and SQL/Navigator products I was after other tools.

My next question what are the best steps a DBA should take to avoid fragmentation issues and utilimately having to export and import the whole tablespace.

Thanks

Ketan

 

[nigelmcguinness]

Read "How to stop defragmenting and start living: The Definitive Word on Fragmentation" - available from the Oracle web site in metalink.

The gist of it is - use uniform extent sizes for all tables and indexes - so that empty extents always get reused.

Also to realise that there is no point worrying about segments having multiple extents ( less than 1000 is OK)
there is no significant performance disadvantage.


A simple scheme is divide your segments into
small
medium
large

Then only have three extent sizes (init and next the same)
corresponding to each type of segment. Keep different sizes in separate tablespaces.

Read the article.

 

[sonik]

Thank you Nigel for your tips, I tried to find the document you suggested on Metalink but could not find it there in fact there has been a thread raised on metalink for just the same problem. Do you by any chance have the documnet yourself. If so would be kind enough to email to me ketan_soni@yahoo.com.

Regards

Ketan

 

[karluk]

Try the Internet DBA section of OTN. There are several interesting papers in the performance and scalability area, including the article on SAFE that Nigel is referring to. The link is

http://technet.oracle.com/deploy/performance/

 

[carp]

This was originally an article in Oracle Magazine; you might want to go to

http://www.oracle.com,

find Oracle Magazine Online, and then cruise through their archives.

 

[hacketta]

Quest Software offer loads of Oracle monitoring tools and they can be downloaded for evaluation purposes. Obviously they're not as cheap as SQL*Plus reports but much nicer to work with. If you're after scripts

http://www.orsweb.com

is always a good place to start.

I definately reckon the SAFE concept is the best to use if you don't want to worry about fragmentation.

 

[sethu]

Quest has a lot of problems creating core dumps and crashing the database. We stopped using it recently and they admit that lots of values like buffer cache hit ratio and all are wrong. The best tool is OEM by all means.

--Sethu