Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Optimalize SQL Query Oracle R12

3706549May 22 2018 — edited May 23 2018

All,

I would like to optimalize join between following tables, please advise.

Table rows count:

  • inv.mtl_material_transactions = 17.466.581
  • inv.mtl_transaction_lot_numbers = 13.680.350
  • gme.gme_material_details = 5.302.282
  • gme.gme_batch_header = 790.828
  • inv.mtl_system_items_b = 516.148
  • GMD.fm_matl_dtl = 280.056

Query which I wrote (run for 1 minute first 50 rows):

SELECT
gbh
.batch_no,
gbh
.batch_id,
msi
.segment1 as ITEM,
--msi.description,
lot
.lot_number as LOT,
gmd
.line_type,
msi
.inventory_item_id
,fmd.attribute1 as SIGNIFICANT_ITEM
--,msi.inventory_item_id||lot.lot_number as THING_ID
FROM
gme
.gme_batch_header gbh,
gme
.gme_material_details gmd,
inv
.mtl_material_transactions mmt,
inv
.mtl_system_items_b msi,
inv
.mtl_transaction_lot_numbers lot
,GMD.fm_matl_dtl fmd
WHERE 1=1
--AND gmd.line_type = -1
AND gbh.batch_id = gmd.batch_id
AND gbh.organization_id = 1509
AND gbh.formula_id = fmd.formula_id
AND msi.inventory_item_id = fmd.inventory_item_id
AND msi.organization_id = fmd.organization_id
AND gmd.organization_id = gbh.organization_id
AND gmd.organization_id = msi.organization_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.trx_source_line_id = gmd.material_detail_id
AND mmt.transaction_source_id = gmd.batch_id
AND mmt.transaction_id = lot.transaction_id
AND lot.TRANSACTION_SOURCE_ID = gbh.Batch_Id
AND lot.organization_id = mmt.organization_id
AND lot.inventory_item_id = mmt.inventory_item_id

Indexes for tables:

  

