Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Which do you trust? - command line or OEM?

Status
Not open for further replies.

aking

Technical User
Aug 11, 2002
112
GB
Hi.
I'm running Oracle 10g on windows 2003.
Which is more trustworthy - the command line or Enterprise Manager?
Enterprise Manager has recommended that i shrink various indexes and tables. It makes the same recommendations even after i have shrunk all the objects it suggests.
I couldn't see a way from within OEM of finding the size of anything. So I ran a select statement on the command line querying the dba_segments table so that i could see what sizes the indexes and tables were so i could monitor if Enterprise Manager segment advisor was really doing anything.
The sizes of tables and indexes reported by segment advisor are completely different from the sizes reported in my select statement.
e.g. a table segment advisor claims to be 110MB with 50MB reclaimable space is reported as 2MB in my select statement!

So i'm wondering if there is a definitive way of getting the size of an oracle object? Whether it's a good idea to trust segment advisor or maybe i should just ignore its recommendations? And if there is a way (either via OEM or the command line) to see that a shrink job has really done anything?
thanks.
ps. this was select statement i used:
SQL> SELECT tablespace_name, segment_name, segment_type, bytes, blocks, extents FROM dba_segments WHERE tablespace_name = 'PRODUCTION';



 
AKing,

I personally use (and trust) the same SQL statement that you posted...run from the command line.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Are you sure the space advisor job has run since your reorgs? It's only scheduled once per day, so you either have to wait a day for the next execution or run a special ad-hoc job to gather fresh segment statistics. Otherwise it's completely normal that the segment space advisor report doesn't reflect the result of your reorgs.

Looking at one of my own databases, I see that bytes/1024/1024 selected from dba_segments matches the "Allocated Space (MB)" in the segment space advisor report rounded to two decimal places. So I think it's rather unlikely that you have spotted an actual bug in OEM.
 
I always trust the command line and recommend it to anybody who will listen. GUI tools like OEM are fine and good, but if you rely on them too long, you forget your commands, tables, views, and everything else that is useful (and justifies hiring you instead of a mouse-clicker). The probability of something causing a GUI to go belly-up in the ditch is incredibly higher than command line. In a pinch, command line will be there regardless of the OS, and it's much less prone to crashing or sucking your machine's resources dry. If you do rely heavily on a GUI application, at least take a peek now and again at the SQL it's generating. OK, end of rant!
 
Thanks guys.
i figured that the command line was the one to trust - even tho i am definitely (and proud to be!) a mouse clicker ;-)
Anyway I finally found the way in Enterprise Manager to see sizes of tables and indexes - quite a tortuous path to get to. So enterprise manager and my sql statement now agree, it's only segment advisor that's way off.
And I figure the answer is somewhere in what you said Karluk - even tho my segment advisor runs every night, and even tho i've run (succesfully) manual jobs as well, i don't think segent advisor is using up to date information.
When i can face it i'll ask metalink how to force segment advisor to gather fresh stats - as i guess there's some trick to it beyond just running an ad-hoc job (which i've done now countless times).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top