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
