Re: OLTP compression slow update

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Mon, 24 Dec 2012 22:54:48 -0800
Message-ID: <50D94DB8.6010503_at_oracle.com>



Michael,
1. What version of Oracle DB are you using? There are a number of performance improvements in 11.2.0.3, so if you're running on an earlier version, you should upgrade to the latest and try again.

2. What do the table schemas look like, and what do the update statements look like? There can be a very big difference between updating one column versus updating many columns; there can also be big differences based on whether the values in the column(s) being updated were in the symbol table for the database block in question, and whether the new values are already in the symbol table or not...

-KJ

*Kevin Jernigan* 	(650) 607-0392 (o)
*Senior Director Product Management* 	(415) 710-8828 (m)
kevin.jernigan_at_oracle.com <mailto:kevin.jernigan_at_oracle.com>
*Advanced Compression - ACO*: 	*Information Lifecycle Management* - ILM
   Advanced Row Compression 	*Temporal database* (Total Recall etc)
   Advanced LOB Compression 	*SecureFiles*
   Advanced LOB Deduplication 	*Database File System* - DBFS
   RMAN Backup Compression 	*Direct NFS Client* - dNFS
   Data Pump Export Compression 	*CloneDB*
   Data Guard Redo Network Transport Compression 	*Database Resource 
Manager* - DBRM
   Flashback Data Archive History Table Optimization 	*Continuous Query 
Notification* - CQN
*Hybrid Columnar Compression* - HCC 	*Index Organized Tables* - IOT
*Database Smart Flash Cache* 	*OISP*

On 12/24/12 8:52 PM, Michael Dinh wrote:
> Hello,
> Does anyone have any experience with updating compressed table?
>
> The only difference between 2 tables is no compression vs OLTP compression.
>
> PL/SQL using FOR loop updating 1 row and a time and commit every 10,000
> rows.
>
> I understand it's very bad practice; however, is it reasonable to have
> 2.11x CPU and 1.79x slower?
>
> Thanks Michael.
>
> ==============================
> NOCOMPRESS
> ==============================
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 911861 2580.04 3626.40 156281 2735585 976167
> 911861
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 911862 2580.04 3626.40 156281 2735585 976167
> 911861
>
> Rows (1st) Rows (avg) Rows (max) Row Source Operation
> ---------- ---------- ----------
> ---------------------------------------------------
> 0 0 0 UPDATE FCT_SALE_PROC_DETAIL_TST (cr=3
> pr=4 pw=0 time=59210 us)
> 1 1 1 INDEX UNIQUE SCAN
> PK_SALE_PROC_DETAIL_TST (cr=3 pr=3 pw=0 time=44295 us cost=2 size=230
> card=1)(object id 3009701)
>
>
> ==============================
> COMPRESS
> ==============================
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 911861 5456.29 6494.85 124096 3088642 4392342
> 911861
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 911862 5456.29 6494.85 124096 3088642 4392342
> 911861
>
> Rows (1st) Rows (avg) Rows (max) Row Source Operation
> ---------- ---------- ----------
> ---------------------------------------------------
> 0 0 0 UPDATE FCT_SALE_PROC_DETAIL (cr=3 pr=0
> pw=0 time=4001 us)
> 1 1 1 INDEX UNIQUE SCAN PK_SALE_PROC_DETAIL
> (cr=3 pr=0 pw=0 time=63 us cost=2 size=230 card=1)(object id 2953360)
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 25 2012 - 07:54:48 CET

Original text of this message