CODEWITHSUNDEEP
springoracle-databasespring-batch
ranell
ranell

Reputation: 703

Long delay for SQL Step under Spring batch improved by RAM DB increasing

I have a SQL running for 8 hours in Spring Batch, while when executed manually, it do not take more than 1 hour.

After investigation with DBA we raise SGA PGA value from 2,2 to 32, 10 respecively, and surprisingly, the batch realize the SQL step in 1 hour.

I want to understand how it's working and PGA SGA value role in that situation. And from that understanding maybe to optimize the ressources use

As you can see, the code is really very simple:

JobConfiguration
   private Step StepThree() {
        return createTasklet("StepThree", MyRepository::StepThree);
    }
     
    @Bean
    public Job myJob() {
        return jobBuilderFactory.get("job")
                .start(StepOne())
                .next(StepTwo())
                .next(StepThree())
                .listener(jobExecutionCheckListener)
                .build();
    }
            
   @Override
   public void StepThree() {
        String sql =
                "insert into myTableToCopy\n" +
                        "select *\n" +
                        "from myTable source\n" +
                        "where not exists(select 1\n" +
                        "                 from myTableToCopy target\n" +
                        "                 where target.Car_ID = 
      source.Car_ID\n")";
      jdbcTemplate.getJdbcOperations().execute(sql);
    }

Application is using Hikari - driver-class-name:oracle.jdbc.OracleDrive Oracle : 12.1.0.2.0

Thanks

Upvotes: 1

Views: 189

Answers (1)

httPants
httPants

Reputation: 2143

You're inserting all the rows into myTableToCopy in a single sql insert statement which is a single transaction. This will require the database to allocate alot of memory for potentially rolling back the transaction if required if the number of rows being inserted is high. Since you're already using spring batch, it would use less database resources if you split that single insert statement up into batches.

To do that, you could have an ItemReader (eg. JdbcCursorItemReader) that uses this sql statement...

select * from myTable source where not exists(select 1 from myTableToCopy target where target.Car_ID = source.Car_ID

You would then have an ItemWriter that would insert a list of items into the myTableToCopy table and define the Step with a chunksize to control how many items you are inserting per transaction. This way you have many transactions of a much smaller size for inserting the data into the myTableToCopy table which would use less resources on your database server.

Upvotes: 1

Related Questions