Query taking huge time

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..

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 ?

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;

Select allOpen in new window

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

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..

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

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

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

What indexes do you have available?

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

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.

>>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.

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..!!!

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.

----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*/ ....

>>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?

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.

>>as in select /*+ index

Is not the rule hint.

understood slightwv...

RULE is a rule hint, index is a index hint..

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....

thanks slightwv for your information.

@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 .

---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?

Yes I did the that . And indexes are present.

Thanks experts for all your help , when I used HINTS on my query the performance increased today , after these tables were analyzed.