Solved

SQL Query tune

Posted on 2012-03-29
7
1,108 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
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
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

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 36

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

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

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

14 Experts available now in Live!

Get 1:1 Help Now