| 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 !!
>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).Received on Fri Sep 14 2001 - 14:08:55 CDT
 |
 |