Monitor network speed

 sar -n ALL 1

How to Check SQL_IDs Having Multiple PLAN_HASH_VALUE ?

identify SQL_IDs having multiple PLAN_HASH_VALUEs by grouping on SQL_ID and counting distinct PLAN_HASH_VALUE. This is very useful for finding SQLs with plan instability or plan changes.

Oracle execution plans can be checked from the cursor cache using views like V$SQL, and detailed plans can be displayed using DBMS_XPLAN.DISPLAY_CURSOR .

Oracle documents that DBMS_XPLAN can display plans from cached cursors, AWR, SQL tuning sets, and SQL plan baseline

---

1. Check SQL_IDs Having Multiple PLAN_HASH_VALUEs in Current Memory

Use this query on V$SQL.

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions) AS total_executions

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY plan_count DESC, total_executions DESC;

Meaning

• SQL_ID: SQL statement identifier
• PLAN_COUNT: number of different plans used by the SQL
• PLAN_HASH_VALUES: list of plan hash values
• TOTAL_EXECUTIONS: total executions from cursor cache

If one SQL_ID has more than one PLAN_HASH_VALUE, it means Oracle has generated multiple execution plans for the same SQL.

---

2. Detailed Child Cursor Level Query

This gives more detail for each SQL_ID, child cursor, and plan hash value.

SELECT sql_id,

child_number,

plan_hash_value,

executions,

parsing_schema_name,

optimizer_mode,

loaded_versions,

invalidations,

first_load_time,

last_active_time

FROM v$sql

WHERE sql_id IN (

SELECT sql_id

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

)

ORDER BY sql_id, plan_hash_value, child_number;

This is useful when you want to see which child cursor used which plan.

---

3. Group PLAN_HASH_VALUE Against Each SQL_ID

If you want a grouped summary of all SQL_IDs and their plan hash values:

SELECT sql_id,

plan_hash_value,

COUNT(*) AS child_cursor_count,

SUM(executions) AS executions,

MIN(first_load_time) AS first_load_time,

MAX(last_active_time) AS last_active_time

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id, plan_hash_value

ORDER BY sql_id, executions DESC;

This output shows one row per:

SQL_ID + PLAN_HASH_VALUE

Example output:

SQL_ID PLAN_HASH_VALUE CHILD_CURSOR_COUNT EXECUTIONS

------------- --------------- ------------------ ----------

abc123xyz789 1234567890 2 500

abc123xyz789 9876543210 1 20

def456pqr111 5555555555 1 100

---

4. Check Only One Specific SQL_ID

If you want to check plan hash values for one SQL ID:

SELECT sql_id,

plan_hash_value,

COUNT(*) AS child_cursor_count,

SUM(executions) AS total_executions,

MIN(child_number) AS min_child_number,

MAX(child_number) AS max_child_number

FROM v$sql

WHERE sql_id = '&sql_id'

GROUP BY sql_id, plan_hash_value

ORDER BY total_executions DESC;

Example:

SELECT sql_id,

plan_hash_value,

COUNT() AS child_cursor_count,

SU(executions) AS total_executions,

* MIN(child_number) AS min_chi*d_number,

MAX(child_number)AS max_child_number

FROM v$sql

WERE sql_id = '5g8t1m9n2abc3'

GROUP BY sql_id, plan_hash_value

ORDER BY total_executions DESC;

---

5. Historical Check from AWR

If you want to check SQL_IDs with multiple plans historically, use DBA_HIST_SQLSTAT.

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions_delta) AS total_executions

FROM dba_hist_sqlstat

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY plan_count DESC, total_executions DESC;

Use this when the SQL is not present in V$SQL, or you want to analyze old plan changes. Oracle documents that DBMS_XPLAN.DISPLAY_AWR can display execution plans stored in AWR. [docs.oracle.com]

---

6. Historical Grouping by SQL_ID and PLAN_HASH_VALUE

SELECT sql_id,

plan_hash_value,

SUM(executions_delta) AS executions,

ROUND(SUM(elapsed_time_delta) / 1000000, 2) AS elapsed_seconds,

ROUND(SUM(cpu_time_delta) / 1000000, 2) AS cpu_seconds,

SUM(buffer_gets_delta) AS buffer_gets,

SUM(disk_reads_delta) AS disk_reads,

MIN(snap_id) AS first_snap_id,

MAX(snap_id) AS last_snap_id

FROM dba_hist_sqlstat

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id, plan_hash_value

ORDER BY sql_id, executions DESC;

