| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help On Performance Issue
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
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
--Received on Sat May 11 2002 - 11:48:53 CDT
> ----
> 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
>
>
>
 |
 |