Re: ORATUNE...The TOOLS tablespace...?

In article <39d162a7.97180141_at_news.mindspring.com>,   buckatl_at_yahoo.com wrote:
> First, thanks to all of you for your comments. Second, I don't
> believe that any DBA where I live and work, Atlanta, Ga, would ever
> have to leave under less than desirable circumstances. A quick
> perusal of the Oracle jobs available here would generate more than
> 1,300 posted jobs for Oracle people, on one IT job website alone, with
> about 300 of those falling under the category of DBA. So in this
> town, I would imagine that if someone leaves, it's in the pursuit of
> that do-re-mi, and not because someone got their extents all in a wad.
> When the previous DBA left, I was 'assigned' the job of Oracle DBA,
> which I welcomed and encouraged, because I had reached my personal
> limit with writing AS/400 software. And it saved my company many
> thousands of dollars because they didn't have to hire a recruiter fill
> the position.
> So anyway, after seeing the results of creating a db with the scripts
> that were left for me as my only guide, I did change them so that the
> USER1 tablespace was set to minextents=1, and that saved many dozens
> of MB space. So having said that, I have a follow-up question for
> ORATUNE. Can you help me figure the desirable attributes for a TOOLS
> tablespace? The databases I currently work with are each in total
> about 3gb, with a 4k blocksize. I have two of them running on a
> dual-processor NT machine with 1gb RAM, and RAID storage equivalent to
> about 70gb. At any given time during the business day there are about
> 70-80 mostly inactive client-server sessions attached to one of the
> instances, and no more than a dozen to the other. Any advice you can
> give on tuning these instances would be appreciated. And what sort of
> vendor packages would use the TOOLS tablespace?
> Thanks again,
> Kenneth Buck
>
>

Usually I create a TOOLS tablespace with MINEXTENTS 1, INITIAL_EXTENT 32k, NEXT_EXTENT 32k, PCTINCREASE 1, MAX_EXTENTS unlimited. This allows for any third-party tools, like DBArtisan, SQL*Lab, etc. to utilize a tablespace expressly created for monitoring and tuning utilities. I also have my scripts/utilities create necesary tables in the TOOLS tablespace, to keep them away from production tables/indexes. I usually create a TOOLS_IDX tablespace as well, where I place any indexes I may need for tables that may be around longer than a day or two or may be larger than 1000 rows. I have modified the utlestat.sql script to create persistent statistics tables in the TOOLS tablespace, which allows me to use utlbstat/utlestat to monitor database performance and allows me to compare, in the database, two different runs of utlbstat/utlestat to monitor performance gains/losses with each set of modifications to the database (I dislike paging through paper reports). I also put the PLAN_TABLE in the TOOLS tablespace.

I shall presume that you are running Oracle 8i (or at least Oracle 8); I shall send you a fairly long script, containing 23 reports covering a number of aspects of an instance. I will give one caveat that the script was written for Oracle on UNIX and after running it on NT I find that the shared pool statistics, the last report, is a bit skewed to say the least. As I get that report adjusted I will send you a new copy. This should help you get started on tuning your instances.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.