Summary of rules for finding available blocks during data block insertion:High water level and low high water level: the status of data blocks between low water level and high water level may be unformatted or formatted. Below the low and high levels are formatted and can be used.
1. First, insert a piece of data and only use data blocks below the high level.
Location of the high water point: the boundary of the data block contained in the L1 block, or the boundary of the Zone
2. Insert a row of data for the first time and format the number of blocks?
There is no specific value. From the DUMP L1 block, there are 5 formatted values, 32 64 values, and so on.
3. How to insert a row of data through L3 --> L2 --> L1 -- data block to determine which block to insert?
In the ASSM tablespace: a row is randomly inserted into different blocks under the high level in different sessions.
The details are as follows:
L3 selects L2, which is not random. Determine the L2 to be selected based on the L2 Hint for inserts: 0x01c00081 line.
In L2, HASH based on PID to get a random value. Select L1. -- but this is affected by the high water level. If there is only one zone in the high water level range, in fact, only L1 corresponding to this zone will be selected.
In L1, HASH is performed based on the PID to obtain a random value. Select a data block based on this value.
-- During the experiment, you should note that in the same window, sqlplus quickly exits and then logs on again, using the same SID, so the inserted row will be in the same data block.
4. How many sessions insert one row of data?
Assume that no more than one data block is required for data inserted in each row:
The same session inserts data into a table in an orderly manner and inserts the same data block.
If multiple sessions insert data into a table, the data is unordered and the data inserted in each session is in different data blocks.
That is:Insertion of multiple sessions is unordered, and insertion of the same session is sequential. When reading data, it is read by ROWID in ascending order by default.
Experiment: insert two statements for the same session, and insert one statement for the new session. You can use DBMS_ROWID to view the data block where the row is located. The same block is inserted for the same session, but not for different sessions. It's too simple.
Performance problems caused by improper setting of high watermark and putfree values -- hot blocks cause buffer busy waits to wait for the event to insert a piece of data, what is the performance problem of buffer busy waits when only data blocks below high water level are used? First, the location of the high water point: the boundary of the data block contained in the L1 block, or the boundary of the Zone.
The number of data blocks contained in a L1 or a zone is limited. For example, when 8 KB is BLOCK, the system automatically manages the partition size. The first 16 zones only have 8 data blocks, and the 1-64 M time zone size is 1 M, 128 blocks. After 64 M, the partition size is 8 M, with 1024 data blocks.
1024 pieces can be used in an 8 m partition to remove the database storing metadata. IF 1000 pieces are available, 1000 concurrent insert operations are supported, when there are more concurrent operations, it is inevitable that multiple sessions operate on the same data block at the same time. In this case, the hot block appears-waiting for the event buffer busy waits.
1000 concurrent jobs are already very large and are used in log-type applications.
If there are more than 1000 concurrent threads and many buffer busy waits occur, which has affected the system performance, there is an imperfect solution: Before the business peak, insert a large number of data into the table, push the high water level, and then delete the data. Before the high water level, more data blocks can be inserted to support higher concurrency.
The buffer busy waits wait event may be caused by an unreasonable PCTFREE value. In ASSM, five statuses are used in L1: 75-100% 50-75% 25-50% 0-25% FULL to indicate the idle status of the data block, if the value is close to this value, for example, PUTFREE is 20% or 24%, in this case, several rows of data in the data block may be inserted, updated, or deleted, which will change the status of the data block, the status of the data block must be recorded by changing the status of the L1 block.
One L1 block manages multiple data blocks (for example, one L1 manages 1024 data blocks when 8 KB data blocks are in the 8 m zone ), if multiple data blocks managed by one L1 need to update the space status of the block at the same time, it will also cause contention for L1 blocks-hot blocks-waiting for the event buffer busy waits.
This experiment can also be done, but it is not very good. To delete a row and insert a row or several lines with the PCTFREE value, the block status changes, at this time, DUMP L1 block to verify. Experiment: Verify that the inserted rows can only be inserted to data blocks below the high water level by default. Create a table first and assign multiple partitions to the table manually. Use multiple sessions to insert a row of data into each session. Verify the insertion sequence of data blocks by the way.
############################ 33
BYS @ bys3> create table test11 (aa int, bb varchar2 (10 ));
Table created.
BYS @ bys3> insert into test11 values (99, 'first ');
1 row created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> alter table test11 allocate extent (size 1 m );
Table altered.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> select header_file, header_block from dba_segments where segment_name = 'test11' and owner = 'bys ';
HEADER_FILE HEADER_BLOCK
-----------------------
4 170
################### DUMP Header: Extent Control Header -- high water level Highwater: 0x020.b0, in block 176. That is, the last block of the first partition.
-----------------------------------------------------------------
Extent Header: spare1: 0 spare2: 0 # extents: 17 # blocks: 256
Last map 0x00000000 # maps: 0 offset: 2716
Highwater: 0x01_b0 ext #: 0 blk #: 8 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 5
Mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark: -- a dba with a Low water level and a high water level is a block.
Highwater: 0x01_b0 ext #: 0 blk #: 8 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 5
Mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x00000a8
Level 1 BMB for Low HWM block: 0x00000a8
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0109a9 --- L2 blocks are block 169
Last Level 1 BMB: 0x01000101
Last Level ii bmb: 0x0109a9
Last Level iii bmb: 0x00000000
Map Header: next 0x00000000 # extents: 17 obj #: 22989 flag: 0x10000000
Inc #0
Extent Map
-----------------------------------------------------------------
0x00000a8 length: 8 -- the starting address of the first partition is 0x00000a8, file 4, and block 168.
0x01_b0 length: 8 -- the starting address of the second zone is 0x01_b0, block 176 of file 4, which corresponds to the high water level.
0x020.b8 length: 8
0x01_c0 length: 8
0x00000c8 length: 8
0x01_d0 length: 8
0x01_d8 length: 8
0x01_e0 length: 8
0x0425e8 length: 8
0x00000f0 length: 8
0x010001a8 length: 8
0x010001b0 length: 8
0x010001b8 length: 8
0x010001c0 length: 8
0x010001c8 length: 8
0x010001d0 length: 8
0x01000100 length: 128
Auxillary Map
--------------------------------------------------------
Extent 0: L1 dba: 0x00000a8 Data dba: 0x00000ab --- here we can see from L1 DBA that this L1 manages Zone 0 and Zone 1.
Extent 1: L1 dba: 0x00000a8 Data dba: 0x00000b0
Extent 2: L1 dba: 0x020.b8 Data dba: 0x020.b9
Extent 3: L1 dba: 0x020.b8 Data dba: 0x01_c0
Extent 4: L1 dba: 0x00000c8 Data dba: 0x00000c9
Extent 5: L1 dba: 0x020.c8 Data dba: 0x01_d0
Extent 6: L1 dba: 0x00000d8 Data dba: 0x00000d9
Extent 7: L1 dba: 0x01_d8 Data dba: 0x01_e0
Extent 8: L1 dba: 0x020.e8 Data dba: 0x020.e9
Extent 9: L1 dba: 0x00000e8 Data dba: 0x00000f0
Extent 10: L1 dba: 0x010001a8 Data dba: 0x010001a9
Extent 11: L1 dba: 0x010001a8 Data dba: 0x010001b0
Extent 12: L1 dba: 0x010001b8 Data dba: 0x010001b9
Extent 13: L1 dba: 0x010001b8 Data dba: 0x010001c0
Extent 14: L1 dba: 0x010001c8 Data dba: 0x010001c9
Extent 15: L1 dba: 0x010001c8 Data dba: 0x010001d0
Extent 16: L1 dba: 0x01000100 Data dba: 0x01000102
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x0425a9
######################################## ###### DUMP the first L1 -- the only L1BYS @ bys3> alter system dump datafile 4 block 168 in the high water level;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_27244.trc
############## DUMP information is as follows:
Dump of First Level Bitmap Block
--------------------------------
Nbits: 4 nranges: 2 parent dba: 0x0109a9 poffset: 0
Unformatted: 8 total: 16 first useful block: 3
Owning instance: 1
Instance ownership changed at 01/25/2014 22:21:43
Last successful Search 01/25/2014 22:21:43
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 5
Extent Map Block Offset: 4294967295
First free datablock: 3
Bitmap block lock opcode 3
Locker xid: 0x0003. 006.00001365
Dealloc scn: 8820451.0
Flag: 0x00000001 (-/HWM)
Inc #: 0 Objd: 22989
HWM Flag: HWM Set
Highwater: 0x01_b0 ext #: 0 blk #: 8 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 5
Mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges: --- here we can also verify that this L1 manages two zones, which can correspond to the Auxillary Map of the field header.
--------------------------------------------------------
0x00000a8 Length: 8 Offset: 0
0x01_b0 Length: 8 Offset: 8
0: Metadata 1: Metadata 2: Metadata 3: 75-100% free
4: 75-100% free 5: 75-100% free 6: 75-100% free 7: 75-100% free
8: unformatted 9: unformatted 10: unformatted 11: unformatted
12: unformatted 13: unformatted 14: unformatted 15: unformatted
From the DUMP information, we can see that the first block of L1 is block 168, which is also the address of the first block of this segment.
0: Metadata 1: Metadata 2: Metadata the three correspond to block L1 of block 168, block L2 of block 169, and block 170 is the segment header-the first L3.
The high water level is in block 176, that is, the first block in the second zone. Here, because there are only eight blocks in a zone, one L1 manages two zones.
Insert a row of data. From the above DUMP information, we can see that only the section under high water level is formatted. Three of the zones store the management information of the ASSM. Therefore, in fact, 55 blocks are used, that is, 5 blocks are formatted for this insertion ..
Note: In other experiments, the number of data blocks inserted in one data row is not fixed. There are 16, 32, and 64 data blocks.
The number of data blocks to be formatted by inserting a row of data should be related to the area size and the location of the high water level. In short, the data block is formatted in batches. It is unclear how many data blocks are inserted in a batch.
###################### 3. Use the following script to start 30 sessions simultaneously for data insertion, then, view the inserted ROWID:
Result;From the query, we can see that the inserted data blocks are all under the high water level 176, and are randomly inserted into each block.
[Oracle @ bys3 ~] $ Cat insert. SQL
Sqlplus bys/bys <EOF
Insert into bys. test11 values ($1, 'Hello ');
Commit;
Exec dbms_lock.sleep (2000 );
EOF
#######
./Insert. SQL 1 &
./Insert. SQL 2 &
./Insert. SQL 3 &
./Insert. SQL 4 &
./Insert. SQL 5 &
./Insert. SQL 6 &
./Insert. SQL 7 &
./Insert. SQL 8 &
./Insert. SQL 9 &
./Insert. SQL 10 &
./Insert. SQL 11 &
./Insert. SQL 12 &
./Insert. SQL 13 &
./Insert. SQL 14 &
./Insert. SQL 15 &
./Insert. SQL 16 &
./Insert. SQL 17 &
./Insert. SQL 18 &
./Insert. SQL 19 &
./Insert. SQL 20 &
./Insert. SQL 21 &
./Insert. SQL 22 &
./Insert. SQL 23 &
./Insert. SQL 24 &
./Insert. SQL 25 &
./Insert. SQL 26 &
./Insert. SQL 27 &
./Insert. SQL 28 &
./Insert. SQL 29 &
./Insert. SQL 30 &
#################### Query after insertion:
BYS @ bys3> select dbms_rowid.ROWID_RELATIVE_FNO (rowid) fno, dbms_rowid.rowid_block_number (rowid) block #, aa, bb from test11 order by block #;
Fno block # AA BB
----------------------------------------
4 171 99 first
4 171 1 hello
4 171 1 hello
4 171 4 hello
4 171 14 hello
4 171 18 hello
4 171 21 hello
4 171 26 hello
4 172 1 hello
4 172 12 hello
4 172 11 hello
4 172 19 hello
4 172 24 hello
4 172 28 hello
4 173 8 hello
4 173 29 hello
4 173 25 hello
4 173 13 hello
4 173 15 hello
4 174 27 hello
4 174 16 hello
4 174 6 hello
4 174 7 hello
4 174 2 hello
4 175 9 hello
4 175 5 hello
4 175 17 hello
4 175 22 hello
4 175 23 hello
4 175 3 hello
From the query, we can see that the inserted data blocks are all under the high water level 176, and are randomly inserted into each block.
