OraFAQ Forum: Performance Tuning » Report Taking So Much Of Time and goes into ORA-01555

Home » RDBMS Server » Performance Tuning » Report Taking So Much Of Time and goes into ORA-01555 (Oracle Apps 11.5.10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Report Taking So Much Of Time and goes into ORA-01555 [message #396889]

Wed, 08 April 2009 05:35

sumitgarg
Messages: 3
Registered: April 2009
Location: Ahmedabad

Junior Member

Hi All,

I m running a report in Oracle Apps and it goes in to error with ora-01555 error.The report contains insert and update statements as follows:

INSERT INTO HHLI.HHLI_RG1
(INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
MNF_QTY, --r
CT3, --i
EXPORT , --rv
EXCISE, --iv
ORGANIZATION_ID
)
Select
inventory_item_id,
subinventory_code,
sum(decode(sign(mmtt.primary_quantity),1,mmtt.primary_quantity,0)) m_qty,
sum(decode(sign(mmtt.primary_quantity),-1,mmtt.primary_quantity,0)) ct3,
sum(decode(sign(mmtt.primary_quantity),1,nvl(mmtt.primary_quantity,0)*nvl(mmtt.actual_cost,nvl(mmtt.transaction_cost,0)),0)
) rcpts_value,
sum(decode(sign(mmtt.primary_quantity),-1,nvl(mmtt.primary_quantity,0)*nvl(mmtt.actual_cost,nvl(mmtt.transaction_cost,0)),0)
) isss_value ,
mmtt.ORGANIZATION_ID
From Mtl_Material_Transactions MMTT
Where MMTT.Organization_Id = :P_ORG_ID
And MMTT.INVENTORY_ITEM_ID = Nvl(:P_Item_code,MMTT.INVENTORY_ITEM_ID)
and Trunc(MMTT.Transaction_date) >= :P_From_Date
and Trunc(MMTT.Transaction_date) <= :P_To_Date
and MMTT.Subinventory_Code = Nvl(:P_Subinv,MMTT.Subinventory_Code)
group by mmtt.subinventory_code, mmtt.ORGANIZATION_ID , mmtt.inventory_item_id;

INSERT INTO HHLI.HHLI_RG1
(INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
op_qty,
ORGANIZATION_ID
)
Select inventory_item_id,
subinventory_code,
sum(primary_quantity),
mmtt.ORGANIZATION_ID
From Mtl_Material_Transactions MMTT
Where organization_Id = :P_ORG_ID
And inventory_item_id = Nvl(:P_Item_code,inventory_item_id)
And Trunc(MMTT.Transaction_date) < :P_from_Date
And MMTT.Subinventory_Code = Nvl(:P_Subinv,MMTT.Subinventory_Code)
group by inventory_item_id,
subinventory_code,
mmtt.ORGANIZATION_ID;

UPDATE /*+ DRIVING_SITE(mtl_material_transactions) */ HHLI.hhli_rg1 m
SET wip = (
SELECT new_cost
FROM mtl_material_transactions k
WHERE k.transaction_id = (SELECT MAX(TRANSACTION_ID)
FROM MTL_MATERIAL_TRANSACTIONS L
WHERE L.INVENTORY_ITEM_ID = k.INVENTORY_ITEM_ID
AND L.ORGANIZATION_ID = k.ORGANIZATION_ID
AND TRUNC(L.TRANSACTION_DATE) < :P_from_DATE
)
AND k.inventory_item_id = m.inventory_item_id
AND k.organization_id = m.organization_id
AND trunc(k.transaction_date) < :P_from_Date
);

UPDATE /*+ DRIVING_SITE(mtl_material_transactions) */HHLI.hhli_rg1 m
SET OUT_SRC = (
SELECT new_cost
FROM mtl_material_transactions k
WHERE k.transaction_id = (SELECT MAX(TRANSACTION_ID)
FROM MTL_MATERIAL_TRANSACTIONS L
WHERE L.INVENTORY_ITEM_ID = k.INVENTORY_ITEM_ID
AND L.ORGANIZATION_ID = k.ORGANIZATION_ID
AND TRUNC(L.TRANSACTION_DATE) <= :P_to_DATE
)
AND k.inventory_item_id = m.inventory_item_id
AND k.organization_id = m.organization_id
AND trunc(k.transaction_date) <= :P_to_Date
);
exception
when no_data_found then
null;
when others then
null;
end;

Report message to a moderator

Re: Report Taking So Much Of Time and goes into ORA-01555 [message #396921 is a reply to message #396889]

Wed, 08 April 2009 07:05

sumitgarg
Messages: 3
Registered: April 2009
Location: Ahmedabad

Junior Member

Dear All,
Please Update On It.

Regards
Sumit

Report message to a moderator

Re: Report Taking So Much Of Time and goes into ORA-01555 [message #396934 is a reply to message #396889]

Wed, 08 April 2009 07:26

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Suggest you read these:
http://www.orafaq.com/forum/t/84315/129190/
http://www.orafaq.com/forum/t/88153/0/

With the information you have provided there is nothing we can suggest - other than that you should remove the "exception when others then null" as it is a really bad idea.

You need to work out which statements are running slowly then provide us with sufficient information to enable us to help you.
An explain plan would be a good start.

Report message to a moderator

Re: Report Taking So Much Of Time and goes into ORA-01555 [message #397098 is a reply to message #396889]

Wed, 08 April 2009 22:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

visit http://asktom.oracle.com & do keyword search on ORA-01555

A long running SELECT is victim of ORA-01555 & not the cause.
The root cause of the problem is a session which is doing DML against same table & doing frequent COMMIT.

Solution is 1 of 2
1) Do not do frequent COMMIT
2) increase size of UNDO

Report message to a moderator

Re: Report Taking So Much Of Time and goes into ORA-01555 [message #397100 is a reply to message #397098]

Wed, 08 April 2009 23:13

sumitgarg
Messages: 3
Registered: April 2009
Location: Ahmedabad

Junior Member

Hi All,

Thanks for your reply,
i already increase the size of undo to 14400,previously it was 7200.
please let me know the impact of increasing the size of undo.
also help me to fasten the report.

Regards
Sumit

Report message to a moderator

Previous Topic:	how to optimize sql for query big table? (merged 3)
Next Topic:	PCTFREE and PCTUSED

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 18:22:09 CST 2024