Re: DBMS_STATS and CBO

Hm, it's strange.
When you create a temp datafile there is some data written to it despite you actually store anything there or not (headers or whatever control structs). So, the tempfile is being used from beginning. The mechanism of creating sparse files is quite simple: just forward seek command is issued on the file, over the current end of file and then one byte (maybe block in Oracle) is written at the end position. That way, if OS & filesystem support sparse files, they don't actually allocate space for the empty part. Space is allocated only when something is written to these parts (when reading empty space in sparse file, nulls are retrieved).

To avoid any sparse file issues in future, I recommend you to copy the tempfiles to another name using cp, then rename back, that way the file isn't sparse anymore (well, until the tempfile autoextends, then file gets sparse again). And compare file sizes periodically with ls -l and ls -ls.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, September 18, 2003 8:04 PM

>
> At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since
it
> remained unused, it didn't grab any significant disk space. It's
possible,
> that at some time, the free space in the file system fell below 5 Gig.
When
> I tried to create the index, the error occurred immediately. So, the file
> never tried to grow at all ... ever. The big question is why not? The OS
> recorded no I/O errors at the time, and we have had no I/O errors on any
of
> the other stuff using the file system.
>
> What I am wondering is if there is some IMPLIED disk address assignment
that
> occurs when the tempfile (a "sparse" file, I assume) is created, and can
> this assignment be overwritten or screwed up in some way if the tempfile
> remains unused so that when the tempfile finally wants its space, the
> filesystem has changed from the time the file was created, and now
tempfile
> can't grab anything. Or do we have some other kind of weirdness going on
> here?
>
> Is this making sense?
>
> > -----Original Message-----
> > From: Tanel Poder [mailto:tanel.poder.003_at_mail.ee]
> > Sent: Thursday, September 18, 2003 11:35 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: DBMS_STATS and CBO
> >
> >
> > Btw, how much free space do you have in OS where your tempfiles are?
> >
> > Tanel.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, September 18, 2003 6:49 PM
> >
> >
> > > (Resending)
> > >
> > > Any comments on the following??
> > >
> > > When creating index, got
> > > ORA-00603: ORACLE server session terminated by fatal error
> > >
> > > apparently caused by
> > >
> > > ksedmp: internal or fatal error
> > > ORA-01114: IO error writing block to file 121 (block # 149)
> > > ORA-27063: skgfospo: number of bytes read/written is incorrect
> > > Additional information: 16384
> > > Additional information: 49152
> > >
> > > which I determined was caused by attempted write to temp
> > tablespace using
> > a
> > > tempfile. The tablespace was dropped and recreated, and
> > all was well
> > again.
> > >
> > > What I think MIGHT have happened is the tablespace created
> > weeks ago, but
> > > not used. So it didn't grab any actual storage. In the
> > mean time, some
> > of
> > > the storage might have been used by something else, but storage was
> > > released. Now tempfile goes to grab some space, but
> > filesystem is all
> > > screwed up about what storage the tempfile should be grabbing.
> > >
> > > Does this sound plausible?
> > > Is there something else going on here?
> > > Is this another one of those spiffy cool things in Oracle
> > that are just
> > > something else to go wrong? There seems to be no way of
> > creating a LMT
> > > tempfile so that it pre-grabs the disk space.
> > >
> > > Note that the index create blew up immediately, so the
> > original tempfile
> > > never grabbed any space. So, I think I can say that is
> > definitely didn't
> > > run out of space; but maybe somebody walked across the
> > space the tempfile
> > > thought it was going to get in the future.
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Stephen Lee
> > > INET: Stephen.Lee_at_DTAG.Com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web
> > hosting services
> > >
> > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Tanel Poder
> > INET: tanel.poder.003_at_mail.ee
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephen Lee
> INET: Stephen.Lee_at_DTAG.Com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).