Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krishnaprasad.

Asked: March 06, 2018 - 3:32 am UTC

Last updated: March 09, 2018 - 2:36 am UTC

Version: 12c

Viewed 1000+ times

You Asked

hi Team,

We have set initrans value for partition table to 255 in our exadata box, this table have very high concurrency , after which we noticed huge archive generation , here can help us how this inittrans value is related with archive generation , we are very sure that cause of archive generation is due to high inittrans value .

Currently we have reduced it to 100 , we have used alter modify command to do that , does it will help us to reduce the archive generation

and Connor said...

we are very sure that cause of archive generation is due to high inittrans value

How? What evidence do you have ?

Once set, initrans will change the size of the table for new blocks, because we need more space to fit all those ITL's in there. But you can see from the demo below, the amount of redo consumed is very similar. ("mystat" in the script below just queries v$mystat so you can see the cumulative total for redo, and subtract each from the previous for delta)

SQL> create table t1 ( x int, y int );

Table created.

SQL> create table t2 ( x int, y int ) initrans 255;

Table created.

SQL>
SQL> @mystat
Enter value for statname: redo size

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                        142484
redo size for lost write detection                                    0
redo size for direct writes                                           0

3 rows selected.

SQL> begin
  2   for i in 1 .. 100000 loop
  3     insert into t1 values (i,i);
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> @mystat
Enter value for statname: redo size

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      26343880
redo size for lost write detection                                    0
redo size for direct writes                                           0

3 rows selected.

SQL> begin
  2   for i in 1 .. 100000 loop
  3     insert into t2 values (i,i);
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> @mystat
Enter value for statname: redo size

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      52559260
redo size for lost write detection                                    0
redo size for direct writes                                           0

3 rows selected.

SQL>
SQL> select bytes from user_segments where segment_name in ('T1','T2');

     BYTES
----------
   2097152
   4194304

2 rows selected.


So in both cases - about 26MB of redo. The double size of T2 can be seen by dumping a random block

Start dump data blocks tsn: 4 file#:7 minblk 3842 maxblk 3842
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=4 rdba=29363970
BH (0x7ffa67f5bb18) file#: 7 rdba: 0x01c00f02 (7/3842) class: 1 ba: 0x7ffa67112000
  set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1,28
  dbwrid: 0 obj: 218764 objn: 218764 tsn: [0/4] afn: 7 hint: f
  hash: [0x7ff97e268b28,0x7ff97e268b28] lru: [0x7ffa67f5bd58,0x7ffa67f5ba98]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x7ffa67f5bac0,0x7ffa67f60920] objaq: [0x7ffa67f5c050,0x7ffa67f5bad0]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0xd7996562418 tch: 1
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0xd799656241a] HSUB: [3728]
  Printing buffer operation history (latest change first):
  cnt: 10
  01. sid:15 L192:kcbbic2:bic:FBD     02. sid:15 L191:kcbbic2:bic:FBW   
  03. sid:15 L602:bic1_int:bis:FWC    04. sid:15 L822:bic1_int:ent:rtn  
  05. sid:15 L832:oswmqbg1:clr:WRT    06. sid:15 L930:kubc:sw:mq        
  07. sid:15 L913:bxsv:sw:objq        08. sid:15 L608:bxsv:bis:FBW      
  09. sid:15 L607:bxsv:bis:FFW        10. sid:06 L353:gcur:set:MEXCL    
  11. sid:06 L464:chg1_mn:bic:FMS     12. sid:06 L778:chg1_mn:bis:FMS   
  13. sid:06 L353:gcur:set:MEXCL      14. sid:06 L464:chg1_mn:bic:FMS   
  15. sid:06 L778:chg1_mn:bis:FMS     16. sid:06 L353:gcur:set:MEXCL    
Block dump from disk:
buffer tsn: 4 rdba: 0x01c00f02 (7/3842)
scn: 0xd799656241a seq: 0xf4 flg: 0x04 tail: 0x241a06f4
frmt: 0x02 chkval: 0x99db type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump:  0x01c00f02
 Object id on Block? Y
 seg/obj: 0x3568c  csc:  0x00000d7996562418  itc: 169  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00f00 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.00f.0000c379  0x014021aa.32e7.0d  ----  244  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x06   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x07   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x08   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x09   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...

0xa1   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa2   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa3   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa4   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa5   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa6   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa7   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa8   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa9   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01c00f02


See all those ITL's thus leaving less space for data. You can also see that (for 8k) I'm capped at around 170 ITL's anyway. So setting 255 is overkill.

If you altered initrans *after* the fact, then existing blocks are unchanged.

But ultimately, the question has to be asked - why are you having 255 concurrent transactions on a single block ? The solution here might not be initrans but to spread the blocks out using pctfree 90 or similar.

Rating

  (1 rating)

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

Comments

Krishnaprasad Yadav, March 08, 2018 - 12:19 pm UTC

hi ,

can you help with your solution :
solution here might not be initrans but to spread the blocks out using pctfree 90 or similar.


if my understanding for facts are correct then if pctfree is set for 90 then their could be hardly any row in block ,


Request you to share some light on this , i am not able to understand how keeping pctfree 90 will help ??
Connor McDonald
March 09, 2018 - 2:36 am UTC

It for the use case where you have a lot of activity against a small number of rows.

Example:

I have 100 sales outlets, and hence 100 rows in a table called SALES_LOCATIONS. Let's say that every sale I do comes back an updates the SALES_LOCATIONS table. That's not great design, but we'll put that aside for the time being.

100 rows will most probably fit in one block. So now I've got 100 concurrent transactions all hammering away at a single block. I need potentially 100 initrans for that block.

Let's now set pctfree 95 and do 'alter table SALES_LOCATIONS move'.

Now I might fit only 5 rows on a block, and hence my 100 rows are now spread across 20 blocks. I don't need a high initrans anymore, because any given block only has 5 rows on it, and hence I can't do more than 5 concurrent (non-blocking) transactions on that block.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.