Re: Help On Performance Issue

Please, don't do that: pctincrease should be left 0. Uniform extent management is today's space management strategy and pctincrease !=0 destroys this.
Locally managed tablespaces enforce this (why would Oracle have invented it you think?).

And the cost based optimizer WAS crap in the first versions it was introduced (was it V7.1 and V7.2??).
In Oracle 8 and 8i it works fairly good. Maybe when you have well tuned sql statements rule based is better, but not in general.

Also: to have a table in 1 extent is a myth too. Other threads discuss this intensively.
An excessive number of extents is bad and you should indead check if that's the case here.
What is excessive? Many different opinions but my personal rule is: create the table/index with an extent size that makes the table or index 100 extents or less at it's estimated maximum size. Limit it to only 4 or 5 different extent sizes for all tables and 4 or 5 for indexes.
When a segment grows to 500 extents consider reorganizing. When that's a problem, 1000 extents is ok too.

But the real question here is the plan and performance difference. Nothing to add to what is said already.

vob <vob_at_aol.com> schreef in berichtnieuws abjhj7$ps1$06$1_at_news.t-online.com...
> you need to reduce the number of extents to 1 and set pctincrease to 55.
> never use the cost based optimitzer switch to rulebased
>
> the new versoins of oracle are crap
>
>
> "mtwalla" <mtwalla_at_bluefishgroup.com> schrieb im Newsbeitrag
> news:axUC8.75$_D5.42516_at_news.uswest.net...
> > I have a query running against two database. Database 1 has more than
data
> > than database 2, but I contend database 1 has a configuration problem
and
> > its not the size of the data resulting in the perofrmance issues of
> database
> > 1.
> >
> > Here is a tkprof from database 1 & 2. Would anyone like to take some
stabs
> > at what causing the differnces in performance?
> >
> > Database 1:
> > call count cpu elapsed disk query current
> > rows
>

• ------ -------- ---------- ---------- ---------- ---------- ----
  > --
  > > ----
  > > Parse 1 0.03 0.02 0 0 0
  > > 0
  > > Execute 2 0.04 0.05 0 0 6
  > > 0
  > > Fetch 276 28.26 142.66 29522 21028 1739
  > > 4115
  >
  
• ------ -------- ---------- ---------- ---------- ---------- ----
  > --
  > > ----
  > > total 279 28.33 142.73 29522 21028 1745
  > > 4115
  > >
  > > Misses in library cache during parse: 1
  > > Optimizer goal: CHOOSE
  > > Parsing user id: 19
  > >
  > > Rows Row Source Operation
  > > ------- ---------------------------------------------------
  > > 4115 SORT UNIQUE
  > > 124947 MERGE JOIN
  > > 134349 MERGE JOIN
  > > 394053 MERGE JOIN
  > > 394053 INDEX FULL SCAN (object id 3977)
  > > 788104 SORT JOIN
  > > 450026 TABLE ACCESS FULL DM_SYSOBJECT_R
  > > 528400 SORT JOIN
  > > 54273 TABLE ACCESS FULL DM_SYSOBJECT_S
  > > 124947 SORT JOIN
  > > 131586 TABLE ACCESS FULL WEB_CONTENT_S
  > >
  > > Database 2:
  > > call count cpu elapsed disk query current
  > > rows
  >
  
• ------ -------- ---------- ---------- ---------- ---------- ----
  > --
  > > ----
  > > Parse 1 0.08 0.17 0 63 0
  > > 0
  > > Execute 2 0.06 0.05 0 0 2
  > > 0
  > > Fetch 2613 15.16 17.19 302 1148627 158
  > > 39178
  >
  
• ------ -------- ---------- ---------- ---------- ---------- ----
  > --
  > > ----
  > > total 2616 15.30 17.41 302 1148690 160
  > > 39178
  > >
  > > Misses in library cache during parse: 1
  > > Optimizer goal: CHOOSE
  > > Parsing user id: 5
  > >
  > > Rows Row Source Operation
  > > ------- ---------------------------------------------------
  > > 39178 SORT UNIQUE
  > > 87755 NESTED LOOPS
  > > 88893 NESTED LOOPS
  > > 161689 NESTED LOOPS
  > > 161689 TABLE ACCESS FULL WEB_CONTENT_R
  > > 323376 TABLE ACCESS BY INDEX ROWID DM_SYSOBJECT_R
  > > 323376 INDEX UNIQUE SCAN (object id 61115)
  > > 250580 TABLE ACCESS BY INDEX ROWID DM_SYSOBJECT_S
  > > 323376 INDEX UNIQUE SCAN (object id 61114)
  > > 87755 TABLE ACCESS BY INDEX ROWID WEB_CONTENT_S
  > > 177784 INDEX UNIQUE SCAN (object id 61450)
  > >
  > > Why would the EXPLAIN PLAN differ, the same query is being run against
  > both
  > > Databses? Also, might the # of
  > > phyical disk reads be so much higher against database 1? Being a novice
  > DBA,
  > > my thought is there might be an SGA
  > > contention or sizing issue on database 1?
  > >
  > >
  > > Mike
  > >
  > >
  > >
  >
  >