Solved

More CPU time

Posted on 2014-11-10
8
190 Views
Last Modified: 2014-11-12
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
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40434858
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40434946
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40434960
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40434993
Will try this and check ... and update very soon.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40435000
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40436914
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40437306
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 40437430
thanks a ton..
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle sqlplus query delimiter 8 53
running myfile.SQL from command line SQLPLUS 12c does not exit. 7 54
Password_rules_securitty.. 12 46
MS SQL Server Management Studio R2 4 60
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question