Solved

More CPU time

Posted on 2014-11-10
8
173 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
  • 4
  • 4
8 Comments
 
LVL 73

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 73

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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40434993
Will try this and check ... and update very soon.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now