degrade in performance
From: Antony Raj <ca_raj_at_yahoo.com>
Date: Tue, 31 Jan 2012 09:25:23 -0800 (PST)
Message-ID: <1328030723.5907.YahooMailNeo_at_web36801.mail.mud.yahoo.com>
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
Date: Tue, 31 Jan 2012 09:25:23 -0800 (PST)
Message-ID: <1328030723.5907.YahooMailNeo_at_web36801.mail.mud.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 31 2012 - 11:25:23 CST