Note_Tech

All technological notes.

Project maintained by simonangel-fong Hosted on GitHub Pages — Theme by mattgraham

DBA - Data Blocks, Extents, Segments

Back

How Table Data is stored?

data_storage_diagram

data_storage_diagram

Database block content

data_block01

Managing Storage Space

Space Management in Data Blocks

Optimization of Free Space in Data Blocks

Optimization by Increasing Free Space

本节着重于在数据块层面如何优化和重用空闲空间

重用释放空间

Optimization by Coalescing Fragmented Space

合并碎片空间

diagram_fragmented_space01

  • figure shows a data block with noncontiguous free space:
    • The block is depicted as containing 7 rows of slabs 厚片 of different shades. The 3rd and 6th row contain white slabs, which indicate free space.

diagram_fragmented_space01

  • shows a data block after space has been coalesced.
    • Underneath the data block header, the block has 6 stacked slabs of different shades. The top slab is clear, indicating free space.

Lab: Segments, Extent, and Blocks

Create Tablespace with default value

show user
--USER is "PDBTS_ADMIN"
show con_name
--CON_NAME
--------------------------------
--PDBTS

create tablespace ts1;


SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TS1') FROM dual;
--"CREATE TABLESPACE "TS1" DATAFILE
--  SIZE 104857600
--  AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
--  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
--  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
-- NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO"

-- verify
select * from dba_tablespaces
where tablespace_name='TS1';
--TS1	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO		DISABLED					SHARED	DISABLED					OFF	N
  • Default values:
    • PERMANENT tbsp
    • Size of tbsp: 100M
    • Auto extend next: 100M
    • Max size: 31.99G
    • block size: 8k
      • initially gives 8*block_size for initial extent, that is 65536.
    • using bitmaps/Automatic Segment Space Management(ASSM) to manage free space
    • no compression
    • enable logging
-- create user with tbsp
CREATE USER alex identified by alex
default tablespace ts1;

-- grant privileges
grant create session, create table, unlimited tablespace to alex;

-- verify user
select * from dba_users
where username='ALEX';
--ALEX	119		OPEN		03-Dec-2024	TS1	TEMP	TEMP	06-Jun-2024	DEFAULT	DEFAULT_CONSUMER_GROUP		11G 12C 	N	PASSWORD	N	NO		N	NO	USING_NLS_COMP	NO	NO	06-Jun-2024
  • default tbsp is ts1
    • any object created without any option under this user will store directly to ts1 tbsp.
-- create a tb with user
CREATE TABLE ALEX.EMP
(
    EMP_ID NUMBER,
    NAME VARCHAR2(100),
    NOTE VARCHAR2(1000)
);

-- verify
SELECT * FROM DBA_TABLES
WHERE TABLE_NAME='EMP'
AND OWNER='ALEX';
-- ALEX	EMP	TS1			VALID	10		1	255								YES	N									         1	         1	    N	ENABLED			NO		N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	NO	NO		DISABLED	YES		DISABLED	DISABLED		NO	NO	NO	DEFAULT	NO			NO	NO	DISABLED					USING_NLS_COMP	N	N	NO	NO		NO	NO	NO	NO			NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
  • the tbsp name of the tb is ts1.

Segment and Extent

-- query segments in the tb
SELECT * FROM DBA_SEGMENTS
WHERE OWNER='ALEX'
AND SEGMENT_NAME='EMP';
-- return zero rows
--   because no rows in the tb.
--   TB with no inserted data does not need storage, so Oracle does not create any segment.

-- insert and commmit a row
INSERT INTO ALEX.EMP
VALUES (1,'KHALED','the best teacher ever : )');
commit;
-- note: even not commit, a segment will be created.

-- query segment in the tb after commit
SELECT * FROM DBA_SEGMENTS
WHERE OWNER='ALEX'
AND SEGMENT_NAME='EMP';
--ALEX	EMP		TABLE	ASSM	TS1	131	130	65536	8	1	65536	1048576	1	2147483645	2147483645						131	DEFAULT	DEFAULT	DEFAULT	DISABLED
  • Table does not have any segment until inserting data.
    • Segment is created once the first row is inserted, even if it is committted.
  • segment name: table name
  • segment type: table
  • segment subtype: ASSM
    • using ASSM to manage free space
  • tbsp: ts1
  • contains 8 block, 1 extents

lab_segment_extent_block

-- query extents in the tb
select * from dba_extents
where owner='ALEX'
and  SEGMENT_NAME='EMP';
--ALEX	EMP		TABLE	TS1	0	131	128	65536	8	131
  • Currently, this tb contain only one extent, because the extent ID is 0
    • This extent has 8 blocks.
    • extent size: 65536
    • related to file no.:131

lab_segment_extent_block

begin
 for i in 1..1000
 loop
 insert into ALEX.EMP values (i,'just test name', 'just test comments');
 end loop;
 commit;
end;
/

-- query segment after 1000 inserted rows
SELECT * FROM DBA_SEGMENTS
WHERE OWNER='ALEX'
AND SEGMENT_NAME='EMP';
-- ALEX	EMP		TABLE	ASSM	TS1	131	130	131072	16	2	65536	1048576	1	2147483645	2147483645						131	DEFAULT	DEFAULT	DEFAULT	DISABLED

-- query extents after 1000 rows
select * from dba_extents
where owner='ALEX'
and  SEGMENT_NAME='EMP';
--ALEX	EMP		TABLE	TS1	0	131	128	65536	8	131
--ALEX	EMP		TABLE	TS1	1	131	136	65536	8	131

lab_segment_extent_block

lab_segment_extent_block

  • After inserting 1000 rows, tb has 2 extents with 16 blocks
begin
 for i in 1..1000000
 loop
 insert into ALEX.EMP values (i,'just test name', 'just test comments');
 end loop;
 commit;
end;
/

-- query segment after 1000000 rows
SELECT * FROM DBA_SEGMENTS
WHERE OWNER='ALEX'
AND SEGMENT_NAME='EMP';
--ALEX	EMP		TABLE	ASSM	TS1	131	130	51380224	6272	64	65536	1048576	1	2147483645	2147483645


-- query extent after 1000000 rows
select * from dba_extents
where owner='ALEX'
and  SEGMENT_NAME='EMP';
--ALEX	EMP		TABLE	TS1	0	131	128	65536	8	131
--ALEX	EMP		TABLE	TS1	1	131	136	65536	8	131
-- ...
--ALEX	EMP		TABLE	TS1	62	131	6144	1048576	128	131
--ALEX	EMP		TABLE	TS1	63	131	6272	1048576	128	131
  • the tb has 64 extents with 6272 blocks.
  • tb size: 51380224 bytes = 49 MB
  • Oracle change the extent size, from 8 blocks to 128 blocks.
    • Because of the use of ASSM

lab_segment_extent_block

lab_segment_extent_block

lab_segment_extent_block

RowID

select rowid,emp_id,name,note
from ALEX.emp
where emp_id=1000000;
-- AAAR03ACDAAABjeABJ	1000000	just test name	just test comments
  • ROWID is a pseudocolumn
    • pseudocolumn: behaves like a table column, but is not actually stored in the table.
  • ROWID is the physical location of a row.
    • Consequently it is the fastest way of locating a row, faster even than a primary key lookup.
    • Contains 18 digits: OOOOOOFFFBBBBBBRRR
      • OOOOOO object number
      • FFF Relative File Number
      • BBBBBB The data block number
      • RRR The row number

Delete and Truncate

-- delete all data
delete from ALEX.EMP;
commit;

SELECT * FROM DBA_SEGMENTS
WHERE OWNER='ALEX'
AND SEGMENT_NAME='EMP';
--ALEX	EMP		TABLE	ASSM	TS1	131	130	51380224	6272	64	65536	1048576	1	2147483645	2147483645

select * from dba_extents
where owner='ALEX'
and  SEGMENT_NAME='EMP';
  • the size of segment, blocks, and extents are not changed.
    • But data can be inserted into these blocks due to ASSM

lab_segment_extent_block

lab_segment_extent_block

truncate table ALEX.EMP;

SELECT * FROM DBA_SEGMENTS
WHERE OWNER='ALEX'
AND SEGMENT_NAME='EMP';
--ALEX	EMP		TABLE	ASSM	TS1	131	130	65536	8	1	65536	1048576	1	2147483645	2147483645						131	DEFAULT	DEFAULT	DEFAULT	DISABLED

select * from dba_extents
where owner='ALEX'
and  SEGMENT_NAME='EMP';
-- ALEX	EMP		TABLE	TS1	0	131	128	65536	8	131

the size is reset after truncate statement.

lab_segment_extent_block

lab_segment_extent_block

Chained and Migrated Rows

RowChaining

  • depicts the insertion of a large row in a data block.
    • The row is too large for the left block, so the database chains the row by placing the first row piece in the left block and the second row piece in the right block.

diagram_Row_Migration

  • the left block contains a row that is updated so that the row is now too large for the block. T
  • he database moves the entire row to the right block and leaves a pointer to the migrated row in the left block.

Segments

Lab: Type of Segment

Setup env

show user
show con_name

-- create tb
create table dept
(
  deptno NUMBER(2),
  dept_name varchar2(100)
);

-- confirm
select * from dba_tables
where table_name='DEPT';

Data Segment and Index Segment

-- query user segment
SELECT * FROM USER_SEGMENTS
WHERE SEGMENT_NAME='DEPT';
-- return no row, due to no data.

INSERT into dept
values (1, 'IT Dept');

INSERT into dept
values (2, 'FINANCE');

commit;

-- query user segment
SELECT * FROM USER_SEGMENTS
WHERE SEGMENT_NAME='DEPT';
--DEPT		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED

lab_segment

  • segment type: table
-- create index
create unique index dept_inx
on DEPT(deptno);

-- query
SELECT * FROM USER_SEGMENTS;
-- DEPT		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
-- DEPT_INX		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED

lab_segment

  • Segment types: table and index

Undo Segment


update dept
set dept_name='XXXX';

