Overview

This document guides how to search plans and find queries containing specific types of plans through examples.

Method

1. Extract query searching for rowid during hash join

Extraction Query

SELECT (SELECT username 
          FROM dba_users 
         WHERE user_id = a.parsing_user_id) AS parsing_username,
       a.last_active_time,
       a.sql_text
  FROM v$sqlarea a,
       ( SELECT DISTINCT bb.sql_id
           FROM v$sql_plan bb,
                ( SELECT DISTINCT sql_id
                    FROM v$sql_plan
                   WHERE UPPER(operation) LIKE 'HASH JOIN%'
                     AND UPPER(access_predicates) LIKE '%ROWID%'
                ) cc
          WHERE bb.sql_id = cc.sql_id
            AND bb.object_owner NOT IN ('SYS')
       ) b
 WHERE a.sql_id = b.sql_id
   AND (UPPER(a.sql_text) LIKE '%ROWID%');

Execution Example

-- Create test data
SQL> conn tibero/tmax
Connected to Tibero.

SQL> CREATE TABLE t1 AS SELECT LEVEL id, RPAD('A',100,'A') data FROM dual CONNECT BY LEVEL <= 1000;

Table 'T1' created.

SQL> CREATE TABLE t2 AS SELECT LEVEL id, RPAD('B',100,'B') data FROM dual CONNECT BY LEVEL <= 1000;

Table 'T2' created.

SQL> CREATE INDEX t1_idx ON t1(id);

Index 'T1_IDX' created.

SQL> CREATE INDEX t2_idx ON t2(id);

Index 'T2_IDX' created.

-- Execute query and check plan
SQL> SET AUTOT TRACEONLY
SELECT /*+ USE_HASH(t1 t2) */
       t1.data
  FROM t1
 WHERE t1.ROWID IN (
       SELECT ROWID FROM t2 WHERE t2.id < 10
);

SQL ID: au8yy6kdc1hp1
Child number: 239
Plan hash value: 3354623698

Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1  HASH JOIN (Cost:14, %%CPU:0, Rows:12)
   2    INDEX (RANGE SCAN): T2_IDX (Cost:2, %%CPU:0, Rows:12)
   3    TABLE ACCESS (FULL): T1 (Cost:12, %%CPU:0, Rows:1000)


Predicate Information
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - access: ("T1"."ROWID" = "T2"."ROWID") (0.002)
   2 - access: ("T2"."ID" < 10) (0.012)


Note
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - dynamic sampling used for this table (20 blocks)
   3 - dynamic sampling used for this table (20 blocks)


NAME                                VALUE
------------------------------ ----------
db block gets                         208
consistent gets                       228
physical reads                          1
redo size                               0
sorts (disk)                            0
sorts (memory)                          2
rows processed                          0



-- Query to check rowid search during hash join
SQL> COL PARSING_USERNAME FOR A20
SQL> COL LAST_ACTIVE_TIME FOR A25
SQL> COL SQL_TEXT FOR A60
SQL> SELECT (SELECT username 
          FROM dba_users 
         WHERE user_id = a.parsing_user_id) AS parsing_username,
       a.last_active_time,
       a.sql_text
  FROM v$sqlarea a,
       ( SELECT DISTINCT bb.sql_id
           FROM v$sql_plan bb,
                ( SELECT DISTINCT sql_id
                    FROM v$sql_plan
                   WHERE UPPER(operation) LIKE 'HASH JOIN%'
                     AND UPPER(access_predicates) LIKE '%ROWID%'
                ) cc
          WHERE bb.sql_id = cc.sql_id
            AND bb.object_owner NOT IN ('SYS')
       ) b
 WHERE a.sql_id = b.sql_id
   AND (UPPER(a.sql_text) LIKE '%ROWID%');

PARSING_USERNAME     LAST_ACTIVE_TIME          SQL_TEXT
-------------------- ------------------------- ------------------------------------------------------------
TIBERO               2025/10/27                SELECT /*+ USE_HASH(t1 t2) */
                                                      t1.data
                                                 FROM t1
                                                WHERE t1.ROWID IN (
                                                      SELECT ROWID FROM t2 WHERE t2.id < 10
                                               )


1 row selected.

2. Extract queries with 2 index scans under hash join

Extraction Query

SELECT DISTINCT s.sql_id,
       (SELECT username FROM dba_users WHERE user_id = s.parsing_user_id) AS parsing_username,
       s.last_active_time,
       s.sql_text
  FROM v$sql s
 WHERE s.sql_id IN (
    SELECT b.sql_id
      FROM v$sql_plan b
     WHERE UPPER(b.operation) LIKE 'HASH JOIN%'
     GROUP BY b.sql_id, b.child_number, b.id
     HAVING COUNT(
              CASE
                WHEN EXISTS (
                       SELECT 1
                         FROM v$sql_plan a
                        WHERE a.sql_id = b.sql_id
                          AND a.child_number = b.child_number
                          AND a.parent_id = b.id
                          AND UPPER(a.operation) LIKE 'INDEX%'
                     )
                THEN 1
              END
            ) = 1
        AND (
             SELECT COUNT(*)
               FROM v$sql_plan a
              WHERE a.sql_id = b.sql_id
                AND a.child_number = b.child_number
                AND a.parent_id = b.id
                AND UPPER(a.operation) LIKE 'INDEX%'
            ) = 2
 )
 ORDER BY s.last_active_time DESC;

Execution Example

-- Using the same tables and indexes as in 1-2
-- Execute query and check plan
SQL> SELECT /*+ USE_HASH(t1 t2) INDEX(t1 T1_IDX) INDEX(t2 T2_IDX) */ t1.id
  FROM t1, t2
 WHERE t1.id = t2.id;

SQL ID: 4rsgs8sx2ubcu
Child number: 289
Plan hash value: 943179889

Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1  HASH JOIN (Cost:24, %%CPU:0, Rows:1194)
   2    INDEX (FULL): T2_IDX (Cost:4, %%CPU:0, Rows:1000)
   3    TABLE ACCESS (ROWID): T1 (Cost:20, %%CPU:0, Rows:1000)
   4      INDEX (FULL): T1_IDX (Cost:4, %%CPU:0, Rows:1000)


Predicate Information
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - access: ("T2"."ID" = "T1"."ID") (0.001)


Note
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - dynamic sampling used for this table (20 blocks)
   4 - dynamic sampling used for this table (20 blocks)


NAME                                VALUE
------------------------------ ----------
db block gets                         204
consistent gets                       125
physical reads                          0
redo size                               0
sorts (disk)                            0
sorts (memory)                          2
rows processed                       1000

SQL> col sql_id for a20
SQL> col parsing_username for a20 
SQL> col last_active_time for a25
SQL> col sql_text for a50
SQL>SELECT DISTINCT s.sql_id,
       (SELECT username FROM dba_users WHERE user_id = s.parsing_user_id) AS parsing_username,
       s.last_active_time,
       s.sql_text
  FROM v$sql s
 WHERE s.sql_id IN (
    SELECT b.sql_id
      FROM v$sql_plan b
     WHERE UPPER(b.operation) LIKE 'HASH JOIN%'
     GROUP BY b.sql_id, b.child_number, b.id
     HAVING COUNT(
              CASE
                WHEN EXISTS (
                       SELECT 1
                         FROM v$sql_plan a
                        WHERE a.sql_id = b.sql_id
                          AND a.child_number = b.child_number
                          AND a.parent_id = b.id
                          AND UPPER(a.operation) LIKE 'INDEX%'
                     )
                THEN 1
              END
            ) = 1
        AND (
             SELECT COUNT(*)
               FROM v$sql_plan a
              WHERE a.sql_id = b.sql_id
                AND a.child_number = b.child_number
                AND a.parent_id = b.id
                AND UPPER(a.operation) LIKE 'INDEX%'
            ) = 2
 )
 ORDER BY s.last_active_time DESC;
 
 SQL_ID               PARSING_USERNAME     LAST_ACTIVE_TIME          SQL_TEXT
-------------------- -------------------- ------------------------- --------------------------------------------------
23ngd7705wmzc        TIBERO               2025/11/06                SELECT /*+ USE_HASH(t1 t2) INDEX(t1 T1_IDX) INDEX(
                                                                    t2 T2_IDX) */ t1.id
                                                                      FROM t1, t2
                                                                     WHERE t1.id = t2.id

1 rows selected.