COLUMN_NAMETABLE_NAME
ITEM_IDFM_MATL_DTL
LINE_TYPEFM_MATL_DTL
FORMULA_IDFM_MATL_DTL
FORMULALINE_IDFM_MATL_DTL
FORMULA_IDFM_MATL_DTL
LINE_TYPEFM_MATL_DTL
LINE_NOFM_MATL_DTL
ORGANIZATION_IDGME_BATCH_HEADER
BATCH_NOGME_BATCH_HEADER
BATCH_STATUSGME_BATCH_HEADER
BATCH_CLOSE_DATEGME_BATCH_HEADER
BATCH_IDGME_BATCH_HEADER
ORGANIZATION_IDGME_BATCH_HEADER
BATCH_NOGME_BATCH_HEADER
BATCH_TYPEGME_BATCH_HEADER
ORGANIZATION_IDGME_MATERIAL_DETAILS
INVENTORY_ITEM_IDGME_MATERIAL_DETAILS
LINE_TYPEGME_MATERIAL_DETAILS
BATCH_IDGME_MATERIAL_DETAILS
PHANTOM_IDGME_MATERIAL_DETAILS
MATERIAL_DETAIL_IDGME_MATERIAL_DETAILS
BATCH_IDGME_MATERIAL_DETAILS
LINE_NOGME_MATERIAL_DETAILS
LINE_TYPEGME_MATERIAL_DETAILS
INVENTORY_ITEM_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
COSTED_FLAGMTL_MATERIAL_TRANSACTIONS
TRANSACTION_GROUP_IDMTL_MATERIAL_TRANSACTIONS
PARENT_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SET_IDMTL_MATERIAL_TRANSACTIONS
PM_COST_COLLECTEDMTL_MATERIAL_TRANSACTIONS
PM_COST_COLLECTOR_GROUP_IDMTL_MATERIAL_TRANSACTIONS
COMPLETION_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
PICK_SLIP_NUMBERMTL_MATERIAL_TRANSACTIONS
MOVE_ORDER_LINE_IDMTL_MATERIAL_TRANSACTIONS
PICKING_LINE_IDMTL_MATERIAL_TRANSACTIONS
RCV_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
PROJECT_IDMTL_MATERIAL_TRANSACTIONS
TO_PROJECT_IDMTL_MATERIAL_TRANSACTIONS
SOURCE_PROJECT_IDMTL_MATERIAL_TRANSACTIONS
TRANSFER_TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
INVOICED_FLAGMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
OPM_COSTED_FLAGMTL_MATERIAL_TRANSACTIONS
TRANSACTION_EXTRACTEDMTL_MATERIAL_TRANSACTIONS
TRX_SOURCE_LINE_IDMTL_MATERIAL_TRANSACTIONS
XML_DOCUMENT_IDMTL_MATERIAL_TRANSACTIONS
SUBINVENTORY_CODEMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
VENDOR_LOT_NUMBERMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
SHIPMENT_NUMBERMTL_MATERIAL_TRANSACTIONS
ACCT_PERIOD_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_NAMEMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATEMTL_MATERIAL_TRANSACTIONS
TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_IDMTL_MATERIAL_TRANSACTIONS
TRANSACTION_IDMTL_MATERIAL_TRANSACTIONS
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
SEGMENT1MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
PLANNER_CODEMTL_SYSTEM_ITEMS_B
WEB_STATUSMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
CUSTOMER_ORDER_ENABLED_FLAGMTL_SYSTEM_ITEMS_B
SERVICE_ITEM_FLAGMTL_SYSTEM_ITEMS_B
VENDOR_WARRANTY_FLAGMTL_SYSTEM_ITEMS_B
USAGE_ITEM_FLAGMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
WIP_SUPPLY_LOCATOR_IDMTL_SYSTEM_ITEMS_B
BASE_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
STYLE_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
EAM_ITEM_TYPEMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
DESCRIPTIONMTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_STATUS_CODEMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
AUTO_CREATED_CONFIG_FLAGMTL_SYSTEM_ITEMS_B
WH_UPDATE_DATEMTL_SYSTEM_ITEMS_B
ITEM_CATALOG_GROUP_IDMTL_SYSTEM_ITEMS_B
CATALOG_STATUS_FLAGMTL_SYSTEM_ITEMS_B
PRODUCT_FAMILY_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
COMMS_NL_TRACKABLE_FLAGMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
BUYER_IDMTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_IDMTL_SYSTEM_ITEMS_B
ORGANIZATION_IDMTL_SYSTEM_ITEMS_B
TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDMTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_DATEMTL_TRANSACTION_LOT_NUMBERS
LOT_NUMBERMTL_TRANSACTION_LOT_NUMBERS
INVENTORY_ITEM_IDMTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDMTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_SOURCE_IDMTL_TRANSACTION_LOT_NUMBERS
PRODUCT_CODEMTL_TRANSACTION_LOT_NUMBERS
PRODUCT_TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
SERIAL_TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_IDMTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDMTL_TRANSACTION_LOT_NUMBERS

Comments

BEDE

Explain plan?

The fact that the first 50 rows are fetched after more than 1 minute, would indicate that a hash join is performed: that makes the first rows be fetched quite late, but afterwards the rows will just flow fast. Although, without seeing the explain-plan, it's only a guess.

Still, I'm quite surprised that the only filtering I may see is by gbh.organization_id = 1509 and there is not filter for some period of tine (one would expect you would like to see the transactions within a period of tine).

And what would you do with millions of records?

To have some idea how much the whole thing would last, do something like below:

with tb as (

select ... --- heder whould be placed your query

)

select count(*)

from tb

;

To have the first rows fetched sooner the explain-plan should have nested loops followed by index scan (unique scan or range scan).

Some hints like leading and use_nl may change the execution plan for the query.

