Interesting
Doug C, November 11, 2001 - 5:30 pm UTC
Couple of follow ups
You said that you should add 64K for locally managed overhead. According to the SAFE method document, you should add 2*block size. Which would be appropriate?
Second - it seems that locally managed tablespace storage parameters seem to take precedence over the rollback storage clause in some cases. For example, if I have a locally managed tablespace defined uniform with minextents 1, no matter what I define in the rollback segment storage clause, the mins are all added into the initial, and the next and minextents come straight from the locally managed clause. Therefore, is the best thing to do to create a locally managed tablespace say initial=next=1M minextents say 5, maxextents unlimited and put an upper limit on the datafile? That way, it's just create the rollback segment and it'll inherit the right thing to do and let it ride.
November 11, 2001 - 8:41 pm UTC
I don't know what SAFE is personally, but the overhead is 64k. Its easy to test this yourself. I have an 8k blocksize so, 2*blocksize = 16k. If I try an LMT with 128k extents and a table with 2 extents, I get:
ops$tkyte@ORA717DEV.US.ORACLE.COM> create tablespace testing_lmt
2 datafile '/tmp/testing_lmt.dbf'
3 size 272k <b> /* 2*128 + 16 */ </b>
4 extent management local
5 uniform size 128k
6 /
Tablespace created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int ) storage ( minextents 2 ) tablespace testing_lmt
2 /
create table t ( x int ) storage ( minextents 2 ) tablespace testing_lmt
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 1 in tablespace TESTING_LMT
<b>not enough room -- but it should be according to SAFE?</b>
ops$tkyte@ORA717DEV.US.ORACLE.COM> drop tablespace testing_lmt including contents;
Tablespace dropped.
ops$tkyte@ORA717DEV.US.ORACLE.COM> host rm -f /tmp/testing_lmt.dbf;
ops$tkyte@ORA717DEV.US.ORACLE.COM> create tablespace testing_lmt
2 datafile '/tmp/testing_lmt.dbf'
3 size 320k <b> /* 2*128 + 64 */</b>
4 extent management local
5 uniform size 128k
6 /
Tablespace created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int ) storage ( minextents 2 ) tablespace testing_lmt
2 /
Table created.
That fits perfectly and if you look at the free space/used space in the tablespace -- it would show you 320k is totally used.
As for the taking precendence -- LMT storage parameters ALWAYS take precendence over the storage clause of anything!
We'll take the minextents applied to next and add the initial to get the LMT equivalent of an "initial". The storage parameters in the data dictionary can seem misleading cause we'll just turn your initial and next and minextents into ONE initial request which will be satisfied by some number of equi-sized extents.
the maxextents for a rollback segment in an LMT is 32k and its unlimited for all other objects -- no changing that. Use the datafile maxsize to regulate it smaller.
I say just set initial = next = LMT extent size, minextents = number if extents you want it to be minimally and forget it from then on it.
Abdal-Rahman Agha, November 11, 2001 - 11:31 pm UTC
Helpful information.
Venkat, November 12, 2001 - 7:22 am UTC
TOM, Good explanation and ur book is a "BIBLE of ORACLE".
Doug,can u tell me where can I find the SAFE document.
TIA
I think SAFE document made error
A reader, November 12, 2001 - 12:24 pm UTC
From the SAFE document, in section 2.1.11,
2.1.11 Size Files to be a multiple of the tablespace extent size plus 1 block
The only possible extent fragmentation in a tablespace with uniform extent sizes is fragmentation at the end of every file because the extent size does not match the size of the file. Oracle uses the first block of every file to maintain internal bookkeeping information. Therefore, when allocating files to a tablespace, make sure to set the size of the file to be a multiple of the extent size for the tablespace plus one block. Thus, if the DBA wants to create a tablespace with an extent size of 5120K, and a total useful space of 200MB, and if the block size for the database is 4K, then the following
specification should be used (204804 = 200*1024 + 4)
CREATE TABLESPACE foo datafile bar size 204804K;
I add 64k to each LMT datafile as TOM said, I think the Oracle SAFE document made a mistake!
Subbarao, February 01, 2002 - 4:13 am UTC
Dear Tom,
I have created a tablespace with this syntax
CREATE TABLESPACE "TT"
LOGGING
DATAFILE 'D:\ORA81\ORADATA\TT.ora' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K
then i am trying to create a rollback segment like this, I am getting an error.
CREATE PUBLIC ROLLBACK SEGMENT "RBS2"
TABLESPACE "TT"
STORAGE ( INITIAL 16K NEXT 8K OPTIMAL 64K MINEXTENTS 2
MAXEXTENTS UNLIMITED)
ALTER ROLLBACK SEGMENT "RBS2" ONLINE
ora-03237
03237, 00000, "Initial Extent of specified size cannot be allocated"
// *Cause: Too large a size for an initial extent due to freelist//
group specification
// *Action: Reduce number of freelist groups specified for segment
I doubted and created a table in the same tablespace, that is going thru. Regarding freelist groups i didn't specify any parameter. what could be the problem.
Is there any concept like having dedicated rollback segment to a tablespace. Right now i am planning to have 10 rbs in a single tablespace. I am thinking all the transactions will be using these rbs. Am I correct in this ? Please comment on this.
February 01, 2002 - 9:20 am UTC
Sorry -- i'm not going to look at this one too much. Reasoning:
o 8k extents are way too small for a rollback segment.
o rollback segments should have equi sized extents.
o 64k is trivially small for a rollback segment, I wouldn't even use an EXTENT that small for an RBS.
Use REAL sizes. Don't use impractical ones like you are.
(btw, rollback segment extents must have 4 blocks. I'll guess that you don't have a 2k block size.... go from there)...
It is ok to have all rbs in one tablespace if you like, in 9i -- we promote the use of a single UNDO tablespace instead of individual rollback segments in fact.
UNDO tablespace - always the way to go?
steve, September 04, 2002 - 9:35 pm UTC
Hi Tom,
We are moving from 8.1.7 to 9.2.0.1 (Solaris) and are
now faced with the decision of how to handle rollback
segments. This is a data warehouse in the 2 terabyte
range (3 months retention). I read through most of the
archives on UNDO TABLESPACES. Based on your last statement
in this article:
"we promote the use of a single UNDO tablespace instead of individual rollback segments in fact. "
Q1: Does this mean that it's pretty much a "no brainer"
to use an UNDO tablespace instead of rollback segments.
Or is it still a bit of a "it depends" type situation.
Q2: For some reason, AUTOEXTEND ON makes me nervous
What's your take on it (for the UNDO TABLESPACE)?
Thanks.
Steve
September 05, 2002 - 7:16 am UTC
q1) I find it a no-brainer. I cannot imagine going back to individual rollback segments personally.
q2) depends on your needs. Do you have a DBA sitting there 24x7 who will grow the tablespace as it nears needing extension? If not, autoextend for the datafiles (such as system, undo, temp) onto disks that have some capacity -- for the "hey, we needed some space for a bit" type operations is pretty cool in my opinion. You can always come back and reclaim it later. So, if you have some disk sitting around that is used for "scratch stuff", you might let your files autoextend there -- for when it is needed.
Please advise!
Yogeeraj, November 26, 2002 - 10:10 am UTC
Hello,
Thank you the clear explanation about the creation for rollback segments that you have given in answer to the Original question. Now my question about how many will i need?
Below a few statistics from my current database (that will be soon transferred to another server):
===========================================================
system@CMTDB.CMT.MU> SELECT substr(name,1,10), extents, rssize, aveactive, aveshrink, extends,shrinks
FROM v$rollname rn, v$rollstat rs
WHERE rn.usn = rs.usn;
SUBSTR(NAM EXTENTS RSSIZE AVEACTIVE AVESHRINK EXTENDS SHRINKS
__________ __________ __________ __________ __________ __________ __________
SYSTEM 10 827392 0 0 0 0
RBS0 8 4186112 526157 0 0 0
RBS1 8 4186112 1956913 4653056 71 8
RBS2 8 4186112 605252 0 0 0
RBS3 8 4186112 717729 3670016 7 1
RBS4 8 4186112 542683 0 0 0
RBS5 8 4186112 617348 3145728 6 1
RBS6 8 4186112 590018 0 0 0
RBS7 8 4186112 723508 0 0 0
RBS8 8 4186112 865569 4805973 55 6
RBS9 8 4186112 537650 0 0 0
RBS10 8 4186112 729842 0 0 0
RBS11 8 4186112 871015 1048576 2 1
RBS12 8 4186112 767571 1048576 6 3
RBS13 8 4186112 607657 0 0 0
RBS14 8 4186112 611434 0 0 0
RBS15 8 4186112 602380 0 0 0
RBS16 8 4186112 577176 0 0 0
RBS17 8 4186112 544919 4718592 18 2
RBS18 8 4186112 597384 1572864 6 2
RBS19 8 4186112 651884 0 0 0
RBS20 8 4186112 2148267 3670016 7 1
RBS21 8 4186112 619422 0 0 0
RBS22 8 4186112 956216 1572864 3 1
RBS23 8 4186112 641275 524288 1 1
RBS24 8 4186112 579881 0 0 0
RBS25 8 4186112 626852 0 0 0
RBS26 8 4186112 617793 524288 1 1
RBS27 8 4186112 598643 2621440 5 1
RBS28 8 4186112 841133 2097152 8 2
30 rows selected.
Elapsed: 00:00:00.08
system@CMTDB.CMT.MU>
============================================================
On my "new database":
============================================================
SQL> SELECT substr(name,1,10), extents, rssize, aveactive, aveshrink, extends,shrinks
FROM v$rollname rn, v$rollstat rs
WHERE rn.usn = rs.usn;
SUBSTR(NAM EXTENTS RSSIZE AVEACTIVE AVESHRINK EXTENDS SHRINKS
---------- ---------- ---------- ---------- ---------- ---------- ----------
SYSTEM 10 827392 0 0 0 0
RBS_01 26 27254784 0 0 0 0
RBS_02 25 26206208 0 0 0 0
RBS_03 25 26206208 0 0 0 0
RBS_04 25 26206208 0 0 0 0
RBS_05 25 26206208 0 0 0 0
RBS_06 25 26206208 0 0 0 0
RBS_07 25 26206208 0 0 0 0
RBS_08 26 27254784 0 0 0 0
9 rows selected.
SQL>
============================================================
how can i query my current production database to get the right number?
thank you a lot for a reply
Best Regards
Yogeeraj
November 26, 2002 - 10:20 am UTC
the right number of what?
Number of rollback segments
Yogeeraj, November 26, 2002 - 11:02 am UTC
oops! sorry.
I meant the right number of rollback segments.
thanks
Yogeeraj
November 26, 2002 - 12:48 pm UTC
I guess you would create more.
excellent again
mary, March 27, 2003 - 3:51 pm UTC
Tom, per way above --
the create tablespace for rbs_01 would actually be 25m + 64k (for the locally
managed tablespace overhead) -- else, it'll immediately grow to 26m. but yes,
that would do it.
----
Would this be 25064, 25664 (1024*25+64) or 24968(appears in your code sample right below above description)?
March 27, 2003 - 4:19 pm UTC
that 24968 was cut and pasted from a live script that was using dmt's in days gone by. that file would have immediately gone to 25m
autoextend increment size
mary, April 01, 2003 - 3:53 pm UTC
Tom, do you have any recommendations for the auto-extend increment size for the rollback datafiles?
April 01, 2003 - 7:32 pm UTC
the size of the extents or N times the size of the extents. since the file will grow by that much anyway at least...
Rollback Segment sizing...
Shailesh, April 07, 2003 - 9:54 am UTC
Hi Tom,
Sizing of Rollback segemnt I find it really confusing. We are using Oracle 8.1.6 on Windows 2000. We have defined following enough para, But still get error "ORA-01555: snapshot too old: rollback segment number 5 with name "RB4" too small".
Tablespace :-
--------------
CREATE TABLESPACE RBS DATAFILE 'E:\ORANT\oradata\PortOra\RBS\rbs01.dat' SIZE 600M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM;
ALTER DATABASE DATAFILE 'E:\ORANT\oradata\PortOra\RBS\rbs01.dat' AUTOEXTEND ON NEXT 100M Maxsize 4000M;
Rollback Segement :-
------------------
CREATE PUBLIC ROLLBACK SEGMENT RB1 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
Total 15 Rollback segments are created using above syntax. Still get an error "Rollback Segment too small".
1) How can I set proper size for Rollback segments?
2) How to Calculate transaction size?
Please guide me.
Thanks & Regards,
Shailesh
April 07, 2003 - 2:02 pm UTC
you need to make them (the rb1..rb15 segments) permanently larger. as it exists, you have 15, 2 megabyte rbs's. Me, on a fairly "busy" system, I might start with 10 or 15, 25 megabyte rbs's.
The problem is -- yours are too small, they wrap too quickly for your queries. You need to permanently allocate them larger using minextents. Since your extent sizes are 1m in size -- minextents of 15 or so (and LOSE that optimal thing all together) would do it perhaps.
If you know how fast you wrap (v$rollstat and/or statspack can help you there) and you know the duration of your long running queries - you can figure it out.
Say you wrap every 5 minutes on average. Say you have a 10 minute long query. You need 2+ times the RBS so you wrap 10+ minutes on average.
Optimal value?
Shailesh, April 11, 2003 - 1:07 am UTC
Hi Tom,
Thanks for your explanation, I tried it and increased optimal size. To make this I need to take all RBS segments offline, drop and recreate RBS tablespace. following way...
CREATE TABLESPACE RBS DATAFILE 'E:\ORANT\oradata\PortOra\RBS\rbs01.dat' SIZE 600M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 20M;
and may be specify more optimal value
CREATE PUBLIC ROLLBACK SEGMENT RB1 TABLESPACE RBS
STORAGE ( OPTIMAL 20M );
Is there any other way to do this?
Do I need to go on trying till error disappears?
Is there any way or method to calculate transaction size and then define RBS parameters accordingly.
Thanks & Regards,
Shailesh
April 11, 2003 - 8:52 am UTC
well, i did say (and LOSE that optimal
thing all together)
but now you have 2 20m extents -- and since an extent cannot be reused whilst an active transaction is in there -- I might prefer 20 2m extents instead (extent size 2m, minextents 20)
You can run statspack for say 15-30 minutes.
See how much UNDO you generate during your busy times.
Figure out your long running query.
Compute how much permanently allocated undo you need in order to go for the time your longest running query goes for without reusing the undo.
Eg: if you generate 2 meg of undo per minute. You have a query that runs 10 minutes. You need at least 20meg of undo allocated.
Some clarifications
Sanjaya Balasuriya, September 29, 2003 - 2:58 am UTC
Tom,
I want a clarification on 2 conflicting concepts.
As I read in your site and I was knowing the correct way to create a rollback segment is as;
create rollback segment rbs_001
tablespace rbs
storage ( initial 1M
next 1M
minextents 20
maxextents 100 );
This creates rollback segmenet with equal initial and next extents.
But in a book I recently read (Oracle8i DBA Bible), it gives an example like;
create rollback segment rbs1
tablespace somrthing
storage ( initial 1M
minextetns 5);
and they say that initial extent size is 5M.
Which is true ?
Thanks in advance.
-San
September 29, 2003 - 7:46 am UTC
they meant to say perhaps that the initial allocation of space will be 5m (if the tablespace isn't a LMT -- in which case, it could be almost ANY size of course :)
the initial extent is 1m (in a DMT)...
the space allocated initially would be 5m (in a DMT)...
OEM undo sizing
reader, January 03, 2004 - 9:47 am UTC
Tom,
What does OEM look at to determine the amount of UNDO needed to maintain a retention period of X minutes?
OEM gives a worst case scenario, and an average number that shows how much space a given UNDO_RETENTION will likely require, but I was just wondering how/when it tracks this. Does it take its own snapshots periodically to get these numbers?
January 03, 2004 - 10:02 am UTC
looks at the current/recent workload (so you need to use that advisor at or around "normal processing times").
it looks at what you are generating and multiplies basically (to put it very simply)
its all in the v$ tables, you can see how much undo you generate in x units of time, they record it all. look at views like v$rollstat and v$undostat
rollback segments being used by inactive transactions?
Pravesh Karthik from India, January 17, 2005 - 8:47 am UTC
Tom,
How can i find rollback segments being used by inactive transactions?
1. we query from v$transaction,dba_rollback_segs and v$session for getting used_ublk size. how can i get the blocks used by session that are not there in v$transaction.
2. are there are chances that the previous transaction has not released the space used and still holding in rollback segments ?
Thanks a lot
Pravesh Karthik
January 17, 2005 - 8:51 am UTC
you first have to define what an "inactive transaction" is?
1) doesn't make sense. if there are blocks in use by a session, they would be reported in v$transaction.
not sure what you are after.
A reader, January 17, 2005 - 12:16 pm UTC
Tom,
I had a similar question today, like the above person. The undo tablespace was nearing 100% ..i queried using the below sql
SELECT s.sid, s.username, s.program,
t.used_ublk, t.used_urec,t.used_ublk,c.segment_name,c.tablespace_name
FROM v$session s, v$transaction t , dba_rollback_segs c
WHERE s.taddr = t.addr
and t.xidusn = c.segment_id
to see how much rollback segment each user is using.. the max used was 200mb. and there were 5 sessions only. but the tablespace size started near 100% -- 12 gb undo tablespace. can you please let us know why the t.used_ublk didnt sum up to the tablespace used size?
January 17, 2005 - 5:17 pm UTC
that is good -- the tablespace was allocated and we didn't need to go through that overhead anymore.
There are rollback segments, they have extents, extents have blocks. blocks consume space.
say, you have a rollback segment in 100 extents, each extent is 1m.
That rollback segment is in a tablespace with 101m of space available to it.
So, this tablespace has 100m out of 101m allocated to the rollback segment. What can we say about that?
a) the rollback segment is 100m in size
b) with 100 extents.
that is about it, we cannot (given the information supplied) tell you how much of the rollback segment is being used (that is what v$transaction tells you), we can only say "the rollback segment is 100 meg, start doing work"
The rollback segment should be allocated out -- you don't want to keep allocating and deallocating and allocating and deallocating. The rollback segment would have been FULL if your query did account for 100% of the space *allocated*.
Just like a table with a 100mg initial extent initially has 100meg ALLOCATED (the tablespace appears "full") but the table itself it EMPTY.
The rollback segments where allocated -- but not 100% used!
A reader, January 17, 2005 - 7:16 pm UTC
Thanks for your prompt reply.
So, in case if the 12gb of undo space is used by available segmetns and one or more segments have less space occupied as compared to other segments (since the segment had never been picked by long running transaction and it never got to use more space)and if any new and a long transaction picks up the smallest segment .... it would not have enough space in that segment, though, there are spaces available in other segments not being used currently --- how do oracle manage this? ... will it not through error because of less space?
Thanks for your time
January 17, 2005 - 8:10 pm UTC
are you using old fashioned "manual do it yourself rollback segments"
or new fangled "automatic undo managed" rollback segments?
AUM
A reader, January 18, 2005 - 2:13 am UTC
Using AUM
...thanks
January 18, 2005 - 9:52 am UTC
then we will steal extents from other rollback segments for you and reallocate the space.
so, if you have 12g of undo tablespace
and just suppose oracle has created 12 rollback segments in there for you
and each was 1gig in size
and you needed 2gig of rollback for a really big transaction
we would steal 1gig of unused space (assuming it exists) from the other rollback segments leaving you with 12 rollback segments, one that is 2gig and the others that are each just a little smaller than 1gig each now.
Very good information.
A reader, January 18, 2005 - 10:28 am UTC
Thanks tom,
..if a transaction is going on in a segment will it allow to use the space from that segment? to the bigger transaction or it will use only the segment that is not been used by any other transaction?
Thanks again ...
January 18, 2005 - 11:38 am UTC
not enough clarity in the question. what space? why would it (whatever it is) use space from that segment?
I assume "it" is rollback -- rollback is stored in undo segments, rollback segments -- a transaction takes place against your data, the rollback would not be there with your data.
If you are asking "can multiple transactions use the same RBS"? the answer is yes, they can, they can in fact use the same extent in an rbs.
Excellent !! -- Thanks
A reader, January 18, 2005 - 11:49 am UTC
"can multiple transactions use the same RBS"? the answer is yes, they can, they can in fact use the same extent in an rbs.
... that has answered my query , thank you very much
Trace file with roolback information
j, February 20, 2005 - 12:54 am UTC
Dear Tom,
I found this trace file in my bdump folder,
Redo thread mounted by this instance: 1
Oracle process number: 6
Windows thread id: 2272, image: ORACLE.EXE
*** 2005-02-17 20:02:11.595
*** SESSION ID:(5.1) 2005-02-17 20:02:11.517
SMON: found rollback segment # 11 at address 93e343e0 to take offline.
I cannot found what this means,
thanks
February 20, 2005 - 9:50 am UTC
see support note Note 106925.1
it is just an informational message, someone offlined a rollback segment with active transactions. it was placed into pending, it would truly go offline later when the active transactions complete and smon found it.
Question about extent sizes in AUM
Guy Lambregts, April 22, 2005 - 11:27 am UTC
Dear Mr Tom,
Someone asked me a question about sizing of undo extents in AUM (release 9.2). I told that regardless the blocksize the first extent is always 64K, that the first block in the first extent doesn' t appear in the data dictionary. I told as well Oracle initially allocates 2 extents of 64K. So far so good, it seems I' m right with that.
However, if we look to the select below, we notice an extent size behaviour which I haven' t seen yet. (it must be once the first day) We see Oracle allocates a bigger sized extent for the 13 th extent, however the 24 th is again 64K.
Right now I think that as soon as a rollback segment needs more space, as soon as it ask for a free extent, it takes the one it can get = if there is free space of 64K, because another segment has shrinked it will take that, so extent sizes can be discontinuous.
Am I right Mr Tom ?
SQL> select extent_id,blocks,bytes from dba_undo_extents where tablespace_name l
ike 'UNDOTBS2' and segment_name='_SYSSMU19$' and rownum < 41;
EXTENT_ID BLOCKS BYTES
---------- ---------- ----------
0 7 57344
1 8 65536
2 8 65536
3 8 65536
4 8 65536
5 8 65536
6 8 65536
7 8 65536
8 8 65536
9 8 65536
10 8 65536
11 8 65536
12 8 65536
13 128 1048576
14 128 1048576
15 128 1048576
16 128 1048576
17 128 1048576
18 128 1048576
19 128 1048576
20 128 1048576
21 128 1048576
22 128 1048576
23 128 1048576
24 8 65536
25 8 65536
26 128 1048576
27 128 1048576
28 128 1048576
29 8 65536
30 8 65536
31 8 65536
32 8 65536
33 8 65536
34 8 65536
35 8 65536
36 128 1048576
37 128 1048576
38 128 1048576
39 128 1048576
April 22, 2005 - 12:26 pm UTC
that looks alot like extent stealing going on -- yes.
With AUM, for the first time, an extent can move from one undo segment to another. That would definitely explain why you see some 64k ones in the middle -- they were probably stolen (v$undostat has a steal count)
A reader, April 26, 2005 - 6:24 am UTC
undo segment extension
Mark, July 06, 2005 - 8:56 am UTC
Tom,
Had an 'event' last night and here is my STATSPACK report in part:
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
PHYDB001 3103272564 phydb001 1 8.1.7.4.1 NO CTDB01
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 13677 04-Jul-05 22:45:03 53
End Snap: 13678 05-Jul-05 00:01:52 53
Elapsed: 76.82 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 393216 log_buffer: 3145728
db_block_size: 8192 shared_pool_size: 467M
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: -559,557.07 -213,299.03
Logical reads: 70,102.31 26,722.48
Block changes: 11,386.05 4,340.28
Physical reads: 1,323.41 504.47
Physical reads - %: 1.89 1.89
Physical writes: 277.69 105.85
User calls: 10.27 3.92
Parses: 58.31 22.23
Hard parses: 8.25 3.14
Hard parse - %: 14.15 14.13
Sorts: 54.01 20.59
Logons: 0.04 0.02
Executes: 7,696.98 2,934.03
Transactions: 2.62
% Blocks changed per Read: 16.24 Recursive Call %: 99.88
Rollback per transaction %: 0.00 Rows per Sort: 432.83
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.97 Redo NoWait %: 100.00
Buffer Hit %: 98.11 In-memory Sort %: 99.92
***Library Hit %: 99.83 ***Soft Parse %: 85.86
***Execute to Parse %: 99.24
***Execute to Parse Ratio: 131.58 Latch Hit %: 99.93
Parse CPU to Parse Elapsd %: 80.35
Parse CPU to Parse El Ratio: 5.09 % Non-Parse CPU: -850.59
Shared Pool Statistics Begin End
------ ------
Memory Usage % (75 - 85): 73.23 70.29
% SQL with executions>1: 64.64 61.39
% Memory for SQL w/exec>1: 46.26 41.05
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 1,432,144 783,640 7.86
db file parallel write 8,234 118,507 1.19
db file scattered read 205,326 101,257 1.02
direct path write 57,177 43,698 .44
direct path read 122,249 40,429 .41
-------------------------------------------------------------
Wait Events for DB: PHYDB001 Instance: phydb001 Snaps: 13677 -13678
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
SQL*Net message from client 47,633 0 1,941,639 408 3.9
db file sequential read 1,432,144 0 783,640 5 118.4
db file parallel write 8,234 0 118,507 144 0.7
db file scattered read 205,326 0 101,257 5 17.0
direct path write 57,177 0 43,698 8 4.7
direct path read 122,249 0 40,429 3 10.1
enqueue 298 18 37,031 1243 0.0
log file parallel write 31,809 2 29,240 9 2.6
log file switch completion 352 146 24,418 694 0.0
log file sync 4,757 0 16,629 35 0.4
log file sequential read 95,232 0 15,097 2 7.9
control file sequential read 11,218 0 10,872 10 0.9
file open 135,296 0 10,759 1 11.2
control file parallel write 4,269 0 10,747 25 0.4
buffer busy waits 87,445 0 10,405 1 7.2
latch free 15,628 9,635 1,522 1 1.3
log file single write 472 0 957 20 0.0
undo segment extension 794,228 794,187 672 0 65.7
file identify 1,256 0 461 4 0.1
refresh controlfile command 14 0 292 209 0.0
log buffer space 9 0 116 129 0.0
LGWR wait for redo copy 446 14 107 2 0.0
db file single write 55 0 54 10 0.0
SQL*Net message to client 47,634 0 11 0 3.9
library cache pin 60 0 2 0 0.0
SQL*Net break/reset to clien 14 0 2 1 0.0
SQL*Net more data to client 378 0 0 0 0.0
SQL*Net more data from clien 91 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: PHYDB001 Instance: phydb001 Snaps: 13677 -13678
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
rdbms ipc message 92,259 4,837 5,843,476 633 7.6
pmon timer 1,504 1,497 461,114 3066 0.1
smon timer 84 1 452,605 53882 0.0
db file parallel write 8,234 0 118,507 144 0.7
log file parallel write 31,807 2 29,237 9 2.6
direct path read 80,990 0 22,206 3 6.7
enqueue 152 8 18,573 1222 0.0
log file sequential read 95,233 0 15,097 2 7.9
control file parallel write 4,231 0 10,659 25 0.3
control file sequential read 8,604 0 10,072 12 0.7
file open 132,459 0 9,610 1 11.0
rdbms ipc reply 5,887 11 4,736 8 0.5
log file single write 472 0 957 20 0.0
file identify 1,180 0 427 4 0.1
db file sequential read 188 0 206 11 0.0
LGWR wait for redo copy 446 14 107 2 0.0
direct path write 16,482 0 76 0 1.4
latch free 120 111 46 4 0.0
db file scattered read 62 0 38 6 0.0
db file single write 17 0 17 10 0.0
buffer busy waits 271 0 4 0 0.0
-------------------------------------------------------------
(it is slightly customized)
My concern was the undo segment extension timeouts happening. During this time, I saw three things going on:
1) Job creating Report Data
2) Job creating Application Data
3) Job creating company statements
(medical billing software this application is).
My question is:
Will doing Direct-Path loads of these three items mitigate this problem?
Oracle 8.1.7.4 STANDARD
July 06, 2005 - 9:07 am UTC
why are you worried about an event the consumed such a small fraction of time as compared, say, to IO on the system?
but, if you want to get rid of those
a) do not use OPTIMAL
b) make them permanently larger than they are
direct path loads can bypass UNDO generation ON THE TABLE SEGMENT (but not indexes)
They will not reuse existing space under the high water mark.
using more direct path operations will simply increase the amount of time your client applications there wait for IO to complete, at least now they are filling the buffer cache and letting dbwr take care of it in the background.