This is the second part in a series on implementing TDE and what happens to the sizing.
At first my plan was to encrypt the dataset I created in my first post, but instead I compressed it.
At this point (and throughout this post), I am working with an un-encrypted dataset.
One of the first things to understand about Encryption is that encrypted data DOES NOT compress.
This is critical when understanding what happens when you implement TDE.
One way to save storage when implementing TDE is to implement encryption AND compression together.
In order to break down the affects of encryption on compressed data VS uncompressed data, I took my dataset (the SOE dataset from swing bench) and I compressed it. I implemented Advanced compression on the tables, and I compressed the indexes and rebuilt them.
I now have 2 copies of the same dataset. 1 is compressed, and 1 is not.
Now let's take a look at the sizing of the Data sets and I will go through the same backup procedures and see what happens.
Here is the total savings by compressing both tables and indexes with advanced compression.
Now to compare this with the previous data uncompressed I am going to backup by tablespace. Below is the sizing of the backups. I used a tag to identify the backups.
Now I'm going to put that in a table and a chart to compare..
First the table of sizes
Now the chart
Now by looking at the chart it is apparent what happens with compression and the data.
At first my plan was to encrypt the dataset I created in my first post, but instead I compressed it.
At this point (and throughout this post), I am working with an un-encrypted dataset.
One of the first things to understand about Encryption is that encrypted data DOES NOT compress.
This is critical when understanding what happens when you implement TDE.
One way to save storage when implementing TDE is to implement encryption AND compression together.
In order to break down the affects of encryption on compressed data VS uncompressed data, I took my dataset (the SOE dataset from swing bench) and I compressed it. I implemented Advanced compression on the tables, and I compressed the indexes and rebuilt them.
I now have 2 copies of the same dataset. 1 is compressed, and 1 is not.
Now let's take a look at the sizing of the Data sets and I will go through the same backup procedures and see what happens.
SEGMENT_NAME SEGMENT_TYPE Space Used uncompressed Space Used Compressed SPACE_SAVINGS
------------------------- ------------ ------------------------- -------------------- -------------
ADDRESSES TABLE 3,392 MB 3,264 MB 3
ADDRESS_CUST_IX INDEX 703 MB 728 MB -3
ADDRESS_PK INDEX 662 MB 888 MB -34
CARDDETAILS_CUST_IX INDEX 703 MB 562 MB 20
CARD_DETAILS TABLE 2,048 MB 1,600 MB 21
CARD_DETAILS_PK INDEX 662 MB 0 MB 100
CUSTOMERS TABLE 3,328 MB 2,880 MB 13
CUSTOMERS_PK INDEX 443 MB 0 MB 100
CUST_ACCOUNT_MANAGER_IX INDEX 417 MB 272 MB 34
CUST_DOB_IX INDEX 528 MB 280 MB 47
CUST_EMAIL_IX INDEX 975 MB 280 MB 71
CUST_FUNC_LOWER_NAME_IX INDEX 683 MB 280 MB 58
INVENTORIES TABLE 176 MB 176 MB 0
INVENTORY_PK INDEX 18 MB 0 MB 100
INV_PRODUCT_IX INDEX 16 MB 12 MB 24
INV_WAREHOUSE_IX INDEX 16 MB 12 MB 24
ITEM_ORDER_IX INDEX 2,000 MB 1,770 MB 11
ITEM_PRODUCT_IX INDEX 1,768 MB 1,301 MB 26
LOGON TABLE 1,728 MB 1,728 MB 0
ORDERENTRY_METADATA TABLE 0 MB 0 MB 0
ORDERS TABLE 3,968 MB 2,816 MB 29
ORDER_ITEMS TABLE 6,976 MB 4,992 MB 28
ORDER_ITEMS_PK INDEX 2,234 MB 0 MB 100
ORDER_PK INDEX 632 MB 0 MB 100
ORD_CUSTOMER_IX INDEX 671 MB 480 MB 28
ORD_ORDER_DATE_IX INDEX 752 MB 439 MB 41
ORD_SALES_REP_IX INDEX 594 MB 438 MB 26
ORD_WAREHOUSE_IX INDEX 709 MB 438 MB 38
PRD_DESC_PK INDEX 0 MB 0 MB 100
PRODUCT_DESCRIPTIONS TABLE 0 MB 0 MB 0
PRODUCT_INFORMATION TABLE 0 MB 0 MB 0
PRODUCT_INFORMATION_PK INDEX 0 MB 0 MB 100
PROD_CATEGORY_IX INDEX 0 MB 0 MB 0
PROD_NAME_IX INDEX 0 MB 0 MB 0
PROD_SUPPLIER_IX INDEX 0 MB 0 MB -100
WAREHOUSES TABLE 0 MB 0 MB 0
WAREHOUSES_PK INDEX 0 MB 0 MB 100
WHS_LOCATION_IX INDEX 0 MB 0 MB -100
Here is the total savings by compressing both tables and indexes with advanced compression.
Space Used uncompressed Space Used Compressed SPACE_SAVINGS
------------------------- ---------------------- -------------
36,804 MB 25,636 MB 30
Now to compare this with the previous data uncompressed I am going to backup by tablespace. Below is the sizing of the backups. I used a tag to identify the backups.
-rw-rw----. 1 oracle oracle 26773323776 Nov 29 17:02 COMPRESSED_SOE.bkp
-rw-rw----. 1 oracle oracle 38441140224 Nov 29 17:04 UNCOMPRESSED_SOE.bkp
-rw-rw----. 1 oracle oracle 10987765760 Nov 29 18:35 BASIC_COMPRESSED_SOE.bkp
-rw-rw----. 1 oracle oracle 11135655936 Nov 29 18:36 BASIC_COMPRESSED_SOE_COMPRESSED.bkp
-rw-rw----. 1 oracle oracle 13360308224 Nov 29 20:12 MEDIUM_COMP_SOE_COMPRESSED.bkp
-rw-rw----. 1 oracle oracle 14383603712 Nov 29 20:12 MEDIUM_COMPRESSED_SOE_.bkp
-rw-rw----. 1 oracle oracle 9420791808 Nov 30 00:12 HIGH_COMP_SOE_COMPRESSED.bkp
-rw-rw----. 1 oracle oracle 9112944640 Nov 30 00:23 HIGH_COMPRESSED_SOE.bkp
Now I'm going to put that in a table and a chart to compare..
First the table of sizes
Now the chart
Now by looking at the chart it is apparent what happens with compression and the data.
- Compression in the database reduced the size of the data by 30%
- An uncompressed backupset matched the size of the data
- Once I compressed the backupset, the difference is size was minimal.
** Bottom line - Compressing the data in the database saved on the uncompressed backupsize. Once the backupset is compressed the final size is about the same.
** Final conclusion -- Most modern backup appliances (ZDLRA, ZFS, DD) compress the backups. When using those appliances with unencrypted data, the final size is the same regardless of whether the data is compressed in the Database.
Now that I've looked at both compressed and uncompressed data at the DB and backupset I am going to compress the data. Next post.
