Re: Oracle 9i tunning

Thanks....

BTW, your diagnoses below is exactly correct from my memory. But I do not have much SQL server experience I learned RDMS on Oracle 7.3 as an end user. The DBA that set up the database I referred to wasn't thinking data warehouse, he was thinking OLTP and we probably got off on a bad start.

Joe

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D178010.EB4A2BE4_at_exesolutions.com...
> Joe wrote:
>
> > Most 747s are acutally flow by autopilot...so SQL server would be the
747 I
> > guess. The only think I need a little help with is the "takeoff".
> >
> > I am not building some huge enterprise db warehouse, it is a single user
db
> > with a couple big tables with respect to the size of the box I am
running
> > the db on. I only want to get some basic configuration advice so that
what
> > little resources my box has are working most efficiently...and I promise
> > that I won't go around flaming Oracle if things don't work out...I am
> > already sold on Oracle...I LIKE ORACLE.
> >
> > Joe
> >
> > "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> > news:3D177B25.B234AD5_at_exesolutions.com...
> > > Joe wrote:
> > >
> > > > Wow, judging by the tone of the two responses of my post, I have
decided
> > > > that being an Oracle DBA must be *way* too stressful for me to
handle.
> > I
> > > > think SQL server is the correct choice for me.
> > > >
> > > > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > > > news:k6mehu8lfr48h3pm7rha15f2tremcnnci9_at_4ax.com...
> > > > > On Mon, 24 Jun 2002 16:38:39 GMT, "Joe" <joegenshlea_at_attbi.com>
wrote:
> > > > >
> > > > > >Oh yeah...
> > > > > >
> > > > > >80 Million rows may be nothing to Oracle but...
> > > > > >
> > > > > >In the last job I had our fact table was 3 million rows and with
the
> > > > default
> > > > > >Oracle 8i installation. I was constantly getting errors when
> > executing
> > > > DML
> > > > > >SQL (It's been over a year so forgive me I am not entirely
accurate
> > with
> > > > the
> > > > > >syntax). The errors had to do with Snapshot too old, and failing
to
> > > > extend
> > > > > >tablepace extents, etc...and we had a DBA with 15 years
experience
> > > > (mostly
> > > > > >OLTP experience albeit). We solved these issues by tweeking the
> > > > > >tablespaces, rollback segments,etc. From my experience with this
> > > > situation,
> > > > > >I decided to mess around with the configuration of the
insallation in
> > my
> > > > > >test environment.
> > > > > >
> > > > > >By the way, I am a one man shop, my background is in Economics
not
> > > > Computer
> > > > > >Science or anything of the sort, so be kind! Most everything I
know
> > about
> > > > > >Oracle I've picked up doing in a research and analysis capacity
and
> > also
> > > > > >what I've gleaned from "Oracle 8i Data Warehousing" by Dodge and
> > Gorman.
> > > > > >
> > > > > >Joe
> > > > > >
> > > > > First of all it strikes me that so many people are incapable to
grasp
> > > > > the correct orthography of the word 'tuning', with *one* n.
> > > > > Secondly, if you only think a *little*, you could know a
> > > > > sort_area_size of 1 G for a system with 2 G is clearly outrageous
and
> > > > > ridiculous. Sort_area_size is a per process limit, if you are not
> > > > > running dedicated server the sort_area_siz is NOT in the SGA, so
> > > > > evidently, the disk lamp in your server must be continually on,
> > > > > because you force the server to page *all the time*.
> > > > >
> > > > > I would recommend to start reading Designing Oracle for
Performance,
> > > > > on page 1, before you even consider datawarehousing.
> > > > >
> > > > > Regards
> > > > >
> > > > >
> > > > > Sybrand Bakker, Senior Oracle DBA
> > > > >
> > > > > To reply remove -verwijderdit from my e-mail address
> > >
> > > I agree. The comment above in this thread "I was constantly getting
errors
> > when
> > > executing DML" is a clear indication of why it is wise to caution
people
> > not to
> > > jump into Oracle and assume it is SQL Server, or any other RDBMS
product
> > for
> > > that matter.
> > >
> > > I've never had it happen to me in over ten years. But then that might
be
> > because
> > > I didn't assume I could use the product without learning it. Just
because
> > you
> > > can fly a Cesna does not mean you can fly a 747 without additional
> > training. And
> > > the same applies here. They may both fly but they are not the same.
(and
> > please
> > > don't assume that the preceding analogy includes some subtle or not so
> > subtle
> > > value judgement ... it does not).
> > >
> > > Daniel Morgan
> > >
>
> Then, with that assurance in mind, here's what I would suggest (and this
is not
> an attempt to blow you off):
>
> Go to http://tahiti.oracle.com
>
> Choose your version of Oracle.
>
> Enter the search criterion 'SGA sizing'
>
> Scroll down through the hits and click on 'Designing and Tuning for
> Performance'.
>
> That should get you the starting point of exploring lots of things.
>
> Snapshot too old likely means that either (A) you were doing incremental
commits
> because you learned that in SQL Server and thought you should do it in
Oracle
> ... you should not, or (B) your rollback segments were too small.
>
> A failure to extend tablespaces and extents is due to not thinking through
your
> requirements and (A) sizing objects appropriately to begin with, or (B)
running
> out of disk space. I think almost all senior Oracle DBAs regard tablespace
> autoextend as a bad idea. And as table extents can run into the billions
I'm not
> sure how you could run out of them unless you didn't specify max extents
when
> building your tables and relied on the tablespace default to do your work
for
> you.
>
> Daniel Morgan
>
Received on Mon Jun 24 2002 - 15:37:16 CDT