 | |
dbms_server_alert.tablespace_byt_free measures really KB?
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
dbms_server_alert.tablespace_byt_free measures really KB? -
02-10-2011
, 09:27 AM
#2
| |||
| |||
|
|
Hello out there, I tried to configure alerts for full tablespaces using dbms_server_alert.set_threshold. Since we do not have licensed diagnostics pack I cannot do this with dbconsole. I have to resort to the command line. Anyway, I want to get a warning when the tablespace has less than 1GB free space. So I issued the following command: begin * *dbms_server_alert.set_threshold(metrics_id = dbms_server_alert.tablespace_byt_free, * * * * * * * * * * * * * * * * * *warning_operator = dbms_server_alert.operator_le, * * * * * * * * * * * * * * * * * *warning_value => '1048576', * * * * * * * * * * * * * * * * * *critical_operator = dbms_server_alert.operator_le, * * * * * * * * * * * * * * * * * *critical_value => '204800', * * * * * * * * * * * * * * * * * *observation_period => 1, * * * * * * * * * * * * * * * * * *consecutive_occurrences => 1, * * * * * * * * * * * * * * * * * *instance_name => null, * * * * * * * * * * * * * * * * * *object_type = dbms_server_alert.object_type_tablespace, * * * * * * * * * * * * * * * * * *object_name => 'TS_TABLES'); end; / But now I get a critical warning saying tablespace TS_TABLES only has 8505 MB free space left. Therefor the question is: are the units of dbms_server_alert.tablespace_byt_free really KB as described in the documentation or are this MB as I suspect? Setting the thresholds to 1024 and 200 the alerts disappear but I want to be sure that I get warnings if there are fewer than 1GB left and not fewer than 1MB. In the latter case there would not be enough time to react. :-) This is Oracle 10.2.0.5 on Windows 2000. Regards, Lothar |
IF possible, set up a 'dummy' tablespace with less than 1 GB of
storage then redirect your alert to this tablespace and see what
happens with your various configuration settings. It would appear
that the documentation is incorrect, however you won't know that for
certain until you test this with a tablespace known to have less than
1 GB free.
David Fitzjarrell
#3
| |||
| |||
|
|
Hello out there, I tried to configure alerts for full tablespaces using dbms_server_alert.set_threshold. Since we do not have licensed diagnostics pack I cannot do this with dbconsole. I have to resort to the command line. Anyway, I want to get a warning when the tablespace has less than 1GB free space. So I issued the following command: begin * *dbms_server_alert.set_threshold(metrics_id = dbms_server_alert.tablespace_byt_free, * * * * * * * * * * * * * * * * * *warning_operator = dbms_server_alert.operator_le, * * * * * * * * * * * * * * * * * *warning_value => '1048576', * * * * * * * * * * * * * * * * * *critical_operator = dbms_server_alert.operator_le, * * * * * * * * * * * * * * * * * *critical_value => '204800', * * * * * * * * * * * * * * * * * *observation_period => 1, * * * * * * * * * * * * * * * * * *consecutive_occurrences => 1, * * * * * * * * * * * * * * * * * *instance_name => null, * * * * * * * * * * * * * * * * * *object_type = dbms_server_alert.object_type_tablespace, * * * * * * * * * * * * * * * * * *object_name => 'TS_TABLES'); end; / But now I get a critical warning saying tablespace TS_TABLES only has 8505 MB free space left. Therefor the question is: are the units of dbms_server_alert.tablespace_byt_free really KB as described in the documentation or are this MB as I suspect? Setting the thresholds to 1024 and 200 the alerts disappear but I want to be sure that I get warnings if there are fewer than 1GB left and not fewer than 1MB. In the latter case there would not be enough time to react. :-) This is Oracle 10.2.0.5 on Windows 2000. Regards, Lothar |
you in the long run - as jg said - this is a bug - see MOS Bug
5569515
Just curious, but are you doing any cleanup, archiving etc or just
letting it continue to grow? Database archiving and purging seems to
be overlooked in a LOT of shops. I recently reviewed a db that purged
a lot of data to recover space, however, because of the way that LMT
works, the space is not recovered until one does a SHRINK on the
objects within that tablespace. If one understands that once LMT
tablespace segments are allocated to an object they remain allocated
to that object until you "enable row movement then alter ... shrink or
alter ... shrink cascade", then you can make plans to clean them up.
This is especially true if the old, purged data contained very large
*LOBS. The previous DBA did not fully understand this, and just kept
adding more datafiles to the tablespace. When the "SHRINK" ran, they
recovered more than half of the database size. Adding a purge job
that runs daily - without doing the shrink, those objects are now
holding at a constant number of segments. Segment allocation in LMT
is not a cheap operation, so if an object has empty or unused space,
it does not need to spend I/O and time to do that allocation.
As for indexes with "unused" space, you might just want to leave it
that way as you add more data, it will more than likely just grow
right back to where it is now and it may not be worth the resource
usage to rebuild it.
One thing you might consider is a holistic methodology of managing
space - and not relying on the "OH CRAP!!!" methodology. Do the work
up front and it will save your sanity and just maybe your marriage/
relationship(s).
[you: Sorry <fill in term of endearment here>, I have to run to the
office and fix yet another space issue ... them: But you promised we
would <fill in couples activity here> tonight. ]
0) ensure you understand the database, the environment and application
to know how it currently uses space.
1) ensure you have done transactional rate analysis to determine rate
of growth both in row size and row quantity (actual and projected).
This is not always static, but is a very good start)
2) ensure you have sufficient disk space
3) ensure you have sufficient disk space (Yes, I put this in twice as
most places try to "skimp" on the disk space - DON'T SKIMP!!)
4) Develop a full-scope data retention plan (what to keep, what to
throw away, what to archive - where and how long)
5) ACTUALLY IMPLEMENT THE PLAN!!!
6) consider using ASM (even on Windows)
If this is done [mostly] correctly, you should very rarely need to
worry about the monitoring portion. I did not say "don't do
monitoring" - I did say you won't need to worry about it. With ASM,
you also will not need to worry about data files or temp files or redo
log files filling up causing your db to "suspend" until corrected as
ASM will manage this for you. And if you use ASM AND have sufficient
diskgroup space - you just may get to sleep more. Don't be afraid of
ASM - it is your friend.
onedbguru
Famous last words heard in the back woods of W.Va "Hey Bubba, hold my
beer and watch this... "
#4
| |||
| |||
|
|
On Feb 10, 10:27*am, Lothar Armbrüster [...] CAVEATE: This does post not solve the immediate problem but may help you in the long run - as jg said - this is a bug - see MOS Bug 5569515 Just curious, but are you doing any cleanup, archiving etc or just letting it continue to grow? Database archiving and purging seems to be overlooked in a LOT of shops. *I recently reviewed a db that purged a lot of data to recover space, however, because of the way that LMT works, the space is not recovered until one does a SHRINK on the objects within that tablespace. If one understands that once LMT tablespace segments are allocated to an object they remain allocated to that object until you "enable row movement then alter ... shrink or alter ... shrink cascade", then you can make plans to clean them up. This is especially true if the old, purged data contained very large *LOBS. *The previous DBA did not fully understand this, and just kept adding more datafiles to the tablespace. When the "SHRINK" ran, they recovered more than half of the database size. *Adding a purge job that runs daily - without doing the shrink, those objects are now holding at a constant number of segments. *Segment allocation in LMT is not a cheap operation, so if an object has empty or unused space, it does not need to spend I/O and time to do that allocation. [...] onedbguru Famous last words heard in the back woods of W.Va "Hey Bubba, hold my beer and watch this... " |
thank you for your detailed post. It contains some suggentions I will
consider.
To satisfy your curiosity: I normally let the database grow, since
most of the
data needs to be kept over longer periods of time. From time to time I
increase
a datafile. I consider a growth of 2-3GB for two of my tablespaces per
year normal
and expected growth. I checked the free space of these tablespaces
regularly and
was slightly annoyed by dbconsole saying on tablespace is 97% full
although
having over 1GB of free space. Recently I stumbled upon the
dbms_server_alert
package and the posibility to set the threshold to an absolute value
for free space.
So I see which tablespaces need care. Setting this caused me to run
into the bug.
Regards,
Lothar
#5
| |||
| |||
|
|
On 11 Feb., 03:39, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote: On Feb 10, 10:27*am, Lothar Armbrüster [...] CAVEATE: This does post not solve the immediate problem but may help you in the long run - as jg said - this is a bug - see MOS Bug 5569515 Just curious, but are you doing any cleanup, archiving etc or just letting it continue to grow? Database archiving and purging seems to be overlooked in a LOT of shops. *I recently reviewed a db that purged a lot of data to recover space, however, because of the way that LMT works, the space is not recovered until one does a SHRINK on the objects within that tablespace. If one understands that once LMT tablespace segments are allocated to an object they remain allocated to that object until you "enable row movement then alter ... shrink or alter ... shrink cascade", then you can make plans to clean them up. This is especially true if the old, purged data contained very large *LOBS. *The previous DBA did not fully understand this, and just kept adding more datafiles to the tablespace. When the "SHRINK" ran, they recovered more than half of the database size. *Adding a purge job that runs daily - without doing the shrink, those objects are now holding at a constant number of segments. *Segment allocation in LMT is not a cheap operation, so if an object has empty or unused space, it does not need to spend I/O and time to do that allocation. [...] onedbguru Famous last words heard in the back woods of W.Va "Hey Bubba, hold my beer and watch this... " Hello onedbguru, thank you for your detailed post. It contains some suggentions I will consider. To satisfy your curiosity: I normally let the database grow, since most of the data needs to be kept over longer periods of time. From time to time I increase a datafile. I consider a growth of 2-3GB for two of my tablespaces per year normal and expected growth. I checked the free space of these tablespaces regularly and was slightly annoyed by dbconsole saying on tablespace is 97% full although having over 1GB of free space. Recently I stumbled upon the dbms_server_alert package and the posibility to set the threshold to an absolute value for free space. So I see which tablespaces need care. Setting this caused me to run into the bug. Regards, Lothar |
The problem is percentages.. I had a db at one time where the
tablespace was 95% full with 300GB free and at approximately 5G/day
added, well you do the math...
#6
| |||
| |||
|
#7
| |||
| |||
|
|
Lothar: # Setting the thresholds to 1024 and 200 the alerts disappear but I want to be sure that I get warnings if there are fewer than 1GB left and not fewer than 1MB. In the latter case there would not be enough time to react. :-) How about some in house developed scripting to monitor for you and generate alerts as needed? While I am an old grouchy groundhog I prefer to do critical scripting and monitoring outside of the database system. *The OEM based monitoring and alerting has a place and a space ... and each to their own ... but I like to do things the old fashioned cron way! |
Agreed. OEM tends to crash at the most inopportune times...
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.