Success Stories
ORACLE
PERFORMANCE TUNING FOR A LARGE FINANCIAL FIRM (2007)
Problem:
- Nightly database loads were overlapping daytime activities
- Database reports were running too long and customers were dissatisfied
- Maintenance windows were non-existent due to long loads
Action:
- Quickly identified multiple sources of bottlenecks within the Oracle DB
- Worked to resolve each bottleneck and re-baseline after each effort
- Documented the complete solution for re-deployment to client’s other sites
Result:
- Decreased nightly loads from 15 hours to four hours
- Decreased average report time from 2 hours to 15 minutes
- Maintenance windows were available nightly to keep systems up to date
COMPLEX RAC IMPLEMENTATION FOR A LARGE MANUFACTURER (2007)
Problem:
- Customer received estimates from vendor that did not meet timeline requirements
- Customer received estimates from vendor that were out of budget
- Customer was in danger of failing to complete a mission critical project
Action:
- Reviewed customer requirements to congifure HP MC/SG and Oracle RAC
- Reviewed customer needs for two different RAC systems running in NPARS
- Provided an estimate that was 1/2 vendor estimate in time and 1/3 in cost
- Completed MC/SG and RAC configurations plus data load plus training
Result:
- Completed installation and plus training in ½ vendor estimated time for install-only
- Saved the customer $30,000 in consulting fees
- Customer was able to complete mission critical projects on time
SERVER
RECOVERY FOR A LARGE MANUFACTURER (2006)
Problem:
- RedHat server crashed and would not reboot
- RedHat Support said the server was unrecoverable due to missing files
- Customer did not have a backup of the server
- Customer was prepared to rebuild the server and lose all existing data
Action:
- Quickly isolated the problem to boot order and mount points
- Booted in single user mode from CD and mounted file systems
- Copied files that were needed in early boot stage from mount point directly to root
Result:
- Successfully recovered system with no data loss
- Customer saved $30,000 in re-work for rebuilding the system and recovering data
- Assisted customer with deploying a backup strategy for the system
REPORT PERFORMANCE TUNING FOR ALSTOM (2006)
Problem:
- Report run times against the data warehouse were unacceptably long for end users
- Goal was to have all reports on less than 1 year of data return in 15 seconds or less
- Average run time for the reports at the beginning of the project was over 1 hour
Action:
- Reviewed each report and optimized SQL and underlying database structures
- Each report was reviewed an recommendations documented
- Improved report SQL for some reports
- Sub-optimal initialization parameters
- Added indexes as needed without affecting nightly Load run times
- B*Tree indexes on high cardinality columns
- Bitmap indexes on low cardinality columns
- Bitmap Join Indexes where appropriate
- Function based indexes where appropriate
- Created Materialized Views to pre-calculate data
- Reviewed reports to identify common elements to minimize the number of Materialized Views needed
- Created Views and set parameters to transparently redirect user queries
- Put a process in place to Monitor the run times going forward
- Each morning 50 reports are executed and the run times recorded
- Summary and individual results are displayed to all end users on a web page
Result:
- Average run time across all reports was reduced from over 1 hour to 14.8 seconds
- Users have visibility into the performance of the database on a day by day basis reducing calls to the IT help desk by 80%
SAN PERFORMANCE
IMPROVEMENTS FOR ALSTOM (2005)
Problem:
- Oracle Database performance was too slow for SLA requirements
- DBAs, System Admins, SAN Admins and vendor support could not isolate the problem
- Customer was ready to spend nearly $1 Million on new server and storage hardware
- Customer was being led in different directions and needed a firm handle on the issue
Action:
- Quickly identified system bottlenecks, mostly large queues waiting for disk IO
- Found insufficient configurations for large IO operations
- Demonstrated that the problem was not isolated to the DB, but was a general IO problem
- Documented necessary configuration changes and implemented some of them
Result:
- Improved IO throughput from 9 MBps to 120 MBps
- Saved nearly $1 Million in unnecessary hardware upgrades
- Customer was able to meet SLAs for report run times
BARE METAL DATABASE RESTORATION FOR REGIONAL BUILDING SUPPLIER (2005)
Problem:
- Server crash left all production database offline
- Server hardware was not repairable
Action:
- New server hardware was acquired, however it did not match the spec of the damaged hardware
- Oracle was installed on the new server
- Databases were restored from tape and recovered to fullest extent possible (the last logs available on tape)
Result:
