https://database-heartbeat.com/

Understanding Exadata disk layout | Page On-Call DBA Exadata

Step By Step Patching an Exadata cell node Exadata Storage Cell Patching

OracleDBPro - Pini Dibask Blog: Data Protection Flashback

Oracle Wait Events Cheat Sheet - Performance Tuning Quick Reference | Oracle DBA Scripts & Database Utilities Wait Events

SQL Plan Baseline, SQL Patch, SQL Profile: Differences and Use Cases – Osman’s DBlog SQL PROFILE ,SQL PATCH ,SQL BASE LINE

In Oracle RAC (Real Application Clusters), the components CSSD, CRS, EVMD, and DIAG are core parts of the Clusterware stack. They work together to ensure cluster stability, node coordination, failover, and diagnostics.

1. CSSD (Cluster Synchronization Services Daemon)

🔹 What it is

CSSD (ocssd.bin) is the heartbeat and cluster membership manager.

🔹 Core Responsibilities

✅ Node Membership Management

Tracks which nodes are alive or dead
Maintains the cluster node list

✅ Heartbeat Mechanism

Uses two types:

Network heartbeat → via private interconnect
Disk heartbeat → via voting disks

👉 If a node misses heartbeat → it is considered dead.

🔹 Split-Brain Prevention

This is CSSD’s most critical job.

Uses voting disks
Requires majority quorum
If a node loses quorum → it gets evicted (rebooted)

🔹 Failure Scenario

Example:

Node loses network connectivity
CSSD checks voting disks
If quorum lost → node is killed to protect data integrity

🔹 Key Files / Logs


$GRID_HOME/log/<node>/cssd/ocssd.log

🔹 Key Process


ocssd.bin


⚙️ 2. CRS (Cluster Ready Services)
🔹 What it is
CRS (crsd.bin) is the resource manager of Oracle Clusterware.

🔹 Core Responsibilities
✅ Resource Management
Manages:


Databases


Instances


Listeners


VIPs


ASM



✅ Start/Stop Resources


Starts resources in correct order


Handles dependencies


Example:
ASM → Database → Services


✅ Failover Management


If a resource fails → CRS restarts it


If node fails → relocates resources to another node



🔹 Resource Dependency Example
Database depends on ASM
Listener depends on network


🔹 Logs
$GRID_HOME/log/<node>/crsd/crsd.log


🔹 Key Process
crsd.bin


📡 3. EVMD (Event Manager Daemon)
🔹 What it is
EVMD (evmd.bin) is the event notification system.

🔹 Core Responsibilities
✅ Event Publishing


Publishes cluster events:


Node up/down


Instance start/stop


Failover events





✅ Event Subscription


Applications/scripts can subscribe to events



✅ FAN (Fast Application Notification)


Sends events to clients (e.g., JDBC, OCI)


Helps apps react instantly to failures



🔹 Example Use Case


Node crashes


EVMD sends event


Application connection pool drops dead connections immediately



🔹 Logs
$GRID_HOME/log/<node>/evmd/evmd.log


🔹 Key Process
evmd.bin


🩺 4. DIAG (Diagnostic Daemon)
🔹 What it is
DIAG (diagdaemon / integrated diag framework) is responsible for diagnostics and health monitoring.

🔹 Core Responsibilities
✅ Log Collection


Collects logs from all cluster components



✅ Health Monitoring


Tracks component health


Works with Cluster Health Monitor (CHM)



✅ Incident Detection


Detects critical issues


Generates trace files and dumps



🔹 Integration


Works with:


ADR (Automatic Diagnostic Repository)


Trace infrastructure





🔹 Logs Location
$GRID_HOME/log/<node>/diag/


🔗 How They Work Together
🔄 Startup Flow


OHASD starts


CSSD starts → establishes cluster membership


CRS starts → manages resources


EVMD starts → enables event system


DIAG runs in background



🔄 Failure Flow Example
Node Crash:


CSSD detects heartbeat loss


Node evicted


CRS relocates resources


EVMD sends notifications


DIAG logs everything



🧩 Architecture Relationship
OHASD (Oracle High Availability Service)
   │
   ├── CSSD  → Cluster membership + heartbeat
   ├── CRS   → Resource management
   ├── EVMD  → Event system
   └── DIAG  → Diagnostics


🚨 Quick Comparison Table
Component Role Critical Function
CSSD Cluster control Node membership, heartbeat
CRS Resource manager Start/stop/failover
EVMD Event system FAN notifications
DIAG Diagnostics Logs, health, incidents

🛠️ Important Commands
Check cluster status
crsctl stat res -t

Check cluster health
crsctl check cluster -all

Check CSS
crsctl check cssd

Component	Role	Critical Function
CSSD	Cluster control	Node membership, heartbeat
CRS	Resource manager	Start/stop/failover
EVMD	Event system	FAN notifications
DIAG	Diagnostics	Logs, health, incidents

1.Gather Dictionary Stats: EXEC DBMS_STATS.gather_dictionary_stats;

2.Gather Fixed Object Stats: EXEC DBMS_STATS.gather_fixed_objects_stats;

3.Gather Full Database Stats:

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

degree => 8

);

4. Gather Schema Stats

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

