OLTP compression slow update
Date: Mon, 24 Dec 2012 20:52:20 -0800
Message-ID: <CAENwkM5=UX1Zs2TrP_G61cOWQy4Z9eSu-yB_HVxL+3jEQAvmhg_at_mail.gmail.com>
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
Received on Tue Dec 25 2012 - 05:52:20 CET