Re: Temp Tablespace Group, Adding Tempfile and Resumable Transaction Issue?
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: oratune <oratune@xxxxxxxxx>
- Date: Thu, 7 Jan 2010 03:48:12 +0800
Yeah, temp tablespace files are created as sparse files. If you use ls
-l*s*on Unix you'll see the real amount of OS blocks used by the file
as the
first column of the output.
Sparse files cause problems - one thing is that DBAs see from df that
there's lots of free space on a mountpoint - and place new datafiles there.
Now if a sparse file actually starts using its space, it may end up with out
of space and resulting IO errors.
Also, IIRC, sparse files disable direct IO and cause fallback to buffered IO
in some cases.
So, a good practice for creating temp files is to use "mkfile" command in OS
first to create the files which really reserve the disk space for themselves
and then "create temp tablespace tempfile 'xyz' REUSE"
--
Tanel Poder
http://blog.tanelpoder.com (just switched to new blog template!)
On Thu, Jan 7, 2010 at 3:23 AM, David Fitzjarrell <oratune@xxxxxxxxx> wrote:
> These are temp files (sparse files on Unix) which should allocate in far
> less than 28 seconds. I've added temp files before and the allocation took
> no time at all. That being said I do agree with your assessment of the file
> being announced before it was actually 'available'; I'm guessing but it
> appears the temp file was allocated and then immediately began to expand to
> the allocated size and while the expansion was in progress the file was
> locked and unavailable for use. The session attempted to acquire a write
> lock on the file which failed, creating a situation resumable transactions
> weren't designed to handle.
>
> That's my guess, anyway.
>
> David Fitzjarrell
>
>
>
> ------------------------------
> *From:* Mark W. Farnham <mwf@xxxxxxxx>
> *To:* kjped1313@xxxxxxxxx; oracle Freelists <oracle-l@xxxxxxxxxxxxx>
> *Sent:* Wed, January 6, 2010 1:41:08 PM
> *Subject:* RE: Temp Tablespace Group, Adding Tempfile and Resumable
> Transaction Issue?
>
>
>
> I haven't seen this before, but it looks suspicious that:
>
> Wed Jan 6 06:55:15 2010
> alter tablespace temp_sample2 add tempfile
> '/r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:55:43 2010
> Completed: alter tablespace temp_sample2 add tempfile
> '/r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
>
> took 28 seconds (seems reasonable), while
>
> Wed Jan 6 06:56:21 2010
> alter tablespace temp_sample3 add tempfile
> '/r07/oradata/martb/temp_sample3_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:56:21 2010
> Completed: alter tablespace temp_sample3 add tempfile
> '/r07/oradata/martb/temp_sample3_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:56:21 2010
> statement in resumable session 'User DM_OWNER(194), Session 771, Instance
> 1'
> was aborted
>
> all took place in the same second. While I can't prove it, it seems like a
> timing error of announcing the availability of the file before it's
> actually
> ready. (meaning a bug in Oracle.) In the meantime between now and figuring
> out whether this is really a bug and if Oracle can fix it for your release,
> I'm guessing the workaround to raise the maxsize of the last existing
> tablespace file of each file in the group enough to give breathing room
> while you add the other files would work. Of course you've probably got
> standard sizes that this would screw up, but you could fix that at your
> next
> maint. window with little hassle since it is temp. It would also probably
> work to just raise the maxsize if you have room on the device and add files
> later.
>
> Good luck. I'm all ears if anyone knows all about this. I'm also guessing
> this is not trivial to reproduce on demand.
>
> mwf
>
>
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Kellyn Pedersen
> Sent: Wednesday, January 06, 2010 12:00 PM
> To: oracle Freelists
> Subject: Temp Tablespace Group, Adding Tempfile and Resumable Transaction
> Issue?
>
> Hello Folks,
> I had a large adhoc parallel process running in one of my older marts. The
> database is set up with resumable transactions and this process' slaves
> went
> into timeout when it ran out of temp tablespace striped in my temp
> tablespace group.
>
> I added datafiles first to the temp_sample1, which it was complaining
> about,
> but knew that it was striped across and would need space in the others or I
> would have more parallel slaves going into timeout. On the third
> tablespace
> of the group, I added the file bolded below and then one of the processes
> aborted, sending the user this error:
>
> ERROR at line 1:
> ORA-12801: error signaled in parallel query server P024
> ORA-01157: cannot identify/lock data file 1042 - see DBWR trace file
> ORA-01110: data file 1042: '/r07/oradata/martb/temp_sample3_06.dbf'
>
> Anyone seen this before? Is there a known issue with
> parallel processes/resumable transactions and temp tablespace groups, (all,
> or some of these combined) that I'm unaware of and can't fine out there?
>
> The output from the alert log, the dbwr trace file seems nowhere to be
> found!:
>
> Wed Jan 6 06:54:16 2010
> alter tablespace temp_sample1 add tempfile
> '/r07/oradata/martb/temp_sample1_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:54:17 2010
> Completed: alter tablespace temp_sample1 add tempfile
> '/r07/oradata/martb/temp_sample1_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:54:18 2010
> statement in resumable session 'User DM_OWNER(194), Session 693, Instance
> 1'
> was resumed
> Wed Jan 6 06:54:18 2010
> statement in resumable session 'User DM_OWNER(194), Session 572, Instance
> 1'
> was resumed
> Wed Jan 6 06:54:18 2010
> statement in resumable session 'User DM_OWNER(194), Session 704, Instance
> 1'
> was resumed
> Wed Jan 6 06:54:18 2010
> statement in resumable session 'User DM_OWNER(194), Session 726, Instance
> 1'
> was resumed
> Wed Jan 6 06:55:15 2010
> alter tablespace temp_sample2 add tempfile
> 'r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:55:15 2010
> alter tablespace temp_sample2 add tempfile
> '/r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:55:43 2010
> Completed: alter tablespace temp_sample2 add tempfile
> '/r07/oradata/martb/temp_sample2_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:56:21 2010
> alter tablespace temp_sample3 add tempfile
> '/r07/oradata/martb/temp_sample3_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:56:21 2010
> Completed: alter tablespace temp_sample3 add tempfile
> '/r07/oradata/martb/temp_sample3_06.dbf' size 10000M autoextend on next 5M
> maxsize 51200M
> Wed Jan 6 06:56:21 2010
> statement in resumable session 'User DM_OWNER(194), Session 771, Instance
> 1'
> was aborted
> Kellyn Pedersen
> Multi-Platform DBA
> I-Behavior Inc.
> http://www.linkedin.com/in/kellynpedersen
>
> "Go away before I replace you with a very small and efficient shell
> script..."
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
--
Tanel Poder
http://blog.tanelpoder.com
Other related posts:
