Solved

SQL query taking time

Posted on 2014-11-12
21
179 Views
Last Modified: 2014-11-15
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';

Open in new window



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')
 

Open in new window


Records present on the tables are as below:

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

Open in new window


 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.
0
Comment
Question by:Swadhin Ray
  • 5
  • 4
  • 4
  • +3
21 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 40439258
ms_grc_parent_orgs => 1 index on org_hierarchy_name
ms_flow_down_org => 1 composite index on  org_entity_id +  user_name

ensure index stats are updated

next levels to optimize are:
* indexes on dedicated tablespaces
* partitioned table

however, with so few records, the db taking 12 seconds the issue is rather at instance level:
* not enough RAM on the box
* not enough RAM assigned to the instance
* too slow disks
* disks too occupied
* too many waits
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40439427
After creating the index the cost was reduced but the CPU % got increased :

create index  MGPO_ORG_HIERARCHY_NAME_IDX on MS_GRC_PARENT_ORGS(ORG_HIERARCHY_NAME);
create index  MFDO__OEI_USER_NAME_IDX on MS_FLOW_DOWN_ORG(ORG_ENTITY_ID,USER_NAME );

---OUTPUT 
index MGPO_ORG_HIERARCHY_NAME_IDX created.
index MFDO__OEI_USER_NAME_IDX created.

explain plan for
 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';

select * from table(dbms_xplan.display);

Plan hash value: 1039081615
 
-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |     1 |   141 |   229   (2)| 00:00:03 |
|*  1 |  HASH JOIN SEMI       |                         |     1 |   141 |   229   (2)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL   | MS_GRC_PARENT_ORGS      |   161 | 20930 |     5   (0)| 00:00:01 |
|*  3 |   INDEX FAST FULL SCAN| MFDO__OEI_USER_NAME_IDX |  3524 | 38764 |   223   (1)| 00:00:03 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ORG_ENTITY_ID"="MGPO"."ORG_ENTITY_ID")
   2 - filter("ORG_HIERARCHY_NAME"='Level 4 - Department')
   3 - filter("USER_NAME"='E131094')
 
Note
-----
   - 'PLAN_TABLE' is old version

ALTER INDEX MGPO_ORG_HIERARCHY_NAME_IDX
  REBUILD COMPUTE STATISTICS;

ALTER INDEX MFDO__OEI_USER_NAME_IDX
  REBUILD COMPUTE STATISTICS;	

Still the plan was same: 

Plan hash value: 1039081615
 
-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |     1 |   141 |   229   (2)| 00:00:03 |
|*  1 |  HASH JOIN SEMI       |                         |     1 |   141 |   229   (2)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL   | MS_GRC_PARENT_ORGS      |   161 | 20930 |     5   (0)| 00:00:01 |
|*  3 |   INDEX FAST FULL SCAN| MFDO__OEI_USER_NAME_IDX |  3524 | 38764 |   223   (1)| 00:00:03 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ORG_ENTITY_ID"="MGPO"."ORG_ENTITY_ID")
   2 - filter("ORG_HIERARCHY_NAME"='Level 4 - Department')
   3 - filter("USER_NAME"='E131094')
 
Note
-----
   - 'PLAN_TABLE' is old version

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40439597
and the time of the actual query?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40439611
is taking around 9.5-10.5 Secs
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40439715
For the inner select, try only bringing back the 'first' row?

 WHERE EXISTS (SELECT 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';

Open in new window



Maybe use the first rows hint:

...
SELECT /*+ FIRST_ROWS */ 1
...
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 100 total points
ID: 40439759
SELECT /*+ FIRST_ROWS */ 1

How many "first rows" does the CBO try to fetch?!
I thought this hint needs a certain number of rows like: ?!?

select /*+ first_rows(1) */ 1 from...

Open in new window


I don't see the parameter for this hint declared as optional in the official docs:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABGCCFG
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40439762
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.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40439772
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?!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40439774
>>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.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40439778
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...
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 34

Expert Comment

by:johnsone
ID: 40439785
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.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40439789
Got it:
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...
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40439799
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40439806
Getting way off topic.

I think the more important part of my post was:  AND rownum=1
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 200 total points
ID: 40439845
Try using this hint

/*+ OPT_PARAM('optimizer_dynamic_sampling' 6) */

you can make the sampling higher (but not higher than 9)
if it changes the plan, try gathering extended statistics and then remove the hint

I would not use the ROWNUM=1 method
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40439891
>>> 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?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40439927
after making the changes as suggested :

 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';

Open in new window


Now it is taking 0.7 sec
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40440033
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
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 40440139
On the assumption that the improvement is because the dynamic sampling found correlation between org_entity_id and user_name, gathering extended statistics would look something like this...

DECLARE
    v_dummy VARCHAR2(100);
BEGIN
    v_dummy :=
        DBMS_STATS.create_extended_stats(
            'YOUR_SCHEMA',
            'MS_FLOW_DOWN_ORG',
            '(ORG_ENTITY_ID,USER_NAME)'
        );
    DBMS_STATS.gather_table_stats('YOUR_SCHEMA', 'MS_FLOW_DOWN_ORG');
END;

Open in new window


and after that you should hopefully be able to remove the hint but still gain the benefit.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40444369
This question was really a learning for me. Thanks a lot experts for all your comments.


Thanks,
Sloba
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 40444370
Awesome inputs provided on this question...
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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

707 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

12 Experts available now in Live!

Get 1:1 Help Now