Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

More CPU time

Hello Experts,

I have the below table which does not have any index and the SQL query is as like below:

SQL>    explain plan for
  2      SELECT   process_instance_id,
  3  			   substrb(dbms_lob.substr(wm_concat(comments), 4000, 1),
  4  					   1,
  5  					   4000) AS comments
  6  		FROM   ms_qs_comments_log
  7  		GROUP  BY process_instance_id
  8  		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   |                    |   325 | 11375 |     6  (17)| 0
|   1 |  SORT GROUP BY     |                    |   325 | 11375 |     6  (17)| 0
|   2 |   TABLE ACCESS FULL| MS_QS_COMMENTS_LOG |   454 | 15890 |     5   (0)| 0
--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected

Open in new window


How I can optimize the SQL in such a way that it get reduce the CPU time.
0
Swadhin Ray
Asked:
Swadhin Ray
  • 4
  • 4
1 Solution
 
sdstuberCommented:
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
0
 
Swadhin RaySenior Technical Engineer Author Commented:
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"  :

http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html
0
 
sdstuberCommented:
I'm saying create your own like concat_agg in my article

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

except internally don't let the string grow to more than 4000 characters.

If your concatenated string will never be more than 32K then the easiest method would be to simply apply substr inside the concat type body itself,  this way is also the most efficient in terms of performance
but has the caveat that it will break if you concatenate more than 32K before exiting.

change
       returnvalue  := LTRIM(self.v_result, ',');
to
      returnvalue  := substr(self.v_result,2,4000);


If your concatenations might exceed 32K, then you'll have to address it at each concatenation.  This is a little slower but will work regardless of the amount of data you aggregate.


change
     self.v_result  := self.v_result || ',' || p_string;
to
    self.v_result  := substr(self.v_result || ',' || p_string,1,4001);


and change
     self.v_result  := self.v_result || ctx2.v_result;
to
     self.v_result  := substr(self.v_result || ctx2.v_result,1,4001);



to make it even smarter, you could check the length and only do the concatenation if the string is less than or equal to 4000.  Because, what's the use of concatenating more if you're just going to remove it anyway.


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

if length(self.v_result) <= 4000 then
      self.v_result  := self.v_result || ctx2.v_result;
end if;


then apply the substr only once at the end

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




if the values above seem off by one it's to compensate for the leading comma that is carried through the type until the end
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Swadhin RaySenior Technical Engineer Author Commented:
Will try this and check ... and update very soon.
0
 
sdstuberCommented:
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);
0
 
Swadhin RaySenior Technical Engineer Author Commented:
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;
	

Open 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

Open in new window

0
 
sdstuberCommented:
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
0
 
Swadhin RaySenior Technical Engineer Author Commented:
thanks a ton..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now