EXEC DBMS_STATS.gather_schema_stats(

ownname => 'SCOTT',

method_opt => 'FOR ALL COLUMNS SIZE 1',

granularity => 'ALL',

degree => 8,

cascade => TRUE,

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

);

5. Gather Table Stats

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

EXEC DBMS_STATS.gather_table_stats(

ownname => 'SCOTT',

tabname => 'EMP',

cascade => TRUE,

method_opt => 'FOR ALL COLUMNS SIZE 1',

granularity => 'ALL',

degree => 8

);

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'HR',
tabname => 'EMPLOYEES',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => TRUE
);
END;

6. Gather Index Stats

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMP_PK');

7. Gather Partition Stats

BEGIN

DBMS_STATS.gather_table_stats(

ownname => 'SCOTT',

tabname => 'SALES',

partname => 'SALES_Q1',

granularity => 'APPROX_GLOBAL AND PARTITION',

degree => 8

);

END;

The METHOD_OPT parameter in Oracle's DBMS_STATS procedures is a powerful and nuanced tool that controls how column statistics are gathered—especially histograms and extended statistics. Here's a breakdown to help you use it effectively

What METHOD_OPT Does

It determines:

Which columns get base statistics (min, max, NDV, nulls, etc.)
Whether histograms are created, and what type
Whether extended statistics (like column groups) are gathered

🧾 Syntax Overview

METHOD_OPT => 'FOR [column_spec] SIZE [histogram_spec]'

🔹 Column Spec Options


`FOR ALL COLUMNS ==> Gathers stats for all columns (default)`
`FOR ALL INDEXED COLUMNS ==> Only indexed columns (not recommended)`
`FOR ALL HIDDEN COLUMNS ==> Only virtual/hidden columns (special use case)`
`FOR COLUMNS (col1, col2) ==> Specific columns or column group`

🔸 Histogram Spec Options


`SIZE 1 ==> No histograms`
`SIZE AUTO ==> Oracle decides based on usage and data distribution (recommended)`
`SIZE SKEWONLY ==> Histograms only if data is skewed`
`SIZE REPEAT ==> Repeats previous histogram settings`
`SIZE ==> Explicit number of histogram buckets (e.g. 254)`

✅ Examples

1. No Histograms

METHOD_OPT => 'FOR ALL COLUMNS SIZE 1'

2. Auto Histograms

METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'

3. Specific Columns with Histograms

METHOD_OPT => 'FOR COLUMNS (col1, col2) SIZE 254'

4. Column Group Statistics

METHOD_OPT => 'FOR COLUMNS (col1, col2) SIZE AUTO'

This gathers extended stats for the column group.

🧠 Best Practices

Use SIZE AUTO for most environments—it lets Oracle decide intelligently.
Avoid FOR ALL INDEXED COLUMNS unless you have a very narrow workload.
Use column groups for correlated columns in WHERE or JOIN clauses.

In Oracle's DBMS_STATS package, the ESTIMATE_PERCENT parameter controls how much of the data is sampled when gathering statistics. It directly affects the accuracy and performance of the stats collection process.

What Does ESTIMATE_PERCENT Do?

It specifies the percentage of rows Oracle should sample from a table (or index) to generate statistics. You can set it to:

A specific number (e.g., 10, 50, 100)
Or use the automatic setting: DBMS_STATS.AUTO_SAMPLE_SIZE

Recommended Setting: DBMS_STATS.AUTO_SAMPLE_SIZE

Oracle recommends using:

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

🔍 Why?

Oracle uses adaptive sampling algorithms to determine the best sample size.
It avoids full table scans unless necessary.
It ensures accurate NDV (Number of Distinct Values) without high overhead.
Required for incremental statistics and hybrid histograms

1. Auto Sample Size (Best Practice)

EXEC DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'HR',

tabname => 'EMPLOYEES',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

cascade => TRUE );

2. Full Table Scan (100% Sampling)

estimate_percent => 100

Tuesday, September 23, 2025

URL

1. CSSD (Cluster Synchronization Services Daemon)

🔹 What it is

🔹 Core Responsibilities

✅ Node Membership Management

✅ Heartbeat Mechanism

🔹 Split-Brain Prevention

🔹 Failure Scenario

🔹 Key Files / Logs

🔹 Key Process

⚙️ 2. CRS (Cluster Ready Services)

🔹 What it is

🔹 Core Responsibilities

✅ Resource Management

✅ Start/Stop Resources

✅ Failover Management

🔹 Resource Dependency Example

🔹 Logs

🔹 Key Process

📡 3. EVMD (Event Manager Daemon)

🔹 What it is

🔹 Core Responsibilities

✅ Event Publishing

✅ Event Subscription

✅ FAN (Fast Application Notification)

🔹 Example Use Case

🔹 Logs

🔹 Key Process

🩺 4. DIAG (Diagnostic Daemon)

🔹 What it is

🔹 Core Responsibilities

✅ Log Collection

✅ Health Monitoring

✅ Incident Detection

🔹 Integration

🔹 Logs Location

🔗 How They Work Together

🔄 Startup Flow

🔄 Failure Flow Example

Node Crash:

🧩 Architecture Relationship

🚨 Quick Comparison Table

🛠️ Important Commands

Check cluster status

Check cluster health

Check CSS

Monday, July 14, 2025

Gather Statistics