Home » RDBMS Server » Performance Tuning » Negative Tablespace Free Space value (Oracle, 10.2.0, Windows Server 2003)
Negative Tablespace Free Space value [message #415679] Tue, 28 July 2009 12:22 Go to next message
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 #415681 is a reply to message #415679] Tue, 28 July 2009 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
When 32-bit integer exceeds 2GB they are displayed a negative numbers.

It is strictly a data presentation issue.
Re: Negative Tablespace Free Space value [message #415682 is a reply to message #415679] Tue, 28 July 2009 12:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #415684 is a reply to message #415683] Tue, 28 July 2009 12:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you try purging the recylebin?
Re: Negative Tablespace Free Space value [message #415685 is a reply to message #415684] Tue, 28 July 2009 12:36 Go to previous messageGo to next message
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 #415686 is a reply to message #415679] Tue, 28 July 2009 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>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.

>the slowing down of insert statements on the database.
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.
Re: Negative Tablespace Free Space value [message #415687 is a reply to message #415685] Tue, 28 July 2009 12:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
See if you can reproduce the case with simple sql from sql*plus.
OEM 9x used to have a similar bug like this and you may be encountering the same.
Re: Negative Tablespace Free Space value [message #415688 is a reply to message #415686] Tue, 28 July 2009 12:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #415851 is a reply to message #415848] Wed, 29 July 2009 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I will not try to become blind reading your table, use code tags and align the columns.

Regards
Michel
Re: Negative Tablespace Free Space value [message #415994 is a reply to message #415679] Thu, 30 July 2009 01:59 Go to previous messageGo to next message
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
Re: Negative Tablespace Free Space value [message #416002 is a reply to message #415994] Thu, 30 July 2009 02:24 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the formatting.

Quote:
Apparently the EM shows the values incorrectly.

As some of us used to say: "Those who live by the GUI, die by the GUI."

Regards
Lichel
Previous Topic: performance issue oracle 10g RAC
Next Topic: Erratic explain plan
Goto Forum:
  


Current Time: Sun Dec 04 04:56:00 CST 2016

Total time taken to generate the page: 0.06633 seconds