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 directory

2. 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.dbf

4. 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.dbf

6. 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.dbf

9. 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	216

20. 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
445

Verification:

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,444444444444444444444444444444444444

21. 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,85886287689208984375

Conclusions: 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.