Swadhin Ray
asked on
SQL query taking time
Hello Experts,
I have the below SQL which is taking 12 sec to execute when attached to a web application:
Execution plan is as below:
Records present on the tables are as below:
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and the time of the actual query?
ASKER
is taking around 9.5-10.5 Secs
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The FIRST_ROWS hint has nothing to do with the number of rows to be returned. Essentially the default behavior of the CBO is what is the ALL_ROWS hint. This tends toward full table scans. A FIRST_ROWS hint tends toward nested loops. The emphasis is on getting back the first rows the fastest.
Given the size of the tables, it doesn't look to me like you would ever get to use an index on the outer table. What if you switched the order of the 2 columns in the MFDO__OEI_USER_NAME_IDX index? That might get you an index range scan rather than a full scan. That might save some time.
Given the size of the tables, it doesn't look to me like you would ever get to use an index on the outer table. What if you switched the order of the 2 columns in the MFDO__OEI_USER_NAME_IDX index? That might get you an index range scan rather than a full scan. That might save some time.
The FIRST_ROWS hint has nothing to do with the number of rows to be returned.That is not fully true, as shown here:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABGCCFG
The FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently. For integer, specify the number of rows to return.
So, again: Obviously this "integer" parameter is not optional. What does Oracle / the CBO do if you ommit this value?!
>>I thought this hint needs a certain number of rows like: ?!?
Never knew about the number parameter. I try to avoid hints and let the optimizer do its magic. The number is optional. Not sure what the benefit to specifying the number is.
You would just need to experiment to see any possible benefits.
Never knew about the number parameter. I try to avoid hints and let the optimizer do its magic. The number is optional. Not sure what the benefit to specifying the number is.
You would just need to experiment to see any possible benefits.
Never knew about the number parameter. I try to avoid hints and let the optimizer do its magic.I totally agree with you ;-)
But, nevertheless, if this parameter is optional then the official docs are wrong about this one...
I never used the integer hint. It is a optimizer path hint. It tells the optimizer which join method to try to use. ALL_ROWS will tend to use hash joins and FIRST_ROWS will tend to use nested loops.
Got it:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm
Obviously deprecated...
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm
The FIRST_ROWS hint specified without an argument, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability only.
Obviously deprecated...
We tend to not use hints. The optimizer has gotten much better so we don't find that we need to use them that often.
FIRST_ROWS is a blanket hint, we tried not to use it. Hinting the optimizer to use specific indexes was a much better way to go than using this hint.
FIRST_ROWS is a blanket hint, we tried not to use it. Hinting the optimizer to use specific indexes was a much better way to go than using this hint.
Getting way off topic.
I think the more important part of my post was: AND rownum=1
I think the more important part of my post was: AND rownum=1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>> I have the below SQL which is taking 12 sec to execute when attached to a web application:
What is the execution time of the query by itself - without the web app?
What is the execution time of the query by itself - without the web app?
ASKER
after making the changes as suggested :
Now it is taking 0.7 sec
SELECT org_entity_name
,org_entity_id
,org_hierarchy_name
,division
,area
,organization
FROM ms_grc_parent_orgs mgpo
WHERE EXISTS (SELECT /*+ OPT_PARAM('optimizer_dynamic_sampling' 6) */ 1
FROM ms_flow_down_org
WHERE org_entity_id = mgpo.org_entity_id
AND user_name = 'E131094'
and rownum =1)
AND org_hierarchy_name = 'Level 4 - Department';
Now it is taking 0.7 sec
you should not need rownum=1 that's just a wasted step in the plan
the HASH JOIN SEMI was already doing the same thing (or should have anyway - that's what the "SEMI" means)
after your remove the rownum, get full plan with the predicate clauses
the HASH JOIN SEMI was already doing the same thing (or should have anyway - that's what the "SEMI" means)
after your remove the rownum, get full plan with the predicate clauses
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This question was really a learning for me. Thanks a lot experts for all your comments.
Thanks,
Sloba
Thanks,
Sloba
ASKER
Awesome inputs provided on this question...
ASKER
Open in new window