Thanks for the question, Alberto.

Asked: September 06, 2003 - 11:26 am UTC

Last updated: July 16, 2008 - 10:08 am UTC

Version: 8.1.7/9.0.1

Viewed 1000+ times

You Asked

Hi Tom,

I have investigated the freelist machinery recently, and have found some results that you may find interesting, and I would be happy if you could comment on it. I know you're very busy at the present time - so pretend this question was submitted anytime in the future weeks ...

1) After having read Metalink notes 157250.1 (very well written) and 1029850.6 (very detailed), i noticed that the association between a process/session and the (process)freelist it uses while inserting is static, and it is

freelist# = 1 + (V$PROCESS.PID % FREELISTS)

So sessions with the same value for (V$PROCESS.PID % FREELISTS) will contend on the same freelist.

I have verified it (in 9.0.1) by running a test case, similar to your one on page 200 of "Expert one-on-one", and setting FREELISTS=2, using two sessions with the same value for (v$PROCESS.PID % 2); i got the same results (same values for the wait times in v$waitstat) that i get when I set FREELISTS=1.

Of course, if the values for (v$PROCESS.PID % 2) are different, I get zero wait times.

2) So, if we have 2 sessions concurrently inserting, it is not enough to set FREELISTS = 2 to eradicate the contention; the probability that the 2 sessions will contend is 1/FREELISTS, so a surprising 50% for FREELIST=2. Much better then the 100% for FREELISTS=1, but well above the near-zero I expected.

If we e.g. set a goal of a 10% contention probability, we would have to set FREELISTS=10 - a value much higher than I expected indeed.

3) Now, from the same Metalink notes, I understand that the blocks are transferred on the (process) freelists (from the master freelist, the transaction freelists, or from the new space got by advancing the High Water Mark) in batch of 5 (max), so we can expect to have statistically an average of 2.5 blocks "wasted" on each freelist if FREELISTS > 1.

So, let's call it a 2.5 * FREELISTS "space price".

In tablespace space "wasted" - it will never be a problem in these days of cheap disks.

IMHO the only real problem may arise for the blocks that come from bumping the hwm, since the performance of full table/index scan would be negatively affected; for the blocks coming from deletion/updates, the space is not wasted at all (whatever the setting of FREELISTS, they are there - just distributed differently).

4) Conclusion of 1+2+3:

It is a good idea to consider first thing a value much higher the just the 'number of sessions concurrently inserting', since it will greatly reduce the contention, and it seems to be quite cheap given the math in (3). As always, this does not mean that one has to forget about the drawbacks (eg the longer full scans) and be lazy on the analysis ...

5) Now, all these ruminations of mine where motivated by a table of ours that

- it's big (110,000 blocks of 8k size);
- gets an average of 6,000,000 inserts/day (and we have big peak hours);
- it is always accessed through the pk (never full scanned) [regular OLTP];
- we can't partition (it belongs to a third-party product);
- had been installed by the third-party company with FREELISTS=1;
- wait time is a very bad thing since the users are impatient web users that don't like to wait.

Needless to mention, we got 'buffer busy waits' (20 min/hour total), contention on 'data block' and 'undo header'.

For a table like this, i would say that only the sky is the limit for FREELISTS ... say we set FREELISTS=20; the space wasted would be about

20 * 2.5 / 110,000 = 0.05%

and i don't see any problem even if all the additional space come straight from bumping the hwm (we never full scan, and the scan would be only 0.05% longer even if we did).

Would you buy it ?

6) I know the existence of ASSM, and for learning purposes I will investigate it soon - but unfortunately all our databases (including the one hosting the table in (5)) are 8.1.7.4 and are going to be for a long long time ...

Many thanks in advance
Alberto

and Tom said...

then yes, you can use many freelists to help spread it out at the expense of "space utilization" to a degree.

freelist GROUPS are a bottleneck as well -- you might add more of then, even in a single instance. it'll tend to use more space but is more highly concurrent.

anytime two processes get onto the same freelist, you'll have this sort of concurrent access contention -- it is not possible to remove it 100% but we can spread it out with lots of freelists and freelist groups.

In my new book "effective oracle by design", i go into this in more detail. Here is short blurb explaining why freelist groups might be useful here:

(3)Multiple Freelist Groups

A freelist group is a collection of freelists. You may choose to create a table with two freelist groups of five freelists each. This is typically used in a Real Application Clusters (RAC) or Oracle Parallel Server (OPS) environment in order to give each instance its own set of freelists to manage and avoid cross-instance contention.

Freelist groups also can be useful for increasing concurrency in single-instance configurations. The reason for this has to do with the way free space is managed when there are multiple freelists. When a session modifies a block so that the block will be placed onto a freelist, it goes onto a master freelist for the segment. As space is needed, the blocks are moved in small amounts from the master freelist onto the individual freelists for the segment called process free lists. This master freelist itself can be a point of contention if sessions are consuming free blocks at a high rate of speed. (called process free lists)
Now, if you have multiple freelist groups, you'll spread the points of contention out over many master freelists.

As an analogy, suppose you have a water cooler and a room full of thirsty people. The water cooler represents the single freelist, and the people in the room represent sessions that want to insert into a table. With a single water cooler from which to dispense water (free space), everyone will need to get in line and wait for the people in front of them to get their water. That is, there will be a line, or contention, for this single resource, as illustrated in Figure 4-1.

So, you add more water coolers to the room, say ten of them. Now, people can get in one of ten lines in order to get a drink, so things go much faster. This is analogous to having multiple freelists. But there is still a bottleneck in this system. As the water coolers are used, they run out of water and must be refilled. At this point, you have only one person capable of refilling the water coolers. This single person represents your single freelist group. Now, this one person cannot refill more than one water cooler at a time, so whenever more than one watercooler needs refilling, people need to wait. This is where the multiple freelist groups come into play. You could create the segment with ten freelists in two freelist groups, as illustrated in Figure 4-2. Now, you have two people who can refill any one of their five assigned water coolers. Those thirsty people get their drinks faster than ever before.

