Objective: To achieve a 4X improvement in throughput.
Usage: The system is used for batch data generation that is delivered to downstream systems for distribution and presentation.
Old hardware platform: P690 running AIX with 16 cores, 40 GB memory, and 4 TB EMC VMAX storage.
New hardware platform: BL685 G7 running Linux with 64 cores, 512 GB memory, and 6 Meta LUNS (1,632 GB each) EMC VMAX storage.
Phase 1: Determine the initial Oracle environment implementation and settings after a reviewing of the existing and new hardware, software, processes, and data. Estimated duration: 1 to 3 weeks.
Phase 2: Determine the optimal Oracle environment and identify process (application/query and usage) changes after a complete “deep-dive”. Estimated duration: 4 to 8 weeks.
To review the current database performance and error issues that Toolbox is experiencing and to identify changes that are likely to have the highest Return-On-Investment (in terms of reliability and performance).
To review proposed and “in work” changes being made by the Toolbox teams with respect to the database to confirm direction or suggest alternatives to support more concurrency and better performance.
TOC Table Partitioning by Company Code
“Cleaned-up” TOC Table by using new TOC Archive (history) table to leave only “active” data
Restructure TOC Table into multiple tables for more efficient operations
Database “sharding” of company groups in different DMS_OWN schema instances
Proposed changes should be identified as:
working under 11g
To help estimate performance expectations, concurrency limits, and storage capacity per “average” and “worst-case” data volumes per Company
ensure temp spaces can support “worst case” concurrency
To participate in real-time problem analysis, including training on:
what to look for and how to interpret,
and how to fix or avoid problems via various database and/or application configuration changes
To review PIMS proposal to scale system by adding multiple Oracle databases to increase parallel processing and throughput.
Review and Advise on Recent Errors
streams falling behind
How best to support a remote, “fail over” site
TOC Materialized View issues
DB objects marked as invalid
explain plans being re-written
Review Toolbox team direction regarding database changes. (See Goals above.)
Protect Toolbox UI performance. We know that a combination of data loading, UTR Exporting, and publishing hurt UI performance
how to prevent this situation, but continue to get as much processing done as possible; here are some of the proposals to consider:
dedicated UTR DB server
avoid data loading and publishing for the same Company
Estimated amount and type of concurrency that our current and planned Oracle configurations should be expected to support, while maintaining efficiency and UI performance
Prevent the Oracle optimizer from being “wrong”:
index hints and stored outlines
when “best” to run per application activity (e.g. right after a major data load?)
how to clear the “Shared Memory” pool on a running system
“per company” database table partitions
add Company to TOC table (?)
Review TeraXml Search Server use of database.
eSWAT data load options
transportable table spaces
Review PIMS solution for increasing number of Oracle databases to scale system throughput
Review database SQL and access method and provide recommendations for optimizing performance and scalability
Consultant should have some working experience with Exadata.
- Installation, configuration, upgrade, and daily administration of Oracle databases (5 years)
Oracle database and hardware performance tuning, monitoring, and capacity planning (5 years)
Implementation support, cloning, code migration (5 years)
ATG is a +
OEM is a +