This is good for performance comparison between plans.

---

7. Best Query for Plan Instability Report

For DBA troubleshooting, this is usually the most useful query:

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_count,

SUM(executions_delta) AS total_executions,

ROUND(SUM(elapsed_time_delta) / 1000000, 2) AS total_elapsed_sec,

ROUND(SUM(cpu_time_delta) / 1000000, 2) AS total_cpu_sec,

SUM(buffer_gets_delta) AS total_buffer_gets,

SUM(disk_reads_delta) AS total_disk_reads

FROM dba_hist_sqlstat

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY total_elapsed_sec DESC;

This shows SQLs that had multiple plans and consumed the most elapsed time.

---

8. RAC-Aware Query

If your database is RAC, include INST_ID from GV$SQL.

SELECT inst_id,

sql_id,

plan_hash_value,

COUNT(*) AS child_cursor_count,

SUM(executions) AS executions,

MAX(last_active_time) AS last_active_time

FROM gv$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY inst_id, sql_id, plan_hash_value

ORDER BY sql_id, inst_id, executions DESC;

To find SQL_IDs with multiple plans across RAC:

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions) AS total_executions

FROM gv$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY plan_count DESC, total_executions DESC;

---

9. Display Execution Plan for Each PLAN_HASH_VALUE

After finding multiple plan hash values, display the plan using:

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '&sql_id',

cursor_child_no => NULL,

format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'

)

);

For historical AWR plan:

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_AWR(

sql_id => '&sql_id',

plan_hash_value => &plan_hash_value,

format => 'TYPICAL'

)

);

---

Recommended Quick Script

SET LINESIZE 220

SET PAGESIZE 100

COLUMN sql_id FORMAT A15

COLUMN plan_hash_values FORMAT A80

PROMPT === SQL_IDs with Multiple Plan Hash Values from V$SQL ===

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions) AS total_executions

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY plan_count DESC, total_executions DESC;

---

Plan Hash Grouping Summary

Use this if your goal is simply:

“Group plan hash value against SQL ID.”

SELECT sql_id,

plan_hash_value,

COUNT(*) AS cursor_count,

SUM(executions) AS executions

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id, plan_hash_value

ORDER BY sql_id, executions DESC;

SQL_IDs having multiple plan hash values and executions more than 5

SELECT sql_id,

       plan_hash_value,

       COUNT(*) AS child_cursor_count,

       SUM(executions) AS executions,

       MIN(first_load_time) AS first_load_time,

       MAX(last_active_time) AS last_active_time

FROM   v$sql

WHERE  sql_id IS NOT NULL

AND    plan_hash_value <> 0

GROUP BY sql_id, plan_hash_value

HAVING SUM(executions) > 5

ORDER BY sql_id, executions DESC;

SQL_IDs Having More Than One PLAN_HASH_VALUE

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_hash_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions) AS total_executions

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY plan_hash_count DESC, total_executions DESC;

Yes, to find SQL_IDs where PLAN_HASH_VALUE is more than 1, meaning the same SQL_ID has used multiple execution plans, use COUNT(DISTINCT plan_hash_value) > 1.

SQL_IDs Having More Than One PLAN_HASH_VALUE

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_hash_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions) AS total_executions

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY plan_hash_count DESC, total_executions DESC;

If You Also Want Executions Greater Than 5

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_hash_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions) AS total_executions

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

AND SUM(executions) > 5

ORDER BY plan_hash_count DESC, total_executions DESC;

Detailed Output Per SQL_ID and PLAN_HASH_VALUE

Use this after identifying SQL_IDs with multiple plans:

SELECT sql_id,

plan_hash_value,

COUNT(*) AS child_cursor_count,

SUM(executions) AS executions,

MIN(first_load_time) AS first_load_time,

MAX(last_active_time) AS last_active_time

FROM v$sql

WHERE sql_id IN (

SELECT sql_id

FROM v$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

)

AND plan_hash_value <> 0

GROUP BY sql_id, plan_hash_value

ORDER BY sql_id, executions DESC;

Best RAC Version Using GV$SQL

If it is a RAC database, use this:

SELECT sql_id,

COUNT(DISTINCT plan_hash_value) AS plan_hash_count,

LISTAGG(DISTINCT plan_hash_value, ', ')

WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,

SUM(executions) AS total_executions

FROM gv$sql

WHERE sql_id IS NOT NULL

AND plan_hash_value <> 0

GROUP BY sql_id

HAVING COUNT(DISTINCT plan_hash_value) > 1

