Database: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0
I write this post in order to follow step by step how a bigfile tablespace is created and managed in 19c. The main purpose is to find a way to estimate the space benefits of defragmenting the tablespace. I will use two methods.
Method 1. By calculating the theoretical size of the tables. It is outlined below in steps 19-20. The limitation of this calculation is that right now it works for tables only
Method 2. By using the Segment Advisor. Outlined below in steps 21-22
1. Check the datafile does not exist at OS level:
[oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf
ls: cannot access /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf: No such file or directory2. Create the tablespace as user SYSTEM:
CREATE bigfile tablespace "TEST_TBS" datafile
'/u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf' size 10M
autoextend ON next 10M maxsize 5G
logging online permanent blocksize 8192 extent management local
autoallocate default nocompress segment space management auto
;3. Check DB file getting created:
[oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf
-rw-r-----. 1 oracle oinstall 11M Nov 13 08:37 /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf4. Login as ‘radu’ user. Create test tables in the test_tbs tablespace:
create table qz_results (
player varchar2(10)
, round integer
, score number
)
tablespace TEST_TBS
;
--
create table qz_results2 (
player varchar2(10)
, round integer
, score number
)
tablespace TEST_TBS
;5. Check size of the DB file. Should not be changed: 11M
[oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf
-rw-r-----. 1 oracle oinstall 11M Nov 13 08:42 /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf6. Run test SQL:
SELECT
fs.tablespace_name "Tablespace",
( df.totalspace - fs.freespace ) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 *(fs.freespace / df.totalspace)) "Pct. Free"
FROM
(
SELECT
tablespace_name,
round(SUM(bytes) / 1048576) totalspace
FROM
dba_data_files
GROUP BY
tablespace_name
) df,
(
SELECT
tablespace_name,
round(SUM(bytes) / 1048576) freespace
FROM
dba_free_space
GROUP BY
tablespace_name
) fs
WHERE
df.tablespace_name = fs.tablespace_name
and df.tablespace_name = 'TEST_TBS';Output:
Tablespace Used MB Free MB Total MB Pct. Free
TEST_TBS 6 4 10 40
7. Populate first test table with 9 million rows. Example of block used to populate the table:
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO qz_results VALUES (
'Johansson',
1,
110
);
INSERT INTO qz_results VALUES (
'Johansson',
2,
125
);
INSERT INTO qz_results VALUES (
'Johansson',
3,
105
);
INSERT INTO qz_results VALUES (
'Radetskiy',
1,
108
);
INSERT INTO qz_results VALUES (
'Radetskiy',
2,
115
);
INSERT INTO qz_results VALUES (
'Radetskiy',
3,
122
);
INSERT INTO qz_results VALUES (
'Williams',
1,
120
);
INSERT INTO qz_results VALUES (
'Williams',
2,
112
);
INSERT INTO qz_results VALUES (
'Williams',
3,
112
);
END LOOP;
COMMIT;
END;8. Check size of the DB file –> 291M
[oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf
-rw-r-----. 1 oracle oinstall 291M Nov 13 09:29 /u01/app/oracle/oradata/ORCLCDB/orcl/test_tbs.dbf9. Run the control SQL from step 6. Used –> 284M
Tablespace Used MB Free MB Total MB Pct. Free
TEST_TBS 284 14 298 5
10. Populate second test table with 9 million rows.
11. Check size of the DB file –> 526M
12. Run the control SQL from step 6. Used –> 500M
Tablespace Used MB Free MB Total MB Pct. Free
TEST_TBS 500 25 525 5
13. Delete all the rows from the first test table and commit.
delete from qz_results;
commit;14. Check size of the DB file –> 526M. Unchanged
15. Run the control SQL from step 6. Used –> 500M Unchanged
Tablespace Used MB Free MB Total MB Pct. Free
TEST_TBS 500 25 525 5
16. Delete two thirds (6 million) rows form the second table and commit.
delete from qz_results2 where player in ('Radetskiy', 'Williams');
commit;17. Check size of the DB file –> 526M. Unchanged
18. Run the control SQL from step 6. Used –> 500M Unchanged
Tablespace Used MB Free MB Total MB Pct. Free
TEST_TBS 500 25 525 5
19. Calculate the theoretical size of the tables by using the DBA_TABLES (this can only succeed if the statistics were collected for the impacted objects) –> approx. 50 MB
select owner, table_name,
num_rows * avg_row_len / 1024 / 1024 "Theoretical Size (MB)" from dba_tables
where owner = 'RADU'
and tablespace_name = 'TEST_TBS'
;
OWNER TABLE_NAME Theoretical Size (MB)
RADU QZ_RESULTS 0
RADU QZ_RESULTS2 48,63739013671875
select sum( num_rows * avg_row_len / 1024 / 1024 ) "Theoretical Size (MB)" from dba_tables
where owner = 'RADU'
and tablespace_name = 'TEST_TBS'
;
Theoretical Size (MB)
48,63739013671875
Note: using dba_segments will not generate a correct theoretical size!
select segment_name, bytes/1024/1024
from dba_segments
where owner = 'RADU'
and tablespace_name = 'TEST_TBS'
and segment_type='TABLE'
;
SEGMENT_NAME MB
QZ_RESULTS 216
QZ_RESULTS2 21620. Calculate estimate space savings by adding a 10% tolerance to the total theoretical size computed on step 20 –> 445 MB
select 500 - (0 + 50) * 110 / 100 "MB Saved" from dual;
MB Saved
445Verification:
The tables in the previous steps were created so they generate a predictable space usage. As 18 million rows needed 500 MB and we deleted 16 million rows, the approx. potential space saving will be –> approx. 445 MB
select (16 * 500) / 18 "MB Deleted" from dual;
MB Deleted
444,44444444444444444444444444444444444421. Running the Segment Advisor Manually with PL/SQL (ref.: Oracle Documentation)
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='radu_task';
descr:='Tablespace Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);
dbms_advisor.create_object (
task_name => name,
object_type => 'TABLESPACE',
attr1 => 'TEST_TBS',
attr2 => NULL,
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
end;
/22. Query the DBA_ADVISOR_* tables and estimate savings –> 330 MB
select message, more_info from DBA_ADVISOR_FINDINGS
where task_name = 'radu_task';
MESSAGE MORE_INFO
Enable row movement of the table RADU.QZ_RESULTS and perform shrink, estimated savings is 226484224 bytes. Allocated Space:226492416: Used Space:8192: Reclaimable Space :226484224:
Enable row movement of the table RADU.QZ_RESULTS2 and perform shrink, estimated savings is 123592167 bytes. Allocated Space:226492416: Used Space:102900249: Reclaimable Space :123592167:
select benefit_type from DBA_ADVISOR_RECOMMENDATIONS
where task_name = 'radu_task'
;
BENEFIT_TYPE
Enable row movement of the table RADU.QZ_RESULTS and perform shrink, estimated savings is 226484224 bytes.
Enable row movement of the table RADU.QZ_RESULTS2 and perform shrink, estimated savings is 123592167 bytes.
select (123592167 + 226484224) / 1024 / 1024 "MB Advisor Savings" from dual;
MB Advisor Savings
333,85886287689208984375Conclusions: two methods were outlined. Method 1 has limitations and provides a bigger potential benefit while method 2 uses the segment advisor at tablespace level and provides a more conservative estimation that might fir much more test cases beside the special case outlined in this article.