Solved

Query taking huge time

Posted on 2012-03-12
26
696 Views
Last Modified: 2012-03-16
Hello Experts,

I have a query here which is taking more time. All the tables are having more than lakhs of records except the mfg_lookups  table.

SELECT distinct  ma.manufacturer_id,
  ma.manufacturer_name ,
  e.organization_id organization_id ,
  mlu.meaning eco_status,
  mlu.meaning approval_status
FROM eng_engineering_changes e,
  mfg_lookups mlu,
  mtl_mfg_part_numbers map ,
  mtl_manufacturers ma
WHERE e.organization_id =1
and e.status_type       = mlu.lookup_code
AND mlu.lookup_type    IN ( 'ECG_ECN_STATUS','ENG_ECN_APPROVAL_STATUS' )
AND e.organization_id   = map.organization_id
AND ma.manufacturer_id  = map.manufacturer_id;

Open in new window

0
Comment
Question by:Swadhin Ray
  • 10
  • 9
  • 5
  • +2
26 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37709059
well, the first thing you have to check out for is

explain plan for
SELECT distinct  ma.manufacturer_id,
  ma.manufacturer_name ,
  e.organization_id organization_id ,
  mlu.meaning eco_status,
  mlu.meaning approval_status
FROM eng_engineering_changes e,
  mfg_lookups mlu,
  mtl_mfg_part_numbers map ,
  mtl_manufacturers ma
WHERE e.organization_id =1
and e.status_type       = mlu.lookup_code
AND mlu.lookup_type    IN ( 'ECG_ECN_STATUS','ENG_ECN_APPROVAL_STATUS' )
AND e.organization_id   = map.organization_id
AND ma.manufacturer_id  = map.manufacturer_id;

after this run this query

SELECT * FROM TABLE(dbms_xplan.display);

which will show you the explain plan of the query, basis on which you will come to see the cost and the full table scans(if any) so that you can fine tune the query basis on that..
0
 
LVL 3

Expert Comment

by:zofcentr
ID: 37709081
mfg_lookup table has more or less then others ? From context of your description I guess that less, but just want to confirm.
Do you have any indexes on this tables ?
Do you have statistics gathered for objects and for system ?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 37709095
have you tried the group by instead of the distinct ?
SELECT ma.manufacturer_id,
  ma.manufacturer_name ,
  e.organization_id organization_id ,
  mlu.meaning eco_status,
  mlu.meaning approval_status
FROM eng_engineering_changes e,
  mfg_lookups mlu,
  mtl_mfg_part_numbers map ,
  mtl_manufacturers ma
WHERE e.organization_id =1
and e.status_type       = mlu.lookup_code
AND mlu.lookup_type    IN ( 'ECG_ECN_STATUS','ENG_ECN_APPROVAL_STATUS' )
AND e.organization_id   = map.organization_id
AND ma.manufacturer_id  = map.manufacturer_id
GROUP BY
ma.manufacturer_id,
  ma.manufacturer_name ,
  e.organization_id,
  mlu.meaning,
  mlu.meaning;

Open in new window

0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37709109
Table records are as below:

select count(*) from eng_engineering_changes ;
357870
 
select count(*) from mfg_lookups ;
4661
 
select count(*) from mtl_mfg_part_numbers ;
655708
 
select count(*) from mtl_manufacturers ;
3825
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37709111
author , you haven't told the output.. which is needed for further analysis ..

checking out the explain plan is the first thing to check out in any query to tune it..
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37709269
MFG_LOOKUPS VIEW and owner is different and I have readonly permision with MFG_LOOKUPS as SYNONYM on my schema
Sorry for the delay on explain plan :

Here you go :
explain-plan.xls
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37709278
MFG_LOOKUPS is a view in another schema and I have only read only permission.
And this view is granted as a synonyms to my readonly user.

As told this is have less records.

 @Geert_Gruwez : I also run your query but same result , performance didn't increased.


Thanks,
Sloba
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37709305
I see that

ENG_ENGINEERING_CHANGES
MTL_MANUFACTURERS
MTL_MFG_PART_NUMBERS

are going Full table scans and i see in your query you are using a column organization_id from the ENG_ENGINEERING_CHANGES table. I think this column is not indexed.. create an index on this column

and the next big table you have got is MTL_MFG_PART_NUMBERS, you have joined this table in your query, check if you can select any criteria in this table.

rather than using join as  ma.manufacturer_id  = map.manufacturer_id see if you can replace this with a query such as map.manufacturer_id in()

and check whether this table has manufacturer_id column indexed...

this will help your query to get fine tuned and retrieve the output fast
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37709319
What indexes do you have available?

I would think about creating one on manufacturer_id and organization_id in both tables.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37711152
I cannot create any indexes on any table as these are the base tables where we cannot create any index.

@wasimibm: organization_id  is a non index column and I cannot create index as I told before I have only read only privileges where I cannot create any index and which is our base tables .

>>>rather than using join as  ma.manufacturer_id  = map.manufacturer_id see if you can replace this with a query such as map.manufacturer_id in() .

Can't use "in() " as I dont know who many will be there but as of now there are more then 4000 records for which is need to be mapped.

Now if I have a schema with read only privileges and now if I want to optimize a query then what best I can do?

@slightwv : manufacturer_id  is having a index but the table is a synonym for my schema.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711190
>>manufacturer_id  is having a index but the table is a synonym for my schema.

