Tuning Database Design phase -- Logical & Physical Databse Tuning

The analysis and design phases of the SDLC are the most important for performance because during these phases you need to develop a logical data model of business processes the system must support; and create a blueprint that will support the documented requirements, i.e. system design specifications. The database design phase can be divided in to logical design and physical design. Whereas the logical design is concerned with the ‘what,’ the physical design is concerned with the ‘how.’ Logical and physical design can be carried out in parallel, on table-by-table, file-by-file or when working with hierarchical and network database models. Such parallel activities require the designer to have a thorough understanding of the software and hardware in order to take full advantage of both software and hardware characteristics. For example, decisions taken during physical design for improving performance, such as merging relations together might affect the structure of the logical data model, which will have an associated effect on the application design. Logical Database Design phase The logical design phase is a process that translates software-independent conceptual model into a software-dependent internal model by defining the appropriate domain definitions, the required tables, indexes, and the necessary access restrictions or authorizations, etc. This requires mapping all objects in the logical model to specific constructs of the database software. Objectives of the logical design phase should include: Identifying the type of database application Gathering data from the existing system and business requirements Analyzing data and identify entities Identifying relationships between entities Creating a conceptual data model Identifying degrees of relationships and cardinalities Identifying all attributes for each entity Creating a logical data model Identifying domains for each attribute Creating a data dictionary Identifying data constraints and validation techniques & Reviewing global logical data model with user(s). Etc. Tuning the logical design phase Remove features not compatible with the relational model. Check logical model for redundancy. Remove multi-valued attributes and complex recursive relationship types. Validate logical data relations using normalization and ensure that each table is at least in the third normal form (3NF). Above 4NF may not be suitable for ad hoc queries because of over complex SQL codes. Build and validate global logical data model (metadata). Validate logical data relations against user transaction to make sure it’s support by the views. Review and validate global logical data model with potential user(s) of the system. Physical Database Design phase The physical design is the process of producing a description of the implementation of the database on data storage and what access methods will be used to access the data effectively. Therefore, physical design not only affects the location of the data in the storage devices but also the performance of the system. For example, performance can be affected by the characteristics of the storage media, such as seek time, sector, and block size, buffer pool size, and the number of disk platters and read/write (I/O) heads. Likewise, such factors as the creation of indexes or partitions can have a considerable performance effect on the database’s access speed and efficiency. The physical design phase allows designers to make decisions on how the database is to be implemented. Objectives of the physical design phase should include: Deciding which DBMS will be employed. Translating global logical data model for the target DBMS. Designing the base relations and enterprise constraints. Examining the features of the DBSM to be used. Creating a storage capacity plan (disk space required by the database). Analyzing initial data and growth. Designing database procedures to implement business rules. Identifying data migration procedures. Identifying and create backup and recovery strategies. Designing user views and security mechanisms. Creating test data during this phase as part of the overall test plan, which details the procedures that specify how and when the testing will be performed. Tuning the physical design phase Eliminate triggers where possible because they considerable slow down database performance. Triggers are written in interpretive PL/SQL code and do not allow COMMIT or ROLLBACK commands because they are event driven, and can thus produce large transaction that degrade database performance. Analyze data usage to make sure that attributes used in any predicates for very frequent or critical transactions have a higher priority for access structures. Determine an efficient file organization system for each file relation, i.e. Heap (unordered), Hash, Indexed Sequential Access method (ISAM), B+-tree, or Clusters. E.g. consider clustering tables that are often accessed in join statements. Determine whether adding indexes will improve performance of the system. Add a secondary index on attributes that are frequently involved in selection or join operations, sort operations, built-in aggregation, and any attribute that is heavily used as a secondary or foreign key. Use partitions to improve query performance and database availability through partition elimination since the query optimizer knows the partitions containing the requested data. Specify the degree of parallel processing at the object level that can be overridden via “hints” in your queries. Parallel processing options allow you to involve multiple processors in the execution of a single command. Minimize the amount of disk storage used, by using RAID configured disks, storage area network (SAN), and network attached storage (NAS) media storage technologies.   References:  Afyouni, A. H. (2004). Oracle 9i Performance Tuning: Optimizing Database Productivity. Thompson Course Tech. Alapati, S. R. (2008). Expert Oracle Database 11g Administration. Apress. Burleson, D. K. & Danchenkov, A. B. (2005). Oracle Tuning: The Definitive Reference. Rampant Techpress Connolly, M. T, & Begg, E. C, (2002). Database Systems: A Practical Approach to Design, Implementation, and Management. Addison-Wesley. Donar, T. (2002). Tru64 UNIX-Oracle9i cluster quick reference (HP Technologies) Freeman, R. G. (2008). Oracle Database 11g New Features: Maximize the New Capabilities of the Latest Database Release. McGraw Hill-Osborne. Johnson, J. C. (2002). OCP: Oracle9i performance tuning study guide. Illustrated Edition. John Wiley and Sons. Mittra, S. S. (2002). Database Performance Tuning and optimization: Using Oracle. Illustrated Edition: Springer. Oracle8i Designing and Tuning for Performance Release 2 (8.1.6). Retrieved on September 02, 2009 from http://www.mcs.csuhayward.edu/support/oracle/doc/8.1.7/server.817/a76992/ch19_mem.htm Page, W. G. (1999). Using Oracle8/8i, Special Edition. Chapter 20: Que Books. Retrieved on September 28, 2009 from http://docs.rinet.ru/O8/index.htm Piedad, F. & Hawkins, M. (2001). High Availability: Design, Techniques, and Processes. Prentice Hall PTR, New Jersey. Rob, P. & Coronel, C. (2004). Database Systems: Design, Implementation, & Management. 6th Edition. Thompson Course technology Stephens, R., Plew, R., & Sams, A. J. (2003). Teach Yourself SQL in 24 Hours, 3rd Edition. Sams Publishing. Shelly, G. B., Cashman, T. J., & Rosenblatt, H. J. (2000). System Analysis and Design. 4th Edition. Shell-Cashman Series. Course Technology. Turban, E., Aronson, J. E., Liang, T. P. (2005). Decision Support Systems and Intelligent     Systems, 7th Editon. Pearson Prentice Hall. Whalen, Edward. (2005). Oracle Database 10g Linux administration. McGraw-Hill- Osborne/Oracle Press.