ORDER BY plan_hash_count DESC, total_executions DESC;

Key Condition

HAVING COUNT(DISTINCT plan_hash_value) > 1

This is the main condition to find SQL IDs having multiple plan hash values.

DBA Tip

If a SQL_ID has multiple PLAN_HASH_VALUEs, check:

• Statistics changes
• Bind peeking
• Adaptive cursor sharing
• Different optimizer environment
• Index creation or drop
• SQL profile
• SQL baseline
• Object invalidation
• RAC instance-specific plan difference

Step by Step How to Check PLAN_HASH_VALUE Against SQL_ID in Oracle ?

 Check PLAN_HASH_VALUE Against SQL_ID in Oracle ?

1. Objective

To verify which PLAN_HASH_VALUE is associated with a specific SQL_ID in Oracle.

This is useful when:

• Checking if SQL execution plan changed
• Troubleshooting SQL performance issues
• Comparing current and historical plans
• Validating SQL Plan Baseline usage
• Investigating plan regression

---

2. Required Input

You need the SQL ID.

Example:

SQL_ID = '5g8t1m9n2abc3'

If you do not have the SQL ID, you can search it from V$SQL using part of the SQL text.

SELECT sql_id,

child_number,

plan_hash_value,

sql_text

FROM v$sql

WHERE sql_text LIKE '%your_unique_sql_text%'

AND sql_text NOT LIKE '%v$sql%';

---

3. Check Current PLAN_HASH_VALUE from Cursor Cache

Use this query when the SQL is currently present in the shared pool.

SET LINESIZE 200

SET PAGESIZE 100

SELECT sql_id,

child_number,

plan_hash_value,

executions,

parsing_schema_name,

last_active_time

FROM v$sql

WHERE sql_id = '&sql_id'

ORDER BY child_number;

Example

SELECT sql_id,

child_number,

plan_hash_value,

executions,

parsing_schema_name,

last_active_time

FROM v$sql

WHERE sql_id = '5g8t1m9n2abc3'

ORDER BY child_number;

How to Read the Output

If output shows:

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS

------------- ------------ --------------- ----------

5g8t1m9n2abc3 0 1234567890 150

5g8t1m9n2abc3 1 9876543210 25

It means the same SQL ID has multiple child cursors and multiple execution plans.

---

4. Check Distinct PLAN_HASH_VALUE for a SQL_ID

Use this if you only want the list of unique plan hash values.

SELECT DISTINCT

sql_id,

plan_hash_value

FROM v$sql

WHERE sql_id = '&sql_id'

ORDER BY plan_hash_value;

Example

SELECT DISTINCT

sql_id,

plan_hash_value

FROM v$sql

WHERE sql_id = '5g8t1m9n2abc3'

ORDER BY plan_hash_value;

---

5. Check PLAN_HASH_VALUE with Execution Statistics

This query helps identify which plan is used most frequently.

SELECT sql_id,

plan_hash_value,

COUNT(*) child_count,

SUM(executions) total_executions,

MIN(first_load_time) first_load_time,

MAX(last_active_time) last_active_time

FROM v$sql

WHERE sql_id = '&sql_id'

GROUP BY sql_id, plan_hash_value

ORDER BY total_executions DESC;

Interpretation

• CHILD_COUNT shows how many child cursors used the same plan.
• TOTAL_EXECUTIONS shows how often that plan was executed.
• Multiple PLAN_HASH_VALUE entries can indicate plan changes or different optimizer choices.

---

6. Display Current Execution Plan Using DBMS_XPLAN

Use DBMS_XPLAN.DISPLAY_CURSOR to see the actual cached execution plan. Oracle documentation states that DISPLAY_CURSOR displays the execution plan of a loaded cursor.

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '&sql_id',

cursor_child_no => NULL,

format => 'ALLSTATS LAST'

)

);

Example

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '5g8t1m9n2abc3',

cursor_child_no => NULL,

format => 'ALLSTATS LAST'

)

);

Notes

• cursor_child_no => NULL displays all child cursors for that SQL ID.
• ALLSTATS LAST shows actual runtime statistics for the last execution if statistics are available.
• If no rows are returned, the SQL may not be available in the cursor cache.

---

7. Display Plan for a Specific Child Cursor

If the SQL ID has multiple child cursors, check a specific one.

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '&sql_id',

cursor_child_no => &child_number,

format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'

)

);

Example

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '5g8t1m9n2abc3',

cursor_child_no => 0,

format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'

)

);

