Oracle Advanced Compression using Swingbench Order Entry Schema with Scale Factor 100

In this entry I will talk about my experience using Swingbench Version 2.4.0.845 where I used Oracle Advanced Compression while creating the Order Entry Schema with scale factor 100

Here is the Order Entry schema diagram that I reverse engineered using DbWrench

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 27 09:14:53 2012 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

We can see all the tablespaces that are part of the database.

SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS SOE 6 rows selected.

The Swingbench user is called SOE, so here we look into all the tables owner by that user 

SQL> select table_name from dba_tables where owner='SOE' and partitioned='NO' ; TABLE_NAME ------------------------------ CUSTOMERS WAREHOUSES ORDER_ITEMS ORDERS INVENTORIES PRODUCT_INFORMATION LOGON PRODUCT_DESCRIPTIONS ORDERENTRY_METADATA 9 rows selected.

Below we see the file id and file name that is being used by the tablespace SOE

SQL> select distinct ddf.file_id "File ID", ddf.file_name "File Name" from dba_extents de, dba_data_files ddf where de.file_id = ddf.file_id and ddf.tablespace_name = 'SOE';    File ID ---------- File Name --------------------------------------------------------------------------------          5 +DATA/testdb/datafile/soe.dbf

Now we check to see if Compression has been enabled, and the kink of compression we are using.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM dba_tables where owner='SOE'; TABLE_NAME                     COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ CUSTOMERS                      ENABLED  OLTP WAREHOUSES                     ENABLED  OLTP ORDER_ITEMS                    ENABLED  OLTP ORDERS                         ENABLED  OLTP INVENTORIES                    ENABLED  OLTP PRODUCT_INFORMATION            ENABLED  OLTP LOGON                          ENABLED  OLTP PRODUCT_DESCRIPTIONS           ENABLED  OLTP ORDERENTRY_METADATA            DISABLED 9 rows selected.

Below we see all the indxes associated with each of the table created for the Order Entry Schema

SQL> select TABLE_NAME, INDEX_NAME from dba_indexes where OWNER='SOE'; TABLE_NAME                     INDEX_NAME ------------------------------ ------------------------------ CUSTOMERS                      CUSTOMERS_PK CUSTOMERS                      CUST_UPPER_NAME_IX CUSTOMERS                      CUST_ACCOUNT_MANAGER_IX CUSTOMERS                      CUST_LNAME_IX CUSTOMERS                      CUST_EMAIL_IX INVENTORIES                    INVENTORY_PK INVENTORIES                    INV_PRODUCT_IX INVENTORIES                    INV_WAREHOUSE_IX ORDERS                         ORDER_PK ORDERS                         ORD_CUSTOMER_IX ORDERS                         ORD_SALES_REP_IX TABLE_NAME                     INDEX_NAME ------------------------------ ------------------------------ ORDERS                         ORD_ORDER_DATE_IX ORDERS                         ORD_WAREHOUSE_IX ORDER_ITEMS                    ORDER_ITEMS_PK ORDER_ITEMS                    ITEM_ORDER_IX ORDER_ITEMS                    ITEM_PRODUCT_IX PRODUCT_DESCRIPTIONS           PRD_DESC_PK PRODUCT_DESCRIPTIONS           PROD_NAME_IX PRODUCT_INFORMATION            PRODUCT_INFORMATION_PK PRODUCT_INFORMATION            PROD_CATEGORY_IX PRODUCT_INFORMATION            PROD_SUPPLIER_IX WAREHOUSES                     WAREHOUSES_PK TABLE_NAME                     INDEX_NAME ------------------------------ ------------------------------ WAREHOUSES                     WHS_LOCATION_IX 23 rows selected.

Here we check to see if compression has been enabled for each of the indexes associated with the table.

