Solved

SQL Query tune

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

867 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

15 Experts available now in Live!

Get 1:1 Help Now