Disk is cheap?
CJ, September 11, 2002 - 8:39 am UTC
You obviously do not have to deal with EMC disk administrators who hoard disk like it was gold, complain that their cabinets are full, and it costs $$$$$ to buy another cabinet to hold more disk.
September 11, 2002 - 8:40 am UTC
Compare your salary and time to the cost of hardware (and yes, yes I do -- all of the time, constantly, continously).
Big RBS...
Robert C, September 11, 2002 - 4:37 pm UTC
Tom, I don't have your book with me now, but did you show how to get RBS info to find a big RBS ? if not, would you please show me here ?
Also I tried "set autotrace traceonly explain" on UPDATE statement, unlike SELECT, the UPDATE was carried out, I had to rollback. This is not the correct way to analyze the UPDATE, is it ? how would you do it and avoid the execution ? (well other than ...where 1 = 0)
Thanks!
September 11, 2002 - 7:37 pm UTC
see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:233214228495 <code>
or just realize that RBS should not wrap during the period of time you expect your longest running query to execute. Say that is 20 minutes, you can just monitor v$rollstat during 30 minutes of busy time and see if they do wrap. If they do -- too small, make them bigger (and how much bigger should be easy to figure out given the number of times they wrap in 30minutes and their current size)
the traceonly explain only skips the FETCH with SELECTS, for all other statements, away it goes (it executes the statements).
You'll have to use the "old fashioned way" which is to EXPLAIN PLAN the statement if you just want the query plan to be displayed for the update.
Rollback segments not extending
Sree, September 12, 2002 - 9:53 am UTC
Tom, In a production database during a batch job I observered that the rollback segments are not automatically extending even though there is freespace availabe in the tablespace. Instead they are wrapping around lot of times (~40wraps) and often jobs fail with ORA-01555 error.
I have 4 rollback segments of 400M each (initial 10M, next 10M, extents 40, optimal 400M) in 2GB RBS tablespace. They were extending well when the size was 20M.
I wish to understand:
what is stopping them from extending beyond 400M?
What does 40 wraps indicate to me in resizing the rollbacks?
Thank you.
September 12, 2002 - 3:29 pm UTC
RBS ONLY extends due to an insert/update or delete causing them to.
A SELECT will never cause it to extend. A select reads it, it does not extend it.
This is one of the major advantages in 9i with UNDO tablespaces. You tell us how LONG to retain undo, and we'll grow to hold that much.
Nothing is stopping them from extending, it is just that you are not doing anything to make them extend. What you need to do is permanently allocate them LARGER (set minextents higher)
ORA-01555 : Another Possibility ?
Robert C, September 14, 2002 - 2:40 pm UTC
Tom, just reviewed (again) the ORA-01555 part in your book...you gave 3 reasons:
#1 RBS too small
#2 Fetch across commit
#3 Block cleanout
In Case#1, the reader - the report guy got hit with ora-01555 because of the writer's actions.
In Case#2, the writer shot himself in the foot...
What I am wondering is :
Other than reason#2, if you are the ONLY user modifying data on the system, is it at all possible for you to get ORA-01555, due to your OWN action, say in a very tiny RBS ?
Thanks
September 14, 2002 - 3:22 pm UTC
theoretically -- yes, cause the background processes are always there and they can be doing transactions as well -- so, if you take long enough, #3 can happen due to smon commiting lots, snpN running jobs and so on.
If you don't commit and you are the only one there -- it would take a very long time tho (so practically, I don't think so)