One thing to consider with freelist groups, however, is that space usage can be a real concern here. With a single freelist group and multiple freelists, there can be some small amount of wasted space, but with freelist groups, the amount of wasted space may appear to be huge, depending on how the table is used.

Consider a table where one process is continually inserting into the table and another process is continually processing and deleting rows from that same table (sort of like a queue). It is very easy to get into a situation where the table appears to grow and never reuse space. This is because the process that is inserting data may pull blocks from one freelist group, whereas the other process that is deleting and freeing space places these blocks onto a separate freelist group. Hence, the session freeing space will never make a block available for the session requesting space. This is generally an atypical situation in regular single-instance Oracle, since it can happen with only truly long-running processes that get assigned to separate freelists in different freelist groups. Normally, if the processes come and go, they will tend to balance out over time.

In an environment with multiple Oracle instances, such as you find with OPS or RAC, the amount of wasted space associated with multiple freelist groups can be a problem. If a process inserts into a table on one instance and another instance is responsible for processing the row and deleting it, you may find that table grows without bounds. This is because the freelist groups will be assigned to each instance, and the space freed by instance A will not be usable by instance B. We'll discuss in a moment how ASSM can alleviate this issue in an RAC environment.
.....

Rating

(12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

max five blocks per freelist ?

Alberto Dell'Era, September 06, 2003 - 1:22 pm UTC

So I have just to wait for your new book to arrive (it's flying over the Atlantic Ocean now) to know how the story develops into the next episode, "ASSM" ;-).

Just a clarification: is it accurate to say that the space wasted on each freelist (whether in multiple groups or not), is at most 5 blocks, so 2.5 on average ? That's to quantify the adjective "small" in your excerpt ...

Many thanks again.
Alberto

September 06, 2003 - 1:50 pm UTC

it should stay at 5.  we can sort of see this in this simulation.  My goal with the loop was to find out how many blocks would 80% fill up a block (so I can delete a row from each of 4 or 5 separate blocks).  I wanted to fill the table up.  manually put 5 blocks onto the free list and then insert a row that would not fit nicely on any of the freelist blocks -- causing us to extend the HWM.  but -- those blocks are not on the freelist.  consider (dictionary managed tablespace in 8i for this example)


ops$tkyte@ORA817DEV> create table t ( x int, y varchar2(4000) ) storage( initial 1k next 1k pctincrease 0) pctfree 20 pctused 79;
 
Table created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          l_last_cnt number := 0;
  3  begin
  4          for i in 1 .. 453
  5          loop
  6                  insert into t values ( i, rpad('*',200,'*') );
  7                  commit;
  8                  for x in ( select count(distinct dbms_rowid.rowid_block_number(rowid)) cnt from t )
  9                  loop
 10                          if ( l_last_cnt <> x.cnt )
 11                          then
 12                                  dbms_output.put_line( i || ' ' || x.cnt );
 13                                  l_last_cnt := x.cnt;
 14                          end if;
 15                  end loop;
 16          end loop;
 17  end;
 18  /

<b>this shows us the threshold at which the rows cause us to goto the next block.  about 31 rows/block.  If we delete rows 1, 32, 63, 94 -- we know we deleted a row on block 1, 2, 3, 4...</b>

1 1
32 2
63 3
94 4
124 5
154 6
184 7
214 8
244 9
274 10
304 11
334 12
364 13
394 14
424 15
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> commit;
 
Commit complete.
 
ops$tkyte@ORA817DEV> exec show_space( 'T' )
Free Blocks.............................1
Total Blocks............................64
Total Bytes.............................524288
Unused Blocks...........................48
Unused Bytes............................393216
Last Used Ext FileId....................7
Last Used Ext BlockId...................192905
Last Used Block.........................16
 
PL/SQL procedure successfully completed.

<b>so, one block on the freelist  -- 48 UNUSED blocks above the HWM...</b>
 
ops$tkyte@ORA817DEV> delete from t where x in ( 1 ,32, 63 ,94 );
 
4 rows deleted.
 
ops$tkyte@ORA817DEV> commit;
 
Commit complete.
 
ops$tkyte@ORA817DEV> exec show_space( 'T' )
Free Blocks.............................5
Total Blocks............................64
Total Bytes.............................524288
Unused Blocks...........................48
Unused Bytes............................393216
Last Used Ext FileId....................7
Last Used Ext BlockId...................192905
Last Used Block.........................16
 
PL/SQL procedure successfully completed.
 
<b>now, we just added blocks 1, 2, 3, 4 of the table onto the freelist here.  they dropped down in utilization and can be reused -- but, when we try to put a row onto them that would take them back OVER their pctfree -- we see:</b>

ops$tkyte@ORA817DEV> insert into t values ( 1000, rpad('*',400,'*') );
 
1 row created.
 
ops$tkyte@ORA817DEV> commit;
 
Commit complete.
 
ops$tkyte@ORA817DEV> exec show_space( 'T' )
Free Blocks.............................5
Total Blocks............................64
Total Bytes.............................524288
Unused Blocks...........................43
Unused Bytes............................352256
Last Used Ext FileId....................7
Last Used Ext BlockId...................192905
Last Used Block.........................21
 
PL/SQL procedure successfully completed.

<b>still 5 blocks ON the freelist -- but now there are only 43 UNUSED blocks.  Where did the other 4 go??  they are on that master freelist -- and one of them contains the new row...</b>

measuring free blocks while concurrently inserting

Alberto Dell'Era, September 06, 2003 - 5:09 pm UTC

Oh yes! I should have thought myself to use dbms_space to verify the number of free blocks ...

And so, I have expanded your test case on page 200 of "expert-one-on-one" integrating it with your latest followup, by setting up the following test case:

create table spread (x int)
storage (freelists ... initial 100k next 100k pctincrease 0);

create or replace procedure insert_rows (p_delta number)
is
l_free_blks number;
l_max_free_blks number := 0;
l_freelists number;
begin
select freelists
into l_freelists
from user_tables
where table_name = 'SPREAD';

for i in 1..100000 loop

insert into spread values (i + p_delta);
commit;

dbms_space.free_blocks (
segment_owner => user,
segment_name => 'SPREAD',
segment_type => 'TABLE',
freelist_group_id => 0,
free_blks => l_free_blks );

if (l_free_blks > l_max_free_blks) then
l_max_free_blks := l_free_blks;
end if;

end loop;

dbms_output.put_line ('max free blocks = ' || l_max_free_blks ||
', free blocks/freelist = ' || l_max_free_blks / l_freelists);
end;
/

I have concurrently run insert_rows in two sessions (in 9i, should be the same in 8i). Results:

FREELISTS=1
max free blocks = 5, free blocks/freelist = 5

FREELISTS=2 (this case is point (1) of the original question)
1) using two sessions using different freelists
(i.e. different V$PROCESS.PID % FREELISTS)
max free blocks = 10, free blocks/freelist = 5

2) using two sessions colliding on the same freelist
(i.e. same V$PROCESS.PID % FREELISTS)
max free blocks = 5 , free blocks/freelist = 2.5

So, we have the final answer - the number of free blocks per freelist never goes above 5.

That was pure fun - thanks!
Alberto

5 block boundary ...

Shankar, October 10, 2003 - 9:03 pm UTC

Tom, is it a fair statement to say that oracle moves the HWM by 5 blocks at a time if there is no free block that was found below HWM for inserts? Thanks.

October 11, 2003 - 10:13 am UTC

using a non-ASSM tablespace, yes.

In ASSM?

reader, March 13, 2004 - 5:25 pm UTC

In ASSM tablespace, assuming that there are not enough BMBs to support concurrent processes inserting rows, does oracle allocate another extent to satisfy the insert from another process?
My understanding is that each extent has a BMB that would guide a user process to use the "free" blocks within that extent. Now, if all of the BMBs corresponding to the exisiting extents are used by concurrent inserts, another transaction may force oracle to allocate a new extent. Is this how oracle moves the HWM in ASSM? Is this a possible theory behind ASSM? Thanks.

March 14, 2004 - 10:09 am UTC

You can still have contention for BMB's -- it'll not just allocate a new extent for that. BMB's grow in "number" as the segment itself grows and prevent most of the issues found in systems where pctused/freelists/freelist groups where not set (typical).

You might be interested in support note <Note:247752.1> available on metalink.

Insert bottleneck?

David Pujol, November 02, 2004 - 7:19 pm UTC

Hi Tom, few days ago, I was post you a question about insert performance in a web system based on mod_plsql.
This database saves query results in a permanent tables (I cannot use temporary tables because sessions change) because
this results are queried for another sessions too. There are a lot of inserts simultanious (a lot of concurrency).
To avoid block contention we was hash-partitioned this tables. The "magic key" is always in queries!, I was study for long time this tables access.
Now, I've added 32 partitions more (actually there are 64 partitions). The last week, we got this results:

This is a statspack window time for 30 min

