| Negative Tablespace Free Space value [message #415679] |
Tue, 28 July 2009 12:22  |
Gert Willemsens
Messages: 15
Registered: September 2004
Location: Belgium
|
Junior Member |
|
|
Hello everyone,
I have this weird thing going on in one of my databases.
Through the Enterprise Manager I get an alert saying that "Tablespace INDEXES is 5392533 percent full".
If I take a look at the tablespace space used (%) information the metric value keeps on climbing way above 5 million...
If I take a look at the tablespaces full page through "All Metrics", I get the same value. The tablespace free space is -2Gb (negative) and keeps on dropping...
The other tablespaces have "normal" values.
Apparently this has been going on for some time now, but the database was unattended and no problems were reported by our customer, except for the last few days: some insert statements are beginning to get very slow.
My INDEXES tablespace is configured as follows (taken from the EM DDL generator):
CREATE SMALLFILE TABLESPACE "INDEXES" DATAFILE 'D:\ORACLE\ORADATA\DP\INDEXES01.DBF' SIZE 12288M REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 24K LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
Apparently there is a process installed by my predecessor which rebuilds all indexes in the tablespace once a day. Immediately after this, a DBMS_UTILITY.ANALYZE_SCHEMA('DP','COMPUTE') is done. (I know that GATHER_SCHEMA_STATISTICS is the preferred way...).
The INDEX tablespace contains only about 20 indexes. Most of them are small in size. 2 of them contain about 100Mb in segments, one about 850Mb and one 1.3Gb.
Could anyone tell me how these negative values for tablespace usage are calculated? Is there a known bug which causes this?
Thanks in advance!
Regards,
Gert Willemsens
|
|
|
|
|
|
| Re: Negative Tablespace Free Space value [message #415682 is a reply to message #415679] |
Tue, 28 July 2009 12:28  |
Gert Willemsens
Messages: 15
Registered: September 2004
Location: Belgium
|
Junior Member |
|
|
Just as I posted this I saw that the MAXSIZE value was set to 24K. I thought maybe this had something to do with it, but I changed this to 13Gb and this didn't solve the problem.
This is when I reminded myself that the EM always reverts to KB in the dropdown list...
Anyway, suggestions for the problem of the negative free space value are still welcome...
|
|
|
|
| Re: Negative Tablespace Free Space value [message #415683 is a reply to message #415681] |
Tue, 28 July 2009 12:31 |
Gert Willemsens
Messages: 15
Registered: September 2004
Location: Belgium
|
Junior Member |
|
|
| BlackSwan wrote on Tue, 28 July 2009 19:28 | When 32-bit integer exceeds 2GB they are displayed a negative numbers.
It is strictly a data presentation issue.
|
OK, but how about
1) the other tablespaces appear with normal values, while they are about the same size;
2) the slowing down of insert statements on the database.
I do not see anything else which can cause the wait.
|
|
|
|
|
|
| Re: Negative Tablespace Free Space value [message #415685 is a reply to message #415684] |
Tue, 28 July 2009 12:36 |
Gert Willemsens
Messages: 15
Registered: September 2004
Location: Belgium
|
Junior Member |
|
|
Yes, the recycle bin is empty, or so it seems from
SELECT * FROM DBA_RECYCLEBIN
which results in 0 records.
Does a purge of the recycle bin affect operations? The customer is running production now. Although slow, I wouldn't want to shut them down now...
|
|
|
|
|
|
|
|
| Re: Negative Tablespace Free Space value [message #415688 is a reply to message #415686] |
Tue, 28 July 2009 12:53 |
Gert Willemsens
Messages: 15
Registered: September 2004
Location: Belgium
|
Junior Member |
|
|
| BlackSwan wrote on Tue, 28 July 2009 19:39 | >Apparently there is a process installed by my predecessor which rebuilds all indexes in the tablespace once a day.
On the surface, this is a BAD thing & should not be done.
|
Thanks for the advise. I'll keep this in mind.
| BlackSwan wrote on Tue, 28 July 2009 19:39 |
I'll accept that your report is valid.
Without additional details I choose avoid idle speculation.
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
|
I could give you additional details about the "slowing down", but this is seen in non-Oracle applications (written in Delphi, using ODAC to connect to the database). These applications have years of stable use.
Anyway: I would certainly like to post all available information of what I tested and changed and what the results were, but I am not on site for now, so I'll post this tomorrow. I think it's better to show you instead of explaining..
|
|
|
|
| Re: Negative Tablespace Free Space value [message #415848 is a reply to message #415679] |
Wed, 29 July 2009 09:27 |
Gert Willemsens
Messages: 15
Registered: September 2004
Location: Belgium
|
Junior Member |
|
|
I seem to have found out what was wrong. Apparently the EM shows the values incorrectly. I checked this by querying:
SQL> select a.tablespace_name, a.bytes bytes_used, b.bytes bytes_free, b.largest,
2 round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
3 from
4 (select tablespace_name, sum(bytes) bytes
5 from dba_data_files
6 group by tablespace_name) a,
7 (select tablespace_name, sum(bytes) bytes, max(bytes) largest
8 from dba_free_space
9 group by tablespace_name) b
10 where a.tablespace_name = b.tablespace_name
11 order by ((a.bytes-b.bytes)/a.bytes) desc;
TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED
------------------------------ ------------ ------------ ------------ ------------
USERS 21736980480 2943877120 2415853568 86.46
SYSTEM 536870912 94371840 94306304 82.42
SYSAUX 1073741824 625541120 621674496 41.74
INDEXES 12884901888 10514137088 4160749568 18.4
UNDOTBS1 2147483648 2106261504 2075066368 1.92
These values seem to be more normal.
I then checked how the alerts for the tablespace usage are calculated. This is done by MMON. The tablespace is set to be autoextendible and this is what causes the problem. In case of autoextendible tablespaces, MMON does the threshold monitoring based on the maximum size which was defined when the tablespace was created, or based on the maximum OS file size, whichever is the smallest one.
In the case of my database, the MAXSIZE parameter was initially set to be 10Gb as I found in the creation scripts. In the mean time, it was manually changed to be 13Gb (as I told yesterday when I reset it to the value I saw first).
Changing the AUTOEXTEND to OFF solved the issue. I am still looking though how to let MMON make use of the new value of the MAXSIZE, as to be able to set AUTOEXTEND back to ON (if this should be necessary).
Although our software engineers have not yet been able to find the cause of the slowing down of the insert-statements, I am confident that there is no database issue.
Thanks all for the input.
|
|
|
|
|
|
| Re: Negative Tablespace Free Space value [message #415994 is a reply to message #415679] |
Thu, 30 July 2009 01:59 |
Gert Willemsens
Messages: 15
Registered: September 2004
Location: Belgium
|
Junior Member |
|
|
For your convenience, I'll post my formatted query and the results below and hope that this time it will be more readable...
Excuse me for not being used to be using tags (and forums for that matter).
SQL> select a.tablespace_name, a.bytes bytes_used, b.bytes bytes_free, b.largest,
2 round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
3 from
4 (select tablespace_name, sum(bytes) bytes
5 from dba_data_files
6 group by tablespace_name) a,
7 (select tablespace_name, sum(bytes) bytes, max(bytes) largest
8 from dba_free_space
9 group by tablespace_name) b
10 where a.tablespace_name = b.tablespace_name
11 order by ((a.bytes-b.bytes)/a.bytes) desc;
TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED
------------------------------ ------------ ------------ ------------ ------------
USERS 21736980480 2943877120 2415853568 86.46
SYSTEM 536870912 94371840 94306304 82.42
SYSAUX 1073741824 625541120 621674496 41.74
INDEXES 12884901888 10514137088 4160749568 18.4
UNDOTBS1 2147483648 2106261504 2075066368 1.92
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
