asked on

how to tune the query

SELECT mp.master_organization_id org_id,
mta.organization_id,
mp.organization_code,
mmt.transaction_id,
mmt.transaction_date,
mmt.acct_period_id,
oap.period_name,
mmt.inventory_item_id,
msi.segment1 item_number,
msi.description item_description,
gcc1.segment4 sales_account_product_line,
gcc1.segment5 sales_account_sub_product,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
gl_account,
gcc.segment1 business_group,
gcc.segment2 natural_account,
gcc.segment3 department,
gcc.segment4 product_line,
gcc.segment5 sub_product,
gcc.segment6 extra_segment,
mmt.subinventory_code subinventory,
mmt.locator_id,
mil.segment1 LOCATOR,
mmt.operation_seq_num,
mta.accounting_line_type,
ml1.meaning accounting_line_type_meaning,
mmt.transaction_type_id,
mtt.transaction_type_name,
mmt.transaction_action_id,
ml2.meaning transaction_action,
mmt.transaction_source_type_id,
mtst.transaction_source_type_name,
mmt.transaction_source_id,
ts.transaction_source_name,
mmt.transaction_quantity,
mmt.transaction_uom,
mmt.primary_quantity,
mmt.transaction_reference,
mtr.reason_name,
DECODE (mta.gl_batch_id, -1, '', mta.gl_batch_id) gl_batch_id,
ROUND (
DECODE (
mmt.transaction_type_id,
24, NULL,
80, NULL,
DECODE (
mta.primary_quantity,
0, NULL,
NULL, NULL,
ABS (
mta.base_transaction_value / mta.primary_quantity))),
4)
unit_cost,
mta.base_transaction_value,
mmt.currency_code,
mmt.currency_conversion_date,
mmt.currency_conversion_type,
mmt.currency_conversion_rate,
cic.material_cost,
mmt.transaction_cost,
DECODE (
ts.transaction_source_type_id,
1, (SELECT pv.vendor_name
FROM po_headers_all ph, po_vendors pv
WHERE ph.vendor_id = pv.vendor_id
AND ph.PO_HEADER_ID = ts.transaction_source_id),
NULL)
po_vendor_name
FROM mtl_material_transactions mmt,
mtl_parameters mp,
mtl_transaction_accounts mta,
org_acct_periods oap,
mtl_system_items msi,
gl_code_combinations gcc,
gl_code_combinations gcc1,
mtl_item_locations mil,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
mtl_transaction_reasons mtr,
mfg_lookups ml1,
mfg_lookups ml2,
bom.cst_item_costs cic,
(SELECT 1 transaction_source_type_id,
ph.po_header_id transaction_source_id,
ph.segment1 transaction_source_name
FROM po_headers ph
UNION
SELECT 5 transaction_source_type_id,
we.wip_entity_id transaction_source_id,
we.wip_entity_name transaction_source_name
FROM wip_entities we
UNION
SELECT 2 transaction_source_type_id,
mso.sales_order_id transaction_source_id,
mso.segment1 || '.' || mso.segment2 || '.' || mso.segment3
transaction_source_name
FROM mtl_sales_orders mso
UNION
SELECT 12 transaction_source_type_id,
mso.sales_order_id transaction_source_id,
mso.segment1 || '.' || mso.segment2 || '.' || mso.segment3
transaction_source_name
FROM mtl_sales_orders mso
UNION
SELECT 9 transaction_source_type_id,
mcch.cycle_count_header_id transaction_source_id,
mcch.cycle_count_header_name transaction_source_name
FROM mtl_cycle_count_headers mcch
UNION
SELECT 6 transaction_source_type_id,
mgd.disposition_id transaction_source_id,
mgd.segment1 transaction_source_name
FROM mtl_generic_dispositions mgd) ts
WHERE mta.organization_id = mp.organization_id
AND mmt.transaction_id = mta.transaction_id
AND mmt.acct_period_id = oap.acct_period_id
AND mmt.organization_id = oap.organization_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND msi.sales_account = gcc1.code_combination_id
AND mta.reference_account = gcc.code_combination_id
AND mmt.organization_id = mil.organization_id(+)
AND mmt.subinventory_code = mil.subinventory_code(+)
AND mmt.locator_id = mil.inventory_location_id(+)
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.reason_id = mtr.reason_id(+)
AND mta.accounting_line_type = ml1.lookup_code
AND ml1.lookup_type = 'ACCOUNTING_LINE_TYPE'
AND mmt.transaction_action_id = ml2.lookup_code
AND ml2.lookup_type = 'MTL_TRANSACTION_ACTION'
AND mmt.transaction_source_type_id =
ts.transaction_source_type_id(+)
AND mmt.transaction_source_id = ts.transaction_source_id(+)
AND mp.master_organization_id = fnd_profile.VALUE ('ORG_ID')
AND msi.inventory_item_id = cic.inventory_item_id(+)
AND msi.organization_id = cic.organization_id(+)
AND cic.cost_type_id(+) = 1