This is useful when bind values, optimizer outlines, or child cursor differences need to be reviewed.

---

8. Check Historical PLAN_HASH_VALUE from AWR

If the SQL is no longer in memory, check AWR history.

SELECT sql_id,

plan_hash_value,

SUM(executions_delta) executions,

MIN(snap_id) first_snap_id,

MAX(snap_id) last_snap_id

FROM dba_hist_sqlstat

WHERE sql_id = '&sql_id'

GROUP BY sql_id, plan_hash_value

ORDER BY first_snap_id;

Example

SELECT sql_id,

plan_hash_value,

SUM(executions_delta) executions,

MIN(snap_id) first_snap_id,

MAX(snap_id) last_snap_id

FROM dba_hist_sqlstat

WHERE sql_id = '5g8t1m9n2abc3'

GROUP BY sql_id, plan_hash_value

ORDER BY first_snap_id;

Oracle documentation confirms that DBMS_XPLAN can display execution plans stored in AWR using DISPLAY_AWR

---

9. Display Historical Execution Plan from AWR

Use this when you know the historical PLAN_HASH_VALUE.

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_AWR(

sql_id => '&sql_id',

plan_hash_value => &plan_hash_value,

format => 'TYPICAL'

)

);

Example

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_AWR(

sql_id => '5g8t1m9n2abc3',

plan_hash_value => 1234567890,

format => 'TYPICAL'

)

);

---

10. Check Plan Changes Over Time

This query shows when each plan was used across AWR snapshots.

SELECT s.begin_interval_time,

s.end_interval_time,

st.sql_id,

st.plan_hash_value,

st.executions_delta,

ROUND(st.elapsed_time_delta / 1000000, 2) elapsed_sec,

ROUND(st.cpu_time_delta / 1000000, 2) cpu_sec,

st.buffer_gets_delta,

st.disk_reads_delta

FROM dba_hist_sqlstat st

JOIN dba_hist_snapshot s

ON st.snap_id = s.snap_id

AND st.dbid = s.dbid

AND st.instance_number = s.instance_number

WHERE st.sql_id = '&sql_id'

ORDER BY s.begin_interval_time, st.plan_hash_value;

This is helpful for identifying whether a SQL performance issue started after a plan change.

---

11. Check SQL Plan Baseline for the SQL_ID

If SQL Plan Management is used, check whether the SQL has a baseline.

SELECT sql_handle,

plan_name,

enabled,

accepted,

fixed,

optimizer_cost,

created,

last_executed

FROM dba_sql_plan_baselines

WHERE signature IN (

SELECT exact_matching_signature

FROM v$sql

WHERE sql_id = '&sql_id'

);

Oracle documentation states that DBMS_XPLAN can also display execution plans from SQL plan baselines. 

---

12. Display SQL Plan Baseline

If you get a SQL_HANDLE from the previous query, use:

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

sql_handle => '&sql_handle',

format => 'TYPICAL'

)

);

---

13. Complete Quick-Check Script

Use this as a ready-to-run script.

SET LINESIZE 220

SET PAGESIZE 100

COLUMN sql_id FORMAT A15

COLUMN parsing_schema_name FORMAT A20

COLUMN first_load_time FORMAT A25

COLUMN last_active_time FORMAT A30

ACCEPT sql_id_input CHAR PROMPT 'Enter SQL_ID: '

PROMPT

PROMPT === Current Cursor Cache Plan Hash Values ===

SELECT sql_id,

child_number,

plan_hash_value,

executions,

parsing_schema_name,

first_load_time,

last_active_time

FROM v$sql

WHERE sql_id = '&sql_id_input'

ORDER BY child_number;

PROMPT

PROMPT === Distinct Current Plan Hash Values ===

SELECT DISTINCT

sql_id,

plan_hash_value

FROM v$sql

WHERE sql_id = '&sql_id_input'

ORDER BY plan_hash_value;

PROMPT

PROMPT === Historical AWR Plan Hash Values ===

SELECT sql_id,

plan_hash_value,

SUM(executions_delta) executions,

MIN(snap_id) first_snap_id,

MAX(snap_id) last_snap_id

FROM dba_hist_sqlstat

WHERE sql_id = '&sql_id_input'

GROUP BY sql_id, plan_hash_value

ORDER BY first_snap_id;

PROMPT

PROMPT === Current Execution Plan from Cursor Cache ===

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '&sql_id_input',

cursor_child_no => NULL,

format => 'ALLSTATS LAST'

)

);

---

14. Common Issues and Checks

