| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Strange performance problem
!! Please do not post Off Topic to this List !!
Rachel,
The difference in rows is not significant anywhere in the explain plan.
Thanks for your reply.
Cherie
"Rachel
Carmichael" To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
<carmichr_at_hotm cc:
ail.com> Subject: Re: Strange performance problem
Sent by:
root_at_fatcity.c
om
09/14/01 02:55
PM
Please respond
to ORACLE-L
!! Please do not post Off Topic to this List !!
>It should be an exact copy as of Aug. 16th. I ran the query on the >copy
>and on the current production database and the resulting explain >plans
>were identical except for the number of rows returned. Total >execution
>time and cpu times were similar.
What's the difference in the number of rows? I see buried deep in the explain plan a Cartesian join.... if the numbe rows jumped significantly, that might be the problem
>From: Cherie_Machler_at_gelco.com
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Strange performance problem
>Date: Fri, 14 Sep 2001 11:05:29 -0800
>
>!! Please do not post Off Topic to this List !!
>
>
>I have a nightly load job that was being tracked by our developers.
>According to their nightly logs (going back months), a query was running
>as far back as they can record with a sub-second response time.
>
>Then on a particular date (Aug. 23rd), the query started taking more
>than 20 minutes to complete. It has taken that long to complete ever
>since.
>
>I looked at the explain plan and it looks o.k. Indexes are being used
>and there are no suspicious full table scans. The init.ora file has not
>changed
>since then.
>
>We restored a full copy of the database to an alternate host using rman.
>It should be an exact copy as of Aug. 16th. I ran the query on the copy
>and
>on the current production database and the resulting explain plans were
>identical except for the number of rows returned. Total execution time
>and cpu times were similar.
>
>I looked through our change documentation and I do not see any record
>of data structure changes or any data changes at all in the database
>in question.
>
>I am sort of at a loss for what to try next. What sort of changes might
>cause such an extreme degradation in performance as this?
>
>This is an 8.1.7 database on Sun Solaris 2.8. The optimization is
>rule-based.
>No partitioning. Database is about 80 Gig in size. Following is the
>explain
>plan, if anyone is interested:
>
>SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM,
>ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM
>FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.26 0.27 0 0 0
>0
>Execute 2 0.01 0.01 0 0 1
>0
>Fetch 128 982.19 1026.27 145463 9732999 55484
>1897
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 131 982.46 1026.55 145463 9732999 55485
>1897
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 1897 FILTER
> 2041 NESTED LOOPS
> 2422 HASH JOIN
> 2341 NESTED LOOPS
> 2342 NESTED LOOPS
> 2338 NESTED LOOPS
> 2338 NESTED LOOPS
> 2346 NESTED LOOPS
> 2510 NESTED LOOPS
> 2510 NESTED LOOPS
> 2510 INDEX FAST FULL SCAN (object id 17279)
> 5018 INDEX UNIQUE SCAN (object id 17278)
> 5018 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
> 5018 INDEX UNIQUE SCAN (object id 17266)
> 4854 INDEX RANGE SCAN (object id 17270)
> 4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN
> 4682 INDEX RANGE SCAN (object id 17283)
> 4674 VIEW ACTIVE_EAS_RPT_PROF_VIEW
> 100491 SORT UNIQUE
>43 UNION-ALL
> 10 TABLE ACCESS FULL EAS_RPT_PROF
> 33 FILTER
> 34 NESTED LOOPS
> 734 NESTED LOOPS
> 207976 NESTED LOOPS
> 207976 MERGE JOIN CARTESIAN
> 706 INDEX FAST FULL SCAN (object id 17270)
> 208680 SORT JOIN
> 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
> 415950 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
> 415950 INDEX UNIQUE SCAN (object id 17266)
> 208708 INDEX UNIQUE SCAN (object id 17275)
> 766 TABLE ACCESS FULL EAS_RPT_PROF
> 4678 TABLE ACCESS FULL USER_SIGNON
> 2341 INDEX UNIQUE SCAN (object id 17275)
> 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
> 4461 VIEW ACTIVE_EAS_PERSON_VIEW
>2675205 SORT UNIQUE
> 1105 UNION-ALL
> 128 NESTED LOOPS
> 1107 INDEX RANGE SCAN (object id 17284)
> 128 TABLE ACCESS BY INDEX ROWID EAS_PERSON
> 2212 INDEX UNIQUE SCAN (object id 17277)
> 977 FILTER
> 1008 NESTED LOOPS
> 288511 NESTED LOOPS
> 326271 MERGE JOIN CARTESIAN
> 1107 INDEX RANGE SCAN (object id 17284)
> 327376 SORT JOIN
> 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
> 614780 TABLE ACCESS BY INDEX ROWID EAS_PERSON
> 652540 INDEX UNIQUE SCAN (object id 17277)
> 289517 INDEX UNIQUE SCAN (object id 17275)
> 540 SORT AGGREGATE
> 287 TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG
> 557 INDEX RANGE SCAN (object id 17276)
>1346 SORT AGGREGATE
> 737 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP_STS_LOG
> 1412 INDEX RANGE SCAN (object id 17270)
> 3938 SORT AGGREGATE
> 2066 TABLE ACCESS BY INDEX ROWID EAS_PERSON_ASSGN_STS_LOG
> 4035 INDEX RANGE SCAN (object id 17279)
> 680 SORT AGGREGATE
> 355 TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG
> 696 INDEX RANGE SCAN (object id 17276)
> 2614 SORT AGGREGATE
> 1578 TABLE ACCESS FULL EAS_PERSON_STS_LOG
> 2614 SORT AGGREGATE
> 1578 TABLE ACCESS FULL EAS_PERSON_STS_LOG
> 14 SORT AGGREGATE
> 7 TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG
> 14 INDEX RANGE SCAN (object id 17276)
> 66 SORT AGGREGATE
> 33 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP_STS_LOG
> 66 INDEX RANGE SCAN (object id 17270)
>
>
>I thought for sure that when we restored this database, it would reveal
>clues to what
>happened but nothing that I see has changed. I'd appreciate any clues
>anyone
>can give me about where to look and what to check.
>
>Thanks,
>
>Cherie
>
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: Cherie_Machler_at_gelco.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Cherie_Machler_at_gelco.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Sep 14 2001 - 14:56:16 CDT
 |
 |