pardeshirahul

ASKER

Plan
SELECT STATEMENT ALL_ROWSCost: 821,496 Bytes: 12,040,452 Cardinality: 19,836
      7 NESTED LOOPS Cost: 4 Bytes: 52 Cardinality: 1
            5 NESTED LOOPS Cost: 3 Bytes: 45 Cardinality: 1
                  2 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 11 Cardinality: 1
                        1 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_HEADERS_U1 Cost: 1 Cardinality: 1
                  4 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 34 Cardinality: 1
                        3 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 0 Cardinality: 1
            6 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1 Bytes: 7 Cardinality: 1
      55 HASH JOIN Cost: 821,496 Bytes: 12,040,452 Cardinality: 19,836
            8 TABLE ACCESS FULL TABLE GL.GL_CODE_COMBINATIONS Cost: 279 Bytes: 740,520 Cardinality: 61,710
            54 HASH JOIN RIGHT OUTER Cost: 821,216 Bytes: 11,802,420 Cardinality: 19,836
                  10 TABLE ACCESS BY INDEX ROWID TABLE BOM.CST_ITEM_COSTS Cost: 4,703 Bytes: 597,168 Cardinality: 33,176
                        9 INDEX SKIP SCAN INDEX BOM.CST_ITEM_COSTS_N2 Cost: 2,930 Cardinality: 33,176
                  53 NESTED LOOPS Cost: 816,513 Bytes: 11,445,372 Cardinality: 19,836
                        51 NESTED LOOPS Cost: 816,513 Bytes: 11,445,372 Cardinality: 19,836
                              49 HASH JOIN RIGHT OUTER Cost: 776,817 Bytes: 10,255,212 Cardinality: 19,836
                                    11 TABLE ACCESS FULL TABLE INV.MTL_TRANSACTION_REASONS Cost: 4 Bytes: 3,382 Cardinality: 178
                                    48 HASH JOIN OUTER Cost: 776,813 Bytes: 9,878,328 Cardinality: 19,836
                                          37 HASH JOIN RIGHT OUTER Cost: 708,747 Bytes: 7,140,960 Cardinality: 19,836
                                                12 TABLE ACCESS FULL TABLE INV.MTL_ITEM_LOCATIONS Cost: 839 Bytes: 3,411,800 Cardinality: 136,472
                                                36 HASH JOIN Cost: 707,342 Bytes: 6,645,060 Cardinality: 19,836
                                                      13 TABLE ACCESS FULL TABLE INV.ORG_ACCT_PERIODS Cost: 10 Bytes: 23,776 Cardinality: 1,486
                                                      35 HASH JOIN Cost: 707,332 Bytes: 6,327,684 Cardinality: 19,836
                                                            14 TABLE ACCESS FULL TABLE INV.MTL_TRANSACTION_TYPES Cost: 4 Bytes: 2,626 Cardinality: 101
                                                            34 HASH JOIN Cost: 707,328 Bytes: 5,811,948 Cardinality: 19,836
                                                                  15 TABLE ACCESS FULL TABLE INV.MTL_TXN_SOURCE_TYPES Cost: 3 Bytes: 306 Cardinality: 17
                                                                  33 HASH JOIN Cost: 707,324 Bytes: 5,454,900 Cardinality: 19,836
                                                                        31 HASH JOIN Cost: 298,186 Bytes: 81,605,865 Cardinality: 436,395
                                                                              16 TABLE ACCESS FULL TABLE GL.GL_CODE_COMBINATIONS Cost: 279 Bytes: 1,727,880 Cardinality: 61,710
                                                                              30 NESTED LOOPS Cost: 294,255 Bytes: 69,386,805 Cardinality: 436,395
                                                                                    28 NESTED LOOPS Cost: 294,255 Bytes: 69,386,805 Cardinality: 4,921,772
                                                                                          26 MERGE JOIN CARTESIAN Cost: 9 Bytes: 128 Cardinality: 1
                                                                                                22 MERGE JOIN CARTESIAN Cost: 7 Bytes: 116 Cardinality: 1
                                                                                                      18 TABLE ACCESS BY INDEX ROWID TABLE APPLSYS.FND_LOOKUP_VALUES Cost: 4 Bytes: 58 Cardinality: 1
                                                                                                            17 INDEX RANGE SCAN INDEX (UNIQUE) APPLSYS.FND_LOOKUP_VALUES_U1 Cost: 3 Cardinality: 1
                                                                                                      21 BUFFER SORT Cost: 3 Bytes: 58 Cardinality: 1
                                                                                                            20 TABLE ACCESS BY INDEX ROWID TABLE APPLSYS.FND_LOOKUP_VALUES Cost: 3 Bytes: 58 Cardinality: 1
                                                                                                                  19 INDEX RANGE SCAN INDEX (UNIQUE) APPLSYS.FND_LOOKUP_VALUES_U1 Cost: 2 Cardinality: 1
                                                                                                25 BUFFER SORT Cost: 6 Bytes: 72 Cardinality: 6
                                                                                                      24 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 72 Cardinality: 6
                                                                                                            23 INDEX RANGE SCAN INDEX INV.MTL_PARAMETERS_N1 Cost: 0 Cardinality: 6
                                                                                          27 INDEX RANGE SCAN INDEX INV.MTL_TRANSACTION_ACCOUNTS_N6 Cost: 28,544 Cardinality: 4,921,772
                                                                                    29 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_TRANSACTION_ACCOUNTS Cost: 294,246 Bytes: 10,898,205 Cardinality: 351,555
                                                                        32 TABLE ACCESS FULL TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 296,857 Bytes: 2,007,875,584 Cardinality: 22,816,768
                                          47 VIEW BOLINF. Cost: 42,482 Bytes: 490,086,714 Cardinality: 3,551,353
                                                46 SORT UNIQUE Cost: 42,482 Bytes: 200,197,354 Cardinality: 3,551,353
                                                      45 UNION-ALL
                                                            39 FILTER
                                                                  38 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 4,365 Bytes: 4,331,712 Cardinality: 309,408
                                                            40 TABLE ACCESS FULL TABLE WIP.WIP_ENTITIES Cost: 8,148 Bytes: 27,963,280 Cardinality: 1,747,705
                                                            41 TABLE ACCESS FULL TABLE INV.MTL_SALES_ORDERS Cost: 3,235 Bytes: 36,054,800 Cardinality: 901,370
                                                            42 TABLE ACCESS FULL TABLE INV.MTL_SALES_ORDERS Cost: 3,235 Bytes: 36,054,800 Cardinality: 901,370
                                                            43 TABLE ACCESS FULL TABLE INV.MTL_CYCLE_COUNT_HEADERS Cost: 4 Bytes: 2,496 Cardinality: 104
                                                            44 TABLE ACCESS FULL TABLE INV.MTL_GENERIC_DISPOSITIONS Cost: 8 Bytes: 23,287 Cardinality: 803
                              50 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Cardinality: 1
                        52 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 60 Cardinality: 1

