In Part-1-Oracle Extent Allocation for non-partitioned table, we learned how Oracle allocates extents for non-partitioned tables and which is most appropriate in most of the cases, however when you have large tables with partitions , sub partitions , composite partitions used for your application, then I would suggest you to take closer look at the way Oracle allocates extents for such tables, most of the cases when all the extents and not densely populated with rows then it will tend to have more storage space wasted and few millions of rows would required 100s of GBs of storage which could have been accommodate in lesser storage.
Recently while investigating data fragmentation with one of our client's database, I was amazed to see that there was huge wasted space for the large partitioned tables having multiple sub-partitions. The database I was investigating with fragmentation was an OLTP database having couple of large partitions/sub-partitions tables that occupies majority of database storage and I was surprised to see more than 70% storage was not properly utilized due extent allocation mystery for partitioned table which I will be revealing in this article.
Table Partition with subpartition creation:
SQL> CREATE TABLE "T_OBJ_SUB1"
2 ( "OWNER" VARCHAR2(128 BYTE) ,
3 "OBJECT_NAME" VARCHAR2(128 BYTE) ,
4 "SUBOBJECT_NAME" VARCHAR2(128 BYTE) ,
5 "OBJECT_ID" NUMBER,
6 "DATA_OBJECT_ID" NUMBER,
7 "OBJECT_TYPE" VARCHAR2(23 BYTE) ,
8 "CREATED" DATE,
9 "LAST_DDL_TIME" DATE,
10 "TIMESTAMP" VARCHAR2(19 BYTE) ,
11 "STATUS" VARCHAR2(7 BYTE) ,
12 "TEMPORARY" VARCHAR2(1 BYTE) ,
13 "GENERATED" VARCHAR2(1 BYTE) ,
14 "SECONDARY" VARCHAR2(1 BYTE) ,
15 "NAMESPACE" NUMBER,
16 "EDITION_NAME" VARCHAR2(128 BYTE) ,
17 "SHARING" VARCHAR2(18 BYTE) ,
18 "EDITIONABLE" VARCHAR2(1 BYTE) ,
19 "ORACLE_MAINTAINED" VARCHAR2(1 BYTE) ,
20 "APPLICATION" VARCHAR2(1 BYTE) ,
21 "DEFAULT_COLLATION" VARCHAR2(100 BYTE) ,
22 "DUPLICATED" VARCHAR2(1 BYTE) ,
23 "SHARDED" VARCHAR2(1 BYTE) ,
24 "CREATED_APPID" NUMBER,
25 "CREATED_VSNID" NUMBER,
26 "MODIFIED_APPID" NUMBER,
27 "MODIFIED_VSNID" NUMBER,
28 ins_dt date
29 ) SEGMENT CREATION IMMEDIATE
30 PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
31 NOCOMPRESS LOGGING
32 tablespace IPYPRTBPS1
33 --STORAGE --commented
34 PARTITION BY RANGE ("INS_DT") INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
35 SUBPARTITION BY HASH ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME" )
36 SUBPARTITION TEMPLATE (
37 SUBPARTITION "T1" ,
38 SUBPARTITION "T2" ,
39 SUBPARTITION "T3" ,
40 SUBPARTITION "T4" ,
41 SUBPARTITION "T5" ,
42 SUBPARTITION "T6" ,
43 SUBPARTITION "T7" ,
44 SUBPARTITION "T8" )
45 (PARTITION "P_FIRST" VALUES LESS THAN (TIMESTAMP' 2018-02-12 00:00:00')
46 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
47 STORAGE(
48 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
49 ( SUBPARTITION "P_FIRST_T1" SEGMENT CREATION DEFERRED
50 NOCOMPRESS ,
51 SUBPARTITION "P_FIRST_T2" SEGMENT CREATION DEFERRED
52 NOCOMPRESS ,
53 SUBPARTITION "P_FIRST_T3" SEGMENT CREATION DEFERRED
54 NOCOMPRESS ,
55 SUBPARTITION "P_FIRST_T4" SEGMENT CREATION DEFERRED
56 NOCOMPRESS ,
57 SUBPARTITION "P_FIRST_T5" SEGMENT CREATION DEFERRED
58 NOCOMPRESS ,
59 SUBPARTITION "P_FIRST_T6" SEGMENT CREATION DEFERRED
60 NOCOMPRESS ,
61 SUBPARTITION "P_FIRST_T7" SEGMENT CREATION DEFERRED
62 NOCOMPRESS ,
63 SUBPARTITION "P_FIRST_T8" SEGMENT CREATION DEFERRED
64 NOCOMPRESS ) ) ENABLE ROW MOVEMENT ;
Table created.
SQL>
SQL> insert into t_obj_sub1 (select o.*,o.created from dba_objects o);
194556 rows created.
SQL> commit;
Commit complete.
SQL> set line 500 pagesize 50 echo on
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 99
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL>
SQL>
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
2 from dba_extents where owner='TEST' and segment_name='T_OBJ_SUB1';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- -------------------- ------------------------- ------------------------- --------------- --------- ------- ----------- ----------- ------
TEST T_OBJ_SUB1 SYS_SUBP137446 TABLE SUBPARTITION IPYPRTBPS1 0 8 266496 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137448 TABLE SUBPARTITION IPYPRTBPS1 0 8 270592 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137454 TABLE SUBPARTITION IPYPRTBPS1 0 8 275712 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137455 TABLE SUBPARTITION IPYPRTBPS1 0 8 272640 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137459 TABLE SUBPARTITION IPYPRTBPS1 0 8 277760 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137467 TABLE SUBPARTITION IPYPRTBPS1 0 8 286976 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137469 TABLE SUBPARTITION IPYPRTBPS1 0 8 280704 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137470 TABLE SUBPARTITION IPYPRTBPS1 0 8 284928 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137472 TABLE SUBPARTITION IPYPRTBPS1 0 8 283904 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137473 TABLE SUBPARTITION IPYPRTBPS1 0 8 282880 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137475 TABLE SUBPARTITION IPYPRTBPS1 0 8 290944 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137476 TABLE SUBPARTITION IPYPRTBPS1 0 8 291968 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137487 TABLE SUBPARTITION IPYPRTBPS1 0 8 300288 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137488 TABLE SUBPARTITION IPYPRTBPS1 0 8 296192 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137489 TABLE SUBPARTITION IPYPRTBPS1 0 8 295168 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137493 TABLE SUBPARTITION IPYPRTBPS1 0 8 305408 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137495 TABLE SUBPARTITION IPYPRTBPS1 0 8 309504 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137496 TABLE SUBPARTITION IPYPRTBPS1 0 8 311552 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137497 TABLE SUBPARTITION IPYPRTBPS1 0 8 302336 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137499 TABLE SUBPARTITION IPYPRTBPS1 0 8 306432 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137507 TABLE SUBPARTITION IPYPRTBPS1 0 8 314624 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137509 TABLE SUBPARTITION IPYPRTBPS1 0 8 313600 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137512 TABLE SUBPARTITION IPYPRTBPS1 0 8 315648 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137516 TABLE SUBPARTITION IPYPRTBPS1 0 8 317696 8388608 1024
:
:
:
TEST T_OBJ_SUB1 SYS_SUBP137372 TABLE SUBPARTITION IPYPRTBPS1 0 9 2998400 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137373 TABLE SUBPARTITION IPYPRTBPS1 0 9 2996352 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137374 TABLE SUBPARTITION IPYPRTBPS1 0 9 3000448 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137378 TABLE SUBPARTITION IPYPRTBPS1 0 9 3005568 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137381 TABLE SUBPARTITION IPYPRTBPS1 0 9 3004544 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137382 TABLE SUBPARTITION IPYPRTBPS1 0 9 3006592 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137385 TABLE SUBPARTITION IPYPRTBPS1 0 9 3009664 8388608 1024
342 rows selected.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T_OBJ_SUB1',GRANULARITY=>'ALL', estimate_percent => dbms_stats.auto_sample_size, degree => '4',cascade => TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> col table_owner for a10
SQL> col table_name for a15
SQL> col partition_name for a15
SQL> col subpartition_name for a25
SQL> col partition_position for 999
SQL> col subpartition_position for 9999
SQL> col tablespace_name for a15
SQL> col initial_extent for 999999999999
SQL> col next_extent for 999999999999
SQL> col num_rows for 999999999999
SQL> col blocks for 99999999
SQL> col last_analyzed for a25
SQL>
SQL> select table_owner,table_name ,partition_name,subpartition_name,partition_position,subpartition_position,tablespace_name,initial_extent, next_extent ,
2 num_rows, blocks,last_analyzed
3 from dba_tab_subpartitions where table_name='T_OBJ_SUB1';
TABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_POSITION SUBPARTITION_POSITION TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT NUM_ROWS BLOCKS LAST_ANALYZED
---------- --------------- --------------- ------------------------- ------------------ --------------------- --------------- -------------- ------------- ------------- --------- --------------
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T1 1 1 IPYPRTBPS1 8388608 1048576 8971 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T2 1 2 IPYPRTBPS1 8388608 1048576 9069 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T3 1 3 IPYPRTBPS1 8388608 1048576 8850 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T8 1 8 IPYPRTBPS1 8388608 1048576 9067 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T5 1 5 IPYPRTBPS1 8388608 1048576 9088 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T6 1 6 IPYPRTBPS1 8388608 1048576 9134 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T7 1 7 IPYPRTBPS1 8388608 1048576 9144 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T4 1 4 IPYPRTBPS1 8388608 1048576 9033 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137250 2 1 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137251 2 2 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137252 2 3 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137257 2 8 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137254 2 5 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137255 2 6 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137256 2 7 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137253 2 4 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137331 3 1 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137332 3 2 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137333 3 3 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137334 3 4 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137335 3 5 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137336 3 6 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137337 3 7 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137338 3 8 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137349 4 1 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137350 4 2 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137351 4 3 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137352 4 4 IPYPRTBPS1 8388608
:
:
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137423 57 3 IPYPRTBPS1 8388608 1048576 3 376 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137421 57 1 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137422 57 2 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
456 rows selected.
SQL>
SQL> --Wasted space below HWM
SQL> col owner for a20
SQL> col table_name for a30
SQL> col LAST_ANALYZED for a20
SQL> col EXTENT_MANAGEMENT for a20
SQL> col SEGMENT_SPACE_MANAGEMENT for a20
SQL> col tablespace_name for a20
SQL> col sgment_space_mgmt for a15
SQL> col partition_name for a25
SQL> col subparition_name for a30
SQL>
SQL>
SQL> select * from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name,last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,round((blocks*8),2) "size (kb)" ,
5 round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
6 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
7 from
8 dba_tab_subpartitions T, dba_tablespaces s
9 where
10 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
11 and
12 t.tablespace_name=s.tablespace_name
13 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
14 )
15 where table_owner='TEST' AND TABLE_NAME='T_OBJ_SUB1' ;
TABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED TABLESPACE_NAME EXTENT_MGM SEGMEN NUM_ROWS BLOCKS EMPTY_BLOCKS size (kb) actual_data (kb) wasted_space (kb)
---------- ------------------------------ ------------------------- ------------------------- -------------------- -------------------- ---------- ------ ------------- --------- ------------ ---------- ---------------- -----------------
TEST T_OBJ_SUB1 SYS_P137690 SYS_SUBP137682 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137708 SYS_SUBP137705 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137708 SYS_SUBP137706 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137717 SYS_SUBP137714 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137753 SYS_SUBP137748 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137355 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137483 SYS_SUBP137476 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137510 SYS_SUBP137507 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137332 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137335 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89 :
:
:
TEST T_OBJ_SUB1 SYS_P137690 SYS_SUBP137683 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 754 0 6032 .11 6031.89
TEST T_OBJ_SUB1 SYS_P137528 SYS_SUBP137520 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 376 0 3008 .14 3007.86
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137423 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 3 376 0 3008 .39 3007.61
TEST T_OBJ_SUB1 SYS_P137294 SYS_SUBP137289 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 803 124 0 992 105.08 886.92
320 rows selected.
SQL>
SQL>
SQL>
SQL> select table_owner,table_name,sum(size_kb),sum(actual_data_kb),sum(wasted_space_kb) from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name, last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,
5 round((blocks*8),2) size_kb ,
6 round((num_rows*avg_row_len/1024),2) actual_data_kb,
7 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) wasted_space_kb
8 from
9 dba_tab_subpartitions T, dba_tablespaces s
10 where
11 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
12 and
13 t.tablespace_name=s.tablespace_name
14 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
15 )
16 where table_owner='TEST' AND TABLE_NAME='T_OBJ_SUB1'
17 group by table_owner,table_name;
TABLE_OWNE TABLE_NAME SUM(SIZE_KB) SUM(ACTUAL_DATA_KB) SUM(WASTED_SPACE_KB)
---------- ------------------------------ ------------ ------------------- --------------------
TEST T_OBJ_SUB1 2553184 26097.06 2527086.94 SQL>
SQL>
SQL>
#1: specify 65k or smaller extent_size when you define the paritioned tables.
#2: set hiddent parameter _partition_large_extents to false. (We will discuss this in another article.)
Now let's examine method #1, when we create paritioned/sub-paritioned table T_OBJ_S2 with smaller initial_extent size i.e STORAGE(INITIAL 10K NEXT 20K). Oracle will allocate 64k initial extent_size. Please note that if you create 64k or lesser initial extent size Oracle will allocate at least 64k initial extent and the same algorithm for storage allocation will be applied similar to normal tables that mean first 16 extent size will be 64k and then 17th extent onward size would be 1MB and so on, which is evident from the below example.
With smaller extent size in this case all the data was able to fit in 54MB instead of 2.50GB in previous case with default storage.
Please note that it again depends on how densely you populate each extents, if you populate enough rows in each extents to optimally then it should be fine but if you use default extent allocation scheme for paritioned/sub-partitioned tables and you do not populate enough rows in each extents allocatged for the partitiones/sub-partitions then you may have huge wasted space in your database.
SQL>
SQL> CREATE TABLE "T_OBJ_S2"
2 ( "OWNER" VARCHAR2(128 BYTE) ,
3 "OBJECT_NAME" VARCHAR2(128 BYTE) ,
4 "SUBOBJECT_NAME" VARCHAR2(128 BYTE) ,
5 "OBJECT_ID" NUMBER,
6 "DATA_OBJECT_ID" NUMBER,
7 "OBJECT_TYPE" VARCHAR2(23 BYTE) ,
8 "CREATED" DATE,
9 "LAST_DDL_TIME" DATE,
10 "TIMESTAMP" VARCHAR2(19 BYTE) ,
11 "STATUS" VARCHAR2(7 BYTE) ,
12 "TEMPORARY" VARCHAR2(1 BYTE) ,
13 "GENERATED" VARCHAR2(1 BYTE) ,
14 "SECONDARY" VARCHAR2(1 BYTE) ,
15 "NAMESPACE" NUMBER,
16 "EDITION_NAME" VARCHAR2(128 BYTE) ,
17 "SHARING" VARCHAR2(18 BYTE) ,
18 "EDITIONABLE" VARCHAR2(1 BYTE) ,
19 "ORACLE_MAINTAINED" VARCHAR2(1 BYTE) ,
20 "APPLICATION" VARCHAR2(1 BYTE) ,
21 "DEFAULT_COLLATION" VARCHAR2(100 BYTE) ,
22 "DUPLICATED" VARCHAR2(1 BYTE) ,
23 "SHARDED" VARCHAR2(1 BYTE) ,
24 "CREATED_APPID" NUMBER,
25 "CREATED_VSNID" NUMBER,
26 "MODIFIED_APPID" NUMBER,
27 "MODIFIED_VSNID" NUMBER,
28 ins_dt date
29 ) SEGMENT CREATION IMMEDIATE
30 tablespace IPYPRTBPS1
31 STORAGE(INITIAL 10K NEXT 20K)
32 PARTITION BY RANGE ("INS_DT") INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
33 SUBPARTITION BY HASH ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME" )
34 SUBPARTITION TEMPLATE (
35 SUBPARTITION "T1" ,
36 SUBPARTITION "T2" ,
37 SUBPARTITION "T3" ,
38 SUBPARTITION "T4" ,
39 SUBPARTITION "T5" ,
40 SUBPARTITION "T6" ,
41 SUBPARTITION "T7" ,
42 SUBPARTITION "T8" )
43 (PARTITION "P_FIRST" VALUES LESS THAN (TIMESTAMP' 2018-02-12 00:00:00')
44 ( SUBPARTITION "P_FIRST_T1" SEGMENT CREATION DEFERRED
45 NOCOMPRESS ,
46 SUBPARTITION "P_FIRST_T2" SEGMENT CREATION DEFERRED
47 NOCOMPRESS ,
48 SUBPARTITION "P_FIRST_T3" SEGMENT CREATION DEFERRED
49 NOCOMPRESS ,
50 SUBPARTITION "P_FIRST_T4" SEGMENT CREATION DEFERRED
51 NOCOMPRESS ,
52 SUBPARTITION "P_FIRST_T5" SEGMENT CREATION DEFERRED
53 NOCOMPRESS ,
54 SUBPARTITION "P_FIRST_T6" SEGMENT CREATION DEFERRED
55 NOCOMPRESS ,
56 SUBPARTITION "P_FIRST_T7" SEGMENT CREATION DEFERRED
57 NOCOMPRESS ,
58 SUBPARTITION "P_FIRST_T8" SEGMENT CREATION DEFERRED
59 NOCOMPRESS ) ) ENABLE ROW MOVEMENT ;
Table created.
SQL>
SQL>
SQL> insert into t_obj_s2 (select o.*,o.created from dba_objects o);
195070 rows created.
SQL>
SQL>
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> BEGIN
2 dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T_OBJ_S2',GRANULARITY=>'ALL', estimate_percent => dbms_stats.auto_sample_size, degree => '4',cascade => TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 99
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL>
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
2 from dba_extents where owner='TEST' and segment_name='T_OBJ_S2';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- -------------------- ------------------------- ------------------------- --------------- --------- ------- ----------- ----------- ------
TEST T_OBJ_S2 SYS_SUBP137941 TABLE SUBPARTITION IPYPRTBPS1 0 9 209144 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 0 9 160936 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 1 8 68768 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 2 11 56488 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 3 10 56504 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 4 9 282784 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 0 9 147672 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 1 8 64760 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 2 11 56512 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 3 10 56528 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 4 9 282808 65536 8
TEST T_OBJ_S2 SYS_SUBP137794 TABLE SUBPARTITION IPYPRTBPS1 0 9 160984 65536 8
TEST T_OBJ_S2 SYS_SUBP137794 TABLE SUBPARTITION IPYPRTBPS1 1 8 71920 65536 8
TEST T_OBJ_S2 SYS_SUBP137794 TABLE SUBPARTITION IPYPRTBPS1 2 11 56552 65536 8
TEST T_OBJ_S2 SYS_SUBP137871 TABLE SUBPARTITION IPYPRTBPS1 0 9 198816 65536 8
TEST T_OBJ_S2 SYS_SUBP137871 TABLE SUBPARTITION IPYPRTBPS1 1 8 60640 65536 8
TEST T_OBJ_S2 SYS_SUBP137877 TABLE SUBPARTITION IPYPRTBPS1 0 9 197840 65536 8
TEST T_OBJ_S2 SYS_SUBP137877 TABLE SUBPARTITION IPYPRTBPS1 1 8 60656 65536 8
TEST T_OBJ_S2 SYS_SUBP138053 TABLE SUBPARTITION IPYPRTBPS1 0 9 228568 65536 8
TEST T_OBJ_S2 SYS_SUBP137891 TABLE SUBPARTITION IPYPRTBPS1 0 9 198848 65536 8
:
:
:
TEST T_OBJ_S2 SYS_SUBP138163 TABLE SUBPARTITION IPYPRTBPS1 0 9 281800 65536 8
TEST T_OBJ_S2 SYS_SUBP138164 TABLE SUBPARTITION IPYPRTBPS1 0 9 281784 65536 8
TEST T_OBJ_S2 SYS_SUBP138166 TABLE SUBPARTITION IPYPRTBPS1 0 9 281792 65536 8
TEST T_OBJ_S2 SYS_SUBP138169 TABLE SUBPARTITION IPYPRTBPS1 0 9 281824 65536 8
TEST T_OBJ_S2 SYS_SUBP138170 TABLE SUBPARTITION IPYPRTBPS1 0 9 281816 65536 8
TEST T_OBJ_S2 SYS_SUBP138171 TABLE SUBPARTITION IPYPRTBPS1 0 9 281832 65536 8
TEST T_OBJ_S2 SYS_SUBP138178 TABLE SUBPARTITION IPYPRTBPS1 0 9 282840 65536 8
TEST T_OBJ_S2 SYS_SUBP138179 TABLE SUBPARTITION IPYPRTBPS1 0 9 282856 65536 8
TEST T_OBJ_S2 SYS_SUBP138180 TABLE SUBPARTITION IPYPRTBPS1 0 9 282848 65536 8
TEST T_OBJ_S2 SYS_SUBP138183 TABLE SUBPARTITION IPYPRTBPS1 0 9 282864 65536 8
TEST T_OBJ_S2 SYS_SUBP138186 TABLE SUBPARTITION IPYPRTBPS1 0 9 283800 65536 8
TEST T_OBJ_S2 SYS_SUBP138187 TABLE SUBPARTITION IPYPRTBPS1 0 9 283832 65536 8
TEST T_OBJ_S2 SYS_SUBP138189 TABLE SUBPARTITION IPYPRTBPS1 0 9 283816 65536 8
TEST T_OBJ_S2 SYS_SUBP138190 TABLE SUBPARTITION IPYPRTBPS1 0 9 282872 65536 8
TEST T_OBJ_S2 SYS_SUBP138191 TABLE SUBPARTITION IPYPRTBPS1 0 9 283824 65536 8
TEST T_OBJ_S2 SYS_SUBP138193 TABLE SUBPARTITION IPYPRTBPS1 0 9 283808 65536 8
753 rows selected.
SQL>
SQL> --Wasted space below HWM
SQL> col owner for a20
SQL> col table_name for a30
SQL> col LAST_ANALYZED for a20
SQL> col EXTENT_MANAGEMENT for a20
SQL> col SEGMENT_SPACE_MANAGEMENT for a20
SQL> col tablespace_name for a20
SQL> col sgment_space_mgmt for a15
SQL> col partition_name for a25
SQL> col subparition_name for a30
SQL>
SQL>
SQL> select * from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name,last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,round((blocks*8),2) "size (kb)" ,
5 round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
6 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
7 from
8 dba_tab_subpartitions T, dba_tablespaces s
9 where
10 -- (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
11 --and
12 t.tablespace_name=s.tablespace_name
13 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
14 )
15 where table_owner='TEST' AND TABLE_NAME='T_OBJ_S2' ;
TABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED TABLESPACE_NAME EXTENT_MGM SEGMEN NUM_ROWS BLOCKS EMPTY_BLOCKS size (kb) actual_data (kb) wasted_space (kb)
---------- ------------------------------ ------------------------- ------------------------- -------------------- -------------------- ---------- ------ ------------- --------- ------------ ---------- ---------------- -----------------
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137813 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 5997 244 0 1952 802.33 1149.67
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137814 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 5983 244 0 1952 806.3 1145.7
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137812 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 5998 244 0 1952 808.32 1143.68
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137809 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6048 244 0 1952 809.16 1142.84
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137808 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6012 244 0 1952 810.21 1141.79
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137810 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6043 244 0 1952 814.39 1137.61
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137811 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6105 244 0 1952 816.78 1135.22
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137815 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6092 244 0 1952 820.99 1131.01
TEST T_OBJ_S2 P_FIRST P_FIRST_T3 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 8850 244 0 1952 1218.6 733.4
TEST T_OBJ_S2 P_FIRST P_FIRST_T1 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 8971 244 0 1952 1235.26 716.74
TEST T_OBJ_S2 P_FIRST P_FIRST_T8 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 9067 244 0 1952 1239.63 712.37
TEST T_OBJ_S2 P_FIRST P_FIRST_T4 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 9033 244 0 1952 1243.8 708.2
:
:
:
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137965 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137966 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137967 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137968 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137978 SYS_SUBP137970 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137978 SYS_SUBP137972 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
456 rows selected.
SQL>
SQL>
SQL> select table_owner,table_name,sum(size_kb),sum(actual_data_kb),sum(wasted_space_kb) from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name, last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,
5 round((blocks*8),2) size_kb ,
6 round((num_rows*avg_row_len/1024),2) actual_data_kb,
7 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) wasted_space_kb
8 from
9 dba_tab_subpartitions T, dba_tablespaces s
10 where
11 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
12 and
13 t.tablespace_name=s.tablespace_name
14 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
15 )
16 where table_owner='TEST' AND TABLE_NAME='T_OBJ_S2'
17 group by table_owner,table_name;
TABLE_OWNE TABLE_NAME SUM(SIZE_KB) SUM(ACTUAL_DATA_KB) SUM(WASTED_SPACE_KB)
---------- ------------------------------ ------------ ------------------- --------------------
TEST T_OBJ_S2 54072 26163.87 27908.13
SQL>
No comments:
Post a Comment