Re: Oracle - Undo questions
Date: Fri, 18 Nov 2016 14:48:10 +0000
Message-ID: <LOXP123MB125502BCFD879E565B72B4B8A5B00_at_LOXP123MB1255.GBRP123.PROD.OUTLOOK.COM>
"select statement remote" is good (though it means you'll have to optimise at the remote site if you want the insert to go faster).
No comment about the undo segment name, but if you want to short-circuit the time it takes to check the stats you could always log on while the insert is executing and change my suggested query from v$mystat to v$sesstat where sid = {inserting sid}; do that a couple of time 30 minutes apart and do the arithmetic to calculate diffs by hand.
Regards
Jonathan Lewis
From: Sanjay Mishra <smishra_97_at_yahoo.com> Sent: 18 November 2016 14:34:29
To: Jonathan Lewis; Oracle-L Freelists
Subject: Re: Oracle - Undo questions
Jonthan
Thanks for the update and here are the details
Database A where Data is insertd is 10.2.0.4 Database B from where data is read using Dblink from A--> B is 11.2.0.3 and 3-Node RAC but dblink is pointed using single instance tns setting
Yes the Target Table on database A which get data using INSERT is having 3 indexes and is not empty while loading the data. Loading is done at least once and on-demand I ran the execution Plan and it shows simple "SELECT STATEMENT REMOTE"
target Table has no Date or LOB column as well as there is no Date/Pseducolumn been used in the Select for Loading which contains some analytical function like OVER PARTITION. It used to work earlier but the only change was data been doubled for insert. I had also tripled the UNDO space and even increased the Retention to 5 hr and monitoring Database B side shows UNDO was anyway close to 50% usage.
Another strange thing is that User reported error with UNDO 10 segment as _SYSSMU10_121212121$ segment and while checking Database B or Database A, the UNDO segment 10 name is totally different. Not sure if user is giving correct detail
I will work with User to exectucute and monitor the stats as per email and will update.
thanks
Sanjay
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> Sent: 18 November 2016 02:53:11
To: Hemant-K.Chitale_at_sc.com<mailto:Hemant-K.Chitale_at_sc.com>; Oracle-L Freelists Subject: Re: Oracle - Undo questions
Hemant
In the alert log ? Did you check Database A as well ?
==> Yes checked both Database A and Database B and none reported ORA-0155 error and only reported in app logs
Does the operation join tables from A with the tables from B ?
==> No in Select statement it is all Database B tables but with various subquery
Does the target table have an ON INSERT trigger that does some lookup/validation as well ?
==> No Trigger on target table
Is there a Materialized View that joins the target table with some other table, that needs to be refreshed ?
==> No
Few question
1. Undo Segment_name for RAC vs non-RAC
==> Is Undo for non-RAC are like _SYSSMU10$ and RAC are like _SYSSMU10_121212121$ ?
2. Application is showing _SYSSMU10_121212121$ everytime it failed but database RAC is not showing this name for Rollback Segment 10. 3. Increased the Undo for all RAC instance by doubling it and undo retention also to 10hr in sec and monitoring was not showing that undo usage goes more than even a Gig where Undo is 30G for all instance. 4. Run Oracle Healthcheck as per Doc 1579035.1 and it was not showing Undo usage to anywhere close to actual size
SQL run for 1-2 hr and then give errors ORA-01555 and now it is frequently coming on each run.
thanks for any suggestion to be looked into.
TIA
Sanjay
On Thursday, November 17, 2016 8:44 PM, "Chitale, Hemant K" <Hemant-K.Chitale_at_sc.com<mailto:Hemant-K.Chitale_at_sc.com>> wrote:
>> There was no error reported in Database B In the alert log ? Did you check Database A as well ? Does the operation join tables from A with the tables from B ?
Does the target table have an ON INSERT trigger that does some lookup/validation as well ? Is there a Materialized View that joins the target table with some other table, that needs to be refreshed ?
Hemant K Chitale
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Sanjay Mishra Sent: Friday, November 18, 2016 12:51 AM To: mark.powell2_at_hpe.com<mailto:mark.powell2_at_hpe.com>; Oracle-L Freelists Subject: Re: Oracle - Undo questions
Mark
Sorry my mistake and error was ORA-01555 as that is why mentioned to get help on UNDO details. There was no error reported in Database B for this error but App team running the query reported the error. So that is why looking to see if this has to increase the size on UNDO in Database B or Database A.
Database A is the place where data is inserted Database B is the place from where query initiated from Database A using Database link is reading the data.
Rgds
Sanjay
On Thursday, November 17, 2016 11:38 AM, "Powell, Mark" <mark.powell2_at_hpe.com<mailto:mark.powell2_at_hpe.com><mailto:mark.powell2_at_hpe.com<mailto:mark.powell2_at_hpe.com>>> wrote:
Sanjay, I would expect the full error stack to identify where the error occurred but if you do not have access to the actual error message display then check the alert log for both databases. There should be an entry for the ORA-01555 error.
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>>> on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org><mailto:dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>>>
Sent: Thursday, November 17, 2016 11:25:53 AM
To: Oracle-L Freelists
Subject: Oracle - Undo questions
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
This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 18 2016 - 15:48:10 CET