Issue 1: No rows from V$SQL

Possible reasons:

• SQL aged out of shared pool
• SQL not executed recently
• Wrong SQL ID
• Query executed in another PDB or RAC instance

Use AWR:

SELECT sql_id,

plan_hash_value,

SUM(executions_delta) executions

FROM dba_hist_sqlstat

WHERE sql_id = '&sql_id'

GROUP BY sql_id, plan_hash_value;

Issue 2: Multiple PLAN_HASH_VALUE values

Possible reasons:

• Bind peeking
• Adaptive cursor sharing
• Statistics changed
• Different optimizer environment
• SQL profile or baseline added
• Object/index changes

Issue 3: DBMS_XPLAN.DISPLAY_CURSOR gives insufficient data

Try enhanced format:

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '&sql_id',

cursor_child_no => NULL,

format => 'ADVANCED ALLSTATS LAST +PEEKED_BINDS +OUTLINE'

)

);

---

Final Recommended Flow

Step 1: Confirm SQL_ID

Step 2: Check V$SQL for current PLAN_HASH_VALUE

Step 3: Check distinct PLAN_HASH_VALUE values

Step 4: Display current execution plan using DBMS_XPLAN.DISPLAY_CURSOR

Step 5: Check DBA_HIST_SQLSTAT for historical plans

Step 6: Display old plan using DBMS_XPLAN.DISPLAY_AWR

Step 7: Check SQL Plan Baseline if plan stability is required

Best Quick Query

If you need only one query to check the plan hash value for a SQL ID:

SELECT sql_id,

child_number,

plan_hash_value,

executions,

last_active_time

FROM v$sql

WHERE sql_id = '&sql_id'

ORDER BY child_number;

Troubleshooting Privileges

You may need access to views like V$SQL, V$SQL_PLAN, V$SESSION, and V$SQL_PLAN_STATISTICS_ALL for DISPLAY_CURSOR; Oracle documents these privilege requirements for DBMS_XPLAN.DISPLAY_CURSOR. 

How to check execution plan hash value (PLAN_HASH_VALUE) for a specific SQL_ID ?

Check  execution plan hash value (PLAN_HASH_VALUE) for a specific SQL_ID 

If you want to check the execution plan hash value (PLAN_HASH_VALUE) for a specific SQL_ID in Oracle, use one of these queries:

Current Cursor Cache (V$SQL)

SELECT sql_id,

plan_hash_value,

executions,

parsing_schema_name

FROM v$sql

WHERE sql_id = '&SQL_ID';

Historical Plans from AWR (DBA_HIST_SQLSTAT)

SELECT sql_id,

plan_hash_value,

SUM(executions_delta) executions

FROM dba_hist_sqlstat

WHERE sql_id = '&SQL_ID'

GROUP BY sql_id, plan_hash_value

ORDER BY executions DESC;

``

Get Detailed Plan Information (DBMS_XPLAN)

For the current plan in memory:

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '&SQL_ID',

format => 'ALLSTATS LAST'

)

);

Check All Plans Associated with a SQL_ID

SELECT DISTINCT

sql_id,

plan_hash_value

FROM v$sql

WHERE sql_id = '&SQL_ID'

ORDER BY plan_hash_value;

Check SQL Plan Baselines (if used)

SELECT sql_handle,

plan_name,

enabled,

accepted

FROM dba_sql_plan_baselines

WHERE signature IN (

SELECT exact_matching_signature

FROM v$sql

WHERE sql_id = '&SQL_ID'

);

Example

For SQL_ID 5g8t1m9n2abc3:

SELECT sql_id, plan_hash_value

FROM v$sql

WHERE sql_id = '5g8t1m9n2abc3';

This will return the plan hash value(s) Oracle is currently using for that SQL statement. If multiple rows are returned, the SQL has been executed with different plans (e.g., due to Adaptive Plans, bind peeking, or plan evolution).

How to Run Oracle SQL tuning Advisor against sql_id ?

 Run SQL tuning Advisor against sql_id :

@?/rdbms/admin/sqltrpt.sql

TASK_13481

It will prompt you for the SQL details, usually SQL_ID, tuning scope, and report options depending on your Oracle version.

1. Check available SQL Tuning Advisor tasks

Run as DBA/SYS:

SET LINESIZE 220

COL owner FORMAT A20

COL task_name FORMAT A35

COL advisor_name FORMAT A35

COL status FORMAT A15

COL created FORMAT A20

COL last_modified FORMAT A20

SELECT owner,

task_name,

advisor_name,

status,

TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,

TO_CHAR(last_modified, 'DD-MON-YYYY HH24:MI:SS') AS last_modified

FROM dba_advisor_tasks

WHERE advisor_name = 'SQL Tuning Advisor'

ORDER BY created DESC;

For your specific task:

SELECT owner,

task_name,

advisor_name,

status,

execution_type,

TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,

TO_CHAR(execution_start, 'DD-MON-YYYY HH24:MI:SS') AS execution_start,

TO_CHAR(execution_end, 'DD-MON-YYYY HH24:MI:SS') AS execution_end

FROM dba_advisor_tasks

WHERE task_name = 'TASK_13481';

Expected status should ideally be:

COMPLETED

If the task is not completed, do not accept the profile yet.

---

2. Check SQL Tuning Advisor executions for the task

COL task_name FORMAT A35

COL execution_name FORMAT A35

COL status FORMAT A15

SELECT owner,

task_name,

execution_name,

status,

TO_CHAR(execution_start, 'DD-MON-YYYY HH24:MI:SS') AS execution_start,

TO_CHAR(execution_end, 'DD-MON-YYYY HH24:MI:SS') AS execution_end

FROM dba_advisor_executions

WHERE task_name = 'TASK_13481'

ORDER BY execution_start DESC;

---

3. Check findings for the task

SET LINESIZE 220

COL type FORMAT A30

COL message FORMAT A100

COL more_info FORMAT A100

SELECT owner,

task_name,

finding_id,

type,

message,

more_info

FROM dba_advisor_findings

WHERE task_name = 'TASK_13481'

ORDER BY finding_id;

Look for findings related to:

SQL Profile

Statistics

Index

Restructure SQL

---

4. Check recommendations for the task

SET LINESIZE 220

COL type FORMAT A30

COL benefit FORMAT 999999999

COL rationale FORMAT A120

SELECT owner,

task_name,

rec_id,

type,

rank,

benefit,

rationale

FROM dba_advisor_recommendations

WHERE task_name = 'TASK_13481'

ORDER BY rank, rec_id;

If SQL Profile is recommended, you should see a recommendation type related to SQL Profile.

---

5. Generate SQL Tuning Advisor report before accepting profile

SET LONG 10000000

SET LONGCHUNKSIZE 10000000

SET LINESIZE 220

SET PAGESIZE 50000

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(

task_name => 'TASK_13481',

type => 'TEXT',

level => 'ALL',

section => 'ALL',

owner_name => 'SYS'

) AS report

FROM dual;

If your Oracle version does not accept owner_name, use:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(

task_name => 'TASK_13481',

type => 'TEXT',

level => 'ALL',

section => 'ALL'

) AS report

FROM dual;

Review the report carefully before accepting. SQL Tuning Advisor can recommend SQL Profiles, indexes, stats collection, SQL rewrite, or SQL Plan Baselines.

---

6. Check if SQL Profile already exists

Before running ACCEPT_SQL_PROFILE, check existing profiles:

SET LINESIZE 220

COL name FORMAT A40

COL category FORMAT A20

COL status FORMAT A10

COL force_matching FORMAT A15

COL sql_text FORMAT A80

SELECT name,

category,

status,

force_matching,

created,

last_modified,

SUBSTR(sql_text, 1, 80) AS sql_text

FROM dba_sql_profiles

ORDER BY created DESC;

Search for a profile related to this task or SQL:

SELECT name,

category,

status,

force_matching,

created,

last_modified,

SUBSTR(sql_text, 1, 120) AS sql_text

FROM dba_sql_profiles

WHERE name LIKE '%13481%'

OR description LIKE '%TASK_13481%'

ORDER BY created DESC;

---

7. Accept SQL Profile from the task

Your command is broadly correct:

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(

task_name => 'TASK_13481',

task_owner => 'SYS',

replace => TRUE

);

I usually prefer giving the profile a meaningful name:

DECLARE

l_profile_name VARCHAR2(128);

BEGIN

l_profile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(

task_name => 'TASK_13481',

task_owner => 'SYS',

name => 'SP_TASK_13481',

replace => TRUE,

force_match => FALSE

);

DBMS_OUTPUT.PUT_LINE('Accepted SQL Profile: ' || l_profile_name);

END;

/

Use this only after the report confirms a SQL Profile recommendation with good estimated benefit.

---

8. Verify the accepted SQL Profile

SELECT name,

category,

status,

force_matching,

created,

last_modified,

SUBSTR(sql_text, 1, 120) AS sql_text

