troubleshooting Question

More CPU time

Avatar of Swadhin Ray
Swadhin RayFlag for United States of America asked on
Oracle Database
8 Comments1 Solution226 ViewsLast Modified:
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

How I can optimize the SQL in such a way that it get reduce the CPU time.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros