New SPRIDEN Indexes

To support fast searching for students by first & last name, we are adding new function-based indexes to the SPRIDEN table. Because this table is central to MITSIS, we need to conduct tests to make sure the new indexes do not have an adverse effect on existing programs.

The Indexes

The new indexes are as follows;

CREATE INDEX "SATURN"."SPRIDEN_UPPER_FIRST_LAST_IDX" ON "SATURN"."SPRIDEN" (UPPER("SPRIDEN_FIRST_NAME"), UPPER("SPRIDEN_LAST_NAME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SSIT_INDEX2" ;

CREATE INDEX "SATURN"."SPRIDEN_UPPER_LAST_FIRST_IDX" ON "SATURN"."SPRIDEN" (UPPER("SPRIDEN_LAST_NAME"), UPPER("SPRIDEN_FIRST_NAME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SSIT_INDEX2" ;

Testing

I tracked down several existing SQL queries to see how they were affected by the new indexes. I ran the queries against Dev (with the new indexes in place) and Test (no new indexes in place) and recorded the optimizer cost for each query  as reported by the Explain Plan tool.

1. Queries from csf-common-legacy unit tests

CSF Common Legacy Unit Tests

select
        student0_.PIDM as PIDM41_,
        student0_.MIT_ID as MIT2_41_,
        student0_.LAST_NAME as LAST3_41_,
        student0_.FIRST_NAME as FIRST4_41_,
        student0_.MIDDLE_NAME as MIDDLE5_41_,
        student0_.SEX as SEX41_,
        student0_.CITIZENSHIP as CITIZENS7_41_,
        student0_.BIRTH_DATE as BIRTH8_41_,
        student0_.KRB_NAME as KRB9_41_
    from
        SPV_STUDENT_VIEW student0_
    where
        upper(student0_.LAST_NAME)=upper('Joh%')

Cost (dev): 11
Cost (test): 3617

 select
        student0_.PIDM as PIDM41_,
        student0_.MIT_ID as MIT2_41_,
        student0_.LAST_NAME as LAST3_41_,
        student0_.FIRST_NAME as FIRST4_41_,
        student0_.MIDDLE_NAME as MIDDLE5_41_,
        student0_.SEX as SEX41_,
        student0_.CITIZENSHIP as CITIZENS7_41_,
        student0_.BIRTH_DATE as BIRTH8_41_,
        student0_.KRB_NAME as KRB9_41_
    from
        SPV_STUDENT_VIEW student0_
    where
        upper(student0_.FIRST_NAME)=upper('Ale%')

Cost (dev): 13
Cost (test): 3617

select
        student0_.PIDM as PIDM41_,
        student0_.MIT_ID as MIT2_41_,
        student0_.LAST_NAME as LAST3_41_,
        student0_.FIRST_NAME as FIRST4_41_,
        student0_.MIDDLE_NAME as MIDDLE5_41_,
        student0_.SEX as SEX41_,
        student0_.CITIZENSHIP as CITIZENS7_41_,
        student0_.BIRTH_DATE as BIRTH8_41_,
        student0_.KRB_NAME as KRB9_41_
    from
        SPV_STUDENT_VIEW student0_
    where
        (upper(student0_.FIRST_NAME) like upper('An%'))
        and (upper(student0_.LAST_NAME) like upper('*ohansson'))

Cost (dev): 3
Cost (test): 2283
2. Queries from Oracle forms:

--SOACOMP

select s.first_name, s.last_name
               from tbvsp_sponsor s
              where  last_name like
                     'Smi%'
                 and id like '%';

Cost (dev): 4
Cost (test): 4

– SOAIDEN

           select spriden_pidm, spriden_id, spriden_first_name
               from spriden
               where spriden_id_type = 'S'
                 and spriden_last_name like 'Smi%'
                 and spriden_first_name like '%'
                 and spriden_id like '%';

Cost (dev): 7
Cost (test): 8

      select spriden_pidm, spriden_first_name
               from spriden
               where spriden_id_type = 'S'
                 and spriden_last_name like '%'
                 and spriden_first_name like '%'
                 and spriden_id like '610562178';

Cost (dev): 4
Cost (test): 4

     SELECT SPRIDEN_PIDM,SPRIDEN_ID,SPRIDEN_LAST_NAME,SPRIDEN_FIRST_NAME, SPRIDEN_MI
        FROM SPRIDEN
       WHERE SPRIDEN_LAST_CAPS LIKE 'JOH%'
         AND SPRIDEN_CHANGE_IND IS NULL ORDER BY SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME,SPRIDEN_MI;

Cost (dev): 8
Cost (test): 139
3. Batch Jobs

Some batch jobs that do bulk insert/update operations on SPRIDEN need to be tested to make sure they are not adversely affected by the new indexes. These are:

  • MITID - Ross used to support this
  • Admissions load - Ben
  • Test score load -  Monica
  • Current person load - ???? DBA's

Tests had been co-ordinated with developers and DBA for the above batch jobs and functions. The test results shows that all jobs and functions completed successfully with no
to minimal difference in performance when compared to the metrics of the production run.

The following is some details about the run:

  • Test Scores Load - Monica used the largest input file (which is about 100 times larger than regular daily run) in the current year for the test score load in TEST. It takes about 13 minutes to finish, which is normal according to her.
  • UA Load - The uaload completed in about 18 minutes with the data file provided by Ross in Dev, with no errors.
  • MIT ID - The mit id processed around 1600 records in 24 minutes in Dev environment, it is about the same in performance from what I remembered.
  • Current Person Loads - DBA conducted the tests for us. According the information provided by them, all jobs successfully completed in Test except SXP_UPDATE_FROM_DW_KRBPERSON. All jobs finished within minutes and took about the same time when compared to production runs statistics
  • SATURN.SXP_UPDATE_FROM_DW_KRBPERSON - the script was later updated with the latest changes to resolve the failure due to changes to the data warehouse tables. The first run of the script in Test SATURN.SXP_UPDATE_FROM_DW_KRBPERSON took 5+ hours, but subsequent runs were in line with the time taken in daily production runs. Semyon and Raia had looked into the 5 hours run and discovered that the script itself does not use SPRIDEN at all. They are looking into whether the long elapse time of the first job was due to the fact there might be occasional big volume of data in a day and that the job had not been run for a while in TEST. But as it did not reference SPRIDEN at all, DBA concluded that the additional new indexes to SPRIDEN do not have any impact on the SXP_UPDATE_FROM_DW_KRBPERSON job.
  • No labels