It's good that you mentioned the indexes. Still, without an explain-plan, that doesn't help much.

Have you read https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231 ?

And also: https://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i8327

3706549

Bede,

I apologize that I missed EXPLAIN-PLAN (I have never used it before, thank you for tip), please see below my result (I need get all batches to find product/ingredient), more details in question :

sql.png

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("GBH"."BATCH_ID"="GMD"."BATCH_ID" AND "GMD"."ORGANIZATION_ID"="GBH"."ORGANIZATION_ID" AND

          "GMD"."ORGANIZATION\_ID"="MSI"."ORGANIZATION\_ID")

5 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

6 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

          "MSI"."ORGANIZATION\_ID"="FMD"."ORGANIZATION\_ID")

8 - access("MSI"."ORGANIZATION_ID"=1509)

9 - filter("FMD"."ORGANIZATION_ID"=1509)

10 - filter("GBH"."ORGANIZATION_ID"=1509)

11 - filter("GMD"."ORGANIZATION_ID"=1509)

12 - filter("MMT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND

          "MMT"."INVENTORY\_ITEM\_ID"="MSI"."INVENTORY\_ITEM\_ID" AND "MMT"."TRANSACTION\_SOURCE\_ID"="GMD"."BATCH\_ID")

13 - access("MMT"."TRX_SOURCE_LINE_ID"="GMD"."MATERIAL_DETAIL_ID")

   filter("MMT"."TRX\_SOURCE\_LINE\_ID" IS NOT NULL)

14 - access("MMT"."TRANSACTION_ID"="LOT"."TRANSACTION_ID")

15 - filter("LOT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND "LOT"."TRANSACTION_SOURCE_ID"="GBH"."BATCH_ID"

          AND "LOT"."ORGANIZATION\_ID"="MMT"."ORGANIZATION\_ID" AND "LOT"."INVENTORY\_ITEM\_ID"="MMT"."INVENTORY\_ITEM\_ID")

Now I remove one table and sort condition and I got better result, 1 sec - is it due to sort of conditions + remove 1 table?

SELECT

gbh.batch\_no,

gbh.batch\_id,

msi.segment1 as ITEM,

--msi.description,

lot.lot\_number as LOT,

gmd.line\_type,

msi.inventory\_item\_id

,fmd.attribute1 as SIGNIFICANT\_ITEM

--,msi.inventory\_item\_id||lot.lot\_number as THING\_ID

FROM

gme.gme\_batch\_header gbh,

gme.gme\_material\_details gmd,

--inv.mtl\_material\_transactions mmt,

inv.mtl\_system\_items\_b msi,

inv.mtl\_transaction\_lot\_numbers lot

,GMD.fm\_matl\_dtl fmd

WHERE 1=1

AND fmd.organization\_id = 1509

AND msi.inventory\_item\_id = fmd.inventory\_item\_id

AND msi.organization\_id = fmd.organization\_id

AND fmd.organization\_id = gbh.organization\_id

AND gbh.formula\_id = fmd.formula\_id

AND gbh.BATCH\_STATUS = 4 --4 CLOSED, 3-COMPLETED

AND gbh.batch\_id = gmd.batch\_id

AND gmd.organization\_id = gbh.organization\_id

AND gmd.organization\_id = msi.organization\_id

AND lot.TRANSACTION\_SOURCE\_ID = gbh.Batch\_Id

-- AND mmt.inventory_item_id = msi.inventory_item_id

-- AND mmt.trx_source_line_id = gmd.material_detail_id

--AND mmt.transaction\_source\_id = gmd.batch\_id

-- AND mmt.transaction_id = lot.transaction_id

-- AND lot.organization_id = mmt.organization_id

--AND lot.inventory\_item\_id = mmt.inventory\_item\_id

sql.png

Cookiemonster76

What sort condition?

SeánMacGC

Hello, why not use ANSI Joins, they'd make your JOIN conditions clearer:

