?
Solved

Need help on tuning the query

Posted on 2014-08-18
14
Medium Priority
?
300 Views
Last Modified: 2014-08-20
I need to fine tune the below query , since it is taking more than 7 secs . In the below plan , it is not using the primary index on the column
organization_id in the organization table. Hence , the query performance is below par. Any help in fine tuning the query to use the index on organization
table and better execution plan is really appreciated.
I have attached the execution plan for the query. Organization table is going for an full table scan.

Query :
Select NULL crm_organization_id,
    ilo.organization_id ,
    ilo.PARENT_ID,
    NULL DEAL_GROUP_ID ,
    CASE
      WHEN EXISTS
        (SELECT 1
        FROM dcm.deal_group dg
        WHERE dg.organization_id=ilo.organization_id
        )
      THEN 'T'
      ELSE 'F'
    END IS_INTERNAL ,
    NULL OWNER_USER_ID ,
    DECODE(ilo.STATUS_ID ,11,11,2) STATUS_ID,
    ilo.ORGANIZATION_NAME,
    (
    SELECT listagg(url ,',') within GROUP (
    ORDER BY organization_domain_id) domain
    FROM il_owner.organization_domain ilod
    WHERE ilod.organization_id=ilo.organization_id
    ) domain,
    NULL LOCK_VERSION ,
    NULL NAME_EN,
    NULL TAX_ID,
    c.ADDRESS1,
    c.ADDRESS2,
    c.STATE,
    c.CITY,
    c.ZIP,
    NULL SUMMARY,
    c.COUNTRY_ID ,
    NULL REGION_ID,
    NULL VISIBILITY_TYPE,
    NULL VISIBILITY_RESOURCE_ID,
    'N' IS_ARRANGER , 
    'N' IS_BORROWER , 
    'N' IS_INVESTOR,  
    ilo.status_id    
from organization ilo
LEFT OUTER JOIN contact c
on (ilo.organization_id=c.resource_id and c.resource_type_id=10 and c.contact_type_id=4)

Open in new window

Queryplan.txt
0
Comment
Question by:sam_2012
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 120 total points
ID: 40267200
I see the problem in "HASH JOIN RIGHT OUTER" :
 Can you try using one hint and check the plan and execution time :

 /*+ USE_HASH(contact) */

It would also be good if you can send some sample data and structure of the below tables:

organization
contact
deal_group
organization_domain

Also check if you have the indexes on the columns used in the joins or not.
0
 
LVL 36

Expert Comment

by:ste5an
ID: 40267316
Do you have an index on Contact ( ContactType, ResourceType, Resource_ID) ?
0
 

Author Comment

by:sam_2012
ID: 40267325
Even after adding the use_hash hint , the execution plan i s same. Also , we have an index on resource_id,resource _type_id and contact_type_id  in contact table, we have an index on organization_id in organization_domain table and index in organization_id in organization table.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:sam_2012
ID: 40267328
all indexes are used for deal_group table also , we haven index on organization_id in deal_group table.
0
 
LVL 16

Assisted Solution

by:Swadhin Ray
Swadhin Ray earned 120 total points
ID: 40267331
Can you create a composite index on Contact  table for the below columns:
create index <<index name >> on Contact   (ContactType, ResourceType, Resource_ID)  ;

the check the plan and also time taking to execute.
0
 
LVL 36

Expert Comment

by:ste5an
ID: 40267333
Also , we have an index on resource_id,resource _type_id and contact_type_id  in contact table
Yup, that is what I've suspected.

It's the wrong order of columns to support your query.

For a index supporting your query, the constant columns must be the first in the index. Whether contact_type_id or resource_type_id should be first depends on the selectivity of those predicates.

btw, depeding on the size of the contact table and the remaining tables space you may also to consider adding c.ADDRESS1,c.ADDRESS2, c.STATE, c.CITY, c.ZIP, c.COUNTRY_ID to this index to get a covering index.
0
 

Author Comment

by:sam_2012
ID: 40267339
there is an index on the contact table contact_pk
if u see the execution plan , this index is used. I need some logic to use the index on organization table .
0
 
LVL 36

Expert Comment

by:ste5an
ID: 40267340
Can you post the entire DDL for your contact table?
0
 

Author Comment

by:sam_2012
ID: 40267387
desc contact
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
RESOURCE_TYPE_ID               NOT NULL NUMBER                                                                                                                                                                                        
CONTACT_TYPE_ID                NOT NULL NUMBER                                                                                                                                                                                        
RESOURCE_ID                    NOT NULL NUMBER                                                                                                                                                                                        
COUNTRY_ID                              NUMBER                                                                                                                                                                                        
USERNAME                                VARCHAR2(100 CHAR)                                                                                                                                                                            
FIRST_NAME                              VARCHAR2(30 CHAR)                                                                                                                                                                             
MIDDLE_NAME                             VARCHAR2(30 CHAR)                                                                                                                                                                             
LAST_NAME                               VARCHAR2(30 CHAR)                                                                                                                                                                             
ADDRESS1                                VARCHAR2(50 CHAR)                                                                                                                                                                             
ADDRESS2                                VARCHAR2(50 CHAR)                                                                                                                                                                             
ADDRESS3                                VARCHAR2(50 CHAR)                                                                                                                                                                             
CITY                                    VARCHAR2(50 CHAR)                                                                                                                                                                             
STATE                                   VARCHAR2(50 CHAR)                                                                                                                                                                             
ZIP                                     VARCHAR2(50 CHAR)                                                                                                                                                                             
TELEPHONE_NUMBER                        VARCHAR2(255 CHAR)                                                                                                                                                                            
FAX_NUMBER                              VARCHAR2(255 CHAR)                                                                                                                                                                            
CREATED_BY                     NOT NULL NUMBER                                                                                                                                                                                        
CREATED_DATE                   NOT NULL DATE                                                                                                                                                                                          
LAST_MODIFIED_BY               NOT NULL NUMBER                                                                                                                                                                                        
LAST_MODIFIED_DATE             NOT NULL DATE                                                                                                                                                                                          
CONTACT_ID                     NOT NULL NUMBER  

Open in new window

primary key i s on resource_type_id , contact_type_id and resource_id.
0
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40267395
Did you tried using :

from organization ilo
LEFT OUTER JOIN contact c
on (ilo.organization_id= c.resource_type_id=10 and c.contact_type_id=4 and c.resource_id )
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 1380 total points
ID: 40267414
To clarify an earlier point, columns in an index do not need to be in a specific order for the index to be used.  If you are not using all the columns in the index in order, the index can still be used in an index skip scan.  The details of how they were are in the concepts manual here -> http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT88837

Your query is asking for every row in the ORGANIZATION table.  If you want every row, there is no better way to do that than with a full scan.  Using an index is pointless and just makes it slower.

About the only thing I can see that would improve the query would be removing the table access on CONTACT, but when you are only retrieving 2,600 rows I don't see much improvement that can be gained there.

To me 7 seconds to get back over 250,000 rows seems pretty reasonable.
0
 

Author Comment

by:sam_2012
ID: 40267426
I tried this join , but still no index on organization table is used. Also , when i include any equal condition in the where clause , index is used.

For example
Select *
from
organization ilo left outer join contact c on (c.resource_type_id=10 and c.contact_type_id=4 and c.resource_id =ilo.organization_id ) where organization_id =<> , then the index is used.

I tried with the below option, it does not work.

from organization ilo
 LEFT OUTER JOIN contact c
 on ( c.resource_type_id=10 and c.contact_type_id=4 and c.resource_id =ilo.organization_id )
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 1380 total points
ID: 40267535
Yes, if you put a where condition on the ORGANIZATION table that limits rows and is indexed, it will use an index.  You have changed your results from all rows in the ORGANIZATION table to only some rows.  It shouldn't be surprising that would change the plan.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month16 days, 22 hours left to enroll

864 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