Solved

SQL Query tune

Posted on 2012-03-29
7
1,126 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Expert Comment

by:wshark83
ID: 37781143
how are you joining msi/map table to ma table...? also which field are looking at that exists in the subquery?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 37781162
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
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37781268
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

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37781376
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

0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 37781438
aren't you missing a link between ?
MTL_MFG_PART_NUMBERS and MTL_MANUFACTURERS

>>
WHERE MAP.MANUFACTURER_ID = MA.MANUFACTURER_ID
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 37781635
Yes true
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 37781649
thanks the cost and explain plan looks good now. And executed in seconds..
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

710 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