Database Performance Tuning, Query Optimization, and Distributed Systems

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/35

flashcard set

Earn XP

Description and Tags

Comprehensive vocabulary flashcards covering Database Performance Tuning, Query Optimization, and Distributed Database Management Systems based on Chapters 11 and 12.

36 Terms

1
New cards

Database Performance Tuning

The collection of activities used to make a database management system (DBMS) respond efficiently while using system resources effectively.

2
New cards

Query Optimisation

The DBMS process of evaluating alternative execution strategies and selecting an efficient plan for executing a SQL statement.

3
New cards

Bottleneck

The specific resource or process—such as CPU, memory, disk I/O, network traffic, or locking—that limits overall system performance.

4
New cards

Performance Baseline

A recorded measurement of normal system behaviour under a known workload, against which later metrics are compared to detect deterioration and evaluate tuning changes.

5
New cards

Response Time

A performance metric that measures the total elapsed time from the initial submission of a request to the completed response.

6
New cards

Throughput

A performance metric measuring the number of transactions or requests completed by the system per unit of time.

7
New cards

Database Statistics

Information describing the size and distribution of stored data—such as row counts, page counts, distinct values, histograms, and index depths—used by the optimiser to evaluate cost and selectivity.

8
New cards

Parsing Phase

The query-processing phase that checks SQL syntax and semantics, validates object names and privileges, translates the query into an internal format, and generates an execution plan.

9
New cards

Execution Phase

The query-processing phase that runs the chosen plan by opening tables and indexes, reading data pages, applying filters, performing joins and sorting, and computing results.

10
New cards

Fetching Phase

The query-processing phase that transfers the generated result rows across the network to the requesting client or application, typically in batches.

11
New cards

Query Execution Plan

The specific operational roadmap chosen by the optimiser, detailing access paths, join order, join methods, sorting, and parallelism for a SQL statement.

12
New cards

Access Path

The technique selected by the query optimiser to locate and retrieve rows from a table, such as a full table scan, index scan, or index seek.

13
New cards

Composite Index

An index defined on two or more columns of a table, where the order of columns matters and the leading columns should correspond to common, selective search predicates.

14
New cards

Index Selectivity

A measure of how effectively an index narrows a search, commonly approximated by the ratio distinct valuestotal rows\frac{\text{distinct values}}{\text{total rows}}.

15
New cards

Sargable Condition

A search argument that is formulated in an index-friendly way, allowing the DBMS to utilize an index directly rather than executing full scans or applying functions to indexed columns.

16
New cards

Distributed Database

A single logical database whose data is physically stored across two or more network-connected sites while presenting as one unified database to users.

17
New cards

Distributed Processing

The division of database-processing work among two or more networked computers, which may operate against a single centralised database or across multiple sites.

18
New cards

Distributed Database Management System (DDBMS)

Software that governs the storage and processing of logically related data across interconnected computer sites, abstracting the complexity of distribution from end users.

19
New cards

Transaction Processor (TP)

The DDBMS component that accepts client requests, validates them, determines required sites, coordinates multi-site execution, and returns final results.

20
New cards

Data Processor (DP)

The DDBMS component that manages local storage, executes local data retrieval and update operations, and coordinates with other sites.

21
New cards

Homogeneous DDBMS

A distributed system that integrates multiple sites using the same or closely compatible DBMS software, data model, operating platform, and query language.

22
New cards

Heterogeneous DDBMS

A distributed system that integrates different DBMS products, data models, query languages, or operating systems through translation layers or middleware.

23
New cards

Distribution Transparency

The feature of a DDBMS that conceals the physical distribution, storage, replication, and transaction mechanics of data, presenting the system as a centralised database.

24
New cards

Remote Request

A distributed operation where a single SQL statement accesses data located at a single remote site.

25
New cards

Remote Transaction

A distributed operation consisting of multiple SQL statements within a single transaction that all access data stored at a single remote site.

26
New cards

Distributed Transaction

A transaction that includes multiple SQL statements accessing data across two or more different remote sites.

27
New cards

Distributed Request

The most complex distributed operation, wherein a single SQL statement accesses data stored across two or more distinct sites.

28
New cards

Two-Phase Commit Protocol (2PC)

A distributed consensus protocol that preserves transaction atomicity across multiple sites via a Prepare/Vote phase followed by an all-or-nothing Decision phase.

29
New cards

Horizontal Fragmentation

A distributed database design method that divides a table horizontally into subsets of rows based on specific conditions or attributes.

30
New cards

Vertical Fragmentation

A distributed database design method that breaks a table into subsets of columns, preserving the primary key in each fragment to enable full reconstruction.

31
New cards

Mixed Fragmentation

A data partitioning approach that combines horizontal and vertical fragmentation by dividing a table by both rows and columns.

32
New cards

Completeness Rule

A fragmentation correctness rule stating that every data item belonging to the original relation must appear in at least one fragment without loss.

33
New cards

Reconstruction Rule

A fragmentation correctness rule stating that it must be possible to rebuild the original table without data loss using relational operations such as joins or unions.

34
New cards

Disjointness Rule

A fragmentation correctness rule requiring that each data item appear in only one fragment, with the exception of primary keys replicated for vertical reconstruction.

35
New cards

Data Allocation

The design process of assigning table fragments and their replicas to specific physical storage sites based on access frequencies, communication costs, and reliability needs.

36
New cards

CAP Theorem

A distributed computing theorem stating that in the presence of a network partition, a system must make a trade-off between Consistency and Availability.