33,008,485 435,340 75.8 11.8 487783486
INSERT INTO TMP_CDHHDESG_W ( IDTMPCDHOTELES,IDTIPHAB,ADUREGIMENP
EDI,NINREGIMENPEDI,FECHA,ESTADO,NOTAS,NUMHABDISP,NUMHABPETI,CUPO
OVENTA,TIPHABCOG,IDTIPTAR,IDTIPTEM,IDPRODUCTOS,IDPRODUCTOS_TAR,C
LIPRECIOHAB,CLIPRECIOREG,HOTPRECIOHAB,HOTPRECIOREG,ADUREGIMENINC
L,NINREGIMENINCL,SUPLESTINF,NETAOCOMIS,COMISIONA,PORCIVA,TIPONIN

and now (add 32 partitions) I'm getting this results:

This is a statspack window time for 1:30 min

142,053,081 1,049,380 135.4 17.2 487783486
INSERT INTO TMP_CDHHDESG_W ( IDTMPCDHOTELES,IDTIPHAB,ADUREGIMENP
EDI,NINREGIMENPEDI,FECHA,ESTADO,NOTAS,NUMHABDISP,NUMHABPETI,CUPO
OVENTA,TIPHABCOG,IDTIPTAR,IDTIPTEM,IDPRODUCTOS,IDPRODUCTOS_TAR,C
LIPRECIOHAB,CLIPRECIOREG,HOTPRECIOHAB,HOTPRECIOREG,ADUREGIMENINC
L,NINREGIMENINCL,SUPLESTINF,NETAOCOMIS,COMISIONA,PORCIVA,TIPONIN

I've doubled buffer gets for execution. The storage parameters are:

.........
TABLESPACE CONSDISP_W
PCTUSED 60
PCTFREE 30
INITRANS 5
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 24
FREELIST GROUPS 1
)
............

Now, I've noted that db file sequential read has increased a lot of. I've reduced buffer busy waits but, I think that
now, i'm getting poor performance than when I had 32 partitions only:

Before, this tables had one pctfree = 20 and initrans = 2. I don't understand why now I'm getting double block gets and why now the
insert operation is more expensive. (I was increase pctfree for minimize number of rows in block buffer)

I have a few questions about this topic and I'd like to know your appreciate opinion:

1- My first question is about customize this "temporary tables". Do you think that is a good idea to partitioned this tables
(I think that I'll get less segment contention (more segments))??.

2- What is the better way to improve performance in this insert operations??? I've noted that when I trunc this tables, I get a better perfornamce, but,
to trunc this tables, I have go out all sessions to be executed. Now, I have a job (every 5 minutes) that deletes historical data.
I think that this delete is not necessary, I trunc this tables one time in the every morning.

3- Why when I trunc this tables I'm getting a better performance?. Will I improve performance (less contention) if I use freelists groups?

.... sorry for my english Tom, I'm studing english too ... and a lot of thanks Tom!

Regards
David Pujol

November 03, 2004 - 6:57 am UTC

You'll have to help me to reproduce (and please explain what the numbers we are looking at are exactly -- i can guess, but I hate guessing.) 

the number of partitions won't do that -- must be something else in your implementation, your indexing scheme, something.  consider:



ops$tkyte@ORA9IR2> CREATE TABLE t8
  2  (
  3    id        number,
  4    data      varchar2(255),
  5    constraint t8_pk primary key (id) using index LOCAL
  6  )
  7  PARTITION BY hash(id) partitions 8 ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE t16
  2  (
  3    id        number,
  4    data      varchar2(255),
  5    constraint t16_pk primary key (id) using index LOCAL
  6  )
  7  PARTITION BY hash(id) partitions 16 ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE t32
  2  (
  3    id        number,
  4    data      varchar2(255),
  5    constraint t32_pk primary key (id) using index LOCAL
  6  )
  7  PARTITION BY hash(id) partitions 32 ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE t64
  2  (
  3    id        number,
  4    data      varchar2(255),
  5    constraint t64_pk primary key (id) using index LOCAL
  6  )
  7  PARTITION BY hash(id) partitions 64 ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, l evel 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> begin
  2      for i in 1 .. 10000
  3      loop
  4          insert into t8(id,data) values(i,rpad('x',150,'x'));
  5          insert into t16(id,data) values(i,rpad('x',150,'x'));
  6          insert into t32(id,data) values(i,rpad('x',150,'x'));
  7          insert into t64(id,data) values(i,rpad('x',150,'x'));
  8          commit;
  9      end loop;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.


INSERT into t8(id,data) values (:b1,rpad('x',150,'x'))
                                                                                                  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute  10000      1.64       1.48          0      10097      41994       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      1.64       1.49          0      10098      41994       10000
********************************************************************************
INSERT into t16(id,data) values (:b1,rpad('x',150,'x'))
                                                                                                  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      1.02       1.41          0      10061      31660       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      1.02       1.41          0      10061      31660       10000
********************************************************************************
INSERT into t32(id,data) values (:b1,rpad('x',150,'x'))
                                                                                                  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      1.29       1.13          0      10000      32110       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      1.29       1.13          0      10000      32110       10000
********************************************************************************
INSERT into t64(id,data) values (:b1,rpad('x',150,'x'))
                                                                                                  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute  10000      0.95       1.09          0      10000      32216       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      0.96       1.09          0      10000      32216       10000

<b>they are about the same.</b>



1) yes, partition seems sound as long as the key you partition by is used by all and the indexes are all local.

2) need more information -- inserts should not break down like that, but you'd have to tell us how to simulate the use of your tables precisely.  full schema and all.

Insert Bottleneck

David Pujol, November 03, 2004 - 12:11 pm UTC

