Re: Help On Performance Issue

mtwalla wrote:

> 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

It could be a very large number of issues. And some as simple as which one has the more valid statistics for the optimizer to use.

If it is worth tracking down the number of possibilities is quite large. But start by exploring the archives on google as this advice is given frequently.

Daniel Morgan Received on Fri May 10 2002 - 15:37:54 CDT