Database indexing

Problem: Audits getting slower over time but nothing has changed.
Solution: Re-index your database audit tables.

At UCI, we use In-progress classes and update them on a daily basis. With 30,000 students in DW, a lot of database records get changed, thus database indexing becomes overwhelming.

DW and Oracle doesn't know when or how to re index the audit tables so it would be wise to add this to your quarterly maintenance. If I don't re index, our audits take twice as long to process.

The tables - dap_audit_dtl, dap_audtree_dtl

The indexes - for dap_audit_dtl: (via xsql, select index_name from dba_ind_columns where table_name='DAP_AUDIT_DTL';)

for dap_audtree_dtl: (via xsql, select index_name from dba_ind_columns where table_name='DAP_AUDTREE_DTL';)

Two options in re indexing:

A) Simply rebuild the index in xsql:

1. login to the account.
2. login to xsql.
3. type the rebuild index command for each of the indexes: "alter index DAP_AUDIT_DTL_A rebuild unrecoverable;"
4. Do this for each index. (DAP_AUDIT_DTL_A, DAP_AUDIT_DTL_B... etc.)

B) Rebuild the index in a new tablespace (recommended for very large FTE's):

1. Same as above but add the tablespace command: "alter index DAP_AUDIT_DTL_A rebuild tablespace (tablespace name) unrecoverable;"

Option B recommended by Oracle DBA for very large tables because instead of using temp space to create the new indexes, it just builds them in the new tablespace. Plus this is a much faster process.

Our I alternate between two tablespaces that are 6 GB large just for dap audit indexes. Yes, that is very large. Our tablespaces just for audits are 20 GB.

** Please consult with your IT staff at your site before experimenting with this. ***

Hardware... a key to DW success.

What we have:

Two Sun Sparc T5220 with 4 core threads.
4 GB of RAM
292 GB of Storage.

It's Sun's base model. This is sufficient for our needs and it fit our budget at the time of purchase.

We named them sole and luna, it's Italian for sun and moon. One server acts as the primary server and the other acts as an axillary server for student what-if processing and CPA batching.

Our previous inadequate server was a Sun Sunfire V880. It had served Degreeworks for 8 years and wasn't good at all. We were only able to manage because I setup a queue so that DW could run no more than 5 simultaneous audits. Any more than 6 audits at the same time would make the machine run like a turtle in molasses.

Now many clients have been successful and happy with this server but I couldn't wait to take it out back and beat it with a Louisville Slugger.

One reason why we weren't happy was the poor I/O bus speeds on the V880. We set up a software RAID (hardware RAID isn't available for this server) on the drives and writing takes up to many resources. Thus, a longer wait time.

The other big problem that might be key for other institutions of similar size is the complexity of our scribing. Our requirements for GE is filled with group rules. For many small college clients, an audit will process in about less than 5 seconds. For us, it's about 15-20 seconds. This may not seem like a lot, especially for other sites with less than 8,000 FTE's, but we have 28,000 FTE's.

On a side note, we're not the slowest audit. I think Boston University is running at 25-30 second audits for 30,000 students. They wrote programs similar to us to manage this issue.

We had DW consultants look into our situation and they couldn't help us. We were stuck with this setup for about 8 years.

Enough with our history. I know Sungard never endorses a machine and gives you a hand out on what they recommend. I've given them permission for new clients similar in size and capacity to contact me to help them with their hardware decisions. This is what I send.

My Suggestions for Hardware Purchase:

1) Faster CPU = faster audits

2) More CPU's or threads = faster batches (from the V880 to T5220, a new grade processing goes from 48 hours to 6 hours.) This will be important if you plan to use the Curriculum Planning Assistant.

3) More memory = More accounts. We only have room for Test and Prod, we like to get more resources for a Dev account. This basically has to deal with Oracle and the need to create a db instance for each account. If you host the DB on a different machine, you won't have this problem.

Having more accounts is a definite recommendation. Especially for debugging DW updates. (And you will debug the new updates. I'll post details later.)

4) Maximum hard drive space!!! You won't believe how much space the product takes. Although the product needs about 5-7GB of space, your database is going to be enormous. Our Prod account is roughly 50-60 GB. Yes, no joke. Half for just audits and the other half for the CPA warehouse. We have 28K students and growing.

Retention is an issue but I've got a work around to store the audit "page", not the audit tree, in a separate DB table. This saves time and resources for batch auditing.

Good luck finding hardware!