Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query tune

Posted on 2012-03-29
7
Medium Priority
?
1,156 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 38

Accepted Solution

by:
Geert Gruwez earned 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

916 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