FROM

         gme.gme_batch_header gbh

JOIN gme.gme_material_details gmd ON (gbh.batch_id = gmd.batch_id AND gmd.organization_id = gbh.organization_id)
JOIN inv
.mtl_material_transactions mmt ON (mmt.trx_source_line_id = gmd.material_detail_id AND mmt.transaction_source_id = gmd.batch_id)
JOIN inv
.mtl_system_items_b msi ON (gmd.organization_id = msi.organization_id AND mmt.inventory_item_id = msi.inventory_item_id)
JOIN inv
.mtl_transaction_lot_numbers lot ON (mmt.transaction_id = lot.transaction_id AND lot.TRANSACTION_SOURCE_ID = gbh.Batch_Id AND lot.organization_id = mmt.organization_id AND lot.inventory_item_id = mmt.inventory_item_id)
JOIN GMD.fm_matl_dtl fmd ON (gbh.formula_id = fmd.formula_id AND msi.inventory_item_id = fmd.inventory_item_id AND msi.organization_id = fmd.organization_id)
WHERE gbh.organization_id = 1509





3706549

SeánMacGC,

I tried you suggestions and I think I got even worst result:

err.png

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("GMD"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID" AND "GBH"."BATCH_ID"="GMD"."BATCH_ID" AND

          "GMD"."ORGANIZATION\_ID"="GBH"."ORGANIZATION\_ID")

5 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

6 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

          "MSI"."ORGANIZATION\_ID"="FMD"."ORGANIZATION\_ID")

8 - access("MSI"."ORGANIZATION_ID"=1509)

9 - filter("FMD"."ORGANIZATION_ID"=1509)

10 - filter("GBH"."ORGANIZATION_ID"=1509)

11 - filter("GMD"."ORGANIZATION_ID"=1509)

12 - filter("MMT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND

          "MMT"."INVENTORY\_ITEM\_ID"="MSI"."INVENTORY\_ITEM\_ID" AND "MMT"."TRANSACTION\_SOURCE\_ID"="GMD"."BATCH\_ID")

13 - access("MMT"."TRX_SOURCE_LINE_ID"="GMD"."MATERIAL_DETAIL_ID")

   filter("MMT"."TRX\_SOURCE\_LINE\_ID" IS NOT NULL)

14 - access("MMT"."TRANSACTION_ID"="LOT"."TRANSACTION_ID")

15 - filter("LOT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND "LOT"."TRANSACTION_SOURCE_ID"="GBH"."BATCH_ID"

          AND "LOT"."ORGANIZATION\_ID"="MMT"."ORGANIZATION\_ID" AND "LOT"."INVENTORY\_ITEM\_ID"="MMT"."INVENTORY\_ITEM\_ID")

for now my best results here:

SELECT

gbh.batch\_no,

gbh.batch\_id,

msi.segment1 as ITEM,

--msi.description,

lot.lot\_number as LOT,

gmd.line\_type,

msi.inventory\_item\_id

,fmd.attribute1 as SIGNIFICANT\_ITEM

--,msi.inventory\_item\_id||lot.lot\_number as THING\_ID

FROM

gme.gme\_batch\_header gbh,

gme.gme\_material\_details gmd,

inv.mtl\_material\_transactions mmt,

inv.mtl\_system\_items\_b msi,

inv.mtl\_transaction\_lot\_numbers lot

,GMD.fm\_matl\_dtl fmd

WHERE 1=1

--AND gmd.line\_type = -1

AND gbh.batch\_id = gmd.batch\_id

AND gbh.organization\_id = 1509

AND gbh.formula\_id = fmd.formula\_id

AND msi.inventory\_item\_id = fmd.inventory\_item\_id

AND msi.organization\_id = fmd.organization\_id

AND gmd.organization\_id = gbh.organization\_id

AND gmd.organization\_id = msi.organization\_id

AND mmt.inventory\_item\_id = msi.inventory\_item\_id

