Comments below
Regards
HJR
--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------
"Rob Edgar" <robedgar_at_hkstar.com> wrote in message
news:8qh5vk$i7e1_at_imsp212.netvigator.com...
> Well I sized the block buffers to half available mem to get as much of the
> DB cached in memory as possible then after that split half of remainder to
> share pool and the rest for the "os"..... thought it was better to cache
as
> much data as possible
>
I keep seeing messages here from people trying to do this. It's really not
a very good idea, and isn't the way Oracle is supposed to work. Unless
you're using the new 8i feature of keep buffer pools, it's not going to
achieve a whole lot at the end of the day anyway. One large tablescan, and
the whole lot will be wiped... A larger Shared Pool will make a much
greater improvement (though you can make it too big).
Take a trip to www.ixora.com.au -Steve Adams has some excellent sizing
advice there.
>
> Anyway's I created the temp space, had a few prb with it not "finding" the
> datafile during the create command and in the end I reused the existing
temp
> datafile but under the new temporary tablespace and using local
management.
> That seems OK (except its "dissapeared" as far as my sql tools SQL
Navigator
> and DBArtisan are concerned but thats not a really big deal).
>
I'd be concerned about that (though I don't know these tools myself).
Tablespaces shouldn't just disappear, whatever type they are (because your
data tablespaces should probably also end up as locally managed, too).
Not entirely sure what you meant by 'it didn't find the datafile during the
create command'... you're doing that 'let's be mysterious about error
messages' trick on me again! There should be no finding going on. When you
create a tablespace, you are directing the system to create the relevant
data file (or tempfile in this case) in whatever path you specify. Provided
the right privileges have been specified for the directory you choose, there
shouldn't be any problems.
Since you go on to say that TEMP is 400Mb, and you think that's too big (it
may well not be), why not 1) drop the tablespace, 2) go out to the operating
system and delete the relevant datafile(s) then 4) create a new TEMP
tablespace, and report any error messages you get back here. It should be a
simple case of 'pause...pause....pause...Statement Processed'.
> As far as the "eof" prb, I created a new table with the old contents and
> the insert into this is now OK, I suspect the problem is due to
> fragmentation in the datafile , as that datafile is littered with a lot of
> staging tables created by one developer, so I have created a new
tablesapce
> just fro hime and getting him to move all his data there, after which I
will
> delete the old files and coalesece the freespace.... I would call getting
an
> "eof" a bug but I guess Oracle wont, I mean whatever the problem really is
> it should give a better message or react in a more sensible way than this,
> not only that but all the log and trace files are of no, help the server
> records nothing when I get an "eof" so I am left without a clue as to what
> the problem might be......
>
Well, end of file messages usually indicate that the SGA has keeled over.
On my training classes, which runs on Unix, I always demonstrate an e-o-f
message is returned when you multiplex your control files, but don't set the
file permissions properly. But this last cause would mean you would always
get eof messages, and you indicate that it is an intermittent thing.
Have you ever received this message in one session, and been able to
successfully connect immediately in another? Would be interested in seeing
the error messages (!) in the second session if you were to try to do this.
> Due to trying several thing to get out of th eptoblem I have ended up with
> some silly sizes for tablepsaces like RBS which is 300 mb but contains
only
> 24 x 4mb rb segments so I need to tidy them up, and the temporary
> tablesepace is 400mb which sounds way to big too me.
>
Don't forget the alter database datafile 'path/filename' resize Xm command
can be used to resize datafiles *downwards* as well, provided there's no
data encountered on the way down. Oracle will warn you if the newly-svelt
size you pick is too small for the existing data's comfort.
As for 400Mb being too big.... not necessarily. What's the size of your
biggest sort, and how many concurrent users are likely to do it? I've seen
a database with 4, 2Gb TEMP tablespaces. Lots of Users doing simultaneous
sorts... big TEMP tablespace trequired. 400Mb sounds actually quite modest.
Regards
HJR
[Snip]
Received on Sat Sep 23 2000 - 00:00:51 CDT
