Friday, November 27, 2009

11g Compression and Segment Space Reuse

An interesting behavior could be observed when rows are deleted and re-inserted to compressed and uncompressed table.

First the uncompressed table.
create table insertdml as select * from all_objects where 1 = 2;
alter table insertdml add constraint insert_pk primary key (object_id);

Use the pl/sql code sinppet to find the space usage.
full_blocks 0
full_bytes 0

total_blocks 8
total_bytes 65536
unused_blocks 5
unused_bytes 40960
last_used_extent_file_id 6
last_used_extent_block_id 34416

Add rows to the table to expand it
declare

type insertrow_type is table of all_objects%rowtype index by pls_integer;
insertrow insertrow_type;
begin
select * bulk collect into insertrow from all_objects;

forall row in insertrow.first .. insertrow.last
insert into insertdml values insertrow(row);
end;
/

Check the space usage after the insert
full_blocks 1002
full_bytes 8208384

total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096
Note the last used extent block id and delete the rows from the table and check the space usage

delete from insertdml;
commit;

full_blocks 0
full_bytes 0

total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096
No more full blocks. Again insert the rows and check the space usage

full_blocks 999
full_bytes 8183808

total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096
Total number of blocks hasn't changed and last used extent block is still the same which means no new extents were added to the segment and old extents are reused.

Now the compressed table

drop table insertdml purge;
create table insertdml compress for all operations as select * from all_objects where 1 = 2;
alter table insertdml add constraint insert_pk primary key (object_id);

check the initial space usage as before
full_blocks 0
full_bytes 0

total_blocks 8
total_bytes 65536
unused_blocks 5
unused_bytes 40960
last_used_extent_file_id 6
last_used_extent_block_id 34416

insert rows to extend the table
declare

type insertrow_type is table of all_objects%rowtype index by pls_integer;
insertrow insertrow_type;
begin
select * bulk collect into insertrow from all_objects;

forall row in insertrow.first .. insertrow.last
insert into insertdml values insertrow(row);
end;
/
and check the space usage
full_blocks 401
full_bytes 3284992

total_blocks 640
total_bytes 5242880
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35584

delete rows and check the space usage again
delete from insertdml;
commit;

full_blocks 0
full_bytes 0

total_blocks 640
total_bytes 5242880
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35584

insert rows again and check the space usage
full_blocks 645
full_bytes 5283840

total_blocks 768
total_bytes 6291456
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35712

Total number of blocks has changed and also the last used extent block id which indicates new extents are being added instead of old extents being reused.

Instead of the pl/sql code sinppet quering the user_segments view would also reveal this fact

select segment_name,bytes from user_segments where segment_name='INSERTDML';


This behavior was not observed in Oracle 11g R2.



--after initial insert

full_blocks 326
full_bytes 2670592

total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296

--delete the rows

full_blocks 0
full_bytes 0

total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296


--insert again

full_blocks 327
full_bytes 2678784

total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296


Last used extent block id hasn't changed.

11g Compression and Segment Size

An easy way to observer the compresson in action is to query the user_segments view.

select segment_name,sum(bytes)
from user_extents where segment_type='TABLE'
group by segment_name order by 2 desc;

SEGMENT_NAME SUM(BYTES)
------------------------------ ----------
AIRLINE 196608

Compress the table with
alter table airline move compress for all operations;

But the reduction is always not visible in the user_segments view as the segment is not shrunk beyond the initial storage parameter.

select segment_name,sum(bytes)
from user_extents where segment_type='TABLE'
group by segment_name order by 2 desc;

SEGMENT_NAME SUM(BYTES)
------------------------------ ----------
AIRLINE 196608

It could be observed that compression is being enabled

select table_name,compression,compress_for from user_tables;

TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
AIRLINE ENABLED FOR ALL OPERATIONS

Looking at the storage parameters

... STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP)

But the actual compression could be observed with the dbms_space package.

Before compression

full_blocks 3
full_bytes 24576

total_blocks 24
total_bytes 196608
unused_blocks 18
unused_bytes 147456
last_used_extent_file_id 6
last_used_extent_block_id 1544

After compression

full_blocks 2
full_bytes 16384

total_blocks 24
total_bytes 196608
unused_blocks 19
unused_bytes 155648
last_used_extent_file_id 6
last_used_extent_block_id 128

There's a reduction in the total number of blocks and increase in the number of unused blocks.