AND mmt.trx\_source\_line\_id = gmd.material\_detail\_id

AND mmt.transaction\_source\_id = gmd.batch\_id

AND mmt.transaction\_id = lot.transaction\_id

AND gbh.BATCH\_STATUS = 4 --4 CLOSED, 3-COMPLETED

best.png

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

6 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

          "MSI"."ORGANIZATION\_ID"="FMD"."ORGANIZATION\_ID")

8 - access("MSI"."ORGANIZATION_ID"=1509)

9 - filter("FMD"."ORGANIZATION_ID"=1509)

10 - filter("GBH"."ORGANIZATION_ID"=1509 AND "GBH"."BATCH_STATUS"=4)

11 - filter("GMD"."ORGANIZATION_ID"=1509)

12 - access("GBH"."BATCH_ID"="GMD"."BATCH_ID")

13 - filter("MMT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND

          "MMT"."INVENTORY\_ITEM\_ID"="MSI"."INVENTORY\_ITEM\_ID" AND "MMT"."TRANSACTION\_SOURCE\_ID"="GMD"."BATCH\_ID")

14 - access("MMT"."TRX_SOURCE_LINE_ID"="GMD"."MATERIAL_DETAIL_ID")

   filter("MMT"."TRX\_SOURCE\_LINE\_ID" IS NOT NULL)

15 - access("MMT"."TRANSACTION_ID"="LOT"."TRANSACTION_ID")

Cookiemonster76

You haven't answered my question about the sort condition.

You list of indexed columns isn't particularly helpful since it doesn't include the index name, so we can't tell what columns are in what index.

What does

select count(*) from gme_batch_header where organization_id = 1509;

return?

An index on fm_matl_dtl (fmd.formula_id, fmd.inventory_item_id, organization_id) may help.

SeánMacGC

3706549 wrote:

SeánMacGC,

I tried you suggestions and I think I got even worst result:

Not necessarily, there's something else at play here; please run:

SELECT /*+ gather_plan_statistics*/ ...

then this immediately after the query runs: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));

And post the output here.

3706549

Cookiemonster76,

It's my bad about sort condition, please skip it.

There is the list of columns which are indexed.

Query will return 431.995 rows. Without org condition returns 790.828 rows.

select count(1) from gme.gme_batch_header where organization_id = 1509 and BATCH_STATUS <> -1 ;