-- query segment
SELECT * FROM dba_SEGMENTS
where owner='PDBTS_ADMIN';
-- will not show any undo segment

-- query about undo
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
-- 53	64782	PDBTS_ADMIN	2	1
--USED_UREC: Number of undo records used
--USED_UBLK: Number of undo blocks used

lab_segment

  • undo segment includes 2 records within 1 block.
ROLLBACK;

-- query undo after rollback
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
-- return no rows

Segment Creation on Demand / Deferred Segment Creation

show parameter DEFERRED_SEGMENT_CREATION

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=[TRUE | FALSE];

Lab:Deferred Segment Creation

show user;
show con_name;

show parameter DEFERRED_SEGMENT_CREATION
--NAME                      TYPE    VALUE
--------------------------- ------- -----
--deferred_segment_creation boolean TRUE
  • TRUE: the segment will created for the table after
    • you crate the table using DDL statement (create table)
    • and you insert one record (DML)
create table country
(
  id number,
  name varchar2(100)
);

select *
from user_segments
where segment_name=upper('country');
-- return no rows

insert into country values (1,'Jordan');

select * from user_segments
where segment_name= upper('country');
-- COUNTRY		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED

lab_defer_segment

  • type is table
  • segment is created only after the insert of the first row.

Disable Deferred Segment Creation

--this will create the segment immidiate for the current session
alter session set DEFERRED_SEGMENT_CREATION=false;

show parameter DEFERRED_SEGMENT_CREATION
--NAME                      TYPE    VALUE
----------------------- ------- -----
--deferred_segment_creation boolean FALSE

create table jobs
(
  id number,
  title varchar2(100)
);

select *
from user_segments
where segment_name=upper('jobs');
-- JOBS		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED

Disable Deferred Segment Creation at tb Level

-- reset parameter for the current session
alter session set DEFERRED_SEGMENT_CREATION=true;
-- confirm
show parameter DEFERRED_SEGMENT_CREATION

-- disable at tb level
create table test
(
  n number
)
SEGMENT CREATION IMMEDIATE; -- create the segment when tb is created

-- query right after the creation of tb
select * from user_segments
where segment_name= upper('test');
-- TEST		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED


create table test10 ( n number )
SEGMENT CREATION DEFERRED;    -- enable deferred segment creation

select * from user_segments
where segment_name= upper('test10');
-- return no rows

Lab: Unusable index: Space saving feature

show user;
show con_name;

drop table employees purge;

-- verify enabling deferred segement creation
show parameter DEFERRED_SEGMENT_CREATION
--NAME                      TYPE    VALUE
--------------------------- ------- -----
--deferred_segment_creation boolean TRUE

create table employees
(
    emp_id number,
    fname varchar2(100),
    lname varchar2(100),
    constraint employees_pk primary key (emp_id) -- pk, create automatically a unique index
 );

select * from user_tables
where table_name='EMPLOYEES';
-- EMPLOYEES	USERS			VALID	10		1	255								YES	N									         1	         1	    N	ENABLED			NO		N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	NO	NO		DISABLED	YES		DISABLED	DISABLED		NO	NO	NO	DEFAULT	NO			NO	NO	DISABLED					USING_NLS_COMP	N	N	NO	NO		NO	NO	NO	NO			NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
-- pct_free: 10
--  means the percentage free for the block, 10% are free from the size of the block

-- query index
SELECT * FROM USER_INDEXES;
-- EMPLOYEES_PK	NORMAL	PDBTS_ADMIN	EMPLOYEES	TABLE	UNIQUE	DISABLED		USERS	2	255										10	YES							VALID				1	1	NO	N	N	N	DEFAULT	DEFAULT	DEFAULT	NO						NO				NO	NO	NO	VISIBLE		NO	NO	FULL	NO	YES

select * from user_segments
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' );
-- return no row

lab_unusable_index

  • By default, pk is unique index.

insert into employees values (1,'khaled','alkhudari');
commit;

select * from employees;


select * from user_segments
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' );
--EMPLOYEES		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
--EMPLOYEES_PK		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED

lab_unusable_index

create index fname_index on EMPLOYEES(fname);

select * from user_segments
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX');
-- EMPLOYEES		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
-- EMPLOYEES_PK		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
-- FNAME_INDEX		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED

lab_unusable_index

  • When a tb contains data and a new index is created
    • new segment will be created as well.
create index lname_index
on EMPLOYEES(lname)
UNUSABLE;

select *
from user_segments
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX','LNAME_INDEX');
--EMPLOYEES		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
--EMPLOYEES_PK		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
--FNAME_INDEX		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
  • no segment is created for the new unusable index.
alter index LNAME_INDEX REBUILD;

select * from user_segments
where segment_name IN ( 'EMPLOYEES','EMPLOYEES_PK' ,'FNAME_INDEX','LNAME_INDEX');
--EMPLOYEES		TABLE	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
--EMPLOYEES_PK		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
--FNAME_INDEX		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED
--LNAME_INDEX		INDEX	ASSM	USERS	65536	8	1	65536	1048576	1	2147483645	2147483645						DEFAULT	DEFAULT	DEFAULT	DISABLED

TOP