DBMS_STATS

 

Why Use DBMS_STATS?

Oracle relies on statistics like:

• Number of rows in a table

• Number of distinct values in a column

• Data distribution (histograms)

• Index usage and clustering

Without up-to-date stats, Oracle may choose poor execution plans, leading to slow queries.

---

✅ Common Use Cases of DBMS_STATS

1. Gather Table Statistics

plsql
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => 'HR',
    tabname     => 'EMPLOYEES',
    cascade     => TRUE  -- Also gathers index stats
  );
END;

---

2. Gather Schema Statistics

plsql
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname     => 'HR',
    cascade     => TRUE
  );
END;

---

3. Gather Database Statistics

plsql
BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(
    cascade     => TRUE
  );
END;

---

4. Gather Stats for Specific Columns

plsql
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => 'HR',
    tabname     => 'EMPLOYEES',
    method_opt  => 'FOR COLUMNS salary'
  );
END;

---

5. Delete Statistics

plsql
BEGIN
  DBMS_STATS.DELETE_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES'
  );
END;

---

6. Lock or Unlock Stats (Prevent Auto Updates)

plsql
-- Lock
BEGIN
  DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;

-- Unlock
BEGIN
  DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;

---

7. View Statistics

You can query the dictionary views like:

sql
SELECT * FROM user_tab_statistics WHERE table_name = 'EMPLOYEES';
SELECT * FROM user_tab_col_statistics WHERE table_name = 'EMPLOYEES';

---

💡 Best Practices

Best Practice	Why It Helps
Schedule regular stats gathering	Keeps optimizer decisions accurate
Use cascade => TRUE	Gathers related index stats automatically
Use estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE	Lets Oracle decide best sample size
Lock stats on static tables	Prevents unnecessary changes
Use method_opt for histograms	Helps on skewed data columns

---

🔁 Automating Stats Gathering

Oracle includes a maintenance job that runs nightly by default:

sql
SELECT client_name, status FROM dba_autotask_client;

You can enable/disable it using:

plsql
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;

---

⚠️ When to Manually Gather Stats

• After large data loads or deletions.

• After creating indexes or partitioning.

• When query performance drops unexpectedly.

---

Let me know if you want a script to schedule regular stats gathering or need help analyzing stats!

You said:

Do we have to manually configure table, procedures and functions in DBMS_STATS to gather statistics

ChatGPT said:

Great question — and the answer is: usually no, but sometimes yes, depending on your use case and environment.

---

✅ By Default: No Manual Configuration Needed

Oracle automatically gathers statistics for tables, indexes, and partitions using the automatic stats gathering job, which runs during maintenance windows (typically nightly).

This covers:

• Tables

• Indexes

• Partitions

• System stats (CPU, IO)

You do not need to manually configure each object unless you have specific needs.

---

🔍 When You Do Need Manual Configuration

Here are common scenarios where manual stats gathering is recommended:

---

1. Large Data Loads / Changes

If you do bulk operations (e.g., inserts, updates, deletes) outside the normal maintenance window, the optimizer may use stale stats.

➤ What to do:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
END;

---

2. New Tables, Indexes, or Partitions

When you create new objects, they may not have statistics yet, and Oracle may not gather them immediately.

➤ What to do:

DBMS_STATS.GATHER_TABLE_STATS('HR', 'NEW_SALES_DATA');

---

3. Complex Queries or Poor Execution Plans

If you're seeing bad performance on a query, and stats are missing or outdated, you may need to:

• Gather stats on specific columns (e.g., skewed data)

• Use histograms (method_opt)

• Lock good stats to prevent overwrites

---

4. PL/SQL Procedures and Functions

PL/SQL code itself does not need statistics. But if your procedures include SQL queries, then the tables/indexes they query should have good stats.

So, you don't gather stats on procedures — you gather stats on the objects they query.

---

5. Manually Controlled Environments

In some data warehouses or critical apps, teams disable auto-stats and handle everything manually to ensure full control.

---

💡 Tip: Check Statistics Status

To see if stats are stale or missing:

SELECT table_name, stale_stats, num_rows
FROM dba_tab_statistics
WHERE owner = 'HR';

---

📦 Summary

Object Type	Automatically Handled?	Manual Needed?
Tables & Indexes	✅ Yes (auto job)	Only if bulk-loaded or just created
PL/SQL Procedures	❌ Not applicable	Only gather stats on queried objects
Partitions	✅ Yes (if enabled)	Maybe for partition-specific tuning
Columns (Histograms)	❌ Not by default	Yes, if column is skewed or filtered

---

Let me know if you want a sample script to check and gather stats for all stale tables, or auto-gather after a data load!