SQL & PL/SQL

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

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

What sort condition?

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

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")

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.

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.

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

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 ).

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**_

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.

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

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.

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.

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: