Its unlikely - but is it possible you had a tempfile
before and now a datafile for TEMP?
hth
connor
--- [EMAIL PROTECTED] wrote: >
> I posted earlier about my SQL statement that
> overnight went from less than
> 2 minutes to about 25 minutes on our nightly data
> warehouse loads. We
> used RMAN to move an exact copy of the database from
> before the process
> started running long to a different unix box.
> After running sql_trace and
> tkprof on the SQL statement in question (see below)
> on both the current and
> pre-problem database, the execution times were
> similar and the explain
> plans were identical except for minor differences in
> the number of rows
> returned.
>
> I then looked at all of the initialization
> parameters and they look the
> same except that we created the rman copy with a
> smaller shared pool (due
> to resource constraints on the box we moved the copy
> to).
>
> One thing that I noticed was that the extent sizes
> for the TEMP tablespace
> is different. The day that we started having this
> problem, we had a disk
> failure. The TEMP tablespace was on the failed
> disk. Another DBA dropped
> the TEMP tablespace and recreated it on a different
> disk (apparently with a
> larger extent size). The current next_extent size
> is 4194304. The
> next_extent size on the pre-problem TEMP tablespace
> is 40960.
>
> Is it possible that this difference in extent size
> in the TEMP tablespace
> could cause a ten-fold degradation in performance?
>
> Cherie
>
>
>
>
>
> "Richard Ji"
>
>
> <[EMAIL PROTECTED] To:
> Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> m> cc:
>
>
> Sent by: Subject:
> Re: Strange performance problem
>
> [EMAIL PROTECTED]
>
>
> om
>
>
>
>
>
>
>
>
> 09/14/01 02:46
>
>
> PM
>
>
> Please respond
>
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> !! Please do not post Off Topic to this List !!
>
> Did you check to see if there is anything else
> running on the server that
> might take
> resource away from Oracle? It has happened to me
> once that the SA was
> running something that he shouldn't and it's using a
> lot of system
> resources.
>
> HTH
>
> >>> [EMAIL PROTECTED] 09/14/01 03:05PM >>>
> !! 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
>
=== message truncated ===
=====
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
____________________________________________________________
Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
