More CPU time

don't use WM_CONCAT   - use a supported method of string aggregation.

don't substring twice.  In fact, why are you using substring at all?  WM_CONCAT returns a varchar2, so using dbms_lob functions on it requires implicit type conversion and pl/sql overhead for no real utility

since you don't really want all of the data anyway, try writing your own string aggregator that imposes a 4K cap then you can remove both of the substring operations

are you suggesting to use the user defined function as like :

http://oracle-base.com/articles/misc/string-aggregation-techniques.php

under : User-Defined Aggregate Function

But I have a small question here : what if the concatenation cross 4K then this will fail.

Or can you are saying to use the "Concatenating Strings with an aggregate"  :

https://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html

Will try this and check ... and update very soon.

actually that last one suggestion needs a little work,  length(self.v_result) will return NULL initially


if length(self.v_result) < 4000 then
    self.v_result  := self.v_result || ',' || p_string;
elsif self.v_result is null then
     self.v_result := p_string;
end if;




   if length(self.v_result) < 4000  then
         self.v_result  := self.v_result ||',' || ctx2.v_result;
   elsif self.v_result is null then
         self.v_result :=  ctx2.v_result;
   end if;


and because we check for NULL and build the initial string differently, there is no leading comma, so substr offset is 1


  returnvalue  := substr(self.v_result,1,4000);

Hello sdstuber,

I modified the function as like below and it works perfectly fine but I don't see any change in the  plan, please see the results as like below:

CREATE OR REPLACE TYPE concat_agg_type
    AS OBJECT
(
    -- This could be defined to 32767, but since the aggregate will be used in SQL,
    -- the 4000 limit is appropriate since that's all that SQL varchar2 strings support
    v_result VARCHAR2(4000),

    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type)
        RETURN NUMBER,

    MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_agg_type, p_string IN VARCHAR2)
        RETURN NUMBER,

    MEMBER FUNCTION odciaggregatemerge(self IN OUT concat_agg_type, ctx2 IN concat_agg_type)
        RETURN NUMBER,

    MEMBER FUNCTION odciaggregateterminate(self          IN     concat_agg_type,
                                           returnvalue      OUT VARCHAR2,
                                           flags         IN     NUMBER
                                          )
        RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY concat_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        ctx  := concat_agg_type(NULL);  -- initialize the concatenation to NULL
        RETURN odciconst.success;
    END odciaggregateinitialize;

    MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_agg_type, p_string IN VARCHAR2)
        RETURN NUMBER
    IS
    BEGIN
        --self.v_result  := self.v_result || ',' || p_string;   -- Append a delimiter and new value
		
		if length(self.v_result) <= 4000 then
			self.v_result  := self.v_result || ',' || p_string;
		end if;
		
		
        RETURN odciconst.success;
    END odciaggregateiterate;

    MEMBER FUNCTION odciaggregatemerge(self IN OUT concat_agg_type, ctx2 IN concat_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        -- If merging, simply concatenate them together
        -- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
        --self.v_result  := self.v_result || ctx2.v_result;
        if length(self.v_result) <= 4000 then
			self.v_result  := self.v_result || ctx2.v_result; 
		end if;
		RETURN odciconst.success;
    END odciaggregatemerge;

    MEMBER FUNCTION odciaggregateterminate(self          IN     concat_agg_type,
                                           returnvalue      OUT VARCHAR2,
                                           flags         IN     NUMBER
                                          )
        RETURN NUMBER
    IS
    BEGIN
        -- Since we prefix the string initially with a comma, remove the extra here before returning
       -- returnvalue  := LTRIM(self.v_result, ',');
	    returnvalue  := substr(self.v_result,2,4000); 
        RETURN odciconst.success;
    END odciaggregateterminate;
END;
/

CREATE OR REPLACE FUNCTION concatagg(p_string VARCHAR2)
    RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
    USING concat_agg_type;
	

Select allOpen in new window

Explain plan as same with WM_CONCAT:

SQL> 
SQL>   explain plan for
  2        SELECT process_instance_id
  3              ,concatagg(comments) AS comments
  4          FROM ms_qs_comments_log
  5         GROUP BY process_instance_id
  6         ORDER BY process_instance_id;

Explained

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4111322783
--------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |   380 | 12540 |     6  (17)| 0
|   1 |  SORT GROUP BY     |                    |   380 | 12540 |     6  (17)| 0
|   2 |   TABLE ACCESS FULL| MS_QS_COMMENTS_LOG |   522 | 17226 |     5   (0)| 0
--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected

Select allOpen in new window

Why would you expect to see a change in the plan?


Changing what happens internally to the function has no bearing on the plan.
That was never the goal.  At least it wasn't mine.  A full table scan is completely appropriate for your query.

The goal was to make the aggregation more efficient while still handling potentially large volumes of data.


You should read my previous post.  The code you implemented is the code I later revised.

also you should define  

 v_result VARCHAR2(4000),

to be

   v_result VARCHAR2(8001),

or larger.  It's possible the interim concatenations might extend over the 4k limit.  That's why the return uses a substr

thanks a ton..