Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBMS_STATS and CBO

Re: DBMS_STATS and CBO

From: Tim Gorman <tim_at_sagelogix.com>
Date: Fri, 19 Sep 2003 21:29:43 -0800
Message-ID: <F001.005D087C.20030919212943@fatcity.com>


If you want to get the same effect entirely from the "SQL>" prompt (i.e. without having to issue operating-system copy commands), you can first create a tablespace (named DUMMY_TEMP?) with datafiles of the name and size you want for your TEMP tablespace. Then drop the DUMMY_TEMP tablespace and create the TEMP tablespace over the same files, now as tempfiles. Donšt forget the REUSE clause...

on 9/18/03 11:34 AM, Tanel Poder at tanel.poder.003_at_mail.ee wrote:

> 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: Tim Gorman
  INET: tim_at_sagelogix.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).
Received on Sat Sep 20 2003 - 00:29:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US