The PL/SQL code is listed below

DECLARE
unformatted_blocks NUMBER;
unformatted_bytes NUMBER;
fs1_blocks NUMBER;
fs1_bytes NUMBER;
fs2_blocks NUMBER;
fs2_bytes NUMBER;
fs3_blocks NUMBER;
fs3_bytes NUMBER;
fs4_blocks NUMBER;
fs4_bytes NUMBER;
full_blocks NUMBER;
full_bytes NUMBER;

total_blocks NUMBER;
total_bytes NUMBER;
unused_blocks NUMBER;
unused_bytes NUMBER;
last_used_extent_file_id NUMBER;
last_used_extent_block_id NUMBER;
last_used_block NUMBER;

-- cursor t is select table_name from user_tables order by 1;
-- tname user_tables.table_name%type;
BEGIN
--open t;
--loop
--fetch t into tname;
--exit when t%notfound;

DBMS_SPACE.SPACE_USAGE('TX',
'AIRLINE',
'TABLE',
unformatted_blocks,
unformatted_bytes,
fs1_blocks,
fs1_bytes,
fs2_blocks,
fs2_bytes,
fs3_blocks,
fs3_bytes,
fs4_blocks,
fs4_bytes,
full_blocks,
full_bytes,
NULL);

-- dbms_output.put_line('table name '|| tname);
-- dbms_output.put_line('fs1_blocks ' || fs1_blocks );
-- dbms_output.put_line('fs1_bytes ' || fs1_bytes );
-- dbms_output.put_line('fs2_blocks '||fs2_blocks );
-- dbms_output.put_line('fs2_bytes '||fs2_bytes );
-- dbms_output.put_line('fs3_blocks '||fs3_blocks );
-- dbms_output.put_line('fs3_bytes '|| fs3_bytes );
-- dbms_output.put_line('fs4_blocks '|| fs4_blocks );
-- dbms_output.put_line('fs4_bytes '|| fs4_bytes );
dbms_output.put_line('full_blocks '|| full_blocks);
dbms_output.put_line('full_bytes '|| full_bytes );
dbms_output.put_line('');
--end loop;
--close t;

dbms_space.unused_space('TX',
'AIRLINE',
'TABLE',
total_blocks ,
total_bytes ,
unused_blocks ,
unused_bytes ,
last_used_extent_file_id ,
last_used_extent_block_id ,
last_used_block ,
NULL);

dbms_output.put_line('total_blocks '|| total_blocks);
dbms_output.put_line('total_bytes '|| total_bytes );
dbms_output.put_line('unused_blocks '|| unused_blocks);
dbms_output.put_line('unused_bytes '|| unused_bytes );
dbms_output.put_line('last_used_extent_file_id '|| last_used_extent_file_id);
dbms_output.put_line('last_used_extent_block_id '|| last_used_extent_block_id );
dbms_output.put_line('');


end;
/


Wednesday, November 11, 2009

Updating Timezone

Updating 10.2.0.4 timezone to timezone 11. This is specially handy if the database is later upgraded to 11g Release 2.

Couple of metalink documents to go through 840118.1 and 412160.1 and few KEY points

1. Oracle 9, 10 and 11.1 can be upgraded to RDBMS DSTv11 using patch 8524113, DSTv11 is included in 11gR2 standard version.

2. download and run the utltzuv11.sql (url included in the metalink note 412160.1)

further steps are needed depending on the result of utltzuv11.sql script.

After running utltzuv11.sql, you need do a
select * from sys.sys_tzuv2_temptab;

If a "select * from sys.sys_tzuv2_temptab;" gives no rows then there is no action to take you can simply apply the RDBMS DSTv11 patch without action on stored TSTZ data.

If a "select * from sys.sys_tzuv2_temptab;" give(s) affected TSTZ data then you need to back up the TSTZ data before applying the RDBMS DSTv11 patch.


3. Patch 7695070 needs to be applied before the RDBMS DSTv11 patch can be installed.
Unlike previous DST patches the DSTv11 patch 8524113 needs to have 2 codefixes in place (Bug 7695070 and bug 7395472) to work. 10.2.0.5 and 11.2.0.2 (and higher) include the needed fixes, no need to apply patch 7695070 before installing the RDBMS DSTv11 patch on these versions.

once updated

WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.


will not be prompted when upgrading to 11gR2.