Re: v$process_memory_detail: Category=Other, HEAP_NAME = kolaslAssign

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Sun, 28 Feb 2016 15:57:29 -0500
Message-ID: <CAEpg1wBYz9TTVpsybS4T-gqPi+viBE-2Hc-YdYRr1MDQVfwjaA_at_mail.gmail.com>

Interesting that this came up. We are having what looks like a similar problem (Oracle 12.1.0.2 64bit on Linux) with what we think is a memory issue.

Here's my case:

CREATE OR REPLACE TYPE NCLOB_TAB_TYPE AS TABLE OF NCLOB; CREATE OR REPLACE FUNCTION get_nclob_concat (   in_nclob_tab IN nclob_tab_type,
  in_delimiter IN VARCHAR2 DEFAULT ',')
RETURN NCLOB
IS

l_nclob NCLOB;
l_nvarchar2 NVARCHAR2 (16000);
l_need_nclob BOOLEAN := FALSE;
l_delimiter NVARCHAR2 (1) := in_delimiter;

BEGIN
FOR i IN in_nclob_tab.FIRST .. in_nclob_tab.LAST LOOP
IF l_need_nclob
THEN
l_nclob := l_nclob || l_delimiter || in_nclob_tab (i); ELSE
BEGIN
l_nvarchar2 := l_nvarchar2 || l_delimiter || in_nclob_tab (i); EXCEPTION
WHEN VALUE_ERROR -- ORA 6502
THEN
l_need_nclob := TRUE;
l_nclob := l_nvarchar2 || l_delimiter || in_nclob_tab (i); END;
END IF;
END LOOP; This function is then called millions of times from SQL statement:

insert /*+ APPEND */ into rg2_wrk_part_item_rd_l_li SELECT x.item_id, -- a NUMBER(10)
get_nclob_concat (
  CAST (MULTISET ( SELECT y.text

                                 FROM isz_ww_WRK_PART_ITEM_RD_L y
                               WHERE y.item_id = x.item_id
                               order by y.lang_id) AS nclob_tab_type)
                               ,chr(10))  AS text

FROM (
  select distinct item_id
   from isz_ww_WRK_PART_ITEM_RD_L) x

What we see while monitoring this is that the behavior, sometimes goes very, very bad (which is expressed as very long execute times 40 hrs or more) while at other times it seems to run okay (~20 minutes). We have even simultaneously started two of these statements and see one that runs in 20 minutes and the other goes for 40+ hours. We tried writing this a number of different ways in an attempt to avoid the underlying issue. At first we were passing in an nclobs and just concatenating them. Then we thought since almost all of our data is NVARCHAR2 length, why not write it the way I've shown it here concatenating NVARCHAR2s instead of NCLOBs. That helped but did not solve the problem. Next attempt we changed the input type from an NCLOB to a NVARCHAR2 array, that also helped but did not solve the problem. We are stumped. By the way, we have had a ticket open with Oracle support on this since mid November 2015. So far no help. It's been hard to convince Oracle that there is a problem because eventually the statement finishes successfully.

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 28 2016 - 21:57:29 CET