OLTP compression slow update
- From: Michael Dinh <mdinh235@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 Dec 2012 20:52:20 -0800
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)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
