Oracle UPDATE性能问题和优化 [英] Oracle UPDATE performance issue and optimization
问题描述
让我们查询一下我遇到很多问题.
Let's take a query I had a lot of problems with.
表* CONF_ELEMENTI_FATTURABILI *的大小为160 Gb，并在2587个分区中的edw_partition字段上进行了分区(但并行性在第二个查询中不起作用). * BCK_MEM_ANIMALI_F_ROPT_ELF *和* INT_TEMP51 *非常相似.它们具有索引并包含90k记录(第二个是第一个的GROUP BY). 此更新基于PK.因此，准确地更新了9万条记录.
The table *CONF_ELEMENTI_FATTURABILI* is 160 Gb large and is partitioned on the edw_partition field in 2587 partitions (but parallelism does not work in the second query). *BCK_MEM_ANIMALI_F_ROPT_ELF* and *INT_TEMP51* are very similar. They have indexes and contains 90k record (the second is the GROUP BY of the first one). The update is based on the PK. So exactly 90k records are updated.
在30分钟内，以下更新查询不会结束.我如何才能知道它是否有效. 并行执行不起作用(除一个线程外，所有线程均处于空闲状态).为什么??我应该强制执行一些FULL表/索引扫描吗?
In 30' the following update query does not end. How can I understand if it's working or not. The parallel execution doesn't work (all thread except one were idle). Why? Should I force some FULL table / index scan?
CONF_ELEMENTI_FATTURABILI的其他详细信息:
大小:161419,19 MB
数数范围:140964
NUM_ROWS:262000000(2.6亿)
方块:10241238
分区:是-2587个分区
Other details for CONF_ELEMENTI_FATTURABILI:
Size: 161419,19 MB
Number Extents: 140964
NUM_ROWS: 262000000 (260 million)
BLOCKS: 10241238
PARTITIONED: YES - 2587 partitions
第一配方
alter session enable parallel dml;
UPDATE
CONF_ELEMENTI_FATTURABILI elf
SET ELF_ELEMENTO_CHIUSO = 'C',
ELF_DATA_VER_FIN =
( SELECT TELE_DATA_LETTURA
FROM NETATEMP.int_temp51 t
WHERE t.ELF_STORICO_ID = elf.ELF_STORICO_ID
AND t.edw_partition = elf.edw_partition)
WHERE exists (SELECT 1 FROM netatemp.bck_mem_animali_f_ropt_elf kk WHERE kk.ELF_STORICO_ID = elf.ELF_STORICO_ID
AND kk.edw_partition = elf.edw_partition);
说明计划:
Plan
UPDATE STATEMENT ALL_ROWSCost: 87,99 Bytes: 6.269.021 Cardinality: 85,877
8 UPDATE SIUCONTRATTI.CONF_ELEMENTI_FATTURABILI
5 NESTED LOOPS Cost: 87,99 Bytes: 6.269.021 Cardinality: 85,877
2 SORT UNIQUE Cost: 67 Bytes: 1.975.171 Cardinality: 85,877
1 INDEX FAST FULL SCAN INDEX SIUINTEGRA.MEMBR_4 Cost: 67 Bytes: 1.975.171 Cardinality: 85,877
4 PARTITION LIST ITERATOR Cost: 2 Bytes: 50 Cardinality: 1 Partition #: 5
3 INDEX RANGE SCAN INDEX (UNIQUE) SIUCONTRATTI.CONF_ELF_UK_IDX1 Access Predicates: "KK"."ELF_STORICO_ID"="ELF"."ELF_STORICO_ID" AND "KK"."EDW_PARTITION"="ELF"."EDW_PARTITION" Cost: 2 Bytes: 50 Cardinality: 1 Partition #: 5
7 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.INT_TEMP51 Cost: 69 Bytes: 256 Cardinality: 8
6 INDEX RANGE SCAN INDEX SIUINTEGRA.MEMBR_6 Access Predicates: "T"."ELF_STORICO_ID"=:B1 Cost: 2 Cardinality: 333
第二种配方
UPDATE
CONF_ELEMENTI_FATTURABILI elf
SET ELF_ELEMENTO_CHIUSO = 'C',
ELF_DATA_VER_FIN =
( SELECT TELE_DATA_LETTURA
FROM int_temp51 t
WHERE t.ELF_STORICO_ID = elf.ELF_STORICO_ID
AND t.edw_partition = elf.edw_partition)
WHERE (ELF_STORICO_ID, edw_partition) IN (SELECT /*+ full(kk) parallel(kk, 20) */ ELF_STORICO_ID, edw_partition FROM bck_mem_animali_f_ropt_elf kk);
说明计划:
Plan
UPDATE STATEMENT ALL_ROWSCost: 7,168 Bytes: 6.269.021 Cardinality: 85,877
13 UPDATE SIUCONTRATTI.CONF_ELEMENTI_FATTURABILI
10 PX COORDINATOR
9 PX SEND QC (RANDOM) SYS.:TQ10001 Cost: 7,168 Bytes: 6.269.021 Cardinality: 85,877
8 NESTED LOOPS Cost: 7,168 Bytes: 6.269.021 Cardinality: 85,877
5 SORT UNIQUE Cost: 7 Bytes: 1.975.171 Cardinality: 85,877
4 PX RECEIVE Cost: 7 Bytes: 1.975.171 Cardinality: 85,877
3 PX SEND HASH SYS.:TQ10000 Cost: 7 Bytes: 1.975.171 Cardinality: 85,877
2 PX BLOCK ITERATOR Cost: 7 Bytes: 1.975.171 Cardinality: 85,877
1 TABLE ACCESS FULL TABLE SIUINTEGRA.BCK_MEM_ANIMALI_F_ROPT_ELF Cost: 7 Bytes: 1.975.171 Cardinality: 85,877
7 PARTITION LIST ITERATOR Cost: 2 Bytes: 50 Cardinality: 1 Partition #: 10
6 INDEX RANGE SCAN INDEX (UNIQUE) SIUCONTRATTI.CONF_ELF_UK_IDX1 Access Predicates: "ELF_STORICO_ID"="ELF_STORICO_ID" AND "EDW_PARTITION"="EDW_PARTITION" Cost: 2 Bytes: 50 Cardinality: 1 Partition #: 10
12 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.INT_TEMP51 Cost: 69 Bytes: 256 Cardinality: 8
11 INDEX RANGE SCAN INDEX SIUINTEGRA.MEMBR_6 Access Predicates: "T"."ELF_STORICO_ID"=:B1 Cost: 2 Cardinality: 333
只有一个线程起作用！！！
Just ONE of the thread works!!!
等待:
ROWNUM SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO
1 1012 latch: cache buffers chains 8 0 0 0 0 270
2 1012 read by other session 366 0 141 0,38 3 1407428
3 1012 db file sequential read 1287 0 772 0,6 4 7718838
7 1012 events in waitclass Other 40 38 7613 190,33 195 76130586
5 1012 PX Deq: Execution Msg 3 0 8 2,61 4 78312
6 1012 PX Deq: Table Q Normal 2 0 1 0,34 1 6763
4 1012 cursor: pin S wait on X 1 1 1 0,98 1 9829
8 1114 latch: cache buffers chains 3 0 0 0 0 43
9 1114 read by other session 381 0 131 0,34 4 1308282
10 1114 db file sequential read 1416 0 841 0,59 4 8410582
11 1114 cursor: pin S wait on X 1 1 1 0,98 1 9793
12 1114 PX Deq: Execution Msg 3 0 8 2,65 4 79609
13 1114 PX Deq: Table Q Normal 2 0 1 0,37 1 7441
14 1114 events in waitclass Other 41 38 7576 184,78 195 75758618
15 1154 latch: cache buffers chains 8 0 0 0 0 163
16 1154 read by other session 368 0 141 0,38 3 1412020
17 1154 db file sequential read 1819 0 1156 0,64 3 11555685
18 1154 cursor: pin S wait on X 1 1 1 0,98 1 9833
19 1154 PX Deq: Execution Msg 3 0 8 2,66 4 79754
20 1154 PX Deq: Table Q Normal 2 0 1 0,36 1 7147
21 1154 events in waitclass Other 38 35 7113 187,19 195 71130875
22 1398 latch: cache buffers chains 4 0 0
IO
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
1012 0 9006 1805 0 0
1114 0 9999 2393 0 0
1154 0 10056 2668 0 0
1398 0 10007 1917 0 0
1597 0 10058 2046 0 0
1649 0 10074 2261 0 0
2279 92557 21996787 11908995 170829 206
2322 0 10026 1863 0 0
2460 0 10082 1927 0 0
2479 0 9978 2006 0 0
2694 0 9996 2462 0 0
2800 0 8641 1767 0 0
2829 0 10003 1855 0 0
2840 0 10049 2280 0 0
2888 0 10038 2574 0 0
2998 0 9993 1995 0 0
3147 0 8607 1569 0 0
3186 0 10021 2478 0 0
3219 0 10055 1773 0 0
3227 0 9998 2678 0 0
3228 0 10003 3083 0 0
推荐答案
尽管提供了很多信息，但我不确定这里是否真的有足够有用的信息来帮助回答这个问题，但是我会给它一个提示.去.了解有关表键以及您希望在此处更新多少行的信息将很有用.
Despite providing a lot of information, I'm not sure that there is really enough useful information here to help with answering this question but I'll give it a go. It would be useful to have some information on keys of the tables and also how many rows you expect to be updated here.
如果要更新的表有262M行，而包含要更新的数据的表有90k条记录，那么您可能会尝试根据键在90k到262M行之间的任何位置进行更新.
If the table to be updated has 262M rows and the table containing the data to update from has 90k records, then you could be trying to update anywhere between 90k and 262M rows depending on keys.
以我的经验，编写更新有些困难.优化器永远不会完全按照您的要求来对待它们.它们对于将少量行更新或将列更新为恒定值(SET ELF_ELEMENTO_CHIUSO = 'C')很有用，但并不是很好，您需要在另一个表中查找这些值. SET子句中的子查询似乎经常被表中的每一行调用.
In my experience, writing updates is a little difficult. They are never treated quite how you want by the optimizer. They are useful for updating a handful of rows or updating columns to a constant value (SET ELF_ELEMENTO_CHIUSO = 'C') but not so good wen you need to look these values up in another table. The subquery in the SET clause seems to often get called for every row in the table.
由于这个原因，我通常会尝试编写MERGE语句或UPDATE视图.两种方法都有轻微的缺陷，特别是更新视图，因为要注意很多限制，这里最重要的是它必须是保留键的表.在此处 http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3058
For this reason, I would normally try to either write a MERGE statement or UPDATE a view. Both approaches have slight drawbacks, especially updating a view because there are quite a number of restrictions to look out for, the most important one here being that that it needs to be a key-preserved table. A key preserved table is defined here http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3058
我认为在这种情况下，创建视图时，您的表CONF_ELEMENTI_FATTURABILI应该保留键.因此，使用表更新视图的示例为
I think that in this case that when creating a view then your table CONF_ELEMENTI_FATTURABILI should be key preserved. So an example of updating a view using your tables would be
UPDATE (
select
elf.ELF_STORICO_ID,
elf.edw_partition,
elf.ELF_ELEMENTO_CHIUSO,
elf.ELF_DATA_VER_FIN,
t.TELE_DATA_LETTURA
from
CONF_ELEMENTI_FATTURABILI elf
join NETATEMP.int_temp51 t on (t.ELF_STORICO_ID = elf.ELF_STORICO_ID and
t.edw_partition = elf.edw_partition)
) a
set
a.ELF_ELEMENTO_CHIUSO = 'C',
a.ELF_DATA_VER_FIN = a.TELE_DATA_LETTURA;
由于密钥保存问题，我不能真正保证这会奏效.可能会或可能不会.即使是我自己的数据，我也很难解决.
I can't really guarantee that this will work because of the key preservation issue. It might, or it might not. Even if it were my own data I'd have trouble working it out.
另一个选项是MERGE语句.这更可能有效，因为它不依赖于密钥保存，但与此同时我也遇到了奇怪的问题.
The other option is a MERGE statement. This is more likely to work as it doesn't rely on key preservation but I've also had weird problems with that too.
合并的示例为:
MERGE INTO CONF_ELEMENTI_FATTURABILI elf
USING NETATEMP.int_temp51 t on (t.ELF_STORICO_ID = elf.ELF_STORICO_ID and
t.edw_partition = elf.edw_partition)
WHEN MATCHED THEN UPDATE SET elf.ELF_ELEMENTO_CHIUSO = 'C',
elf.ELF_DATA_VER_FIN = t.TELE_DATA_LETTURA
正如我在上面所说，我确信上面显示的任何一条语句实际上都可以工作，因为它们都有些不合时宜，并且有原因导致其中任何一条均会失败.希望您可以工作.
As I say above, I'm sure that either of the statements shown above will actually work because they are both a little fiddly and there are reasons why either should fail. Hopefully you can get something working though.
这篇关于Oracle UPDATE性能问题和优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
