SQL Query tune

I have a query which is taking more time and this is related to Oracle ERP.

My query is as below:

SELECT 
  map.mfg_part_num
  ,MSI.SEGMENT1
  FROM 
  MTL_SYSTEM_ITEMS MSI
  ,MTL_MFG_PART_NUMBERS MAP
  , MTL_MANUFACTURERS MA
WHERE 1=1
AND MAP.ORGANIZATION_ID =1
AND MSI.INVENTORY_ITEM_ID =MAP.INVENTORY_ITEM_ID 
AND EXISTS (SELECT  1 FROM  PO_APPROVED_SUPPLIER_LIST LIST
  WHERE OWNING_ORGANIZATION_ID=1
  AND NVL(LIST.DISABLE_FLAG,'N')  <>'Y'
  AND LIST.MANUFACTURER_ID = MA.MANUFACTURER_ID  );

Open in new window


And here is my explain plan :

 
--------------------------------------------------------------------------------------------
| Id  | Operation             |  Name                      | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |   318M|    13G|       |   809K|
|*  1 |  HASH JOIN            |                            |   318M|    13G|   755M|   809K|
|   2 |   TABLE ACCESS FULL   | MTL_SYSTEM_ITEMS_B         |    25M|   463M|       |   421K|
|   3 |   MERGE JOIN CARTESIAN|                            |   124M|  3218M|       |   301K|
|*  4 |    INDEX FULL SCAN    | MTL_MANUFACTURERS_U1       |   191 |   955 |       |     9 |
|*  5 |     TABLE ACCESS FULL | PO_APPROVED_SUPPLIER_LIST  |  1192 |  9536 |       |  1337 |
|   6 |    BUFFER SORT        |                            |   653K|    13M|       |   301K|
|*  7 |     TABLE ACCESS FULL | MTL_MFG_PART_NUMBERS       |   653K|    13M|       |  1581 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("MSI"."INVENTORY_ITEM_ID"="MAP"."INVENTORY_ITEM_ID")
   4 - filter( EXISTS (SELECT /*+ */ 0 FROM "PO"."PO_APPROVED_SUPPLIER_LIST" "LIST" WHERE 
              "LIST"."OWNING_ORGANIZATION_ID"=1 AND NVL("LIST"."DISABLE_FLAG",'N')<>'Y' AND 
              "LIST"."MANUFACTURER_ID"=:B1))
   5 - filter("LIST"."OWNING_ORGANIZATION_ID"=1 AND NVL("LIST"."DISABLE_FLAG",'N')<>'Y' AND 
              "LIST"."MANUFACTURER_ID"=:B1)
   7 - filter("MAP"."ORGANIZATION_ID"=1)
 

Open in new window


My schema is not the owner of all the tables used in this query .

Need your help to tune  this query this is really very urgent.
LVL 17
Swadhin Ray Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wshark83Commented:
how are you joining msi/map table to ma table...? also which field are looking at that exists in the subquery?
Naveen KumarProduction Manager / Application Support ManagerCommented:
are the dictionary stats upto date ?

The tables in question need to have the stats gathered on them right to keep the stats upto date. So is that happening.

You can query dba_tables to see when these tables were last analyzed
Swadhin Ray Author Commented:
I just changed the query and here the latest one:

select 
  MAP.MFG_PART_NUM
  ,MSI.SEGMENT1
  from 
  MTL_SYSTEM_ITEMS MSI
  ,MTL_MFG_PART_NUMBERS MAP
  , MTL_MANUFACTURERS MA
  ,PO_APPROVED_SUPPLIER_LIST list
where 1=1
and MSI.INVENTORY_ITEM_ID =MAP.INVENTORY_ITEM_ID 
and MSI.ORGANIZATION_ID        =list.OWNING_ORGANIZATION_ID
and list.MANUFACTURER_ID = MA.MANUFACTURER_ID 
and list.OWNING_ORGANIZATION_ID=1
and MAP.ORGANIZATION_ID =1
and NVL(list.DISABLE_FLAG,'N')  <>'Y' ; 

Open in new window


Explain plan for the latest code is
 
------------------------------------------------------------------------------------------------
| Id  | Operation                      |  Name                         | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                               |  6048 |   342K|  4743 |
|   1 |  NESTED LOOPS                  |                               |  6048 |   342K|  4743 |
|*  2 |   HASH JOIN                    |                               |  6048 |   313K|  4743 |
|   3 |    NESTED LOOPS                |                               |  3042 | 94302 |  3139 |
|*  4 |     TABLE ACCESS FULL          | PO_APPROVED_SUPPLIER_LIST     |     1 |     8 |  1337 |
|   5 |     TABLE ACCESS BY INDEX ROWID| MTL_SYSTEM_ITEMS_B            |  4313 | 99199 |  1802 |
|*  6 |      INDEX RANGE SCAN          | XXCMF_MTL_SYSTEM_ITEMS_B_MRP  |  4313 |       |  1011 |
|*  7 |    TABLE ACCESS FULL           | MTL_MFG_PART_NUMBERS          |   653K|    13M|  1581 |
|*  8 |   INDEX UNIQUE SCAN            | MTL_MANUFACTURERS_U1          |     1 |     5 |       |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("MSI"."INVENTORY_ITEM_ID"="MAP"."INVENTORY_ITEM_ID")
   4 - filter("LIST"."MANUFACTURER_ID" IS NOT NULL AND "LIST"."OWNING_ORGANIZATION_ID"=1 AND 
              NVL("LIST"."DISABLE_FLAG",'N')<>'Y')
   6 - access("MSI"."ORGANIZATION_ID"=1)
       filter("MSI"."ORGANIZATION_ID"="LIST"."OWNING_ORGANIZATION_ID")
   7 - filter("MAP"."ORGANIZATION_ID"=1)
   8 - access("LIST"."MANUFACTURER_ID"="MA"."MANUFACTURER_ID")
 
Note: cpu costing is off

Open in new window



@ nav_kum_v : the tables are analyzed , below is the output for last analyzed :


SELECT LAST_ANALYZED
FROM dba_tables
WHERE table_name IN ('MTL_SYSTEM_ITEMS','MTL_MFG_PART_NUMBERS' , 'MTL_MANUFACTURERS', 'PO_APPROVED_SUPPLIER_LIST' ) ;

Open in new window


OUTPUT:
11-MAR-12
11-MAR-12
11-MAR-12
25-MAR-12

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Swadhin Ray Author Commented:
I also tried like below by changing the query again but still no change in the cost but when I run this query or the second one then it shows it running but no result like got stuck :

select /*+ CHOOSE */
ma.manufacturer_name,
  MAP.MFG_PART_NUM
  ,MSI.SEGMENT1
  from 
  MTL_SYSTEM_ITEMS MSI
  ,(select  /*+ FULL(MTL_MFG_PART_NUMBERS) */ INVENTORY_ITEM_ID,MFG_PART_NUM,ORGANIZATION_ID from MTL_MFG_PART_NUMBERS where ORGANIZATION_ID =1) MAP
  , MTL_MANUFACTURERS MA
  ,(select /*+ FULL(PO_APPROVED_SUPPLIER_LIST) */ MANUFACTURER_ID, OWNING_ORGANIZATION_ID from  PO_APPROVED_SUPPLIER_LIST where OWNING_ORGANIZATION_ID=1
and MANUFACTURER_ID is not null) list
where 1=1
and MSI.INVENTORY_ITEM_ID =MAP.INVENTORY_ITEM_ID 
and MSI.ORGANIZATION_ID =list.OWNING_ORGANIZATION_ID
and list.MANUFACTURER_ID = MA.MANUFACTURER_ID 
and MAP.ORGANIZATION_ID =list.OWNING_ORGANIZATION_ID ;

Open in new window

Geert GOracle dbaCommented:
aren't you missing a link between ?
MTL_MFG_PART_NUMBERS and MTL_MANUFACTURERS

>>
WHERE MAP.MANUFACTURER_ID = MA.MANUFACTURER_ID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Swadhin Ray Author Commented:
Yes true
Swadhin Ray Author Commented:
thanks the cost and explain plan looks good now. And executed in seconds..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.