Automatic Segment Space Management
in [Oracle]
|
Prev: use tlm
Next: Cannot change ARCHIVELOG-MODE via EM
From: vsevolod afanassiev on 5 Mar 2010 07:19 On PCTFREE parameter: I think it is better to avoid chained rows and not only for performance reasons: - There are some bugs related to chained rows/row migration. For example 9.2.0.8 has a bug that may cause UPDATE statement to fail if this UPDATE leads to row migration. - Once a block becomes full you may also have problem when several transactions attempt to update this block simultaneously as by default INITRANS = 1. I suggest analyzing all large tables looking whether significant UPDATE activity leading to row expansion is possible. For example many historic tables would contain following columns: START_DATE COMPLETION_DATE COMPLETION_CODE ERROR_DESCRIPTION Only START_DATE gets populated during INSERT, other columns are populated through UPDATE. This leads to row expansion and chained rows. The most extreme example I've seen is following: a table contained only two columns, USER_ID and PASSWORD_HISTORY. PASSWORD_HISTORY column was VARCHAR(4000) and contained last 20 or so passwords in encrypted format (string 30 characters long) separated by dot. When the system went live each user had only one password in the PASSWORD_HISTORY, as passwords expired 30 days after creation they were recorder in the PASSWORD_HISTORY column. So rows expanded and expanded again and it reached a point where UPDATE based on unique index would run for very long time - longer than Apps Server timeout. So users couldn't reset their passwords and couldn't login to the system. We rebuilt the table with plenty of free space per block and it has been stable since then. On ASSM: I know one reason to use ASSM and one reason not to use it: - Use ASSM as it allows shrinking of segments in 10g. This is very useful feature as shrinking is done online and no additional space is required when it is running (not like index rebuild/table move). However shringing is approx 10 times slower than ALTER TABLE MOVE/ ALTER INDEX REBUILD. - Don't use ASSM: If you have large uncomitted DELETE in ASSM tablespace then single-row inserts are very slow. Typical scenario: something goes wrong during purge, for example Apps Support may decide to change retention period from 90 days to 30 days, this results in enormous DELETE, UNDO runs out of space, DELETE fails and being rolled back. While this is happening the application is almost dead as single- row inserts run for several seconds instead of milliseconds. We have many systems that keep data somewhere between 2 and 100 days in non- partitioned tables, the data gets purged nightly, this purge is often single biggest source of instability.
From: Mladen Gogala on 5 Mar 2010 10:40 On Fri, 05 Mar 2010 04:19:16 -0800, vsevolod afanassiev wrote: > On PCTFREE parameter: I think it is better to avoid chained rows and not > only for performance reasons: Speaking of the chained rows, how many people here have actually used the ANALYZE utility to list chained rows and base the analysis on facts? At one time, there was a query, using th VSIZE function to get you a rough estimate for the number of chained rows. That was replaced by ANALYZE table list chained rows into <chained rows table, created by utlchain>, the utility to produce the list of chained rows. I use it only when the developers complain that "reads from the table are slow". -- http://mgogala.byethost5.com
From: joel garry on 5 Mar 2010 13:45 On Mar 5, 7:40 am, Mladen Gogala <n...(a)email.here.invalid> wrote: > On Fri, 05 Mar 2010 04:19:16 -0800, vsevolod afanassiev wrote: > > On PCTFREE parameter: I think it is better to avoid chained rows and not > > only for performance reasons: > > Speaking of the chained rows, how many people here have actually used the > ANALYZE utility to list chained rows and base the analysis on facts? At > one time, there was a query, using th VSIZE function to get you a rough > estimate for the number of chained rows. That was replaced by ANALYZE > table list chained rows into <chained rows table, created by utlchain>, > the utility to produce the list of chained rows. I use it only when the > developers complain that "reads from the table are slow". > > --http://mgogala.byethost5.com Since it is easy to do this in dbconsole, I do it about every month or two. There are a few tables that get mass updated irregularly, and it lets me know if something has gone unpredictable. There are a couple tables that have a data pattern that makes shrink worthwhile (though less than the tool predicts). It's not obsessive to fix growing problems before people complain. It's just my preciousssssss. jg -- @home.com is bogus. http://www.wired.com/threatlevel/2010/03/schmidt-cyberwar/
From: joel garry on 5 Mar 2010 14:11
On Mar 4, 5:30 pm, "mfuller...(a)gmail.com" <mfuller...(a)gmail.com> wrote: > On Mar 4, 9:49 am, Mladen Gogala <n...(a)email.here.invalid> wrote: > > > On Thu, 04 Mar 2010 06:31:29 -0800, Mark D Powell wrote: > > > The pctfree parameter is still valid when ASSM is in use. Determining > > > the proper value to use for this parameter is an important as ever for > > > limiting migrated rows and for making efficient use of block space. > > > You are right. I discussed that with Tanel Poder few days ago: > > >http://www.orafaq.com/maillist/oracle-l/2003/11/11/0848.htm > > > Needless to say, Tanel was right. Amazingly, after all this time, the > > link to Tanel's paper still works. > > > --http://mgogala.byethost5.com > > I am going to have to give a differing opinion on the subject. PCTFREE > and PCTUSED are interesting parameters to deal with. In my experience, > I do not like to mess with these. Why? In many cases this is needless > micro-management. I don't have time to fix problems that don't have an > impact. It is fun if I have free time and want to expiriment or learn, > but in reality there is usually little payoff. Leave the scripts > alone. They don't need changing to use ASSM and they don't need > setting really (in my experience) unless the following occurs: > > You have row migration issues and it is causing a real performance > impact. Full table scans don't really matter because even if it takes > two blocks to read your row, you are reading all the blocks anyway. If > you are doing a single row index read and the index read takes 4 IO's, > and then the data block takes 1 IO, then another due to row migration, > is your app so sensitive that 6 IO's is too much and 5 IO's is better? > I hope not. If you are doing large range scans and there is a LOT of > row migration that causes an increase in IO that is creating a visible > impact to the query OR a visible impact to the total concurrent IO of > the system, then it should be looked into. > > Another problem I see is many DBA's or developers want to micromanage > the database at this level. That is fine, but really this requires a > very good knowledge of the data and usage of the system. These > settings are great for space utilization and to prevent row migration > where you know exactly how it will be used at all times. If it is a > data warehouse and the table receives 0 row updates ever, then filling > a block completely is good to reduce IO (denser blocks) and to reduce > space consumption as data warehouses can me space hogs. if you know > that the table will receive plenty of updates and you can predict how > much space should be left to allow it to still reside in 1 block, then > great. Use it. > > Sorry to say that many times I see these being set by pulling a number > out of a hat or by defaults set in the script generation tool or > whatever. In my opinion, setting things for no reason shouldn't be > done arbitrarily. If the script has them already, leave them alone. If > it doesn't or you don't like the settings, I wouldn't mess with them > without a good reason. Well, if you are wasting 20% of the space in every big table, and you are doing lots of stuff to the tables, you are lowering the headroom under which everything goes to hell (like the "knee" Cary M. is always going on about). It is a physical DBA's task to know the app and know the data. It isn't too much to expect an app vendor to have figured this out for 1000's of tables, though sadly, I've seen them be wrong about it. We can be grateful that the defaults are reasonable in most cases these days, but if you are waiting for complaints about an app that you don't know about before you do anything, that might not be considered working smart. Disk space may be cheap, but performance analysis under fire isn't. jg -- @home.com is bogus. http://www.breitbart.com/article.php?id=D9E4JSD80&show_article=1 |