FROM dba_sql_profiles

WHERE name = 'SP_TASK_13481';

Check if it is enabled:

SELECT name,

status

FROM dba_sql_profiles

WHERE name = 'SP_TASK_13481';

Expected:

ENABLED

---

9. Disable or drop if needed

Disable profile:

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => 'SP_TASK_13481',

attribute_name => 'STATUS',

value => 'DISABLED'

);

END;

/

Drop profile:

BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE(

name => 'SP_TASK_13481',

ignore => TRUE

);

END;

/

Recommended validation sequence

For your case, run in this order:

SELECT owner, task_name, advisor_name, status

FROM dba_advisor_tasks

WHERE task_name = 'TASK_13481';

SELECT owner, task_name, rec_id, type, rank, benefit

FROM dba_advisor_recommendations

WHERE task_name = 'TASK_13481'

ORDER BY rank;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(

task_name => 'TASK_13481',

type => 'TEXT',

level => 'ALL',

section => 'ALL',

owner_name => 'SYS'

)

FROM dual;

Only after confirming the recommendation, execute:

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_13481', task_owner => 'SYS', replace => TRUE);

How to know and troubleshoot how much time a session has waited on each wait event ?

 If you want to know how much time a session has waited on each wait event, here are the most useful queries.

1. Current Session Wait Information

For a specific session:

SELECT

sid,

serial#,

username,

event,

wait_class,

state,

seconds_in_wait

FROM v$session

WHERE sid = &SID;

This shows the current wait event and how long the session has been waiting.

---

2. Session Wait History (Recent Waits)

SELECT

sid,

event,

wait_time_micro/1000 AS wait_time_ms,

time_since_last_wait_micro/1000 AS since_last_wait_ms,

wait_count

FROM v$session_event

WHERE sid = &SID

ORDER BY wait_time_micro DESC;

Output example:

EVENT WAIT_TIME_MS WAIT_COUNT

------------------------ ------------ ----------

db file sequential read 150000 5000

log file sync 25000 1000

enq: TX row lock contention 10000 100

This is usually the first query I use.

---

3. Total Time Spent by a Session in Each Wait Event

SELECT

sid,

event,

total_waits,

ROUND(time_waited_micro/1000000,2) wait_time_sec,

ROUND(time_waited_micro/

NULLIF(total_waits,0)/1000,2) avg_wait_ms

FROM v$session_event

WHERE sid = &SID

ORDER BY time_waited_micro DESC;

Example:

EVENT TOTAL_WAITS WAIT_TIME_SEC AVG_WAIT_MS

------------------------ ----------- ------------ -----------

db file sequential read 50000 1200 24

log file sync 10000 150 15

---

4. All Active Sessions with Their Top Waits

SELECT

se.sid,

s.serial#,

s.username,

se.event,

se.total_waits,

ROUND(se.time_waited_micro/1000000,2) wait_time_sec

FROM v$session_event se

JOIN v$session s

ON se.sid = s.sid

WHERE s.username IS NOT NULL

ORDER BY se.time_waited_micro DESC;

---

5. Using ASH (Last 1 Hour)

If Diagnostics Pack is licensed:

SELECT

session_id,

sql_id,

event,

COUNT() samples,

ROUND(COUNT()*10,2) wait_seconds

FROM v$active_session_history

WHERE sample_time > SYSDATE - 1/24

AND session_id = &SID

GROUP BY session_id, sql_id, event

ORDER BY samples DESC;

Note: Each ASH sample ≈ 1 second.

---

6. Historical Session Waits from AWR

SELECT

ash.session_id,

ash.sql_id,

ash.event,

COUNT() samples,

ROUND(COUNT()*10,2) wait_seconds

FROM dba_hist_active_sess_history ash

WHERE ash.session_id = &SID

GROUP BY ash.session_id,

ash.sql_id,

ash.event

ORDER BY samples DESC;

---

7. Find the Top Wait Event for a Session

SELECT *

FROM (

SELECT

event,

total_waits,

ROUND(time_waited_micro/1000000,2) wait_time_sec

FROM v$session_event

WHERE sid = &SID

ORDER BY time_waited_micro DESC

)

WHERE ROWNUM = 1;

---

Most Useful DBA Query

Replace 123 with the session ID:

SELECT

event,

total_waits,

ROUND(time_waited_micro/1000000,2) total_wait_sec,

ROUND(time_waited_micro/

NULLIF(total_waits,0)/1000,2) avg_wait_ms

FROM v$session_event

WHERE sid = 123

ORDER BY time_waited_micro DESC;

To get the total wait time aggregated by wait event across all logged-in sessions, use:

SELECT

se.event,

SUM(se.total_waits) AS total_waits,

ROUND(SUM(se.time_waited_micro)/1000000, 2) AS total_wait_time_sec,

ROUND(

SUM(se.time_waited_micro) /

NULLIF(SUM(se.total_waits), 0) / 1000,

2

) AS avg_wait_ms

FROM v$session_event se

JOIN v$session s

ON se.sid = s.sid

WHERE s.username IS NOT NULL

GROUP BY se.event

ORDER BY total_wait_time_sec DESC;

If you also want to see the number of sessions affected by each wait event:

SELECT

se.event,

COUNT(DISTINCT se.sid) AS sessions,

SUM(se.total_waits) AS total_waits,

ROUND(SUM(se.time_waited_micro)/1000000, 2) AS total_wait_time_sec,

ROUND(

SUM(se.time_waited_micro) /

NULLIF(SUM(se.total_waits), 0) / 1000,

2

) AS avg_wait_ms

FROM v$session_event se

JOIN v$session s

ON se.sid = s.sid

WHERE s.username IS NOT NULL

GROUP BY se.event

ORDER BY total_wait_time_sec DESC;

To identify the top wait events by active user:

SELECT

s.username,

se.event,

SUM(se.total_waits) AS total_waits,

ROUND(SUM(se.time_waited_micro)/1000000, 2) AS wait_time_sec

FROM v$session_event se

JOIN v$session s

ON se.sid = s.sid

WHERE s.username IS NOT NULL

GROUP BY s.username, se.event

ORDER BY wait_time_sec DESC;

And if you're troubleshooting performance, exclude idle waits:

SELECT

se.event,

SUM(se.total_waits) total_waits,

ROUND(SUM(se.time_waited_micro)/1000000,2) wait_time_sec

FROM v$session_event se

JOIN v$event_name en

ON se.event = en.name

WHERE en.wait_class <> 'Idle'

GROUP BY se.event

ORDER BY wait_time_sec DESC;

This last query is typically the most useful because it highlights only the waits that contribute to database response-time issues.

How to troubleshoot and get to know about current top wait events since oracle database instance startup ?

 To get the current top wait events since instance startup, use:

SELECT *

FROM (

SELECT

event,

total_waits,

ROUND(time_waited_micro/1000000,2) AS time_waited_sec,

ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) AS avg_wait_ms

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY time_waited_micro DESC

)

WHERE ROWNUM <= 20;

Top Wait Events by Total Wait Time (%)

SELECT

event,

wait_class,

ROUND(time_waited_micro/1000000,2) AS wait_time_sec,

ROUND(

100 * time_waited_micro /

SUM(time_waited_micro) OVER (),

2

) AS pct_wait_time

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY pct_wait_time DESC;

Current Active Waits (Right Now)

SELECT

event,

COUNT(*) sessions_waiting

FROM v$session

WHERE state = 'WAITING'

AND wait_class <> 'Idle'

GROUP BY event

ORDER BY sessions_waiting DESC;

Top Wait Events from ASH (Last 1 Hour)

SELECT

event,

COUNT() samples,

ROUND(

COUNT() * 100 /

SUM(COUNT(*)) OVER (),

2

) pct

FROM v$active_session_history

WHERE sample_time > SYSDATE - 1/24

AND wait_class <> 'Idle'

GROUP BY event

ORDER BY samples DESC;

AWR Top Wait Events for Last 24 Hours

SELECT

event_name,

SUM(total_waits_delta) total_waits,

ROUND(SUM(time_waited_micro_delta)/1000000,2) wait_time_sec

FROM dba_hist_system_event

WHERE wait_class <> 'Idle'

GROUP BY event_name

ORDER BY wait_time_sec DESC;

Quick DBA Query (Most Useful)

This gives the top waits consuming database time:

SELECT *

FROM (

SELECT

event,

wait_class,

ROUND(time_waited_micro/1000000,2) seconds_waited,

ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) avg_wait_ms

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY time_waited_micro DESC

)

WHERE ROWNUM <= 10;

If you're investigating a performance issue right now, also run:

SELECT

sid,

serial#,

username,

event,

sql_id,

seconds_in_wait

FROM v$session

WHERE state='WAITING'

AND wait_class <> 'Idle'

ORDER BY seconds_in_wait DESC;

This shows the exact sessions, SQL_IDs, and wait events currently contributing to the database slowdown.