pardeshirahul

ASKER

any updates

arnold

Can't help not Oracle expert,
but you might start instead of dumping a ton of hay and asking how to improve the search for non-metalic needle, provide some detail on what your situation is and how frequently this query/data extract occurs.
And what issues you experience.

Can this data be broken into possible sacrificing resources on the server by creating a view/views that your modified query will then combine...
Are you querying the entire db, or can it be limited by a range?

johnsone

Taking a very quick scan of the plan, it would appear that some of what you are selecting from is already views. The classic sign of this is tables in the plan that aren't in the FROM clause.

This would appear to me that this is a mostly transactional system, just a guess based on some of the table names. If that is the case, then what I try to eliminate are HASH JOIN, full table scans and cartesian products.

There are a couple of cartesian products, but it looks like the cardinality is low enough that they aren't a problem.

One thing that stands out is the inline view you are creating. Since it doesn't appear that there could be duplicates, I would use UNION ALL instead of UNION. Also, is there any way that you could cut down on the volume of data in the inline view. It appears to be projecting 3,551,353 rows, which seems excessive when the projected result set is less than 20,000.

Make sure all your join conditions are indexed. They probably are and the hash joins are coming from the ALL_ROWS goal, but just be sure.

Also, make sure your statistics are up to date.

There are no hard rules to the best way a query should run. This would just be my personal starting point.

