DATABASE TUNING SYLLABUS (CA5223)

DATABASE TUNING SYLLABUS (CA5223)

ANNA UNIVERSITY TRICHY

 UNIT I FUNDAMENTALS OF TUNING 8

Review of Relational Databases – Relational Algebra – Locking and Concurrency Control – Correctness Consideration – Lock Tuning – Logging and the Recovery Subsystem – Principles of Recovery – Tuning the Recovery Subsystem – Operating Systems Considerations – Hardware Tuning.

UNIT II INDEX TUNING 8

Types of Queries – Data Structures – B tree – B+ Tree – Hash Structures – Bit Map Indexes – Clustering Indexes – Non Clustering Indexes – Composite Indexes – Hot Tables – Comparison of Indexing and Hashing Techniques.

UNIT III QUERY OPTIMIZATION 10 

Techniques – Tuning Relational Systems – Normalization – Tuning Denormalization – Clustering Two Tables – Aggregate Maintenance – Record Layout – Query Tuning – Triggers – Client Server Mechanisms – Objects, Application Tools and Performance – Tuning the Application Interface – Bulk Loading Data – Accessing Multiple Databases.

UNIT IV TROUBLESHOOTING 10

Query Plan Explainers – Performance Monitors – Event Monitors – Finding “Suspicious” Queries – Analyzing a Query’s Access Plan – Profiling a Query Execution – DBMS Subsystems.

UNIT V CASE STUDIES 9

Transaction Chopping – Time Series Databases – Understanding Access Plans – Configuration Parameters: Oracle; SQL Server; DB2UDB – Distributed Database – Implementation.

Total: 45

TEXT BOOKS

1. Dennis Shasha and Philippe Bonnet “Database Tuning, Principles, Experiments, and Troubleshooting Techniques”, Morgan Kaufmann, An Imprint of Elsevier, 2003.

2. M.Tamer Ozsu, Patrick Valduriez and S.Sridhar “Principles of Distributed Database Systems”, Pearson Education, 2007.

REFERENCE

1. Thomas Connoly and Carlolyn Begg, “Database Systems, A Practical Approach to Design, Implementation and Management”, Third Edition, Pearson Education, 2003.