this query returns even less (I don't need cancalled batches) = 408.584

Sadly I cannot create indexes - I have just read rights.

SeanMacGc,

Alright, I ran query which you mentioned here:

EXPLAIN PLAN FOR   

SELECT /*+ gather_plan_statistics*/

    gbh.batch_no,

    gbh.batch_id,

    msi.segment1 as ITEM,

    --msi.description,

    lot.lot_number as LOT,

    gmd.line_type,

    msi.inventory_item_id

    ,fmd.attribute1 as SIGNIFICANT_ITEM

FROM

         gme.gme_batch_header gbh

JOIN gme.gme_material_details gmd ON (gbh.batch_id = gmd.batch_id AND gmd.organization_id = gbh.organization_id)

JOIN inv.mtl_material_transactions mmt ON (mmt.trx_source_line_id = gmd.material_detail_id AND mmt.transaction_source_id = gmd.batch_id)

JOIN inv.mtl_system_items_b msi ON (gmd.organization_id = msi.organization_id AND mmt.inventory_item_id = msi.inventory_item_id)

JOIN inv.mtl_transaction_lot_numbers lot ON (mmt.transaction_id = lot.transaction_id AND lot.TRANSACTION_SOURCE_ID = gbh.Batch_Id AND lot.organization_id = mmt.organization_id AND lot.inventory_item_id = mmt.inventory_item_id)

JOIN GMD.fm_matl_dtl fmd ON (gbh.formula_id = fmd.formula_id AND msi.inventory_item_id = fmd.inventory_item_id AND msi.organization_id = fmd.organization_id)

WHERE gbh.organization_id = 1509

And I got error about rights

User has no SELECT privilege on V$SESSION

SeánMacGC

Well, you really need to have privileges on V$SESSION, that is if you're supposed to do your job properly!

The reason you need those, is that it's important that we can see the optimizer's estimated cardinalities (row counts) at each execution step, as opposed to the actual cardinalities, which will help to determine why the optimizer may be making a bad choice, and that query on DBMS_XPLAN will retrieve those figures for you.

But, you need basic privileges and permissions (otherwise, as you've seen, the gather_plan_statistics won't work, as you've seen ).

3706549

Yes, you are right.

I am still trying to get better performance. I would like to ask you if its better to get lower rows number in column named rows, right?

sql-23_5_2018.png

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")

5 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND

          "MSI"."ORGANIZATION\_ID"="FMD"."ORGANIZATION\_ID")

7 - access("MSI"."ORGANIZATION_ID"=1509)

8 - filter("FMD"."ORGANIZATION_ID"=1509)

9 - filter("GBH"."ORGANIZATION_ID"=1509 AND "GBH"."BATCH_STATUS"=4)

10 - filter("GMD"."ORGANIZATION_ID"=1509)

11 - access("GBH"."BATCH_ID"="GMD"."BATCH_ID" AND "GMD"."LINE_NO"="FMD"."LINE_NO" AND

          "GMD"."LINE\_TYPE"="FMD"."LINE\_TYPE")

12 - access("LOT"."TRANSACTION_SOURCE_ID"="GBH"."BATCH_ID")

   filter("LOT"."TRANSACTION\_SOURCE\_ID" IS NOT NULL)

13 - filter("LOT"."ORGANIZATION_ID"=1509 AND "LOT"."INVENTORY_ITEM_ID"="GMD"."INVENTORY_ITEM_ID")

SQL:

_**SELECT**_

_**gbh.batch\_no,**_

_**gbh.batch\_id,**_

_**msi.segment1 as ITEM,**_

_**msi.description,**_

_**lot.lot\_number as LOT,**_

_**gmd.line\_type,**_

 _**msi.inventory\_item\_id**_

_**,fmd.attribute1 as SIGNIFICANT\_ITEM**_

_**FROM**_

_**gme.gme\_batch\_header gbh,**_

_**gme.gme\_material\_details gmd,**_

_**--inv.mtl\_material\_transactions mmt,**_

_**inv.mtl\_system\_items\_b msi,**_

_**inv.mtl\_transaction\_lot\_numbers lot**_

_**,GMD.fm\_matl\_dtl fmd**_

_**WHERE 1=1**_

_**AND gbh.batch\_id = gmd.batch\_id**_

_**AND gbh.organization\_id = gmd.organization\_id**_

_**AND gbh.formula\_id = fmd.formula\_id**_

_**AND msi.inventory\_item\_id = fmd.inventory\_item\_id**_

_**AND msi.organization\_id = fmd.organization\_id**_

_**AND gmd.LINE\_TYPE = fmd.LINE\_TYPE**_

_**AND gmd.LINE\_NO = fmd.LINE\_NO --MUZE BYT NESMYSL PRO VICE LAJN**_

_**AND gmd.organization\_id = msi.organization\_id**_

_**--AND lot.inventory\_item\_id = msi.inventory\_item\_id**_

_**AND lot.transaction\_source\_id = gbh.batch\_id**_

_**AND lot.organization\_id = gmd.organization\_id**_

_**AND lot.inventory\_item\_id = gmd.inventory\_item\_id**_

_**--AND lot.lot\_number = '1EP17171590' AND msi.inventory\_item\_id = 1765716**_

_**AND gbh.BATCH\_STATUS = 4 --4 CLOSED, 3-COMPLETED**_

_**AND gbh.organization\_id = 1509**_
SeánMacGC

No, not necessarily.

The key thing here is that the estimated 'Rows' value is reasonably accurate -- if that is badly out, then the optimizer can make the wrong choice, resulting in a suboptimal execution step (like opting for a Nested Loop Join where a Hash Join may have been much better), and that's why it helps to see both the 'estimated' and 'actual' row counts at each step of the execution plan. Up-to-date statistics play a central role here, but it's also possible that other factors are at play, like conditions spanning multiple columns of a single table, or a pipelined function, etc.

In your plan you have a series of Nested Loop Joins, for example, and perhaps those might  be better remodelled alternatively, and also bear in mind that with 12c (and 11gr2), execution plans can change from one invocation to the next, due to the adaptive feedback mechanisms.

3706549

OK,

I think I got my excepted result thanks for EXPLAIN PLAN FOR (I have never used it before):

Have you any idea how to make it little bit better? Some join I missed?

end.png

EXPLAIN PLAN FOR

SELECT

gbh.batch\_type,

gbh.batch\_no,

gbh.batch\_id,

msi.segment1 as ITEM,

msi.description,

lot.lot\_number as LOT,

gmd.line\_type,

 msi.inventory\_item\_id

,fmd.attribute1 as SIGNIFICANT\_ITEM

FROM

gme.gme\_batch\_header gbh,

gme.gme\_material\_details gmd,

--inv.mtl\_material\_transactions mmt,

inv.mtl\_system\_items\_b msi,

inv.mtl\_transaction\_lot\_numbers lot

,GMD.fm\_matl\_dtl fmd

WHERE 1=1

AND gbh.batch\_id = gmd.batch\_id

AND gbh.organization\_id = gmd.organization\_id

AND gbh.formula\_id = fmd.formula\_id

AND msi.inventory\_item\_id = fmd.inventory\_item\_id

AND msi.organization\_id = fmd.organization\_id

AND gmd.LINE\_TYPE = fmd.LINE\_TYPE

AND gmd.LINE\_NO = fmd.LINE\_NO --MOZNA BUDE POTREBA ODSTRANIT!

AND gmd.organization\_id = msi.organization\_id

AND lot.transaction\_source\_id = gbh.batch\_id

AND lot.organization\_id = gmd.organization\_id

AND lot.inventory\_item\_id = gmd.inventory\_item\_id

--AND lot.lot\_number = '1EP17171590' AND msi.inventory\_item\_id = 1765716

AND gbh.batch\_type = 0

-- AND gbh.BATCH_STATUS in (3, 4) --4 CLOSED, 3-COMPLETED

AND gbh.BATCH\_STATUS = 4

AND gbh.organization\_id = 1509
SeánMacGC

No, not really, because this is not an execution plan (what actually happened), it's an explain plan (what the database thinks will happen), and without knowing the details here it's very difficult to estimate accurately. For example, it might be that the Full Table Scan on FM_MATL_DTL is suboptimal, but it's not possible to say definitively without seeing the bigger picture.

Cookiemonster76

3706549 wrote:

Cookiemonster76,

There is the list of columns which are indexed.

I know there's a list - I referred to it in reply #6 when I pointed out it needs to include index names so we can see which columns are in which index.

If you are accessing a table using three columns then a single index on all three columns is going to be much more useful than three indexes each covering just one of the three columns.

We can't tell which of those cases apply here because you haven't supplied index names as part of the list.

John_K

SeánMacGC wrote:

3706549 wrote:

SeánMacGC,

I tried you suggestions and I think I got even worst result:

Not necessarily, there's something else at play here; please run:

SELECT /*+ gather_plan_statistics*/ ...

then this immediately after the query runs: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));