SQL> select INDEX_NAME , TABLE_NAME, COMPRESSION from dba_indexes where OWNER='SOE'; INDEX_NAME                     TABLE_NAME                     COMPRESS ------------------------------ ------------------------------ -------- CUSTOMERS_PK                   CUSTOMERS                      DISABLED CUST_UPPER_NAME_IX             CUSTOMERS                      DISABLED CUST_ACCOUNT_MANAGER_IX        CUSTOMERS                      DISABLED CUST_LNAME_IX                  CUSTOMERS                      DISABLED CUST_EMAIL_IX                  CUSTOMERS                      DISABLED INVENTORY_PK                   INVENTORIES                    DISABLED INV_PRODUCT_IX                 INVENTORIES                    DISABLED INV_WAREHOUSE_IX               INVENTORIES                    DISABLED ORDER_PK                       ORDERS                         DISABLED ORD_CUSTOMER_IX                ORDERS                         DISABLED ORD_SALES_REP_IX               ORDERS                         DISABLED INDEX_NAME                     TABLE_NAME                     COMPRESS ------------------------------ ------------------------------ -------- ORD_ORDER_DATE_IX              ORDERS                         DISABLED ORD_WAREHOUSE_IX               ORDERS                         DISABLED ORDER_ITEMS_PK                 ORDER_ITEMS                    DISABLED ITEM_ORDER_IX                  ORDER_ITEMS                    DISABLED ITEM_PRODUCT_IX                ORDER_ITEMS                    DISABLED PRD_DESC_PK                    PRODUCT_DESCRIPTIONS           DISABLED PROD_NAME_IX                   PRODUCT_DESCRIPTIONS           DISABLED PRODUCT_INFORMATION_PK         PRODUCT_INFORMATION            DISABLED PROD_CATEGORY_IX               PRODUCT_INFORMATION            DISABLED PROD_SUPPLIER_IX               PRODUCT_INFORMATION            DISABLED WAREHOUSES_PK                  WAREHOUSES                     DISABLED INDEX_NAME                     TABLE_NAME                     COMPRESS ------------------------------ ------------------------------ -------- WHS_LOCATION_IX                WAREHOUSES                     DISABLED 23 rows selected.

The below sql statement gives us more information on the number of rows in each of the tables, and the number of blocks used by the table.

SQL> SELECT table_name, NUM_ROWS, blocks as BLOCKS_USED, empty_blocks FROM dba_tables WHERE owner='SOE'; TABLE_NAME                       NUM_ROWS BLOCKS_USED EMPTY_BLOCKS ------------------------------ ---------- ----------- ------------ CUSTOMERS                       400000000     3906292            0 WAREHOUSES                           1000          60            0 ORDER_ITEMS                    1350084849     3972147            0 ORDERS                          450000000     3233681            0 INVENTORIES                        899915       20941            0 PRODUCT_INFORMATION                  1000          60            0 LOGON                           100000000      176240            0 PRODUCT_DESCRIPTIONS                 1000          60            0 ORDERENTRY_METADATA                     4          60            0 9 rows selected. SQL>

Here we look to see the size of each of the table in KB. 
As per this excellent article by Burleson Consulting  you need to ask yourself exactly which Oracle table size, you wish to query:

• Do you want only the row space consumed? ( select avg_row_len*num_rows from dba_tables)
• Do you want to include allocated file space for the table? (select . . . from dba_segments)
• Do you want to include un-used extent space? (select . . . from dba_data_files, dba_extents . . )
• Do you want to include un-used space up to the high water mark? This may over-estimate the real Oracle table size.
• Do you want table sizes for Oracle growth monitoring?

The query below gives us the size in terms of number of row space consumed
 

SQL> select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) SIZE_KB from dba_tables WHERE owner='SOE'; TABLE_NAME                        SIZE_KB ------------------------------ ---------- CUSTOMERS                        30859375 WAREHOUSES                          26.37 ORDER_ITEMS                    27687286.9 ORDERS                         21972656.3 INVENTORIES                      12303.53 PRODUCT_INFORMATION                172.85 LOGON                          1269531.25 PRODUCT_DESCRIPTIONS               210.94 ORDERENTRY_METADATA                    .1 9 rows selected. SQL>

The query below gives the table size that includes the unused extents space

SQL>  select segment_name table_name, sum(bytes)/(1024) table_size_kb from user_extents where segment_type='TABLE' and segment_name = 'CUSTOMERS' group by segment_name; TABLE_NAME -------------------------------------------------------------------------------- TABLE_SIZE_KB ------------- CUSTOMERS      31885312

Using the function written Anantha Narayanana 

SQL>  CREATE OR REPLACE FUNCTION get_table_size   2  (t_table_name VARCHAR2)RETURN NUMBER IS   3  l_size NUMBER;   4  BEGIN   5  SELECT sum(bytes)/(1024) INTO l_size  6   7  FROM user_extents   8  WHERE segment_type='TABLE'   9  AND segment_name = t_table_name;  10  11  RETURN l_size;  12  EXCEPTION  13  WHEN OTHERS THEN  14  RETURN NULL;  15  END;  16  / Function created. SQL> SELECT get_table_size('CUSTOMERS') Table_Size from dual; TABLE_SIZE ----------   31885312 SQL>