Hi, Tom, thanks. More information ..., on this tables there are a lot of check constraints and one foreign key. I think that this "constraint fk validation" could be the cause of the problem (improve performance when I trunc tables). Do you agree with me?, do you think that if I remove this constraints I will improve insert performance?.

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_COMISIONA_CH CHECK (comisiona in ('A', 'T') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_CUPOOVENTA_CH CHECK (CUPOOVENTA IN ('C','V') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_DESOSUPNIN1_CK CHECK (desosupnin1 in ('S', 'D') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_DESOSUPNIN2_CK CHECK (desosupnin2 in ('S', 'D') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_ESTADO_CH CHECK (ESTADO IN ('D','P','X') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_NETAOCOMIS_CH CHECK (netaocomis in ('C', 'N') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_SOBRENIN1_CK CHECK (sobrenin1 in ('A', 'R', 'T') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_SOBRENIN2_CK CHECK (sobrenin2 in ('A', 'R', 'T') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_TIPONIN1_CK CHECK (tiponin1 in ('P', 'C') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHABDW_TIPONIN2_CK CHECK (tiponin2 in ('P', 'C') ));

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMP_CDHHDESG_W_ID_PK PRIMARY KEY (IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI, FECHA) USING INDEX LOCAL);

ALTER TABLE TMP_CDHHDESG_W ADD (
CONSTRAINT TMPCDHHDW_IDTMPCDHHAB_FK FOREIGN KEY (IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI)
REFERENCES TMP_CDHHAB_W (IDTMPCDHOTELES,IDTIPHAB,ADUREGIMENPEDI,NINREGIMENPEDI)
ON DELETE CASCADE);

TMP_CDHHAB_W table is always growing too. Processes are inserting in this table too. All indexes are local too....

A lot of thanks!

November 04, 2004 - 9:26 am UTC

no, don't agree or disagree - just agree that "we haven't seen the table yet"

do you want to help me reproduce? set up a test case -- get it to exhibit the behaviour you see with 8 vs 16 partitions -- whatever, post that.

Insert Bottleneck

david pujol, November 03, 2004 - 12:32 pm UTC

Sorry Tom, I forgot this information:

TMP_CDHHAB:
PARTITION BY HASH (IDTMPCDHOTELES)

TMP_CDHHDESG_:
PARTITION BY HASH (IDTMPCDHOTELES, IDTIPHAB)

All columns (PK->FK) are of the same type. When Oracle does foreign key validation:

... FOREIGN KEY (IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI) REFERENCES TMP_CDHHAB_W (IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI).

In this case, there is a partition pruning. (I think).

A lot of thanks Tom!

November 05, 2004 - 10:31 am UTC

give SAMPLE SMALL SCHEMA to demonstrate what you have -- not pages of individual tables, something small, concise, yet 100% representative.

Insert performance

David Pujol, November 13, 2004 - 12:05 pm UTC

Sorry for delay, I've been shutdown ...

My script ......

CREATE TABLE TMP_CDHHAB_M
(
IDTMPCDHOTELES NUMBER NOT NULL,
IDTIPHAB VARCHAR2(4) NOT NULL,
ADUREGIMENPEDI VARCHAR2(2) DEFAULT ' ' NOT NULL,
NINREGIMENPEDI VARCHAR2(2) DEFAULT ' ' NOT NULL,
CANTIDADPEDIDAS NUMBER DEFAULT 0 NOT NULL,
CANTIDADSELECC NUMBER DEFAULT 0 NOT NULL,
ESTADO VARCHAR2(1) DEFAULT 'O' NOT NULL,
ADULTOS NUMBER DEFAULT 0 NOT NULL,
ADUREGIMENINCL VARCHAR2(2) DEFAULT ' ' NOT NULL,
ADUREGDIAS VARCHAR2(1) DEFAULT 'T' NOT NULL,
NINOS NUMBER DEFAULT 0 NOT NULL,
NINREGIMENINCL VARCHAR2(2) DEFAULT ' ' NOT NULL,
NINREGDIAS VARCHAR2(1) DEFAULT 'T' NOT NULL,
PRECIOVENTA NUMBER(11,2) DEFAULT 0 NOT NULL,
PRECIOCOSTE NUMBER(11,2) DEFAULT 0 NOT NULL,
NOTASHAB VARCHAR2(2000),
EDADNINO1 NUMBER(2),
EDADNINO2 NUMBER(2),
FORZARPETICIONSN VARCHAR2(1) DEFAULT 'N'
)
PCTUSED 60
PCTFREE 40
INITRANS 5
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
PCTINCREASE 0
FREELISTS 64
FREELIST GROUPS 4
)
NOLOGGING
-- PARTITION BY HASH (IDTMPCDHOTELES) partitions 64
NOCACHE
NOPARALLEL
ENABLE ROW MOVEMENT
/

CREATE UNIQUE INDEX TMP_CDHHAB_M_ID_PK ON TMP_CDHHAB_M
(IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI)
PCTFREE 40
INITRANS 5
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
FREELISTS 64
FREELIST GROUPS 4
)
NOLOGGING
-- LOCAL
NOPARALLEL
/

ALTER TABLE TMP_CDHHAB_M ADD (
CONSTRAINT TMP_CDHHAB_M_ID_PK PRIMARY KEY (IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI) USING INDEX LOCAL);

CREATE TABLE TMP_CDHHDESG_M
(
IDTMPCDHOTELES NUMBER NOT NULL,
IDTIPHAB VARCHAR2(4) NOT NULL,
ADUREGIMENPEDI VARCHAR2(2) DEFAULT ' ' NOT NULL,
NINREGIMENPEDI VARCHAR2(2) DEFAULT ' ' NOT NULL,
FECHA DATE NOT NULL,
ESTADO VARCHAR2(1) DEFAULT 'X' NOT NULL,
NOTAS VARCHAR2(200),
NUMHABDISP NUMBER DEFAULT 0 NOT NULL,
NUMHABPETI NUMBER DEFAULT 0 NOT NULL,
CUPOOVENTA VARCHAR2(1) DEFAULT 'V' NOT NULL,
TIPHABCOG VARCHAR2(4),
IDTIPTAR VARCHAR2(2),
IDTIPTEM VARCHAR2(2),
IDPRODUCTOS NUMBER,
CLIPRECIOHAB NUMBER(11,2) DEFAULT 0 NOT NULL,
CLIPRECIOREG NUMBER(11,2) DEFAULT 0 NOT NULL,
HOTPRECIOHAB NUMBER(11,2) DEFAULT 0 NOT NULL,
HOTPRECIOREG NUMBER(11,2) DEFAULT 0 NOT NULL,
ADUREGIMENINCL VARCHAR2(2),
NINREGIMENINCL VARCHAR2(2),
SUPLESTINF NUMBER(3),
TIPONIN1 VARCHAR2(1),
DESOSUPNIN1 VARCHAR2(1),
PORCENIN1 NUMBER(5,2),
IMPNIN1 NUMBER(8,2),
SOBRENIN1 VARCHAR2(1),
TIPONIN2 VARCHAR2(1),
DESOSUPNIN2 VARCHAR2(1),
PORCENIN2 NUMBER(5,2),
IMPNIN2 NUMBER(8,2),
SOBRENIN2 VARCHAR2(1),
NETAOCOMIS VARCHAR2(1),
COMISIONA VARCHAR2(1),
PORCIVA NUMBER(4,2),
TIPO3APERS VARCHAR2(1),
DESOSUP3APERS VARCHAR2(1),
PORCEN3APERS NUMBER(5,2),
IMP3APERS NUMBER(8,2),
SOBRE3APERS VARCHAR2(1),
TIPO4APERS VARCHAR2(1),
DESOSUP4APERS VARCHAR2(1),
PORCEN4APERS NUMBER(5,2),
IMP4APERS NUMBER(8,2),
SOBRE4APERS VARCHAR2(1),
DTOVAN NUMBER(5,2),
IDPRODUCTOS_TAR NUMBER,
IDHOTELESCUPO NUMBER,
FORZARPETICIONSN VARCHAR2(1) DEFAULT 'N'
)
PCTUSED 60
PCTFREE 40
INITRANS 5
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 64
FREELIST GROUPS 4
)
NOLOGGING
-- PARTITION BY HASH (IDTMPCDHOTELES, IDTIPHAB) PARTITIONS 8

NOCACHE
NOPARALLEL
ENABLE ROW MOVEMENT
/

CREATE UNIQUE INDEX TMP_CDHHDESG_M_ID_PK ON TMP_CDHHDESG_M
(IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI, FECHA)
PCTFREE 40
INITRANS 5
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
FREELISTS 64
FREELIST GROUPS 4
)
NOLOGGING
-- LOCAL
NOPARALLEL
/

CREATE INDEX TMPCDHHDESGM_CUPO_I ON TMP_CDHHDESG_M
(IDHOTELESCUPO)
PCTFREE 40
INITRANS 5
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 64
FREELIST GROUPS 4
)
NOLOGGING
-- LOCAL
NOPARALLEL
/

ALTER TABLE TMP_CDHHDESG_M ADD (
CONSTRAINT TMP_CDHHDESG_M_ID_PK PRIMARY KEY (IDTMPCDHOTELES, IDTIPHAB, ADUREGIMENPEDI, NINREGIMENPEDI, FECHA) );

My testcase .....

exec dbms_stats.set_table_stats ('testcase', 'tmp_cdhhab_m', numrows=>1000000, numblks=>20000);
exec dbms_stats.set_table_stats ('testcase', 'tmp_cdhhdesg_m', numrows=>5000000, numblks=>50000);

begin
for i in 1 .. 100
loop
INSERTING_TESTCASE;
end loop;
end;
/

procedure INSERTING_TESTCASE is:

insert into TMP_CDHHAB_M
insert into TMP_CDHHDESG_M

I've created this testcase:

1- PARTITIONS 64
PCTFREE 20
FREELISTS 24
ALL INDEXES ARE LOCAL
all CONSTRAINTS

********************************************************************************

INSERT INTO TMP_CDHHAB_M ( IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,CANTIDADPEDIDAS ,CANTIDADSELECC ,ESTADO ,ADULTOS ,
ADUREGIMENINCL ,ADUREGDIAS ,NINOS ,NINREGIMENINCL ,NINREGDIAS ,PRECIOVENTA ,
PRECIOCOSTE ,NOTASHAB )
VALUES
(:B3 ,'1' ,:B2 ,:B1 ,2 ,4 ,'A' ,2 ,'MD' ,'S' ,4 ,'AD' ,'A' ,450 ,578 ,'Notas
varias de la habitación, se trata de un varchar2(4000) que igual para la
demo debería pasarse a un char de 200')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.02 2.70 58 105 643 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.02 2.70 58 105 643 100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 58 0.56 2.68
********************************************************************************

INSERT INTO TMP_CDHHDESG_M (IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,FECHA ,ESTADO ,NOTAS ,NUMHABDISP ,NUMHABPETI ,CUPOOVENTA ,
CLIPRECIOHAB ,CLIPRECIOREG ,HOTPRECIOHAB ,HOTPRECIOREG ,IDHOTELESCUPO)
VALUES
(:B1 ,'1' ,:B4 ,:B3 ,(SYSDATE+ (:B2/86400)) ,'A' ,'notas varias.
Insertaremos unas pocas .................' ,2 ,3 ,'A' ,1000 ,1000 ,1999 ,
2121 ,:B1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 99900 13.03 41.56 1223 104221 834466 99900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99901 13.03 41.56 1223 104221 834466 99900

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1229 1.39 20.58
log file sync 6 0.00 0.00
control file sequential read 36 0.09 0.36
async disk IO 15 0.00 0.00
db file single write 3 0.00 0.00
control file parallel write 6 0.00 0.00
rdbms ipc reply 3 0.25 0.71
log buffer space 7 1.02 4.14
log file switch completion 1 0.18 0.18
********************************************************************************

2- PARTITIONS 32
PCTFREE 20
FREELISTS 24
ALL INDEXES ARE LOCAL
WITHOUT CONSTRAINTS

********************************************************************************

INSERT INTO TMP_CDHHAB_M ( IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,CANTIDADPEDIDAS ,CANTIDADSELECC ,ESTADO ,ADULTOS ,
ADUREGIMENINCL ,ADUREGDIAS ,NINOS ,NINREGIMENINCL ,NINREGDIAS ,PRECIOVENTA ,
PRECIOCOSTE ,NOTASHAB )
VALUES
(:B3 ,'1' ,:B2 ,:B1 ,2 ,4 ,'A' ,2 ,'MD' ,'S' ,4 ,'AD' ,'A' ,450 ,578 ,'Notas
varias de la habitación, se trata de un varchar2(4000) que igual para la
demo debería pasarse a un char de 200')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.07 2.62 40 103 609 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.07 2.62 40 103 609 100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 40 0.88 2.55
********************************************************************************

INSERT INTO TMP_CDHHDESG_M (IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,FECHA ,ESTADO ,NOTAS ,NUMHABDISP ,NUMHABPETI ,CUPOOVENTA ,
CLIPRECIOHAB ,CLIPRECIOREG ,HOTPRECIOHAB ,HOTPRECIOREG ,IDHOTELESCUPO)
VALUES
(:B1 ,'1' ,:B4 ,:B3 ,(SYSDATE+ (:B2/86400)) ,'A' ,'notas varias.
Insertaremos unas pocas .................' ,2 ,3 ,'A' ,1000 ,1000 ,1999 ,
2121 ,:B1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 99900 11.91 37.75 888 103955 731274 99900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99901 11.91 37.75 888 103955 731274 99900

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 892 2.31 18.20
control file sequential read 36 0.02 0.19
async disk IO 15 0.00 0.00
db file single write 3 0.00 0.00
control file parallel write 6 0.00 0.00
rdbms ipc reply 3 0.18 0.55
log buffer space 10 0.66 1.55
log file sync 6 0.00 0.01
log file switch completion 2 0.20 0.36
********************************************************************************

3- PARTITIONS 16
PCTFREE 30
FREELISTS 24
ALL INDEXES ARE LOCAL
without CONSTRAINTS

********************************************************************************

INSERT INTO TMP_CDHHAB_M ( IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,CANTIDADPEDIDAS ,CANTIDADSELECC ,ESTADO ,ADULTOS ,
ADUREGIMENINCL ,ADUREGDIAS ,NINOS ,NINREGIMENINCL ,NINREGDIAS ,PRECIOVENTA ,
PRECIOCOSTE ,NOTASHAB )
VALUES
(:B3 ,'1' ,:B2 ,:B1 ,2 ,4 ,'A' ,2 ,'MD' ,'S' ,4 ,'AD' ,'A' ,450 ,578 ,'Notas
varias de la habitación, se trata de un varchar2(4000) que igual para la
demo debería pasarse a un char de 200')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.04 1.30 18 103 608 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.04 1.30 18 103 608 100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 18 0.64 1.20
********************************************************************************

INSERT INTO TMP_CDHHDESG_M (IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,FECHA ,ESTADO ,NOTAS ,NUMHABDISP ,NUMHABPETI ,CUPOOVENTA ,
CLIPRECIOHAB ,CLIPRECIOREG ,HOTPRECIOHAB ,HOTPRECIOREG ,IDHOTELESCUPO)
VALUES
(:B1 ,'1' ,:B4 ,:B3 ,(SYSDATE+ (:B2/86400)) ,'A' ,'notas varias.
Insertaremos unas pocas .................' ,2 ,3 ,'A' ,1000 ,1000 ,1999 ,
2121 ,:B1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 99900 9.39 32.79 318 104288 731466 99900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99901 9.39 32.79 318 104288 731466 99900

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 325 1.34 9.21
log buffer space 31 1.02 5.73
control file sequential read 48 0.04 0.35
async disk IO 20 0.00 0.00
db file single write 4 0.00 0.00
control file parallel write 8 0.00 0.01
rdbms ipc reply 4 0.28 0.95
log file sync 6 1.02 1.64
log file switch completion 2 0.19 0.35
********************************************************************************

4- PARTITIONS 8
PCTFREE 20
FREELISTS 24
ALL INDEXES ARE LOCAL
without CONSTRAINTS

********************************************************************************

INSERT INTO TMP_CDHHAB_M ( IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,CANTIDADPEDIDAS ,CANTIDADSELECC ,ESTADO ,ADULTOS ,
ADUREGIMENINCL ,ADUREGDIAS ,NINOS ,NINREGIMENINCL ,NINREGDIAS ,PRECIOVENTA ,
PRECIOCOSTE ,NOTASHAB )
VALUES
(:B3 ,'1' ,:B2 ,:B1 ,2 ,4 ,'A' ,2 ,'MD' ,'S' ,4 ,'AD' ,'A' ,450 ,578 ,'Notas
varias de la habitación, se trata de un varchar2(4000) que igual para la
demo debería pasarse a un char de 200')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.01 0.12 2 109 637 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.01 0.12 2 109 637 100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.05 0.09
********************************************************************************

INSERT INTO TMP_CDHHDESG_M (IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,FECHA ,ESTADO ,NOTAS ,NUMHABDISP ,NUMHABPETI ,CUPOOVENTA ,
CLIPRECIOHAB ,CLIPRECIOREG ,HOTPRECIOHAB ,HOTPRECIOREG ,IDHOTELESCUPO)
VALUES
(:B1 ,'1' ,:B4 ,:B3 ,(SYSDATE+ (:B2/86400)) ,'A' ,'notas varias.
Insertaremos unas pocas .................' ,2 ,3 ,'A' ,1000 ,1000 ,1999 ,
2121 ,:B1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 99900 10.32 29.87 257 104174 731180 99900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99901 10.33 29.87 257 104174 731180 99900

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 264 0.80 7.80
log file sync 11 0.00 0.01
control file sequential read 48 0.10 0.42
async disk IO 20 0.00 0.00
db file single write 4 0.00 0.00
control file parallel write 8 0.00 0.01
rdbms ipc reply 4 0.27 0.87
log buffer space 22 1.02 2.30
free buffer waits 3 1.02 2.25
log file switch completion 2 0.18 0.33
********************************************************************************

4- NO PARTITIONS
PCTFREE 40
FREELISTS 64
FREELIST GROUPS 4
without CONSTRAINTS

********************************************************************************

INSERT INTO TMP_CDHHAB_M ( IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,CANTIDADPEDIDAS ,CANTIDADSELECC ,ESTADO ,ADULTOS ,
ADUREGIMENINCL ,ADUREGDIAS ,NINOS ,NINREGIMENINCL ,NINREGDIAS ,PRECIOVENTA ,
PRECIOCOSTE ,NOTASHAB )
VALUES
(:B3 ,'1' ,:B2 ,:B1 ,2 ,4 ,'A' ,2 ,'MD' ,'S' ,4 ,'AD' ,'A' ,450 ,578 ,'Notas
varias de la habitación, se trata de un varchar2(4000) que igual para la
demo debería pasarse a un char de 200')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.04 0.28 6 10 459 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.04 0.28 6 10 459 100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 6 0.14 0.24
********************************************************************************

INSERT INTO TMP_CDHHDESG_M (IDTMPCDHOTELES ,IDTIPHAB ,ADUREGIMENPEDI ,
NINREGIMENPEDI ,FECHA ,ESTADO ,NOTAS ,NUMHABDISP ,NUMHABPETI ,CUPOOVENTA ,
CLIPRECIOHAB ,CLIPRECIOREG ,HOTPRECIOHAB ,HOTPRECIOREG ,IDHOTELESCUPO)
VALUES
(:B1 ,'1' ,:B4 ,:B3 ,(SYSDATE+ (:B2/86400)) ,'A' ,'notas varias.
Insertaremos unas pocas .................' ,2 ,3 ,'A' ,1000 ,1000 ,1999 ,
2121 ,:B1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 99900 8.59 21.68 1 4157 732542 99900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99901 8.59 21.68 1 4157 732542 99900

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log buffer space 43 1.02 4.89
log file sync 4 0.08 0.09
control file sequential read 36 0.17 0.97
db file sequential read 6 0.03 0.10
async disk IO 15 0.00 0.00
db file single write 3 0.03 0.03
control file parallel write 6 0.02 0.03
rdbms ipc reply 3 0.43 0.91
log file switch completion 1 0.16 0.16
********************************************************************************

My conclusion is:

- With 64 partitions I had 64x24 insert points, but I think that there was a lot of cost of insert.
- With 32 partitions I had 32x24 insert points, insert had less cost ..
- .....

- I think that the problem with blocks (doubled in 64 partitions) was caused by delayed block cleanout. Remember that I had one job who deletes every 5 minutes historical data. Now, without partitions, I've improve insert performance (testcase) and I've removed the deletes job. These tables have not constraints now. I've added 4 freelists groups to minimize a possible bottleneck in freelists (these tables will be truncated every week) and it seems that run very well. The last statspack is the best (without partitioning).

I've spread more the rows (pctfree 40) and it seems that now there are less buffer busy waits too.

This is my testcase,

a lot of thanks Tom.

Atentament
David Pujol

November 13, 2004 - 1:20 pm UTC

i'm confused here -- your original question was

..
now, i'm getting poor performance than when I had 32 partitions only:
.....

or to paraphrase: why is 64 partitions so much slower

but your numbers have shown that 64/32 -- no big difference? We were not comparing partitioned vs non-partitioned. And remember -- your initial discussion included:

...
To avoid block contention we was hash-partitioned this tables.
......

so, you were having multi-user concurrency issues, a single user test cannot be used to show your better off now.

Insert performance

David Pujol, November 13, 2004 - 5:00 pm UTC

Yes, I'm agree with you. I think that I was getting bad performance in 64 partitions caused by block cleanout( I was getting twice of visited blocks than 32 partitions). And yes, these tables were partitioned to improve insert performance (minimize contention). Now, I've seen that without partitionning, with an pctfree increase and free lists groups increase, I'm getting the best performance.
And yes, this test should be with concurrent users, I've take 4x15 min windows statspack and it seems that all has been improved.

Sorry for misunderstanding. A lot of thanks.

i am a senior dba!

gaozhiwen, May 08, 2005 - 5:01 am UTC

Though my english is very poor,i am keep up with looking through article in your site!I am from china!
Thanks very much!

Freelist Usage on RAC

a reader, January 05, 2007 - 10:15 am UTC

Tom,

I have a few questions about freelist and freelist groups on a RAC environment.

1. I understand that if I have a 2 node RAC setup, then it is recommended to have 2 freelist groups (one for each instance). What happens when a table has 3 freelist groups on a 2 node RAC setup? Does one group never get used for inserts/updates, but receives blocks from deletions?

2. Can I make the assumption that if my table has an avg_row_len of 720 and a freelist holds 5 blocks, then I can insert about 50 rows before the freelist has to get more blocks from the master freelist. This is the best case scenario with totally empty blocks. ((8k * 5) * ((100 - PCT_FREE)/100)) / avg_row_len ... But what if the table contains a lob that has an average size of 500K. Would the lob segment freelist need to make around 13 request for more blocks for each row? If my statements are correct, it seems that the lob segment would need more freelist groups than its table. I guess I might not fully understand how contention is caused at the master freelist level. But from what I understand, two seperate sessions inserting into a table with a lob, would cause contention at the master freelist level. And that brings me to my next question.

3. How does the master freelist destribute blocks to the freelists? Does it distribute to one freelist at a time, or can it do a certain degree of parallel?

Thanks for all you input and please correct my assumptions. I think Oracle needs to rename the magic dual table in the next version to tom. You always have the best answer to any Oracle question.

January 06, 2007 - 7:58 am UTC

1) each instance will grab it's own freelist group and use it, so that third one would just not be used.

2) probably about 50. Lobs are stored out of line when then exceed 4000 bytes so they would not affect that - they would consume space in a lob index and lob segment at that point. And you would

3) there is a "master freelist" for each freelist group and it would be a point of serialization within a freelist group.

Concurrent inserts and updates

Anil Uniyal, July 16, 2008 - 3:06 am UTC

Hi Tom,

I have a table abc in performance testing environment with 50 concurrent users ,each user inserts 2000 records into abc at same time.
We will have updates on non primary key columns at same time based on primary key.
I am using single instance and not RAC/OPS.

a)What should be my ideal freelists and freelists group setting for abc, assuming there will be no deletes?
b)I also have abc_idx unique index(for primary key) for abc.What should be it's freelists and freelists group setting.

thanks in advance...

Anil

July 16, 2008 - 10:08 am UTC

a) use assm (automatic segment space management) and do not set freelists or freelist groups.

b) see a)

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

max five blocks per freelist ?

measuring free blocks while concurrently inserting

5 block boundary ...

In ASSM?

Insert bottleneck?

Insert Bottleneck

Insert Bottleneck

Insert performance

Insert performance

i am a senior dba!

Freelist Usage on RAC

Concurrent inserts and updates

More to Explore

Performance

freelists higher than 'sessions concurrently inserting'

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

max five blocks per freelist ?

measuring free blocks while concurrently inserting

5 block boundary ...

In ASSM?

Insert bottleneck?

Insert Bottleneck

Insert Bottleneck

Insert performance

Insert performance

i am a senior dba!

Freelist Usage on RAC

Concurrent inserts and updates

More to Explore

Performance