OraFAQ Forum: Performance Tuning » Update non-token column on compressed table

Home » RDBMS Server » Performance Tuning » Update non-token column on compressed table (Oracle EE 11.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update non-token column on compressed table [message #622658]

Thu, 28 August 2014 05:55

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Hi all,

I have an uncompressed fact table in a DWH environment with about 15 surrogate FK columns, a couple of degenerate columns (like journal number, etc.) and some numeric facts. The table has 300 million rows and uses more than 500,000 blocks.

Although it can be a good candidate for compression, it was 'left out' since the journal number is updated in the existing rows depending on some activities in the financial system.

I understand that updating columns that are not part of the compression tokens is viable without any unwanted side effects (the test below demonstrates that).

What I don't understand is how to figure out if a column is actually forming part of the compression tokens (journal number in my case) and therefore decide on the compression strategy accordingly. Is there any easy way I can find it out?

Here is the test which demonstrates that I can update a column that is not a part of the token without any problems:

SQL> 
SQL> create table DBAO nologging as
  2  select OWNER, OBJECT_TYPE, OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
  3  from DBA_OBJECTS
  4  UNION ALL
  5  select OWNER, OBJECT_TYPE, OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
  6  from DBA_OBJECTS
  7  union all
  8  select OWNER, OBJECT_TYPE, OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
  9  from DBA_OBJECTS
 10  union all
 11  select OWNER, OBJECT_TYPE, OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
 12  from DBA_OBJECTS;

Table created.

SQL> 
SQL> create table DBAOC compress nologging as SELECT * FROM DBAO;

Table created.

SQL> 
SQL> begin
  2    DBMS_STATS.GATHER_TABLE_STATS (user,'DBAO');
  3    DBMS_STATS.GATHER_TABLE_STATS (user,'DBAOC');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select TABLE_NAME, blocks from USER_TABLES where TABLE_NAME in ('DBAO','DBAOC');

TABLE_NAME                         BLOCKS
------------------------------ ----------
DBAO                                 6414
DBAOC                                2528

SQL> 
SQL> set AUTOTRACE TRACEONLY
SQL> SET TIMING ON
SQL> update DBAO set OBJECT_ID = OBJECT_ID + 1; --update possible non-token column in uncompressed table

761812 rows updated.

Elapsed: 00:00:08.36

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |   761K|  4463K|  1124   (2)|
|   1 |  UPDATE            | DBAO |       |       |            |
|   2 |   TABLE ACCESS FULL| DBAO |   761K|  4463K|  1124   (2)|
----------------------------------------------------------------

Statistics
----------------------------------------------------------
         21  recursive calls
     896520  db block gets
      16246  consistent gets
          0  physical reads
  309043816  redo size
        567  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     761812  rows processed

SQL> set TIMING off
SQL> set AUTOTRACE oFF
SQL> commit;

Commit complete.

SQL> SET AUTOTRACE TRACEONLY
SQL> SET TIMING ON
SQL> update DBAOC set OBJECT_ID = OBJECT_ID + 1; --update possible non-token column in compressed table

761812 rows updated.

Elapsed: 00:00:06.78 --less than uncompressed table

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | UPDATE STATEMENT   |       |   761K|  4463K|   450   (3)|
|   1 |  UPDATE            | DBAOC |       |       |            |
|   2 |   TABLE ACCESS FULL| DBAOC |   761K|  4463K|   450   (3)|
-----------------------------------------------------------------

Statistics
----------------------------------------------------------
         12  recursive calls
     783055  db block gets     --less than uncompressed table
       2884  consistent gets   --less than uncompressed table
          0  physical reads
  204499544  redo size
        567  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     761812  rows processed

SQL> set TIMING off
SQL> set AUTOTRACE off
SQL> commit;

Commit complete.

SQL> 
SQL> begin
  2    DBMS_STATS.GATHER_TABLE_STATS (user,'DBAO');
  3    DBMS_STATS.GATHER_TABLE_STATS (user,'DBAOC');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select TABLE_NAME, blocks from USER_TABLES where TABLE_NAME in ('DBAO','DBAOC');

TABLE_NAME                         BLOCKS
------------------------------ ----------
DBAO                                 6414
DBAOC                                2560 --stays the same (more or less)

SQL> 
SQL> set AUTOTRACE TRACEONLY
SQL> SET TIMING ON
SQL> update DBAO set OBJECT_NAME = LOWER(OBJECT_NAME); --update possible token column in uncompressed table

761812 rows updated.

Elapsed: 00:00:12.98

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |   761K|    18M|  1123   (1)|
|   1 |  UPDATE            | DBAO |       |       |            |
|   2 |   TABLE ACCESS FULL| DBAO |   761K|    18M|  1123   (1)|
----------------------------------------------------------------

Statistics
----------------------------------------------------------
         19  recursive calls
     848299  db block gets
      15723  consistent gets
          0  physical reads
  343066436  redo size
        567  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     761812  rows processed

SQL> set TIMING off
SQL> set AUTOTRACE off
SQL> commit;

Commit complete.

SQL> set AUTOTRACE TRACEONLY
SQL> SET TIMING ON
SQL> update DBAOC set OBJECT_NAME = LOWER(OBJECT_NAME); --update possible token column in compressed table

761812 rows updated.

Elapsed: 00:00:28.76 --way more than uncompressed table

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | UPDATE STATEMENT   |       |   761K|    18M|   455   (3)|
|   1 |  UPDATE            | DBAOC |       |       |            |
|   2 |   TABLE ACCESS FULL| DBAOC |   761K|    18M|   455   (3)|
-----------------------------------------------------------------

Statistics
----------------------------------------------------------
        316  recursive calls
    4164589  db block gets    --way more than uncompressed table
     856828  consistent gets  --way more than uncompressed table
          0  physical reads
  545653244  redo size
        567  bytes sent via SQL*Net to client
        521  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     761812  rows processed

SQL> set TIMING off
SQL> set AUTOTRACE off
SQL> commit;

Commit complete.

SQL> 
SQL> begin
  2    DBMS_STATS.GATHER_TABLE_STATS (user,'DBAO');
  3    DBMS_STATS.GATHER_TABLE_STATS (user,'DBAOC');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select TABLE_NAME, blocks from USER_TABLES where TABLE_NAME in ('DBAO','DBAOC');

TABLE_NAME                         BLOCKS
------------------------------ ----------
DBAO                                 6414
DBAOC                                6592 --way too high

SQL> 
SQL> SPOOL off

Report message to a moderator

Re: Update non-token column on compressed table [message #622817 is a reply to message #622658]

Sun, 31 August 2014 01:01

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I am guessing you have read Jonathan Lewis.

The short answer as I understand it: If your column is unique (in the sense that it does not repeat values in the block) then it is highly unlikely to be selected as a candidate for deduplication and thus will not participate in compression. Its values will not have been replaced by tokens. Only you can know how unique a column in your data is, and if it shares values in other rows that might be in the same block.

The answer however may also depend upon what type of compression you are picking. BASIC / OLTP / HCC. Deduplication is a strategy which by its nature is not fond of updates and the three strategies are slightly different. The reason is that if you update a value that uses a token, the token must be expanded which if your compression was good, can lead to a large expansion of the row and thus to migration and lots of CPU overhead.

Jonathan in the articles does not talk about HCC compression.

Also, he notes that for BASIC and OLTP, updates to data don't do compression even though OLTP (for all operations) might suggest that it should have.

So if i was to guess I'd say the following:

1. if your update does not change a token then you are updating an uncompressed value.  If the value you are heading to is smaller than the current value then you are safe.  If the value is larger then you get a wider row but only as much wider as the new values was to the old.

2. if your update will change a token value then you are subject to three problems.

  a. the token will be expanded to uncompress all columns that were represented by that token (yes oracle can do multiple passes to get multiple columns compressed way down).  This could result in a much wider row.

  b. after these values are expanded, the row will not be recompressed and so you are stuck with a wider row forever (until you rebuild the table or do some other similar operation?).

  c. if the row is too wide to fit in the current block (which is a real possibility), then it will be migrated.

Read Jonathan's articles if you have not done so already.

Kevin

Report message to a moderator

Re: Update non-token column on compressed table [message #622821 is a reply to message #622817]

Sun, 31 August 2014 05:18

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Hi Kevin, yes I've read Jonathan's articles and posted the same question to him. He replied saying that there isn't an easy way to determine if a column is part of a token but suggested I could "look at the column statistics to compare the number of non-null occurrences with the number of distinct values".

Further, it'll probably be too expensive to maintain a data dictionary for such atomic stuff, even as part of a gather_stats exercise.

So I created two copies of the table in concern (including the appropriate partitions, et al), one compressed and one uncompressed, updated the column in both the tables and compared the statistics and took the decision accordingly.

And yes, as you mentioned, I can't predict how the behaviour will be when HCC is applied (which is something that's going to happen soon in our organisation), I'll probably have to perform the test again and reorganise the table as necessary.

Cheers

Report message to a moderator

Previous Topic:	sql query performance guide
Next Topic:	dbms_addm.real_time_addm_report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 17:35:04 CST 2025