Oracle 具有多个更新语句的函数的性能问题

嗨，

我需要关于如何提高函数的性能的建议，该函数在循环中循环150万条记录中具有多个更新语句。下面是更多的细节。

该函数有一个循环，遍历从表中提取的PLSQL表（集合）中的150万条记录（假设表名为Tab1 ）。

FOR indx in coll_var.FIRST .. coll_var.LAST循环 

在每个迭代中，在同一个表Tab1上执行2个Select语句。
在每次迭代中，对同一个表Tab1执行4-5个更新语句。
只有在每完成200次迭代后，才会发出Commit语句。

目前，该函数需要大约7-8个小时（比预期的时间要高）来执行。报告AWR和ADDM建议增加内存（无法执行） ，并将更新语句显示为大多数占用CPU的语句（ SQL将其95%的数据库时间用于CPU、I/O和群集等待）他们都在进行索引范围扫描。CPU时间最多的SQL看起来
CPU时间执行每个执行的CPU时间%总运行时间%CPU %IO SQL Id SQL模块SQL文本
2、449.86 128、359、0.02、42.14、544.85 96.27 0.14 3g600mxfzbd4x
SQL*Plus更新…
虽然每个执行的CPU为0.02秒，但所有执行的时间都很高。因此，我猜想更新语句没有什么问题，问题是它被执行的次数。

我观察到的另一个问题是函数执行时的内存消耗（来自v$proce ）。我不确定低于此值是否较高？
PGA_USER_MEM|PGA_ALLOC_MEM | PGA_FAL_MEM | PGA_MAX_MEM
--------------------------------------------------------
837328 |952632 |0 |952632当函数未执行时
129350184 |130124088 |393216 |130124088函数执行时
41326496 |130124088 |88276992 |130124088一段时间后，函数仍在执行

我猜想内存消耗是高的，因为集合变量可以一次保存200条记录。这能成为性能的原因吗？作为替代方案，更改了逻辑，在游标上运行循环，而不是在集合上运行，但没有发现任何改进，内存统计看起来也是一样的。

当前参数值：
pga聚合目标1000341504
sga_目标3003121664
sga_max_大小3003121664

我测试了各种功能，对上面提到的逻辑几乎没有改动，但没有找到解决方案，因此请您就如何进一步解决这个问题向我提出建议？如果你需要更多的信息，请告诉我。

这里，在函数中处理第n行的方式取决于处理/更新前（n-1）行的方式。我希望你能理解。以下是算法，便于您更好地理解。

功能F1 IS
游标C1是从Tab1中选择* ；

缓冲区行号：= -100 ；
开始

--将批量数据（一次200条记录）提取到集合变量中。
打开C1 ；
循环
取C1
批量收集至l__收集_ var限制200 ；

FOR indx IN 1 .. l_collection_var.COUNT循环
/*逻辑上半部分*/
开始
从选项卡1中选择*到l_tab1_rec_type ，其中lng = l__收集_var(indx).lng_s和lat = l_收集_var(indx).lat_s和秩= l_收集_var(in dx).秩和line_no < -100 ；
异常当NO_DATA_FOD时，然后
l_tab1_rec_type :=为空；
结束；

如果l_tab1_rec_type.line_no不为空，则
更新选项卡1设置line_no = decode （ line_seq_no、1、null、line_no ） , line_seq_no = l_tab1_rec_type.pnt_cnt+1 , pnt_cnt = l_tab1_rec_type.pnt_cnt+1其中line_no = l_收集_var(indx).line_no ；
更新选项卡1设置line_no = l_收集_var(indx).line_no, line_seq_no = decode(l_tab1_rec_type.line_seq_no , 1,l_tab1_rec_type.pts_cnt - line_seq_no + 1, line_seq_no,pnt_cnt = pnt_cnt+1其中line_no = l_tab1_rec_type.line _否；
结束IF ；

/*逻辑的第二部分与第一部分基本相同，选择查询where子句输入列发生了变化，更新语句发生了小的变化*/

如果/*某些条件*/那么
缓冲区行数：=缓冲区行数- 1 ；
UPDATE选项卡1设置line_no =缓冲区行数，其中line_no = l_收集_var(indx).line_no ；
结束IF ；

END循环;
提交；
当l_收集_var.计数< 200时退出；
END循环;
返回0 ；
结束；

我想我给出了函数的算法，让它看起来更简单，如果你需要整个身体，让我知道。

此外，在更新数据时，由于需要逐行处理，因此无法进行批量操作。

先谢了。
依姆兰。

代码中有一些不好的做法：

-逐行处理（缓慢缓慢）
-在执行更新之前检查行是否存在

对于第一遍，您可以：

-删除选择。修改更新以直接执行此操作
-将这些从内环中取出（indx IN 1.）。并使用批量处理（forall）来执行更新

现在，它们看起来像：

  Update tab1 t
  set line_no   = decode(line_seq_no,1,null,line_no),
    (line_seq_no, pnt_cnt) = (Select pnt_cnt+1, pnt_cnt+1
                              from tab1 s
                              where lng   = l_collection_var(indx).lng_s
                              and lat     = l_collection_var(indx).lat_s
                              and rank    = l_collection_var(indx).rank
                              and line_no < -100)
  where exists (Select line_no
                from tab1 s
                where lng   = l_collection_var(indx).lng_s
                and lat     = l_collection_var(indx).lat_s
                and rank    = l_collection_var(indx).rank
                and line_no < -100);
                
forall indx IN 1 .. l_collection_var.COUNT
  Update tab1
  set line_no   = l_collection_var(indx).line_no,
    line_seq_no = decode(l_tab1_rec_type.line_seq_no,1,l_tab1_rec_type.pts_cnt - line_seq_no + 1,line_seq_no), 
    pnt_cnt     = pnt_cnt+1
  where line_no in (Select line_no
                    from tab1
                    where lng   = l_collection_var(indx).lng_s
                    and lat     = l_collection_var(indx).lat_s
                    and rank    = l_collection_var(indx).rank
                    and line_no < -100);

您可以更进一步，删除显式游标，直接执行所有更新。我不清楚您将如何做到这一点，因为所有的表都称为Tab1。我想现实中不是这样的。