SQL Interview Questions

Common SQL Interview Questions

1. JOIN Types

• INNER JOIN: Only matching rows from both tables
• LEFT JOIN: All from left + matches from right
• RIGHT JOIN: All from right + matches from left
• FULL OUTER JOIN: All rows from both sides

2. WHERE vs HAVING

• WHERE: Filters before grouping
• HAVING: Filters after grouping

3. Subquery Example

SELECT name
FROM employees
WHERE emp_id IN (
  SELECT emp_id FROM departments WHERE dept_name = 'HR'
);

4. GROUP BY and ORDER BY

SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY COUNT(*) DESC;

5. DISTINCT

SELECT DISTINCT department FROM employees;

6. Normalization

• 1NF: Atomic columns
• 2NF: Full functional dependency
• 3NF: No transitive dependency
• BCNF: Stronger version of 3NF
• 4NF: No multi-valued dependency

7. UNION vs UNION ALL

SELECT name FROM employees
UNION
SELECT name FROM contractors;

8. Indexes

Indexes improve performance for lookups, but slow down writes.

9. ACID Properties

• Atomicity: All-or-nothing transactions
• Consistency: Data remains valid
• Isolation: No interference between transactions
• Durability: Permanent changes after commit

10. Primary Key

Uniquely identifies each record, no NULLs allowed.

11. Foreign Key

Refers to a primary key in another table to ensure referential integrity.

12. CASE Statement

SELECT name,
  CASE
    WHEN salary > 50000 THEN 'High'
    WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;

13. Transactions

Ensure ACID compliance using BEGIN, COMMIT, ROLLBACK.

14. JOIN Types Explained

Covered in Question 1 with examples of INNER, LEFT, RIGHT, FULL joins.

15. SELF JOIN

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

16. LIMIT

-- MySQL/Postgres
SELECT * FROM employees LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM employees;

17. TRUNCATE vs DELETE

• DELETE: Row-by-row, can use WHERE, rollback supported
• TRUNCATE: Faster, no WHERE, can't rollback

18. Stored Procedures vs Functions

• Procedure: No return value required, can do INSERT/UPDATE
• Function: Always returns a value, can be used in SELECT

19. CHAR vs VARCHAR

• CHAR: Fixed length, padded with spaces
• VARCHAR: Variable length, more efficient

20. Views

Virtual tables based on SQL queries, used for abstraction and simplification.

Deletes Duplicate Rows

 DELETE FROM employee e

WHERE e.emp_id NOT IN (

    SELECT MIN(emp_id)

    FROM employee

    GROUP BY name, email

);

💡 Explanation:

This query deletes duplicate rows in the EMPLOYEE table, while keeping the row with the minimum emp_id for each combination of name and email.

The subquery identifies the first occurrence (based on the minimum emp_id) of each combination of name and email and keeps it.

The outer query deletes the rest of the rows where the emp_id is not in that list.

🧪 Test Before Deleting

You can verify the duplicates you’re about to delete with this query:

sql

Copy

Edit

SELECT name, email, COUNT(*)

FROM employee

GROUP BY name, email

HAVING COUNT(*) > 1;

This will give you a list of duplicates based on the name and email.

⚠️ Notes:

This method ensures you're keeping one row per duplicate group based on the emp_id — you can adjust it to keep other criteria (like the latest row or the one with the highest salary, etc.).

If your table has more columns and you need a more complex comparison for duplicates (e.g., on multiple columns), you can modify the GROUP BY clause accordingly.

Let me know if you need help adapting this to a more complex schema or business rule!

You said:

Explain me with sample data how this query will delete duplicate rows

ChatGPT said:

Absolutely! Let’s walk through a real example using sample data to show how the query works.

🔹 Sample Table: EMPLOYEE

EMP_ID NAME EMAIL DEPARTMENT

1 John Doe john.doe@email.com HR

2 Jane Smith jane.smith@email.com IT

3 John Doe john.doe@email.com HR

4 Alice Brown alice.brown@email.com Finance

5 John Doe john.doe@email.com HR

6 Jane Smith jane.smith@email.com IT

Here, we have duplicate rows based on the combination of NAME and EMAIL. For example:

