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!
Are the HTTP functions Part of 8.0.24 version or manually need to be downloaded, compiled and built?
Any Suggestions?
Thanks
Raghu
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:
4 - access("GMD"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID" AND "GBH"."BATCH_ID"="GMD"."BATCH_ID" AND
"GMD"."ORGANIZATION\_ID"="GBH"."ORGANIZATION\_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
10 - filter("GBH"."ORGANIZATION_ID"=1509 AND "GBH"."BATCH_STATUS"=4)
12 - access("GBH"."BATCH_ID"="GMD"."BATCH_ID")
13 - filter("MMT"."TRANSACTION_SOURCE_ID" IS NOT NULL AND
14 - access("MMT"."TRX_SOURCE_LINE_ID"="GMD"."MATERIAL_DETAIL_ID")
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:
3706549 wrote:
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
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?
4 - access("GBH"."FORMULA_ID"="FMD"."FORMULA_ID")
5 - access("MSI"."INVENTORY_ITEM_ID"="FMD"."INVENTORY_ITEM_ID" AND
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?
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
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.
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.
SeánMacGC wrote:
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: