The Research Of Oracle Database Performance Optimization In ODS System Of Telecom

Now the marketing concept of China Telecom is more focused on "customers"than "products" for the change of the market. China Telecom had established manysupport systems (e.g., Billing, CRM, Distribution, etc.) before this change happened,but these systems are independent or overlapped with each other because of that thereare many problems in them, Data Islands, multifarious interfaces and various modelsof the data. In order to solve these problems, China Telecom begins to build a ODS(Operational Data Store) system. With the powerful Oracle database,the ODS systembased on business data and other basic data realizes the data sharing between thedifferent systems through using scientific methods of data extraction, sorting, storageand so on. However, lacking fully consider about the performance of this system,data increased fast and the bigger change of data storage distribution will causepotential database performance problems. How to adjust and optimize the databaseperformance has become an important topic in the field of database research.In this thesis, the physical storage structure, the logical structure and theINSTANCE architecture of Oracle database had been researched, especially the coreparts which they are the Shared Pool, Database Buffer Cache and Redo Log Buffer inINSTANCE, then the ideas and strategies of performance optimization for the I/O,memory, CPU, system, SQL statements and the table partitioning were put forward.To solve the CPU overload, using v$osstat view and Oracle resource manager,the optimization methods such as controlling the session to take up resources were putforward, combined with the system real time work load,; To promote performance, toreduce disk competition, reduce link line and avoid dynamic expanding rollback segments of three methods were put forward by analyzing the I/O formation reasons;To promote Memory performance, configuring rationally SGA and PGA parametersto improve hit ratio were put forward; To improve the query speed of SQL statements,the methods, how to select right keywords and how to choose the right table order,were put forward by analyzing the access of SQL statement execution process andtable order; To improve the data availability and query efficiency and to reduceadministrative burden, the method, using range partition, hash list partition, partitionand mixed partition to split the huge table to small pieces, was put forward.Using these methods, these problems in ODS database, the conflict of the globalhot block, the abnormal query sort, ORA-60Error and the huge SQL statementexecution inefficiencies, had been solved. It is proved that the methods for databaseperformance tuning are correct and rational.These methods for database performance optimization put forward in this thesiscan be used not only for the ODS system but also for other system. The databaseadministrator can get same useful advices from these methods.