And post the output here.

3706549 - Please do this.

3706549

SeanMacGC,

you are right, maybe I should remove this table and use it in final result.

Cookiemonster,

can you give me some example which join will be better than? I did not know that about indexes.

See list below:

    

COLUMN_NAMEUNIQUENESSINDEX_NAMETABLE_NAME
ITEM_IDNONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
LINE_TYPENONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
FORMULA_IDNONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
FORMULALINE_IDUNIQUEFM_MATL_DTL_PKFM_MATL_DTL
FORMULA_IDUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
LINE_TYPEUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
LINE_NOUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
ORGANIZATION_IDNONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
BATCH_NONONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
BATCH_STATUSNONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
BATCH_CLOSE_DATENONUNIQUEGME_BATCH_HEADER_N2GME_BATCH_HEADER
BATCH_IDUNIQUEGME_BATCH_HEADER_PKGME_BATCH_HEADER
ORGANIZATION_IDNONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
BATCH_NONONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
BATCH_TYPENONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
ORGANIZATION_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
INVENTORY_ITEM_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
LINE_TYPENONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
BATCH_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
PHANTOM_IDNONUNIQUEGME_MATERIAL_DETAILS_N2GME_MATERIAL_DETAILS
MATERIAL_DETAIL_IDUNIQUEGME_MATERIAL_DETAILS_PKGME_MATERIAL_DETAILS
BATCH_IDUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
LINE_NOUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
LINE_TYPEUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N1MTL_SYSTEM_ITEMS_B
SEGMENT1NONUNIQUEMTL_SYSTEM_ITEMS_B_N1MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N10MTL_SYSTEM_ITEMS_B
PLANNER_CODENONUNIQUEMTL_SYSTEM_ITEMS_B_N10MTL_SYSTEM_ITEMS_B
WEB_STATUSNONUNIQUEMTL_SYSTEM_ITEMS_B_N11MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N11MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
CUSTOMER_ORDER_ENABLED_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
SERVICE_ITEM_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
VENDOR_WARRANTY_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
USAGE_ITEM_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N13MTL_SYSTEM_ITEMS_B
WIP_SUPPLY_LOCATOR_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N13MTL_SYSTEM_ITEMS_B
BASE_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N14MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N14MTL_SYSTEM_ITEMS_B
STYLE_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N15MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N15MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N16MTL_SYSTEM_ITEMS_B
EAM_ITEM_TYPENONUNIQUEMTL_SYSTEM_ITEMS_B_N16MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N2MTL_SYSTEM_ITEMS_B
DESCRIPTIONNONUNIQUEMTL_SYSTEM_ITEMS_B_N2MTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_STATUS_CODENONUNIQUEMTL_SYSTEM_ITEMS_B_N3MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N4MTL_SYSTEM_ITEMS_B
AUTO_CREATED_CONFIG_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N4MTL_SYSTEM_ITEMS_B
WH_UPDATE_DATENONUNIQUEMTL_SYSTEM_ITEMS_B_N5MTL_SYSTEM_ITEMS_B
ITEM_CATALOG_GROUP_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N6MTL_SYSTEM_ITEMS_B
CATALOG_STATUS_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N6MTL_SYSTEM_ITEMS_B
PRODUCT_FAMILY_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N7MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N7MTL_SYSTEM_ITEMS_B
COMMS_NL_TRACKABLE_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N8MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N8MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N9MTL_SYSTEM_ITEMS_B
BUYER_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N9MTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_IDUNIQUEMTL_SYSTEM_ITEMS_B_U1MTL_SYSTEM_ITEMS_B
ORGANIZATION_IDUNIQUEMTL_SYSTEM_ITEMS_B_U1MTL_SYSTEM_ITEMS_B
TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N1MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N2MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_DATENONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N2MTL_TRANSACTION_LOT_NUMBERS
LOT_NUMBERNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
INVENTORY_ITEM_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_SOURCE_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N4MTL_TRANSACTION_LOT_NUMBERS
PRODUCT_CODENONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N5MTL_TRANSACTION_LOT_NUMBERS
PRODUCT_TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N5MTL_TRANSACTION_LOT_NUMBERS
SERIAL_TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2018
Added on May 22 2018
16 comments
3,799 views