| Segment Advisor problem [message #508144] |
Thu, 19 May 2011 10:15  |
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
|
Senior Member |
|
|
Hi,
I have a problem with the Segment Advisor running each night in the database.
Last nigth, i received an email from Enterprise manager saying that one of my tablespace is 100% full.
Quote:
Target Name=xxxxx
Target type=Database Instance
Host=xxxxxxx
Occurred At=May 19, 2011 1:42:30 AM EDT
Message=Tablespace ORIPRO_4M_TABLE is 100 percent full
Metric=Tablespace Space Used (%)
Metric value=100
Tablespace Name=ORIPRO_4M_TABLE
Severity=Critical
Acknowledged=No
Notification Rule Name=Database Availability and Critical States
Notification Rule Owner=SYSMAN
So this morning I am looking at it and tablespace is actually 3Gb used and can extend up to 20Gb. So tablespace seem to be ok for now but what happen during the night? So I check in OEM and found a session that run the following statement:
create table "ORIPRO".DBMS_TABCOMP_TEMP_UNCMP
tablespace "ORIPRO_4M_TABLE" nologging
as select /*+ FULL("ORIPRO"."FICHIER") */ *
from "ORIPRO"."FICHIER" sample block( 97.65) mytab
The table oripro.fichier is located in the tablespace oripro_4m_table and contain a blob column that is stock in another tablespace. The blob contain maybe 200Gb of data. So this is probably why i ran out of space, the create table is trying to put over 200Gb of data in a tablespace that can only contain 20Gb.
So what can i do about this. I don`t want to disable the segment advisor because it may contain usefull information but I don`t want it to test the compression since we are not using it anyway. And if I have a daily process running I don`t want it to crash because tablespace is full because of the advisor.
Any suggestion?
Thank
[Updated on: Thu, 19 May 2011 11:04] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Segment Advisor problem [message #508154 is a reply to message #508151] |
Thu, 19 May 2011 11:37  |
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
|
Senior Member |
|
|
Ok, but what will happen if I have a daily job that create record and at the same time the advisor start. If the advisor fill my tablespace my daily process will terminate in error, right?
|
|
|
|
|
|
| Re: Segment Advisor problem [message #508159 is a reply to message #508158] |
Thu, 19 May 2011 12:05 |
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
|
Senior Member |
|
|
Ok, but in this case, from where this statement is coming from :
create table "ORIPRO".DBMS_TABCOMP_TEMP_UNCMP....
From what I understand this table is generated when using dbms_compression.get_compression_ratio and I think that this is started by the Segment Advisor to evaluate if a table can benefit from compression.
|
|
|
|
|
|
|
|
|
|
| Re: Segment Advisor problem [message #508163 is a reply to message #508162] |
Thu, 19 May 2011 12:15 |
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
|
Senior Member |
|
|
No,
Because when i check in OEM I can see that the process started at 10pm (Advisor is running at this time) by user sys and it execute the following statement
create table "ORIPRO".DBMS_TABCOMP_TEMP_UNCMP
tablespace "ORIPRO_4M_TABLE" nologging
as select /*+ FULL("ORIPRO"."FICHIER") */ *
from "ORIPRO"."FICHIER" sample block( 97.65) mytab |
|
|
|
| Re: Segment Advisor problem [message #508164 is a reply to message #508163] |
Thu, 19 May 2011 12:19 |
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
|
Senior Member |
|
|
And if I look at the documentation about Segment Advisor
Quote:
The Segment Advisor generates the following types of advice:
- If the Segment Advisor determines that an object has a significant amount of free space, it recommends online segment shrink. If the object is a table that is not eligible for shrinking, as in the case of a table in a tablespace without automatic segment space management, the Segment Advisor recommends online table redefinition.
- If the Segment Advisor determines that a table could benefit from compression with the OLTP compression method, it makes a recommendation to that effect. (Automatic Segment Advisor only. See "Automatic Segment Advisor".)
- If the Segment Advisor encounters a table with row chaining above a certain threshold, it records that fact that the table has an excess of chained rows.
the second point is talking about compression.
|
|
|
|
|
|
| Re: Segment Advisor problem [message #508176 is a reply to message #508168] |
Thu, 19 May 2011 13:22 |
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
|
Senior Member |
|
|
I know that it make recommandation.
But it also create internal table to calculate the compression ratio (Metalink note 1284972.1) This note also say:
Quote:
3. Extra disk space might be required to host DBMS_TABCOMP_TEMP_CMP and DBMS_TABCOMP_TEMP_UNCMP, it can result in space pressure in some cases
This seem to be my problem since my table is 200Gb but I don`t have the same amount left in my tablespace. Will continue searching to see if there is something to do about this.
|
|
|
|
|
|
|
|
| Re: Segment Advisor problem [message #508180 is a reply to message #508179] |
Thu, 19 May 2011 14:02 |
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
|
Senior Member |
|
|
Thank for the reply,
What i am thinking is maybe moving my table in another tablespace that will old only this table. This way it will not impact my other tables or process. Or maybe only changing the Segment Advisor schedule to run less often and reduce the chance of causing a problem.
I will think about that.
Thank for your help
Regards,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
