Database Concepts and Performance Tuning

Performance tuning in database systems involves collaboration across roles: DBAs handle database-level optimization, system admins manage hardware and OS configuration, and application developers optimize SQL queries and application code.

"Checkpoint not complete" occurs in Oracle databases when the DBWR cannot write dirty blocks fast enough before the redo logs wrap around. Adding more redo log groups or increasing their size gives DBWR more time to complete checkpoints. SHUTDOWN IMMEDIATE does not resolve this runtime performance issue.

Buffer busy waits occur when multiple sessions attempt to read or modify the same data block concurrently in the buffer cache. It's a contention issue, not a space or free buffer problem. Dirty buffer checkpointing affects write performance, not read contention.

AWR (Automatic Workload Repository) provides performance recommendations in its reports, including SQL tuning advisories, segment advisors, and various optimization suggestions based on workload statistics.

Statspack reports are generated by running spreport.sql, which prompts for snapshot IDs and produces an HTML/text report. spcreate.sql installs Statspack, sptrunc.sql resets tables, and spexp.par is for parameter files.

IMS DB uses a hierarchical data model where data is organized in a tree structure with parent-child relationships. This is different from relational (tables), network (many-to-many), or object-oriented models.

In IMS hierarchical databases, when a parent segment has multiple child segment occurrences with the same segment type, they are called twins. Siblings are different segment types under the same parent, while multiple is not an IMS term.

IMS/DB databases support a maximum of 15 levels in the hierarchical structure. This is a documented limitation of the database model and sufficient for most hierarchical data representations.

In IMS, to delete a segment, you must first establish exclusive control over it using a Get Hold Unique (GHU) call. This ensures no other programs can modify the segment while you're deleting it. The GHU call places a hold on the segment, which is a prerequisite for the subsequent D (delete) call. GU (Get Unique) retrieves without holding, while GN (Get Next) and GNP (Get Next Parent) are navigation calls.

Maximum cardinality specifies the maximum number of entity occurrences that can be associated with a single occurrence of a related entity. Minimum cardinality defines the lower bound, while ERD is the diagram format itself.

A weak entity depends on another entity for its existence but has its own partial key, called a discriminator, that distinguishes instances of the weak entity that share the same parent entity. This differs from an ID-dependent entity, which requires the parent's identifier as part of its own composite key. Strong entities exist independently, while ID-independent entities have no dependency.

In a one-to-many relationship, the entity on the 'one' side is called the parent entity because it can have multiple related child entities, but each child relates to exactly one parent. This is a fundamental concept in hierarchical and relational data modeling. The child entity is on the 'many' side.

An instance entity represents an actual occurrence or realization of a generalized entity type. This is a core modeling concept where you define a generalized template (archetype or supertype) and then create specific instances that conform to that template. In ER modeling, this maps to concrete data records representing the abstract entity definition.

A recursive relationship occurs when an entity is related to itself, such as an employee who manages other employees (both are the same Employee entity type). This is also called a self-referencing relationship. The relationship connects instances of the same entity type to each other, forming hierarchies or networks within a single entity.