There are 3 rows for John Doe with the same email (john.doe@email.com).

There are 2 rows for Jane Smith with the same email (jane.smith@email.com).

Our goal is to delete the duplicates, but keep one row per unique combination of NAME and EMAIL.

🔹 The Query

sql

Copy

Edit

DELETE FROM employee e

WHERE e.emp_id NOT IN (

    SELECT MIN(emp_id)

    FROM employee

    GROUP BY name, email

);

Let’s break it down step-by-step:

1. Subquery:

sql

Copy

Edit

SELECT MIN(emp_id)

FROM employee

GROUP BY name, email;

This part of the query identifies the first occurrence of each duplicate group by selecting the minimum emp_id for each unique combination of NAME and EMAIL.

Output of the subquery:

NAME EMAIL MIN(EMP_ID)

John Doe john.doe@email.com 1

Jane Smith jane.smith@email.com 2

Alice Brown alice.brown@email.com 4

For John Doe and john.doe@email.com, the minimum emp_id is 1.

For Jane Smith and jane.smith@email.com, the minimum emp_id is 2.

For Alice Brown and alice.brown@email.com, the minimum emp_id is 4.

2. Main Query:

sql

Copy

Edit

DELETE FROM employee e

WHERE e.emp_id NOT IN (

    SELECT MIN(emp_id)

    FROM employee

    GROUP BY name, email

);

This deletes all rows where the emp_id is not in the list of minimum emp_ids returned by the subquery.

Applying the query:

It will keep the row with emp_id = 1 (the first row for John Doe).

It will keep the row with emp_id = 2 (the first row for Jane Smith).

It will keep the row with emp_id = 4 (the only row for Alice Brown).

It will delete the other rows for John Doe and Jane Smith because their emp_ids are not the minimum (3 and 5 for John Doe; 6 for Jane Smith).

🔹 Final Result

After running the query, the remaining rows in the EMPLOYEE table will be:

EMP_ID NAME EMAIL DEPARTMENT

1 John Doe john.doe@email.com HR

2 Jane Smith jane.smith@email.com IT

4 Alice Brown alice.brown@email.com Finance

Rows Deleted:

Row with emp_id = 3 for John Doe.

Row with emp_id = 5 for John Doe.

Row with emp_id = 6 for Jane Smith.

✅ Conclusion:

The query successfully keeps one row per duplicate group based on the NAME and EMAIL.

It deletes the extra duplicates while preserving the row with the minimum emp_id for each combination of NAME and EMAIL.

⚠️ What to Keep in Mind:

This query deletes duplicates based on a specific set of columns — in our case, NAME and EMAIL.

You can adjust the query to keep other criteria (e.g., the latest row or highest emp_id).

If you want to delete duplicates based on all columns, you can modify the GROUP BY clause to include all relevant columns.

Let me know if you need any further clarification or adjustments!

You said:

What if John Doe data is also duplicated

ChatGPT said:

Great question! Let's expand the scenario to handle the case where John Doe's data is duplicated across all columns, not just based on NAME and EMAIL.

🔹 New Scenario: All Columns are Considered for Duplicates

Suppose the EMPLOYEE table now looks like this, where all columns are duplicated for John Doe:

EMP_ID NAME EMAIL DEPARTMENT

1 John Doe john.doe@email.com HR

2 Jane Smith jane.smith@email.com IT

3 John Doe john.doe@email.com HR

4 Alice Brown alice.brown@email.com Finance

5 John Doe john.doe@email.com HR

6 Jane Smith jane.smith@email.com IT

In this case, we want to delete all duplicate rows for John Doe, considering all columns (NAME, EMAIL, DEPARTMENT) for comparison.

🔹 The Updated Query

To delete rows where all columns are duplicated, you can modify the GROUP BY clause to group by all the columns in the table:

sql

Copy

Edit

DELETE FROM employee e

WHERE e.emp_id NOT IN (

    SELECT MIN(emp_id)

    FROM employee

    GROUP BY name, email, department

);

💡 How This Query Works:

Subquery:

sql

Copy

Edit

SELECT MIN(emp_id)

