Re: Oracle 9i tunning

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:25:13 CDT