Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)? (Choose two.)
A.
PCTFREE defaults to 10% for all blocks in all segments for all compression methods
B.
ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows
C.
Update operations always attempt to find blocks with free space appropriate to the length of the row being updated
D.
Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted
E.
A block will always be eligible for inserts if the row is short enough to fit into the block
the answer is B and D
Characteristics of Bitmap Segment Management
Bitmap space management uses four bits inside each data block header to indicate the amount of available space in the data block. Unlike traditional space management with a fixed relink and unlink threshold, bitmap space managements allow Oracle to compare the actual row space for an INSERT with the actual available space on the data block. This enables better reuse of the available free space especially for objects with rows of highly varying size. Here are the values inside the four-bit space:
Value Meaning
0000 Unformatted Block
0001 Block is logically full
0010 <25% free space
0011 >25% but <50% free space
0100 > 50% but <75% free space
0101 >75% free space
Table 1: Bitmap value meanings.
A - FALSE; default 0 for compressed tables
B - TRUE; each block is divided into four sections, named FS1 (between 0 and 25% of free space), FS2 (25% to 50% free), FS3 (50% to 75% free), and FS4 (75% to 100% free)
C - FALSE; only before INSERT operations free space in block is tested and when there is a lack of space, new blocks are allocated. In case of UPDATE operations, when there is a lack of space, row migration occurs.
D - TRUE; see C
E - FALSE; if ASSM is enabled and free space in a block is less than PCTFREE, insert to the block will be rejected
I believe C and D are wrong because I've seen many cases where Oracle "recycle" blocks deleted that are still occupying space, but they are in the freelist so can be used to insert or updated data. So the clause ALWAYS in question makes both False in my opinion.
A is False because there is not fixed default for PCTFREE.
By exclusion correct answer are BE.
A is wrong for obvious reasons. All segments include (tables, indexes, undo and temp). On top of that "all" compression is mentioned in the answer.
B is correct. This is a well known feature of ASSM and can be queried via the dbms_space.SPACE_USAGE procedure.
C is wrong. The key to this answer is "always". If a block has sufficient free space to accomodate the row update then Oracle does not need to search for a different block with enough free space.
D is correct. Oracle does this by scanning the BMB in the segment to determine a block that can store the entire row. The main purpose here is to avoid row chanining.
E is wrong. It all depends what the PCTFREE value is for the segment. A block with a PCTFREE of 10% could have rows that uses 90% of the available space, that block is deemed full and not eligible for insert operations.
A...for all compression methods is not correct!!
create table T1
( col1 varchar2(100) );
create table T2
( col1 varchar2(100) )
row store compress basic;
create table T3
( col1 varchar2(100) )
row store compress advanced;
SQL> select TABLE_NAME,COMPRESSION,COMPRESS_FOR,PCT_FREE from dba_tables where OWNER='SCOTT' order by TABLE_NAME;
TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE
-------------------- -------- ------------------------------ ----------
T1 DISABLED 10
T2 ENABLED BASIC 0
T3 ENABLED ADVANCED 10
This make me very confused!!
Someone has any suggestion?
A could be true as the default value for pctfree is 10, but I am not sure for all compression methods
B should be true with ASSM each block is divided into 4 sections named FS1 (between 0 and 25% free space), FS2 (25% to 50% free space), FS3 (50% to 75% free space) and FS4 (75% to 100% free space)
E is not true, a block is only eligible for inserts if it's not full! A block is marked as full when the pctfree threshold is reached, at this point the block is only available for update or delete operations. The block will be available for inserts again only after the block gets under the pctfree treshold
I this case I'd go with answers: B, C
But I am not completely sure, to me they seem like the right answers
I agree with B, but I would go on D rather than C. Update operations should first try to fit the new data in the same block and only when it's impossible they will look for other blocks where to fit the complete row.
A and E have already been explained to be false, which upon which I agree.
there are these 4 fullness "categories", but 6 values to represent the fullness
Value | Meaning | SPACE_USAGE Procedure
0000 | Unformatted Block | unformatted_blocks
0001 | Block is logically full | full_blocks
0010 | <25% free space | fs1_blocks
0011 | >=25% but <50% free space | fs2_blocks
0100 | >=50% but <75% free space | fs3_blocks
0101 | >=75% free space | fs4_blocks
i still would agree to B
A. PCTFREE defaults to 10% for all blocks in all segments for all compression methods
B. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows
C. Update operations always attempt to find blocks with free space appropriate to the length of the row being updated
D. Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted
E) The first block with enough free space to accommodate a row being inserted will always be used for that row.
upvoted 1 times
...
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
ioio
Highly Voted 2 years, 9 months agonautil2
Most Recent 4 months agoOracle2020
4 months, 3 weeks agoauwia
6 months, 1 week agoauwia
6 months, 1 week ago[Removed]
1 year agotrgbighero
1 year, 3 months agoDarkseid1231
1 year, 7 months agoFranky_T
1 year, 9 months agoemburria
1 year, 12 months agoemburria
1 year, 12 months agoryuah
2 years agoFan
2 years, 9 months agoEric_F
1 year, 10 months agoogdru
2 years, 10 months agoadoptc94
3 years, 4 months agoSimoneF
3 years, 1 month agoEkos
3 years, 1 month agoNowOrNever
3 years, 3 months agoadoptc94
3 years, 4 months agoyou1234
3 years, 6 months ago