pardeshirahul

ASKER

the query is not running at all ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

johnsone

That isn't surprising. You have an awful lot of data that needs to be hashed for all the joins and views that need to be at least partially materialized and that takes up a lot of temp space. Increase your temp space.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pardeshirahul

ASKER

set for FIRST_ROWS still the same error ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

slightwv (䄆 Netminder)

I would probably start with this entry in the plan:
TABLE ACCESS FULL TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 296,857 Bytes: 2,007,875,584 Cardinality: 22,816,768

See if there are indexes on that table that you might be able to leverage and as suggested above: Make sure statistics are up to date.

pardeshirahul

ASKER

MTL_MATERIAL_TRANSACTIONS_N28      N      YES      1      XML_DOCUMENT_ID
MTL_MATERIAL_TRANSACTIONS_N3      N      YES      1      SUBINVENTORY_CODE, ORGANIZATION_ID, TRANSACTION_DATE
MTL_MATERIAL_TRANSACTIONS_N4      N      YES      1      VENDOR_LOT_NUMBER
MTL_MATERIAL_TRANSACTIONS_N5      N      YES      1      TRANSACTION_DATE, ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS_N6      N      YES      1      SHIPMENT_NUMBER
MTL_MATERIAL_TRANSACTIONS_N7      N      YES      1      ACCT_PERIOD_ID, ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS_N8      N      YES      1      TRANSACTION_SOURCE_TYPE_ID, ORGANIZATION_ID, TRANSACTION_DATE, TRANSACTION_SOURCE_NAME
MTL_MATERIAL_TRANSACTIONS_N9      N      YES      1      TRANSACTION_ACTION_ID, ORGANIZATION_ID, TRANSACTION_TYPE_ID, TRANSACTION_DATE
MTL_MATERIAL_TRANSACTIONS_U1      Y      YES      1      TRANSACTION_ID
MTL_MATERIAL_TRANSACTIONS_U2      Y      YES      1      TRANSACTION_ACTION_ID, TRANSACTION_TYPE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_ID
MTL_MATERIAL_TRANSACTIONS_UPDR      N      YES      1      TRX_SOURCE_LINE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_ID

johnsone

But does changing it to FIRST_ROWS change the plan? I would think it would change part of it.

As suggested, look for the high cardinality parts of the plan and see what you can do to change it. Indexes and statistics are your friends.

We don't have your system and your data, so we can really only suggest things.

pardeshirahul

ASKER

FIRST_ROWS change the cost of the plan

johnsone

If the cost changed, something else must have changed. Are you saying the cost changed and the 55+ steps involved are all still the same?

pardeshirahul

ASKER