I'm not sure what the synonym has to do with anything.  If there is an index on MTL_MFG_PART_NUMBERS.manufacturer_id, have the DBA make sure statistics are up to date.

>>and now if I want to optimize a query then what best I can do?

I believe you have it.  If you cannot tweak the database and existing indexes cannot be used, then you are limited in what you can do.

I would probably strip out the various combinations of tables to see if one in particular is causing the performance issue.

For exmple, only select the columns and join:
eng_engineering_changes and mfg_lookups

If that runs quick, add mtl_mfg_part_numbers

Continue troubleshooting to see when you get bad performance then focus on what you can to to access the problem table faster.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37711813
I understand slobaray, seeing those tables i think they might be the part of your oracle apps inventory module..

but you can always create indexes on the columns you need, as per oracle , you are not supposed to alter the structure/data types but indexes should not be a problem..

but if you can't create the indexes, perhaps there is little you can do to fine tune the queries..

try a rule hint, but that will be restricted to first(500 rows) depending on the parameter set in your db


select /*+ first_rows */ ... in your query...

as slightwv suggested try trouble shooting by adding up the tables to query starting from two tables, but to really boost up the performance you definitely need indexes..!!!
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37713220
mfg_lookups is a view and the source table is having all the index which I am using it in my query. All the tables have the indexes for all the columns I am using in my query.

So indexes are there for all my Source tables and present in other schema.

Now what best I can do if indexes are there but still the query is taking more time , is I need to modify the code boz group by condition is also taking more time.
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 16

Expert Comment

by:Wasim Akram Shaik
ID: 37713255
----Now what best I can do if indexes are there but still the query is taking more time , is I need to modify the code boz group by condition is also taking more time.

Slobaray, as you can see the explain plan the table access is full, clearly states that index is not present,..

it doesnot matter from which schema do you query the table, oracle will pick up the cost based on CBO(oracle 10g and above).

also if you are sure that indexes are there then analyze the table and recheck the plan, even if the indexes are not picked up, use the index rule hint..

as in select /*+ index(eng_engineering_changes.index_name*/ ....
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37714151
>>explain plan the table access is full, clearly states that index is not present

The Optimizer can choose to not use an index even if one is present.  I forget the percentage but if the Optimizer thinks it will need to access more than around 15-18% of the table, it will choose a Full Table Scan.

Most of the time forcing a change in behavior using hints will not increase performance.

As a test to confirm if an index exists, the easist way is to take the Cost Based Optimizer out of the picture with the RULE hint:

SELECT /*+ RULE */ distinct  ma.manufacturer_id,
...

Rule based optimization says, among other things:  If an index exists, use it.

Have you tried my suggestion to see what table is causing the majority of the problems?
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37714166
yes slightwv agreed, just to emphasize about the explain plan i told that..

yes, if the author is sure about the indexes, i already told to use RULE HINT as

select /*+ index(eng_engineering_changes.index_name*/ so as to force the SQL Engine to use RBO rather than CBO.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37714177
>>as in select /*+ index

Is not the rule hint.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37716217
understood slightwv...

RULE is a rule hint, index is a index hint..
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37716410
I think as my indexes are present but either of the ways is helping me either :

SELECT /*+ RULE */ distinct  ma.manufacturer_id, ....

nor

select /*+ index(eng_engineering_changes.index_name*/ ....

@wasimibm: I just have a doubt if the bracket need to be closed or just need to leave it after index name is been provided ... I also agree this is related to Oracle Apps.

As of now I also don't have any clue for my issue as I already tried this before posting my question.

So see there is no solution for my issue as I am 100% sure for indexes are present on the source schema.

I request you to provide me some clue....
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 37716437
>>I think as my indexes are present but either of the ways is helping me either

The RULE hint was not really meant to help with the performance issue.

Did the explain plan for the RULE hint show you it used an index on the table?  If not, then you don't have the necessary index.

>>I just have a doubt if the bracket need to be closed or just need to leave it after index name is been provided

It is a typo.  You need the parans.  When in doubt, check the docs for proper syntax:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50405
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 250 total points
ID: 37716460
slobarary...

yes, you have to close the bracket..

from any of the suggestion you followed above,, have you checked out the explain plan again?
does it got changed..?

if not, then you may need to look out for indexes,

why dont you query in dba_indexes table for all indexes present on that table?

this will clear out all the confusions and we can have discussion/solution merely based on facts rather than assumptions...
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37716462
thanks slightwv for your information.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37716493
@wasimibm : thanks.. but I have queried that before and told that indexes are present but yes I checked the explain plan after putting the  /*+ RULE */  and /*+ index(eng_engineering_changes.my_index_name)*/  . The plan is same .....no change on that.

Last query from my end is : Can I use multiple hints in one query ?

This is for all the experts :

If yes then can you provide me an example .
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37716542
---Can I use multiple hints in one query ?

Yes, check out for docs for further info and example on how to do that

http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i4852

but, you said,

-----The plan is same .....no change on that.

have you queried in dba_indexes to be sure of that indexes are not present?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37718391
Yes I did the that . And indexes are present.
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 37728596
Thanks experts for all your help , when I used HINTS on my query the performance increased today , after these tables were analyzed.
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

18 Experts available now in Live!

Get 1:1 Help Now