FROM employee

GROUP BY name, email, department;

This subquery groups rows by NAME, EMAIL, and DEPARTMENT, and selects the minimum emp_id for each unique combination of these columns.

For example, if there are multiple rows with the same NAME, EMAIL, and DEPARTMENT, it will select the row with the smallest emp_id.

Main Query:

sql

Copy

Edit

DELETE FROM employee e

WHERE e.emp_id NOT IN (

    SELECT MIN(emp_id)

    FROM employee

    GROUP BY name, email, department

);

The outer query deletes rows where the emp_id is not in the list of minimum emp_ids returned by the subquery.

It ensures that only one row per unique combination of NAME, EMAIL, and DEPARTMENT is kept, and duplicates are deleted.

🔹 Sample Data: How the Query Will Work

Given the following table:

EMP_ID NAME EMAIL DEPARTMENT

1 John Doe john.doe@email.com HR

2 Jane Smith jane.smith@email.com IT

3 John Doe john.doe@email.com HR

4 Alice Brown alice.brown@email.com Finance

5 John Doe john.doe@email.com HR

6 Jane Smith jane.smith@email.com IT

The subquery would return:

NAME EMAIL DEPARTMENT MIN(EMP_ID)

John Doe john.doe@email.com HR 1

Jane Smith jane.smith@email.com IT 2

Alice Brown alice.brown@email.com Finance 4

What happens next?

For John Doe, the minimum emp_id is 1, so we keep the row with emp_id = 1 (the first occurrence of John Doe).

For Jane Smith, the minimum emp_id is 2, so we keep the row with emp_id = 2.

For Alice Brown, there is only one row, so we keep it.

The duplicate rows for John Doe (with emp_id = 3 and emp_id = 5) will be deleted.

🔹 Final Result:

After running the query, the table will look like this:

EMP_ID NAME EMAIL DEPARTMENT

1 John Doe john.doe@email.com HR

2 Jane Smith jane.smith@email.com IT

4 Alice Brown alice.brown@email.com Finance

Deleted Rows:

Row with emp_id = 3 for John Doe.

Row with emp_id = 5 for John Doe.

✅ Summary:

The query ensures that only one row per unique combination of NAME, EMAIL, and DEPARTMENT is kept.

It deletes any duplicate rows where all columns (NAME, EMAIL, DEPARTMENT) match.

⚠️ Additional Considerations:

Ordering of rows: If you want to keep a different row (e.g., the latest or the one with the highest emp_id), you can change the MIN(emp_id) to MAX(emp_id) in the subquery.

If you want to identify or prevent any remaining duplicates, you can run this query before deleting:

sql

Copy

Edit

SELECT name, email, department, COUNT(*)

FROM employee

GROUP BY name, email, department

HAVING COUNT(*) > 1;

This will list any combinations that are still duplicated.

PL/SQL Profiler

 

What is PL/SQL Profiler?

The PL/SQL Profiler is a diagnostic tool that helps you:

• Measure performance metrics for PL/SQL programs.

• Identify slow lines or procedures.

• Understand which parts of your code are executed, how often, and how long they take.

It records execution time, number of executions, and ordering of PL/SQL units like procedures, functions, packages, and triggers.

---

⚙️ Tool Behind It: DBMS_PROFILER

Oracle provides a built-in package called DBMS_PROFILER that enables you to profile your PL/SQL code.

---

🧱 How PL/SQL Profiler Works (High-Level)

1. Enable profiling using DBMS_PROFILER.START_PROFILER.

2. Run your PL/SQL block, procedure, or application.

3. Stop profiling using DBMS_PROFILER.STOP_PROFILER.

4. Data is stored in profiler tables like PLSQL_PROFILER_DATA, which you can query to analyze performance.

---

🛠️ Step-by-Step Setup and Usage

🔸 Step 1: Create Profiler Tables (one-time setup)

Oracle doesn’t automatically create profiler tables. Use the profload.sql script provided by Oracle to create them.

Run this as SYS or DBA user:

sql
@$ORACLE_HOME/rdbms/admin/profload.sql

This script creates these key tables:

Table Name	Purpose
PLSQL_PROFILER_RUNS	Stores metadata about each profiling run
PLSQL_PROFILER_UNITS	Stores info about procedures/functions
PLSQL_PROFILER_DATA	Stores line-by-line execution stats

---

🔸 Step 2: Start the Profiler

plsql
BEGIN
  DBMS_PROFILER.START_PROFILER('Test Run of My Procedure');
END;

---

🔸 Step 3: Run the Code You Want to Profile

plsql
BEGIN
  my_package.my_procedure();
END;

---

🔸 Step 4: Stop the Profiler

plsql
BEGIN
  DBMS_PROFILER.STOP_PROFILER;
END;

---

🔸 Step 5: Query the Results

Example: View execution details

sql
SELECT u.unit_name, d.line#, d.total_occur, d.total_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = (SELECT MAX(runid) FROM plsql_profiler_runs)
ORDER BY d.total_time DESC;

This shows:

• Line numbers in the code

• Number of times each line was executed

• Time spent on each line (in hundredths of milliseconds)

---

📊 What You Can Learn from PL/SQL Profiler

Insight	How It Helps
Execution time per line	Pinpoint slow code
Number of executions per line	Identify loops or unnecessary calls
Most frequently called procedures	Optimize heavily-used code paths
Unexecuted code	Detect dead code or missed logic paths

---

⚠️ Important Notes

• Profiling adds some overhead — use it in dev/test environments only.

• It captures CPU time only, not wait time (I/O, locks, etc.).

• Does not profile SQL queries themselves — just PL/SQL logic.

• For SQL tuning, use SQL Trace, AWR, or SQL Monitor.

---

🧠 Bonus: Tools That Visualize Profiler Data

• Oracle SQL Developer: Has a visual profiler UI under Tools → Profiler

• Third-party tools: TOAD, PL/SQL Developer, etc.

• You can also build a custom report using the profiler tables.

---

✅ Summary

Feature	PL/SQL Profiler
What it does	Measures performance of PL/SQL code
How it works	Tracks execution time, frequency, line info
Key package	DBMS_PROFILER
Tables involved	PLSQL_PROFILER_RUNS, UNITS, DATA
Use cases	Tuning slow procedures, identifying hotspots

---

Let me know if you'd like a ready-made SQL Developer Profiler guide or a sample custom report for profiler results!

You said:

Show me with a realistic Prod Scenario ..What all values will be present in each table

ChatGPT said:

Absolutely! Let’s walk through a realistic production-like scenario using the PL/SQL Profiler, showing what it captures and what data you’ll see in the profiler tables.

---

🏭 Real-World Scenario: Monthly Salary Bonus Update

Imagine you're profiling a procedure in production that:

• Processes all employees.

• Calculates a bonus.

• Updates the employees table.

PL/SQL Procedure:

plsql
CREATE OR REPLACE PROCEDURE process_employee_bonus IS
BEGIN
  FOR emp IN (SELECT employee_id, salary FROM employees) LOOP
    UPDATE employees
    SET bonus = salary * 0.10
    WHERE employee_id = emp.employee_id;
  END LOOP;
END;

---

🔍 Profiling Steps

1. Start profiler

2. Run process_employee_bonus

3. Stop profiler

4. Query profiler tables

---

📊 What the Profiler Captures

🔹 1. PLSQL_PROFILER_RUNS

RUNID	RUN_COMMENT	START_TIME	END_TIME
101	'Bonus Calculation Run'	06-APR-25 10:02:11	06-APR-25 10:02:15

This table shows metadata for the entire profiling session.

---

🔹 2. PLSQL_PROFILER_UNITS

RUNID	UNIT_NUMBER	UNIT_NAME	UNIT_TYPE	LINE_COUNT	LANGUAGE
101	1	PROCESS_EMPLOYEE_BONUS	PROCEDURE	10	PL/SQL

Shows which program units (procedures, packages, triggers, etc.) were profiled and how many lines they contain.

---

🔹 3. PLSQL_PROFILER_DATA

This table contains the core performance data per line of code.

RUNID	UNIT_NUMBER	LINE#	TOTAL_OCCUR	TOTAL_TIME
101	1	2	1	5
101	1	3	1	2
101	1	4	1000	50
101	1	5	1000	500
101	1	6	1	1

Explanation:

• LINE 4–5: These are the FOR LOOP and UPDATE — run 1000 times (one per employee).

• TOTAL_TIME: Time spent per line in hundredths of milliseconds (e.g., 500 = 5ms).

• Line 5 (the UPDATE) clearly takes the most time.

---

🔁 Query to See Top Time-Consuming Lines

sql
SELECT u.unit_name, d.line#, d.total_occur, d.total_time
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON d.runid = u.runid AND d.unit_number = u.unit_number
WHERE d.runid = 101
ORDER BY d.total_time DESC;

You’ll instantly spot that the UPDATE line dominates runtime.

---

🧠 What You Learn

• You get execution frequency per line — to identify hotspots or loops.

• You see total time per line — to focus optimization efforts.

• You detect unexecuted lines (i.e., dead code).

• You spot inefficient SQL inside PL/SQL — like UPDATE in a loop.

---

✅ Production Tuning Example

Problem Identified: Line with UPDATE inside the loop took most of the time.

Optimization Idea: Replace with a bulk update or use FORALL.

---

📌 Summary of Profiler Tables (Simplified)

Table Name	Description
PLSQL_PROFILER_RUNS	Overall metadata per profiling session
PLSQL_PROFILER_UNITS	Each PL/SQL program unit (procedure/function)
PLSQL_PROFILER_DATA	Line-level performance: time spent & executions

---

Would you like a full script that sets up the profiler, runs this test procedure, and queries results automatically? I can generate that for you step-by-step.

Yes! ✅ The PL/SQL Profiler is officially available in Oracle Database, but there are a few things to know based on Oracle version and edition.

---

📦 PL/SQL Profiler Availability

Feature	Available In
DBMS_PROFILER	✅ Yes — Included in Oracle 8i+
Profiler tables (profload.sql)	✅ Yes — Must be manually installed
SQL Developer GUI support	✅ Yes — For versions that support profiling
Oracle Editions	✅ Enterprise, Standard, XE (with some limitations)

⚠️ Note: Oracle Cloud services (Autonomous DB) do not support DBMS_PROFILER directly — you would need alternative tracing or AWR tools.

---

📍 Where It Lives

• The DBMS_PROFILER package is in the schema: SYS

• The profiler tables are created via:

  sql
  @$ORACLE_HOME/rdbms/admin/profload.sql
  

This creates:

• PLSQL_PROFILER_RUNS

• PLSQL_PROFILER_UNITS

• PLSQL_PROFILER_DATA

If you're in SQL Developer, you can also run profiling using the GUI:

Tools → PL/SQL Profiler

---

🧪 How to Check if Profiler Is Installed

Run this:

sql
SELECT * FROM all_objects WHERE object_name = 'DBMS_PROFILER';

If it returns results — ✅ it’s available.

---

🔐 Privileges Needed

To use DBMS_PROFILER, the user needs:

sql
GRANT EXECUTE ON DBMS_PROFILER TO your_user;

Also, to insert into the profiler tables:

sql
GRANT INSERT, UPDATE, DELETE ON PLSQL_PROFILER_RUNS TO your_user;
GRANT INSERT, UPDATE, DELETE ON PLSQL_PROFILER_UNITS TO your_user;
GRANT INSERT, UPDATE, DELETE ON PLSQL_PROFILER_DATA TO your_user;

---

🔁 Alternative in Newer Oracle Versions

If you're on Oracle 12c or later, you can also use:

• Hierarchical Profiler (DBMS_HPROF) — deeper analysis with call graphs.

• SQL Trace / TKPROF / AWR / SQL Monitor — more on SQL + waits.

---

✅ Summary

Feature	Supported in Oracle?
DBMS_PROFILER Package	✅ Yes
SQL Developer Profiler UI	✅ Yes
Profiler Tables	✅ Yes (via profload.sql)
Cloud/Autonomous DB	❌ Not directly supported

---

Want a quick script to check availability and install the profiler tables in your DB? I can give you a full setup script if you're ready to try it.

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!