troubleshooting Question

SQL query taking time

Avatar of Swadhin Ray
Swadhin RayFlag for United States of America asked on
Oracle Database
21 Comments5 Solutions213 ViewsLast Modified:
Hello Experts,

I have the below SQL which is taking 12 sec to execute when attached to a web application:

SELECT org_entity_name
       ,org_entity_id
       ,org_hierarchy_name
       ,division
       ,area
       ,organization
   FROM ms_grc_parent_orgs mgpo
  WHERE EXISTS (SELECT 1
           FROM ms_flow_down_org
          WHERE org_entity_id = mgpo.org_entity_id
                AND user_name = 'E131094')
        AND org_hierarchy_name = 'Level 4 - Department';


Execution plan is as below:

				Plan hash value: 4043587597
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     1 |   141 |   585   (1)| 00:00:08 |
|*  1 |  HASH JOIN SEMI              |                    |     1 |   141 |   585   (1)| 00:00:08 |
|*  2 |   TABLE ACCESS FULL          | MS_GRC_PARENT_ORGS |   161 | 20930 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| MS_FLOW_DOWN_ORG   |  3524 | 38764 |   580   (1)| 00:00:07 |
|*  4 |    INDEX RANGE SCAN          | MFDO_USER_NAME_IDX |  3524 |       |    16   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ORG_ENTITY_ID"="MGPO"."ORG_ENTITY_ID")
   2 - filter("ORG_HIERARCHY_NAME"='Level 4 - Department')
   4 - access("USER_NAME"='E131094')
 

Records present on the tables are as below:

select count(*) from ms_grc_parent_orgs;	 -- 263
			
select count(*) from ms_flow_down_org; -- 243177 

 indexes are present only on table ms_flow_down_org for columns :  ACTIVITY_NAME, ORG_ENTITY_ID, USER_ID, USER_NAME [No composite index present] but even index creation on "ms_grc_parent_orgs" table doesn't help.
No change in the plan or on time.

Please help on this.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 5 Answers and 21 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 5 Answers and 21 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