Plan
SELECT STATEMENT FIRST_ROWSCost: 792,467 Bytes: 11,901,600 Cardinality: 19,836
      7 NESTED LOOPS Cost: 4 Bytes: 52 Cardinality: 1
            5 NESTED LOOPS Cost: 3 Bytes: 45 Cardinality: 1
                  2 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 11 Cardinality: 1
                        1 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_HEADERS_U1 Cost: 1 Cardinality: 1
                  4 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 34 Cardinality: 1
                        3 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 0 Cardinality: 1
            6 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1 Bytes: 7 Cardinality: 1
      54 HASH JOIN Cost: 792,467 Bytes: 11,901,600 Cardinality: 19,836
            8 TABLE ACCESS FULL TABLE GL.GL_CODE_COMBINATIONS Cost: 279 Bytes: 740,520 Cardinality: 61,710
            53 HASH JOIN RIGHT OUTER Cost: 792,187 Bytes: 11,663,568 Cardinality: 19,836
                  10 TABLE ACCESS BY INDEX ROWID TABLE BOM.CST_ITEM_COSTS Cost: 4,703 Bytes: 597,168 Cardinality: 33,176
                        9 INDEX SKIP SCAN INDEX BOM.CST_ITEM_COSTS_N2 Cost: 2,930 Cardinality: 33,176
                  52 NESTED LOOPS Cost: 787,484 Bytes: 11,306,520 Cardinality: 19,836
                        50 NESTED LOOPS Cost: 787,484 Bytes: 11,306,520 Cardinality: 19,836
                              48 HASH JOIN RIGHT OUTER Cost: 747,788 Bytes: 10,116,360 Cardinality: 19,836
                                    11 TABLE ACCESS FULL TABLE INV.MTL_TRANSACTION_REASONS Cost: 4 Bytes: 3,382 Cardinality: 178
                                    47 HASH JOIN OUTER Cost: 747,784 Bytes: 9,739,476 Cardinality: 19,836
                                          37 HASH JOIN RIGHT OUTER Cost: 708,747 Bytes: 7,140,960 Cardinality: 19,836
                                                12 TABLE ACCESS FULL TABLE INV.MTL_ITEM_LOCATIONS Cost: 839 Bytes: 3,411,800 Cardinality: 136,472
                                                36 HASH JOIN Cost: 707,342 Bytes: 6,645,060 Cardinality: 19,836
                                                      13 TABLE ACCESS FULL TABLE INV.ORG_ACCT_PERIODS Cost: 10 Bytes: 23,776 Cardinality: 1,486
                                                      35 HASH JOIN Cost: 707,332 Bytes: 6,327,684 Cardinality: 19,836
                                                            14 TABLE ACCESS FULL TABLE INV.MTL_TRANSACTION_TYPES Cost: 4 Bytes: 2,626 Cardinality: 101
                                                            34 HASH JOIN Cost: 707,328 Bytes: 5,811,948 Cardinality: 19,836
                                                                  15 TABLE ACCESS FULL TABLE INV.MTL_TXN_SOURCE_TYPES Cost: 3 Bytes: 306 Cardinality: 17
                                                                  33 HASH JOIN Cost: 707,324 Bytes: 5,454,900 Cardinality: 19,836
                                                                        31 HASH JOIN Cost: 298,186 Bytes: 81,605,865 Cardinality: 436,395
                                                                              16 TABLE ACCESS FULL TABLE GL.GL_CODE_COMBINATIONS Cost: 279 Bytes: 1,727,880 Cardinality: 61,710
                                                                              30 NESTED LOOPS Cost: 294,255 Bytes: 69,386,805 Cardinality: 436,395
                                                                                    28 NESTED LOOPS Cost: 294,255 Bytes: 69,386,805 Cardinality: 4,921,772
                                                                                          26 MERGE JOIN CARTESIAN Cost: 9 Bytes: 128 Cardinality: 1
                                                                                                22 MERGE JOIN CARTESIAN Cost: 7 Bytes: 116 Cardinality: 1
                                                                                                      18 TABLE ACCESS BY INDEX ROWID TABLE APPLSYS.FND_LOOKUP_VALUES Cost: 4 Bytes: 58 Cardinality: 1
                                                                                                            17 INDEX RANGE SCAN INDEX (UNIQUE) APPLSYS.FND_LOOKUP_VALUES_U1 Cost: 3 Cardinality: 1
                                                                                                      21 BUFFER SORT Cost: 3 Bytes: 58 Cardinality: 1
                                                                                                            20 TABLE ACCESS BY INDEX ROWID TABLE APPLSYS.FND_LOOKUP_VALUES Cost: 3 Bytes: 58 Cardinality: 1
                                                                                                                  19 INDEX RANGE SCAN INDEX (UNIQUE) APPLSYS.FND_LOOKUP_VALUES_U1 Cost: 2 Cardinality: 1
                                                                                                25 BUFFER SORT Cost: 6 Bytes: 72 Cardinality: 6
                                                                                                      24 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 72 Cardinality: 6
                                                                                                            23 INDEX RANGE SCAN INDEX INV.MTL_PARAMETERS_N1 Cost: 0 Cardinality: 6
                                                                                          27 INDEX RANGE SCAN INDEX INV.MTL_TRANSACTION_ACCOUNTS_N6 Cost: 28,544 Cardinality: 4,921,772
                                                                                    29 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_TRANSACTION_ACCOUNTS Cost: 294,246 Bytes: 10,898,205 Cardinality: 351,555
                                                                        32 TABLE ACCESS FULL TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 296,857 Bytes: 2,007,875,584 Cardinality: 22,816,768
                                          46 VIEW BOLINF. Cost: 14,630 Bytes: 465,227,243 Cardinality: 3,551,353
                                                45 UNION-ALL
                                                      39 FILTER
                                                            38 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 4,365 Bytes: 4,331,712 Cardinality: 309,408
                                                      40 TABLE ACCESS FULL TABLE WIP.WIP_ENTITIES Cost: 8,148 Bytes: 27,963,280 Cardinality: 1,747,705
                                                      41 TABLE ACCESS FULL TABLE INV.MTL_SALES_ORDERS Cost: 3,235 Bytes: 36,054,800 Cardinality: 901,370
                                                      42 TABLE ACCESS FULL TABLE INV.MTL_SALES_ORDERS Cost: 3,235 Bytes: 36,054,800 Cardinality: 901,370
                                                      43 TABLE ACCESS FULL TABLE INV.MTL_CYCLE_COUNT_HEADERS Cost: 4 Bytes: 2,496 Cardinality: 104
                                                      44 TABLE ACCESS FULL TABLE INV.MTL_GENERIC_DISPOSITIONS Cost: 8 Bytes: 23,287 Cardinality: 803
                              49 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Cardinality: 1
                        51 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 60 Cardinality: 1

slightwv (䄆 Netminder)

If you are waiting for some "magic" and us to rewrite your query, you may be disappointed.

There is no magic in tuning and given the size and complexity of the query, we don't have the time to understand your system with enough detail to actually tune the query.

Instead of tuning the whole query at once, I would start reducing the query into it's most basic form until you find the bottleneck. Then you are only dealing with a table or three and a few columns. Those queries are much easier to tune.

I've identified the table I would start with. I would probably remove ALL the table joins and all the columns that do not come from that table and see how that single select performs.

Then add in the joined tables one at a time. Eventually you will locate the problem area.

johnsone

So, FIRST_ROWS does change it, not enough to really help.

Your problems are going to be that there is no limiting clause in your where clause, you are essentially looking for all rows. And you are doing a lot of outer joins.

There probably isn't going to be much that you can do. You need to limit the number of rows, get rid of outer joins if they are not necessary. Also, looking into the views involved and see if you can get around the view by only taking the parts of the view that you are actually using.

Mark Geerlings

This is obviously an Oracle e-Business Suite system, In many of them the MTL_MATERIAL_TRANSACTIONS table can have a very large number of rows.

I agree with slightwv's latest suggestion. Start with a query of just that MTL_MATERIAL_TRANSACTIONS table. That table typically has 40 or more indexes. Make sure that your query allows Oracle to use an efficient index (or multiple indexes) on this table first, to get the total number of rows retrieved down to a manageable number.

Whenever you have multiple tables in a query and you get this error: "ORA-01652: unable to extend temp segment ..." that usually indicates a problem in the query. This is usually a missing join condition between two of the tables.

If you start with just this table, then add one table more table at a time, and make sure that you specify the join conditions correctly *AND* that you provide the join conditions that allow Oracle to use the indexes on the tables, it will be possible for you to get this query working.