Home » RDBMS Server » Performance Tuning » Report Taking So Much Of Time and goes into ORA-01555 (Oracle Apps 11.5.10.2)
icon4.gif  Report Taking So Much Of Time and goes into ORA-01555 [message #396889] Wed, 08 April 2009 05:35 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13925
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 Go to previous messageGo to next message
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jun 26 11:42:26 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.