Optimalize SQL Query Oracle R12

3706549 · May 22, 2018 1:05AM

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_NAME	TABLE_NAME
ITEM_ID	FM_MATL_DTL
LINE_TYPE	FM_MATL_DTL
FORMULA_ID	FM_MATL_DTL
FORMULALINE_ID	FM_MATL_DTL
FORMULA_ID	FM_MATL_DTL
LINE_TYPE	FM_MATL_DTL
LINE_NO	FM_MATL_DTL
ORGANIZATION_ID	GME_BATCH_HEADER
BATCH_NO	GME_BATCH_HEADER
BATCH_STATUS	GME_BATCH_HEADER
BATCH_CLOSE_DATE	GME_BATCH_HEADER
BATCH_ID	GME_BATCH_HEADER
ORGANIZATION_ID	GME_BATCH_HEADER
BATCH_NO	GME_BATCH_HEADER
BATCH_TYPE	GME_BATCH_HEADER
ORGANIZATION_ID	GME_MATERIAL_DETAILS
INVENTORY_ITEM_ID	GME_MATERIAL_DETAILS
LINE_TYPE	GME_MATERIAL_DETAILS
BATCH_ID	GME_MATERIAL_DETAILS
PHANTOM_ID	GME_MATERIAL_DETAILS
MATERIAL_DETAIL_ID	GME_MATERIAL_DETAILS
BATCH_ID	GME_MATERIAL_DETAILS
LINE_NO	GME_MATERIAL_DETAILS
LINE_TYPE	GME_MATERIAL_DETAILS
INVENTORY_ITEM_ID	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATE	MTL_MATERIAL_TRANSACTIONS
COSTED_FLAG	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_GROUP_ID	MTL_MATERIAL_TRANSACTIONS
PARENT_TRANSACTION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_SET_ID	MTL_MATERIAL_TRANSACTIONS
PM_COST_COLLECTED	MTL_MATERIAL_TRANSACTIONS
PM_COST_COLLECTOR_GROUP_ID	MTL_MATERIAL_TRANSACTIONS
COMPLETION_TRANSACTION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATE	MTL_MATERIAL_TRANSACTIONS
PICK_SLIP_NUMBER	MTL_MATERIAL_TRANSACTIONS
MOVE_ORDER_LINE_ID	MTL_MATERIAL_TRANSACTIONS
PICKING_LINE_ID	MTL_MATERIAL_TRANSACTIONS
RCV_TRANSACTION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_ID	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATE	MTL_MATERIAL_TRANSACTIONS
PROJECT_ID	MTL_MATERIAL_TRANSACTIONS
TO_PROJECT_ID	MTL_MATERIAL_TRANSACTIONS
SOURCE_PROJECT_ID	MTL_MATERIAL_TRANSACTIONS
TRANSFER_TRANSACTION_ID	MTL_MATERIAL_TRANSACTIONS
INVOICED_FLAG	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_ID	MTL_MATERIAL_TRANSACTIONS
OPM_COSTED_FLAG	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_EXTRACTED	MTL_MATERIAL_TRANSACTIONS
TRX_SOURCE_LINE_ID	MTL_MATERIAL_TRANSACTIONS
XML_DOCUMENT_ID	MTL_MATERIAL_TRANSACTIONS
SUBINVENTORY_CODE	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATE	MTL_MATERIAL_TRANSACTIONS
VENDOR_LOT_NUMBER	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATE	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_MATERIAL_TRANSACTIONS
SHIPMENT_NUMBER	MTL_MATERIAL_TRANSACTIONS
ACCT_PERIOD_ID	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_ID	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATE	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_NAME	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_ID	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_DATE	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_ACTION_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_TYPE_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_SOURCE_TYPE_ID	MTL_MATERIAL_TRANSACTIONS
TRANSACTION_ID	MTL_MATERIAL_TRANSACTIONS
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
SEGMENT1	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
PLANNER_CODE	MTL_SYSTEM_ITEMS_B
WEB_STATUS	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
CUSTOMER_ORDER_ENABLED_FLAG	MTL_SYSTEM_ITEMS_B
SERVICE_ITEM_FLAG	MTL_SYSTEM_ITEMS_B
VENDOR_WARRANTY_FLAG	MTL_SYSTEM_ITEMS_B
USAGE_ITEM_FLAG	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
WIP_SUPPLY_LOCATOR_ID	MTL_SYSTEM_ITEMS_B
BASE_ITEM_ID	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
STYLE_ITEM_ID	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
EAM_ITEM_TYPE	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
DESCRIPTION	MTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_STATUS_CODE	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
AUTO_CREATED_CONFIG_FLAG	MTL_SYSTEM_ITEMS_B
WH_UPDATE_DATE	MTL_SYSTEM_ITEMS_B
ITEM_CATALOG_GROUP_ID	MTL_SYSTEM_ITEMS_B
CATALOG_STATUS_FLAG	MTL_SYSTEM_ITEMS_B
PRODUCT_FAMILY_ITEM_ID	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
COMMS_NL_TRACKABLE_FLAG	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
BUYER_ID	MTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_ID	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	MTL_SYSTEM_ITEMS_B
TRANSACTION_ID	MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_ID	MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_DATE	MTL_TRANSACTION_LOT_NUMBERS
LOT_NUMBER	MTL_TRANSACTION_LOT_NUMBERS
INVENTORY_ITEM_ID	MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_ID	MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_SOURCE_ID	MTL_TRANSACTION_LOT_NUMBERS
PRODUCT_CODE	MTL_TRANSACTION_LOT_NUMBERS
PRODUCT_TRANSACTION_ID	MTL_TRANSACTION_LOT_NUMBERS
SERIAL_TRANSACTION_ID	MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_ID	MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_ID	MTL_TRANSACTION_LOT_NUMBERS

BEDE · May 22, 2018 1:19AM

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 · May 22, 2018 1:31AM

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 :

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

Cookiemonster76 · May 22, 2018 5:12AM

What sort condition?

SeánMacGC · May 22, 2018 5:30AM

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 · May 22, 2018 8:41AM

SeánMacGC,

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

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

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 · May 22, 2018 9:04AM

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 · May 22, 2018 9:27AM

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 · May 23, 2018 1:22AM

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 · May 23, 2018 1:41AM

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 · May 23, 2018 3:01AM

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?

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 · May 23, 2018 3:09AM

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 · May 23, 2018 3:55AM

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?

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 · May 23, 2018 4:09AM

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 · May 23, 2018 4:10AM

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 · May 23, 2018 4:43AM

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 · May 23, 2018 7:39AM

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_NAME	UNIQUENESS	INDEX_NAME	TABLE_NAME
ITEM_ID	NONUNIQUE	FM_MATL_DTLI1	FM_MATL_DTL
LINE_TYPE	NONUNIQUE	FM_MATL_DTLI1	FM_MATL_DTL
FORMULA_ID	NONUNIQUE	FM_MATL_DTLI1	FM_MATL_DTL
FORMULALINE_ID	UNIQUE	FM_MATL_DTL_PK	FM_MATL_DTL
FORMULA_ID	UNIQUE	FM_MATL_DTL_U1	FM_MATL_DTL
LINE_TYPE	UNIQUE	FM_MATL_DTL_U1	FM_MATL_DTL
LINE_NO	UNIQUE	FM_MATL_DTL_U1	FM_MATL_DTL
ORGANIZATION_ID	NONUNIQUE	GME_BATCH_HEADER_N1	GME_BATCH_HEADER
BATCH_NO	NONUNIQUE	GME_BATCH_HEADER_N1	GME_BATCH_HEADER
BATCH_STATUS	NONUNIQUE	GME_BATCH_HEADER_N1	GME_BATCH_HEADER
BATCH_CLOSE_DATE	NONUNIQUE	GME_BATCH_HEADER_N2	GME_BATCH_HEADER
BATCH_ID	UNIQUE	GME_BATCH_HEADER_PK	GME_BATCH_HEADER
ORGANIZATION_ID	NONUNIQUE	GME_BATCH_HEADER_U1	GME_BATCH_HEADER
BATCH_NO	NONUNIQUE	GME_BATCH_HEADER_U1	GME_BATCH_HEADER
BATCH_TYPE	NONUNIQUE	GME_BATCH_HEADER_U1	GME_BATCH_HEADER
ORGANIZATION_ID	NONUNIQUE	GME_MATERIAL_DETAILS_N1	GME_MATERIAL_DETAILS
INVENTORY_ITEM_ID	NONUNIQUE	GME_MATERIAL_DETAILS_N1	GME_MATERIAL_DETAILS
LINE_TYPE	NONUNIQUE	GME_MATERIAL_DETAILS_N1	GME_MATERIAL_DETAILS
BATCH_ID	NONUNIQUE	GME_MATERIAL_DETAILS_N1	GME_MATERIAL_DETAILS
PHANTOM_ID	NONUNIQUE	GME_MATERIAL_DETAILS_N2	GME_MATERIAL_DETAILS
MATERIAL_DETAIL_ID	UNIQUE	GME_MATERIAL_DETAILS_PK	GME_MATERIAL_DETAILS
BATCH_ID	UNIQUE	GME_MATERIAL_DETAILS_U1	GME_MATERIAL_DETAILS
LINE_NO	UNIQUE	GME_MATERIAL_DETAILS_U1	GME_MATERIAL_DETAILS
LINE_TYPE	UNIQUE	GME_MATERIAL_DETAILS_U1	GME_MATERIAL_DETAILS
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N1	MTL_SYSTEM_ITEMS_B
SEGMENT1	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N1	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N10	MTL_SYSTEM_ITEMS_B
PLANNER_CODE	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N10	MTL_SYSTEM_ITEMS_B
WEB_STATUS	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N11	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N11	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N12	MTL_SYSTEM_ITEMS_B
CUSTOMER_ORDER_ENABLED_FLAG	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N12	MTL_SYSTEM_ITEMS_B
SERVICE_ITEM_FLAG	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N12	MTL_SYSTEM_ITEMS_B
VENDOR_WARRANTY_FLAG	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N12	MTL_SYSTEM_ITEMS_B
USAGE_ITEM_FLAG	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N12	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N13	MTL_SYSTEM_ITEMS_B
WIP_SUPPLY_LOCATOR_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N13	MTL_SYSTEM_ITEMS_B
BASE_ITEM_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N14	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N14	MTL_SYSTEM_ITEMS_B
STYLE_ITEM_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N15	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N15	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N16	MTL_SYSTEM_ITEMS_B
EAM_ITEM_TYPE	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N16	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N2	MTL_SYSTEM_ITEMS_B
DESCRIPTION	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N2	MTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_STATUS_CODE	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N3	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N4	MTL_SYSTEM_ITEMS_B
AUTO_CREATED_CONFIG_FLAG	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N4	MTL_SYSTEM_ITEMS_B
WH_UPDATE_DATE	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N5	MTL_SYSTEM_ITEMS_B
ITEM_CATALOG_GROUP_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N6	MTL_SYSTEM_ITEMS_B
CATALOG_STATUS_FLAG	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N6	MTL_SYSTEM_ITEMS_B
PRODUCT_FAMILY_ITEM_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N7	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N7	MTL_SYSTEM_ITEMS_B
COMMS_NL_TRACKABLE_FLAG	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N8	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N8	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N9	MTL_SYSTEM_ITEMS_B
BUYER_ID	NONUNIQUE	MTL_SYSTEM_ITEMS_B_N9	MTL_SYSTEM_ITEMS_B
INVENTORY_ITEM_ID	UNIQUE	MTL_SYSTEM_ITEMS_B_U1	MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID	UNIQUE	MTL_SYSTEM_ITEMS_B_U1	MTL_SYSTEM_ITEMS_B
TRANSACTION_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N1	MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N2	MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_DATE	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N2	MTL_TRANSACTION_LOT_NUMBERS
LOT_NUMBER	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N3	MTL_TRANSACTION_LOT_NUMBERS
INVENTORY_ITEM_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N3	MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N3	MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_SOURCE_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N4	MTL_TRANSACTION_LOT_NUMBERS
PRODUCT_CODE	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N5	MTL_TRANSACTION_LOT_NUMBERS
PRODUCT_TRANSACTION_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N5	MTL_TRANSACTION_LOT_NUMBERS
SERIAL_TRANSACTION_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N7	MTL_TRANSACTION_LOT_NUMBERS
TRANSACTION_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N7	MTL_TRANSACTION_LOT_NUMBERS
ORGANIZATION_ID	NONUNIQUE	MTL_TRANSACTION_LOT_NUMBERS_N7	MTL_TRANSACTION_LOT_NUMBERS

Forum Stats

Howdy, Stranger!

Quick Links

Categories

Optimalize SQL Query Oracle R12

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

Optimalize SQL Query Oracle R12

Answers