Incorrect info in my question -
Khalid, July 04, 2024 - 12:49 pm UTC
Thanks for taking my question.
My apologies I need to make some corrections to my original question, which 1 - I will do in the next post soon as I'm not logged in right now to my other env.
2 - The data size wasn't 10 Million, I copied the num_rows for the wrong Insert statement. The actual record size was 653,nnn,nnn (I don't remember the digits
3 - By the way the 10 Million statement completed in 31 minutes, but when I removed indexes it ran very quickly.
4 - Main Correction - On the 653 million Insert I ran the trace about 3 days after Toad had crashed but still the query showed up in v$session, and I ran DBMS_MONITOR.session_trace_enable(sid, serial#, binds, waits), and then I checked for the trace file location from v$process for that SID, and I noticed that the date of the trace file said June 28, although I ran the trace_enable on July 2. Which means that trace was already generated.
Please bear with me, and would you allow me to restate the problem when I have access to it.
My issue was resolved 653 Million fat records in exactly 4 hours, with bulk-collect, with no-indexes.
However, I'm more interested in the Toad crash and the trace file messages of June 28, and I will come back to it soon as we have the 4th holiday.
Thanks so much.
July 09, 2024 - 12:26 pm UTC
Let us know if/when you have more details
Feedback about same question
A reader, July 17, 2024 - 8:26 pm UTC
Hi Connor: My apologies for the delay. I'm trying to figure out what was the statement that could have crashed Toad. and generated a ORA-30036 DIAGNOSTIC.
You stated in your response " undo_retention has no bearing for transactions and also about recommendation to extend UNDO by 3.7G ..."
however, I got that from the trace file, please see below.
Can you please focus on what could have caused the trace file:
Thanks.
************* ABOUT UNDO_RETENTION ***********
*** 2024-06-28T07:56:17.761081-04:00
Problem: Undo tablespace cannot support required undo retention
Recommendation: Size undo tablespace to 3702 MB
*********** END ABOUT UNDO_RETENTION *********
See also this:
************************************************************
ORA-30036 DIAGNOSTIC
This diagnostic information is dumped to trace file at
most once every 24 hours, it does not indicate any error.
************************************************************
Further it said in the trace file:
ORA-30036 happens when trying to extend undo segment _SYSSMU28_619299446$ (usn=28) by 128 blocks
Reason: Race with other extends
Current undo tablespace UNDOTBS1 (tsn=2)
undo tablespace current size 37743220 blks, maxsize 37748718 blks, extensiable
Undo Retention (reactive):128192, Max Query Length:140546
Parameter Undo Retention:28800, Tuned Undo Retention:128192, High threshold Undo Retention:31536000 autotune:1
Retention Guarantee FALSE
Current Time is 1719575776
August 06, 2024 - 2:10 am UTC
I'm completely lost here.
Please ask a fresh question with the pertinent details
653 million Insert
Rajeshwaran, Jeyabal, August 06, 2024 - 10:11 am UTC
to process 653 million, few things to consider (hope you are loading this through SET based processing )
1) set your target table to nologging
2) set all your indexes to unusable state
3) disable all constraints.
4) enable parallel dml with with DOP 4 or 8
5) then execute the insert statment.
6) whiile that was progressing, monitor its performance from real-time SQL monitor tool.
7) once completed, rebuild indexes and enable constraints.