Re: degrade in performance
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 Jan 2012 18:13:26 -0000
There are probably several design inefficiencies in your data structure and
code, and you haven't given us enough information to make good guesses
about where they are. It sounds as if your first step should be to enable
extended SQL trace for the duration of the batch run so that you can see
which SQL statements are responsible for most of the time, and find out
what access paths they are taking.
I doubt if you need to pay the price for partitioning for such a small data
set, but I think you may need to review your indexing strategies and some
of the SQL statements. (A correlated subquery to find "most recent" - i.e.
your max() subquery is often a death trap with response time that increases
with the data size unless you have the right indexes and the right path;
having a status or flag value where almost all the rows end up at the same
value is also an indexing death trap.) It's possible that there are a
couple of fairly simple, safe, strategies that could get you back to your
desired ten or fifteen minutes with 48 hours - but it's best for you to
start with knowing where the time is going.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Antony Raj" <ca_raj@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, January 31, 2012 5:25 PM
Subject: degrade in performance
Oracle 11.2.0.2 with 2 node RAC
The batch job always runs on the first node.
The job uses three staging tables(Closer to 20 million,non-partitioned) to
process interface jobs.
The sequence of events as follows:
1.Three staging tables are populated (approximately 80000 rows) with and
from many application tables with a status flag of "P"
2.Compare between the previous days rows which has status flag of 'C' and
today's rows.Report the differences of few column values.
Select statement runs in a loop and compare each emplid(bind value) between
today's and yesterday's data.Each SQL is taking approximately 0.02 seconds.
This SQL is doing "Index Range Scan".Though the leaf blocks are increased
every day,the range of scan would always be same as we process the same
number of emplids every day.
The height of the index was 2 at the beginning and 4 as of now.
3.Update status flag from 'C' to 'H'
UPDATE staging SET FLAG='H' WHERE HHC_AUDIT_FLAG='C' AND JOB_ID=(SELECT
MAX(JOB_ID) FROM STAGING WHERE JOB_ID<> 'Today's Job Id')
4.Update status flag from 'P' to 'C'
UPDATE staging SET FLAG='C' WHERE FLAG='P' AND job_id= 'today's job id'
Flag - Skew Data
H COUNT(*)
- ----------
C 87509
H 33007762
Initially the job ran b/n 10-12 minutes and during the course of over 6
months the run time has been increased to 80 minutes.
Theoretically,we are always processing same amount of data every day and
the expectation of response time of this batch job is less than 15 minutes.
I wonder how partitioning would help improving the response time as none of
the SQLs are doing full scan.
Thanks
--
//www.freelists.org/webpage/oracle-l
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1901 / Virus Database: 2109/4777 - Release Date: 01/30/12
--
//www.freelists.org/webpage/oracle-l
Other related posts:
