Re: Understanding Rollback Segment Sizes

joel garry wrote:
>
> 1. Space is cheap. If you have it, anyways.
> 2. Yes, things have changed and this script is obsolete for 9.2.
> 3. Use proper terminology. In Oracle, a database has many schemata
> (singular, schema).
> 4. See the Concepts manual (tahiti.oracle.com, among other places).
> 5. It's called undo now. Read about it in the Database Administrators
> Guide.
> 6. Use Automatic Undo Management and give it plenty of space.
> 7. Some situations do still require RBS management. If you still have
> to use it, don't use the optimal parameter. Search asktom.oracle.com
> (or his books) to understand ORA-155x errors.
> 8. It is important to understand that undo is used to create
> read-consistent views of the db. This means that it's not just the
> size of transactions that matter, but how long they last, and how long
> the other transactions that need to create those views last.
> 9. undo_retention also has impacts on undo.
> 10. If you have 9.2 OEM, it has some pretty pictures for sizing undo
> given the undo retention. If you don't have it, you can check sysdba
> views that have names like '%ADVICE%'. Once things are running, that
> is.
>
> My OEM advisors tell me I need more undo than the rest of the db, but I
> ignore them. But I'm old and crotchety about it, and have a cron job
> that kills obviously senile sessions. YMMV.
>
> jg

I appreciate the input you provided. Unfortunately it didn't really help answer the questions I asked. Don't get me wrong, I will certainly review all the points you made. It's just that I was hoping to get a better idea in "layman's terms" whether I had too much or too little space and how to properly calculate it.

Regarding some of the points you made:

• As you said, space is cheap if you have it. This is a new system so I indeed have it, but with replication, mirroring, and various backups taking up space and/or time, I'm trying to make sure that the space that is being used is being used correctly. I can certainly throw 8GB of space into rollback segments or undo tablespaces, and assume that will be more than enough, but if 2GB would have been just as efficient, it seems rather wasteful.
• I work with Microsoft Access databases, Informix instances and databases, and Oracle databases and schemata (For the record, the guys from Oracle were calling them schemas) throughout the day. I apologize if I used the wrong term but I had been staring at all these CREATE DATABASE Oracle SQLs all day, and so used that in my msg.
• I was on Tahiti all day trying to get a better understanding of the creation SQLs we have here in 8i, and comparing what is in 9i, and looking forward towards 10g. I understand the concepts, just not the sizing decisions.
• I don't have the final word on changing from Rollback Segments to using Undo Tablespaces at the present time, and I am also on a schedule. It has not gone through cycle testing on our test servers with our proprietary software, so I certainly won't be able to switch to Undo tablespaces for this migration. But I will certainly read up on Undo tablespaces, undo sizes, and undo retention. (Our company is often behind the times on Oracle concepts and latest configs. We only make use of the Server portion - Enterprise Edition, and we are still not even using RMAN...we use O/S level data backups and transfers! )

Anyway, thanks for your insight and suggestions. I'll try to review the tahiti.oracle.com website a bit more. If I get too frustrated, I may switch to the www.tahiti.com website though.... ;-) Received on Wed Jan 17 2007 - 21:18:02 CST