Re: Oracle - Undo questions
Date: Thu, 17 Nov 2016 16:56:27 +0000 (UTC)
Message-ID: <1056464918.2519402.1479401787672_at_mail.yahoo.com>
Lothar
On Thursday, November 17, 2016 11:45 AM, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
Hi Sanjay,
the error number was probably 1555.
Regards
Lothar
On 17.11.2016 17:25, Sanjay Mishra (Redacted sender smishra_97 for DMARC) wrote:
I have a ver big Insert using APPENd hint running on Database A and it is selecting data from Database B using Dblink from multiple Tables with lots of joins/subqueries and inserting several million rows (around 5-10 Mil). It failed few time with ORA-0155 error. 1. Does that mean issue with UNDO on Database A or B? 2. Is there way to troubleshoot and get some sizing estimate for UNDO on Database A or Database B(Based on Point 1) that give some clear picture like quering v$undostat or so if the issue happened today and Undostat have anyway data for few days.
Thanks for the time
--
Yes I agree that query tuning and better system can improve the time to have issue avoided but as your other point regarding serialize operation as well as complexity of the query with several Database link from few schemas of same remote database and join is causing to run this from 3-4 hr and causing this occasionally failing. So want to plan Undo size and retention to avoid it. Undo was doubled but still sometime work and other time failed and so want to follow better approach to query and troubleshoot to make the final change.
RgdsSanjay
Actually 5-10 million rows is not a lot with today's hardware. It is likely that your query could run faster. This would also solve the undo issue. I think this is a better solution than checking the undo.
BTW: Are you aware that a dblink will serialize parallel processing across the link?
Sanjay
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 17 2016 - 17:56:27 CET