Re: Oracle - Undo questions

If the SQL is fine, and you already have a reasonable amount of UNDO, you may want to consider breaking the work into batches so that you'll have less active UNDO at any given time. If you want to monitor UNDO usage here are a couple queries I use.

--Curent undo extent usage:
select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;

--Queries by undo usage:
SELECT a.sid, a.username, b.used_urec, b.used_ublk FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr ORDER BY b.used_ublk DESC;

Cheers,
Justin

On Thu, Nov 17, 2016 at 10:56 AM, Sanjay Mishra <dmarc-noreply_at_freelists.org
> wrote:

> Lothar
>
> 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.
>
> Rgds
> Sanjay
>
>
> On Thursday, November 17, 2016 11:45 AM, Lothar Flatz <l.flatz_at_bluewin.ch>
> wrote:
>
>
> Hi Sanjay,
>
> the error number was probably 1555.
> 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?
>
> 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
>
> Sanjay
>
>
>
> --
>
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l