XTended Oracle SQL

I created this simple service a couple of years ago. It’s pretty simple, small and intuitive Python app, so you can easily modify it to suit your own needs and run on any platform: https://github.com/xtender/pySync

Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text’s from v$sqlarea. And thanks to Oracle SqlCL and Oracle SQL Developer, we can easily use oracle.dbtools.app.Format function from their Java library dbtools-common.jar, so if you use SqlCL or SQL Developer, you can use the same formatting options.

You may have already installed SQLDeveloper or SqlCL on your database server, just check $ORACLE_HOME/sqldeveloper or $ORACLE_HOME/sqcl directories. If – not, you need to download appropriate SqlCL version that matches your java version in Oracle. For 12.2 – 19.8 you can download latest SqlCL 20.3. In fact we need just dbtools-common.jar from lib directory. I put it into $ORACLE_HOME/sqlcl/lib directory on the server and load it using loadjava:

loadjava -u login/pass@pdb1 $ORACLE_HOME/sqlcl/lib/dbtools-common.jar

Just change your login credentials and dbname.

Then you need to grant required Java permissions:

exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );

As you can see, in the code below you can specify own formatting options (function getFormat()). For example, formatting options from Trivadis: https://github.com/Trivadis/plsql-formatter-settings/blob/main/settings/sql_developer/trivadis_advanced_format.xml

You can copy the code below, but it would be better to take latest code from https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter

CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS
/* Imports */
import oracle.dbtools.app.Format;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import java.io.StringWriter;
import java.io.PrintWriter;


public class SQLFormatter {

    private static String getStackTrace(Exception e) {
       StringWriter writer = new StringWriter();
       PrintWriter printWriter = new PrintWriter( writer );
       e.printStackTrace( printWriter );
       printWriter.flush();

       return writer.toString();
    }

    public static Format getFormat() {
        oracle.dbtools.app.Format format = new oracle.dbtools.app.Format();
        
        format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
        format.options.put("kwCase", Format.Case.UPPER);
        format.options.put("idCase", Format.Case.NoCaseChange);                             // default: Format.Case.lower
        format.options.put("adjustCaseOnly", false);                                        // default: false (set true to skip formatting)
        format.options.put("formatThreshold", 1);                                           // default: 1 (disables deprecated post-processing logic)
        // Alignment
        format.options.put("alignTabColAliases", false);                                    // default: true
        format.options.put("alignTypeDecl", true);
        format.options.put("alignNamedArgs", true);
        format.options.put("alignEquality", false);
        format.options.put("alignAssignments", true);                                       // default: false
        format.options.put("alignRight", false);                                            // default: false
        // Indentation
        format.options.put("identSpaces", 3);                                               // default: 4
        format.options.put("useTab", false);
        // Line Breaks
        format.options.put("breaksComma", Format.Breaks.Before);                            // default: Format.Breaks.After
        format.options.put("breaksProcArgs", false);
        format.options.put("breaksConcat", Format.Breaks.Before);
        format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
        format.options.put("breaksAfterSelect", true);                                      // default: true
        format.options.put("commasPerLine", 1);                                             // default: 5
        format.options.put("breakOnSubqueries", true);
        format.options.put("breakAnsiiJoin", true);                                         // default: false
        format.options.put("breakParenCondition", true);                                    // default: false
        format.options.put("maxCharLineSize", 120);                                         // default: 128
        format.options.put("forceLinebreaksBeforeComment", false);                          // default: false
        format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);   // default: Format.BreaksX2.X2
        format.options.put("flowControl", Format.FlowControl.IndentedActions);
        // White Space
        format.options.put("spaceAroundOperators", true);
        format.options.put("spaceAfterCommas", true);
        format.options.put("spaceAroundBrackets", Format.Space.Default);
        //format.options.put("formatProgramURL", "default");
        
        return format;
    }
    
  public static String format(String str) 
  {
    String res;
    try {
       //res = new Format().format(str);
       Format f = SQLFormatter.getFormat();
       res = f.format(str);
       }
    catch (Exception e){
       res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
    }
    return res;
  }

  public static CLOB formatClob(oracle.sql.CLOB clob) 
  throws SQLException
  {
    String str = clob.getSubString(1, (int) clob.length());
    String res = SQLFormatter.format(str);
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
    CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
    resClob.setString(1L, res);
    
    return resClob;
  }
}
/

create or replace package SQLFormatter as

  FUNCTION Format(str in varchar2) RETURN VARCHAR2
  AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';

  FUNCTION FormatClob(str in clob) RETURN CLOB
  AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
  
end;
/

Now we can test it:

SQL> select SQLFormatter.format('select 1 a,2 /*123 */ b,3 c, d from dual, dual d2') qtext from dual;

QTEXT
----------------------------------------------------------------------------------------------------
SELECT
   1 a
 , 2 /*123 */ b
 , 3 c
 , d
FROM
   dual
 , dual d2


SQL> select sql_id,SQLFormatter.format(sql_text) qtext from v$sqlarea where rownum<3 and sql_text is not null;

SQL_ID        QTEXT
------------- -------------------------------------
fbbm59qban13m DELETE FROM idl_sb4$
              WHERE
                    obj# = :1
                 AND part = :2
                 AND version <> :3

1gfaj4z5hn1kf DELETE FROM dependency$
              WHERE
                 d_obj# = :1

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

with v(start_hsecs, delta, flag) as (
  select
    hsecs as start_hsecs, 
    0 as delta,
    1 as flag
  from v$timer
  union all
  select
    v.start_hsecs,
    (t.hsecs-v.start_hsecs)/100 as delta,
    case when (t.hsecs-v.start_hsecs)/100 > :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
  from v, v$timer t
  where v.flag>0
    and t.hsecs>=v.start_hsecs
)
select delta
from v
where flag+rownum<=0;

SQL> var N number
SQL> exec :N := 3 /* seconds */;

PL/SQL procedure successfully completed.

SQL> select...

     DELTA
----------
      3.01

1 row selected.

Elapsed: 00:00:03.01

Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:

select count(*) from dual 
connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as
   vSID int;
   res varchar2(30);
begin
   vSID:=userenv('sid');
   select 
           to_char(s.server_group,'fm000')
    ||'-'||to_char(s.server_set,'fm0000')
    ||'-'||to_char(s.server#,'fm0000')
    ||'('||s.sid||','||s.degree||'/'||s.req_degree||')'
    into res
   from v$px_session s 
   where s.sid=vSID;
   return res;
exception when no_data_found then
   return 'no_parallel';
end;
/

Simple example:

select--+ parallel
  px_session_info, count(*)
from sys.obj$
group by px_session_info
/
PX_SESSION_INFO           COUNT(*)
------------------------  --------
001-0002-0001(630,2/2)     38298
001-0002-0002(743,2/2)     34706

I’ve just released new version of my Simple Android Oracle Client.

New features:

• Supported Oracle versions: 11.2, 12.1, 12.2, 18, 19, 20.
• SQL Templates: now you can save and load own script templates
• Server output (dbms_output)
• Export results as JSON, CSV and HTML files (long tap on results)
• Copy results to the Clipboard as JSON or CSV

I use it just for basic troubleshooting and small fixes, but, please, let me know if you need anything else.
Screenshots:

You may know that whenever you call PL/SQL functions from within SQL query, each query in the function is consistent to the SCN of its start and not to the SCN of parent query.

Simple example:

create table test as 
  select level a, level b from dual connect by level<=10;

create or replace function f1(a int) return int as
  res int;
begin
  select b into res 
  from test t 
  where t.a=f1.a;
  dbms_lock.sleep(1);
  return res;
end;
/

As you can see we created simple PL/SQL function that returns the result of the query select b from test where a=:input_var

But lets check what will it return if another session changes data in the table:

-- session 2:
begin
    for i in 1..30 loop
      update test set b=b+1;
      commit;
      dbms_lock.sleep(1);
    end loop;
end;
/
-- session 1:
SQL> select t.*, f1(a) func from test t;

         A          B       FUNC
---------- ---------- ----------
         1          1          1
         2          2          3
         3          3          5
         4          4          7
         5          5          9
         6          6         11
         7          7         13
         8          8         15
         9          9         17
        10         10         19

10 rows selected.

As you can see we got inconsistent results in the column FUNC, but we can easily fix it using OPERATORs:

CREATE OPERATOR f1_op
   BINDING (INT) 
   RETURN INT 
   USING F1;

Lets revert changes back and check our query with new operator now:

--session 1:
SQL> update test set b=a;

10 rows updated.

SQL> commit;

Commit complete.

-- session 2:
begin
    for i in 1..30 loop
      update test set b=b+1;
      commit;
      dbms_lock.sleep(1);
    end loop;
end;
/

-- session 1:
SQL> select t.*, f1(a) func, f1_op(a) op from test t;

         A          B       FUNC         OP
---------- ---------- ---------- ----------
         1          2          2          2
         2          3          5          3
         3          4          8          4
         4          5         11          5
         5          6         14          6
         6          7         17          7
         7          8         20          8
         8          9         23          9
         9         10         26         10
        10         11         29         11

10 rows selected.

As you can see values in the column OP are equal to the values of B, while, in turn, function F1 returns inconsistent values.

As of Oracle 19c OJPPD doesn’t support connect-by and TABLE():

OJPPD: OJPPD bypassed: query block contains START WITH/CONNECT BY.
OJPPD: OJPPD bypassed: View contains TABLE expression.

I forgot to share my files from presentations, so I’m going to keep them here:

Test data:

create table t_str as
select round(dbms_random.value(1e10,9e10)) str from dual connect by level<=1e5
/

PL/SQL variant:

with
  function ff(s varchar2) return varchar2 
  as
      type avarchars is table of varchar2(100) index by varchar2(1);
      st  avarchars;
      idx varchar2(1);
      res varchar2(10);
      
      function iterate( idx in out nocopy varchar2, arr in out nocopy avarchars) 
         return boolean
      as --pragma inline;
      begin
         if idx is null
            then idx:=arr.first; 
            else idx:=arr.next(idx);
         end if;
         return idx is not null;
      end;  
   begin
     for i in 1..length(s) loop
        st(substr(s,i,1)):=1;
     end loop;
     while iterate(idx,st) loop
        res:=res||idx;
     end loop;
     return res;
   end;

select min(ff(str)) res
from t_str
/

SQL-only variant:

select min(fstr)
from t_str t
     cross apply (
     select listagg(c) within group (order by 1) fstr
     from (select
            distinct substr(t.str, level, 1) c
           from dual
           connect by level <= length(t.str)
          )
     )
/

Timings:

SQL> create table t_str as
  2  select round(dbms_random.value(1e10,9e10)) str from dual connect by level<=1e5
  3  /

Table created.

Elapsed: 00:00:00.55
SQL> with
  2    function ff(s varchar2) return varchar2
  3    as
  4        type avarchars is table of varchar2(100) index by varchar2(1);
  5        st  avarchars;
  6        idx varchar2(1);
  7        res varchar2(10);
  8
  9        function iterate( idx in out nocopy varchar2, arr in out nocopy avarchars)
 10           return boolean
 11        as --pragma inline;
 12        begin
 13           if idx is null
 14              then idx:=arr.first;
 15              else idx:=arr.next(idx);
 16           end if;
 17           return idx is not null;
 18        end;
 19     begin
 20       for i in 1..length(s) loop
 21          st(substr(s,i,1)):=1;
 22       end loop;
 23       while iterate(idx,st) loop
 24          res:=res||idx;
 25       end loop;
 26       return res;
 27     end;
 28
 29  select min(ff(str)) res
 30  from t_str
 31  /

RES
--------------------------------------------------------------
0123

Elapsed: 00:00:00.48
SQL> select min(fstr) res2
  2  from t_str t
  3       cross apply (
  4       select listagg(c) within group (order by 1) fstr
  5       from (select
  6              distinct substr(t.str, level, 1) c
  7             from dual
  8             connect by level <= length(t.str)
  9            )
 10       )
 11  /

RES2
--------------------------------------------------------------
0123

Elapsed: 00:00:01.01

And much easier variant if you need your strings contain digits only:

select min(translate('0123456789', translate('z0123456789','z'||str,'z'), chr(0)))
from t_str

select nls_env
      ,count(*) cnt
      ,xmlcast(xmlagg(xmlelement(job_name, job_name||',')).extract('//text()') as clob) jobs
from dba_scheduler_jobs 
group by nls_env;

Simple example: tracefiles for the last 5 days:

select fc.* 
from v$diag_trace_file f
     join v$diag_trace_file_contents fc
          on f.adr_home=fc.adr_home
          and f.trace_filename=fc.trace_filename
where f.modify_time >= systimestamp - interval'5' minute
  and fc.timestamp  >= systimestamp - interval'5' minute
  and fc.component_name = 'SQL_Trace'
  --and fc.section_name like 'kests%'
  ;
--or:
select tr.*
  from v$diag_app_trace_file tf,
       v$diag_sql_trace_records tr
 where tf.sql_trace = 'Y'
   and tf.modify_time > systimestamp - interval'5'minute
   and tr.adr_home = tf.adr_home
   and tr.trace_filename = tf.trace_filename
   and tr.timestamp > systimestamp - interval'5'minute;

I know this syntax for a long time, since when lateral() was not documented yet, but recently I found a bug: the following query successfully returns 1 row:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     --left outer join c on c.c=bb.b
where a=1;

         A          B
---------- ----------
         1

But doesn’t if we uncomment “left join”:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     left outer join c on c.c=bb.b
where a=1;

no rows selected

And outer apply works fine:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a
     outer apply (select * from b where a.a=b.b) bb
     left outer join c on c.c=bb.b
where a=1;

         A          B          C
---------- ---------- ----------
         1

Recently I got a question: why CBO chooses “FIXED TABLE FULL TABLE” scan instead of “FIXED INDEX” in case of a join to flashback_transaction_query view with the predicate “q.xid = a.versions_xid”:

select
  versions_xid,
  versions_startscn,
  versions_starttime,
  versions_endscn,
  versions_endtime,
  --scn_to_timestamp,
  to_char(a.classification), a.* 
from vms.advisory_arch_20190624 versions between
         timestamp timestamp'2019-06-24 13:28:00' and maxvalue a
    join flashback_transaction_query q
      on q.xid = a.versions_xid
 where a.advisoryid = 100511;

As you can see flashback_transaction_query is just a wrapper view for SYS.X$KTUQQRY, ie fixed table, which obviously has good “fixed index” for that:

And simple query with the predicate xid=hextoraw(…) uses it:

In such cases we need to check another part of the equality predicate, because usually it means that we use some function instead of literal, bind variable or SQL operator. Simple example with v$session_event:

explain plan for
SELECT SUM(E.TIME_WAITED)/100 AS TIME_WAITED 
FROM V$SESSION_EVENT E 
WHERE E.SID = SYS_CONTEXT('userenv', 'sid')
AND (E.EVENT = 'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink');

But it works fine with bind variables or if we use “precompute_subquery” hint to replace function call with the computed literal:

SQL> explain plan for
  2  SELECT  SUM(E.TIME_WAITED)/100 AS TIME_WAITED
  3  FROM  V$SESSION_EVENT E
  4  WHERE E.SID = :sid
  5    AND (E.EVENT = 'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink');

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 404853953

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    47 |     1 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    47 |            |          |
|   2 |   NESTED LOOPS            |                 |     3 |   141 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLES (ind:1) |   403 |  6851 |     1 (100)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    30 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("S"."KSLESSID"=TO_NUMBER(:SID) AND "S"."KSLESWTS"<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter(("D"."KSLEDNAM"='TCP Socket (KGAS)' OR "D"."KSLEDNAM" LIKE
              'SQL*Net%dblink') AND "S"."KSLESENM"="D"."INDX")
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
  1  explain plan for
  2  SELECT SUM(E.TIME_WAITED)/100 AS TIME_WAITED
  3  FROM
  4   V$SESSION_EVENT E
  5  WHERE E.SID in (select/*+ precompute_subquery */ SYS_CONTEXT('userenv', 'sid') from dual) AND (E.EVENT =
  6*   'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink')
SQL> /

Explained.

SQL> @xplan ""



PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2330447422

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    50 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    50 |            |          |
|   2 |   NESTED LOOPS            |                 |     3 |   150 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLES (ind:1) |   514 | 10280 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    30 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("S"."KSLESSID"=382 AND "S"."KSLESWTS"<>0 AND ("S"."CON_ID"=0 OR
              "S"."CON_ID"=3) AND "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter("S"."KSLESENM"="D"."INDX" AND ("D"."KSLEDNAM"='TCP Socket (KGAS)' OR
              "D"."KSLEDNAM" LIKE 'SQL*Net%dblink'))

19 rows selected.

So the same problem we got with flashback_transaction_query, because VERSIONS_XID is a pseudocolumn in fact (ie function). So we can easily hide it in inline view:

SQL> ;
  1  explain plan for
  2  with a as (
  3  select/*+ no_merge */
  4         versions_xid, versions_startscn, versions_starttime, versions_endscn, versions_endtime, --scn_to_timestamp,
  5         to_char(x.classification),
  6         x.*
  7    from advisory_arch_20190624 versions between timestamp to_timestamp('2019-06-25 22:14:00', 'YYYY-MM-DD HH24:MI:SS') and maxvalue x
  8    where x.advisoryid = 100511
  9  )
 10  select/*+ leading(a q) use_nl(q) index(q (xid)) */ *
 11  from a
 12*      join flashback_transaction_query q on q.xid = a.versions_xid
SQL> /

Explained.

SQL> @xplan +outline

P_FORMAT
------------------------
typical +outline

1 row selected.


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2200855354

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |  2596 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                        |     1 |  2596 |     6   (0)| 00:00:01 |
|   2 |   VIEW                   |                        |     1 |    98 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL     | ADVISORY_ARCH_20190624 |     1 |     9 |     6   (0)| 00:00:01 |
|*  4 |   FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1)      |     1 |  2498 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$4" "X"@"SEL$4")
      USE_NL(@"SEL$62A6D27E" "X$KTUQQRY"@"SEL$3")
      LEADING(@"SEL$62A6D27E" "A"@"SEL$1" "X$KTUQQRY"@"SEL$3")
      FULL(@"SEL$62A6D27E" "X$KTUQQRY"@"SEL$3")
      NO_ACCESS(@"SEL$62A6D27E" "A"@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3" >"SEL$2")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A" >"SEL$1")
      OUTLINE(@"SEL$5C160134")
      OUTLINE(@"SEL$5")
      MERGE(@"SEL$5C160134" >"SEL$5")
      OUTLINE_LEAF(@"SEL$62A6D27E")
      OUTLINE_LEAF(@"SEL$4")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("X"."ADVISORYID"=100511)
   4 - filter("XID"="A"."VERSIONS_XID" AND ("CON_ID"=0 OR "CON_ID"=3))

And in addition a couple of my scripts for fixed tables:

https://github.com/xtender/xt_scripts/blob/master/fixed_tables.sql

https://github.com/xtender/xt_scripts/blob/master/fixed_indexes.sql

Some time ago there was a question on the sql.ru about splitting validity periods for gym cards to enabled and blocked periods:

-- we have a table with gym cards (card id, validity period: from/to)
CREATE TABLE cards(id_card NUMBER, num_card VARCHAR2(100), dt_from DATE, dt_to DATE);
-- periods when cards were blocked (record id, card id, dates from/to)
CREATE TABLE blocks(id_block NUMBER, id_card NUMBER, dt_from DATE, dt_to DATE);
-- cards data:
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
-- blocks data:
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY'));
COMMIT;

Just for fun, I’ve solved it using match_recognise:

with 
 cards (id_card, num_card, dt_from, dt_to) as (
   select 1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY') from dual union all
   select 2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual union all
   select 3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual 
),blocks (id_block, id_card, dt_from, dt_to) as (
   select 101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY') from dual union all
   select 102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY') from dual union all
   select 103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY') from dual union all
   select 201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY') from dual
)
,data as (
   select *
   from (
         select * 
         from (
            select id_card,dt_from,dt_to,'c' t from cards
            union all
            select id_card,dt_to,dt_from,'b' from blocks
         ) v
         --where v.id_card=1 -- here we can filter card ID if needed
        ) piv
   unpivot(dt for x in (dt_from as 'dt_from', dt_to as 'dt_to'))
)
select * 
from data
match_recognize (
   partition by id_card
   order by dt /* we can modify add "t" into "order by clause" to make it more deterministic */
   measures
      match_number() as N,
      first(dt) as dt_from,
      last(dt) as dt_to
   pattern (x_start+ x_end)
   define
      x_start as x = 'dt_from'
     ,x_end   as x = 'dt_to'
);

You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).

Simple example:

create table xtest(a, b, c) as
select mod(level,1000),level,rpad('x',100,'x')
from dual
connect by level<=1e4
/
create index itest on xtest(a)
/
create or replace view vtest as
select a,count(b) cnt
from xtest
group by a
/
call dbms_stats.gather_table_stats(user,'xtest');
/

select distinct v.* 
from table(sys.odcinumberlist(1,2,3)) c, vtest v
where v.a = c.column_value;

Plan hash value: 699667151

-------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |       |     1 |    19 |    80   (4)| 00:00:01 |
|   1 |  HASH UNIQUE                            |       |     1 |    19 |    80   (4)| 00:00:01 |
|*  2 |   HASH JOIN                             |       |     1 |    19 |    79   (3)| 00:00:01 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |    VIEW                                 | VTEST |  1000 | 17000 |    49   (3)| 00:00:01 |
|   5 |     HASH GROUP BY                       |       |  1000 |  8000 |    49   (3)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                  | XTEST | 10000 | 80000 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("V"."A"=VALUE(KOKBF$))

select/*+ cardinality(c 1) use_nl(v) push_pred(v) */ * 
from json_table('{"a":[1,2,3]}', '$.a[*]' COLUMNS (a int PATH '$')) c
    ,vtest v
where c.a = v.a;

Plan hash value: 664523328

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    28 |    78   (2)| 00:00:01 |
|   1 |  NESTED LOOPS          |       |     1 |    28 |    78   (2)| 00:00:01 |
|   2 |   JSONTABLE EVALUATION |       |       |       |            |          |
|*  3 |   VIEW                 | VTEST |     1 |    26 |    49   (3)| 00:00:01 |
|   4 |    SORT GROUP BY       |       |  1000 |  8000 |    49   (3)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | XTEST | 10000 | 80000 |    48   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("V"."A"="P"."A")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$F534CA49 / V@SEL$1
         U -  push_pred(v)

select/*+ leading(c v) cardinality(c 1) use_nl(v) push_pred(v) */ v.*
from  xmltable('(1,3)' columns a int path '.') c,vtest v
where  c.a = v.a(+);

Plan hash value: 564839666

------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |     1 |    28 |    78   (2)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER                |                       |     1 |    28 |    78   (2)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |     1 |     2 |    29   (0)| 00:00:01 |
|*  3 |   VIEW                             | VTEST                 |     1 |    26 |    49   (3)| 00:00:01 |
|   4 |    SORT GROUP BY                   |                       |  1000 |  8000 |    49   (3)| 00:00:01 |
|   5 |     TABLE ACCESS FULL              | XTEST                 | 10000 | 80000 |    48   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("V"."A"(+)=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),
              50,1,2)) AS int ))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$6722A2F6 / V@SEL$1
         U -  push_pred(v)

create global temporary table temp_collection(a number);

insert into temp_collection select * from table(sys.odcinumberlist(1,2,3));

select/*+ cardinality(c 1) no_merge(v) */
   distinct v.* 
from temp_collection c, vtest v
where v.a = c.a;

Plan hash value: 3561835411

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |     1 |    26 |    41   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                             |                 |     1 |    26 |    41   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                           |                 |     1 |    26 |    40   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                     | TEMP_COLLECTION |     1 |    13 |    29   (0)| 00:00:01 |
|   4 |    VIEW PUSHED PREDICATE                 | VTEST           |     1 |    13 |    11   (0)| 00:00:01 |
|*  5 |     FILTER                               |                 |       |       |            |          |
|   6 |      SORT AGGREGATE                      |                 |     1 |     8 |            |          |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| XTEST           |    10 |    80 |    11   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                  | ITEST           |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(COUNT(*)>0)
   8 - access("A"="C"."A")

You can see that JPPD works fine in case of global temporary tables and, obviously, the first workaround is to avoid such functions with complex views.
But in such simple queries you have 2 other simple options:
1. you can avoid JPPD and get optimal plans using CVM(complex view merge) by just simply rewriting the query using IN or EXISTS:

select * 
from vtest v
where v.a in (select/*+ cardinality(c 1) */ c.* from table(sys.odcinumberlist(1,2,3)) c);

Plan hash value: 1474391442

---------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |       |    10 |   100 |    42   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT                     |       |    10 |   100 |    42   (5)| 00:00:01 |
|   2 |   NESTED LOOPS                            |       |    10 |   100 |    41   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                           |       |    10 |   100 |    41   (3)| 00:00:01 |
|   4 |     SORT UNIQUE                           |       |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     1 |     2 |    29   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                      | ITEST |    10 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID            | XTEST |    10 |    80 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("A"=VALUE(KOKBF$))

select * 
from vtest t
where t.a in (select/*+ cardinality(v 1) */ v.a from json_table('{"a":[1,2,3]}', '$.a[*]' COLUMNS (a int PATH '$')) v);

Plan hash value: 2910004067

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    10 |   100 |    42   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT         |       |    10 |   100 |    42   (5)| 00:00:01 |
|   2 |   NESTED LOOPS                |       |    10 |   100 |    41   (3)| 00:00:01 |
|   3 |    NESTED LOOPS               |       |    10 |   100 |    41   (3)| 00:00:01 |
|   4 |     SORT UNIQUE               |       |       |       |            |          |
|   5 |      JSONTABLE EVALUATION     |       |       |       |            |          |
|*  6 |     INDEX RANGE SCAN          | ITEST |    10 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID| XTEST |    10 |    80 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("A"="P"."A")

select v.*
from  vtest v
where exists(select/*+ cardinality(c 1) */ 1 from xmltable('(1,3)' columns a int path '.') c where c.a = v.a);

Plan hash value: 1646016183

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                       |    10 |   100 |    42   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT                 |                       |    10 |   100 |    42   (5)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                       |    10 |   100 |    41   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                       |    10 |   100 |    41   (3)| 00:00:01 |
|   4 |     SORT UNIQUE                       |                       |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |     1 |     2 |    29   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | ITEST                 |    10 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID        | XTEST                 |    10 |    80 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("A"=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),50,1,2)) AS int ))

2. Avoid JPPD using lateral():

select/*+ cardinality(c 1) no_merge(lat) */
   distinct lat.* 
from table(sys.odcinumberlist(1,2,3)) c, 
     lateral(select * from vtest v where v.a = c.column_value) lat;

Plan hash value: 18036714

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |    10 |   190 |    41   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                            |                 |    10 |   190 |    41   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                          |                 |    10 |   190 |    40   (0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|                 |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |    VIEW                                 | VW_LAT_4DB60E85 |    10 |   170 |    11   (0)| 00:00:01 |
|   5 |     SORT GROUP BY                       |                 |    10 |    80 |    11   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| XTEST           |    10 |    80 |    11   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN                  | ITEST           |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("A"=VALUE(KOKBF$))

Let’s see a bit more complex query:

create table xtest1(id primary key, a) as
  select level,level from dual connect by level<=1000;

create table xtest2(a, b, c) as
   select mod(level,1000),level,rpad('x',100,'x')
   from dual
   connect by level<=1e4
/
create index itest2 on xtest2(a)
/
create or replace view vtest2 as
select a,count(b) cnt
from xtest2
group by a
/

select v.* 
from xtest1 t1,
     vtest2 v
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c)
  and v.a = t1.a;

Plan hash value: 4293766070

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |              |     1 |    36 |    80   (3)| 00:00:01 |
|*  1 |  HASH JOIN                                 |              |     1 |    36 |    80   (3)| 00:00:01 |
|   2 |   JOIN FILTER CREATE                       | :BF0000      |     1 |    10 |    31   (4)| 00:00:01 |
|   3 |    NESTED LOOPS                            |              |     1 |    10 |    31   (4)| 00:00:01 |
|   4 |     NESTED LOOPS                           |              |     1 |    10 |    31   (4)| 00:00:01 |
|   5 |      SORT UNIQUE                           |              |     1 |     2 |    29   (0)| 00:00:01 |
|   6 |       COLLECTION ITERATOR CONSTRUCTOR FETCH|              |     1 |     2 |    29   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                     | SYS_C0026365 |     1 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID            | XTEST1       |     1 |     8 |     1   (0)| 00:00:01 |
|   9 |   VIEW                                     | VTEST2       |  1000 | 26000 |    49   (3)| 00:00:01 |
|  10 |    HASH GROUP BY                           |              |  1000 |  8000 |    49   (3)| 00:00:01 |
|  11 |     JOIN FILTER USE                        | :BF0000      | 10000 | 80000 |    48   (0)| 00:00:01 |
|* 12 |      TABLE ACCESS FULL                     | XTEST2       | 10000 | 80000 |    48   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("V"."A"="T1"."A")
   7 - access("T1"."ID"=VALUE(KOKBF$))
  12 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"))

As you see, CVM can’t help in this case, but we can use lateral():

select/*+ no_merge(lat) */ lat.* 
from xtest1 t1,
     lateral(select * from vtest2 v where v.a = t1.a) lat
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c);

Plan hash value: 1798023704

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |    10 |   360 |    42   (3)| 00:00:01 |
|   1 |  NESTED LOOPS                            |                 |    10 |   360 |    42   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                           |                 |     1 |    10 |    31   (4)| 00:00:01 |
|   3 |    SORT UNIQUE                           |                 |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|                 |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID           | XTEST1          |     1 |     8 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN                    | SYS_C0026365    |     1 |       |     0   (0)| 00:00:01 |
|   7 |   VIEW                                   | VW_LAT_A18161FF |    10 |   260 |    11   (0)| 00:00:01 |
|   8 |    SORT GROUP BY                         |                 |    10 |    80 |    11   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID BATCHED  | XTEST2          |    10 |    80 |    11   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN                    | ITEST2          |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1"."ID"=VALUE(KOKBF$))
  10 - access("A"="T1"."A")

There is also another workaround with non-documented “precompute_subquery” hint:

select v.* 
from xtest1 t1,
     vtest2 v 
where t1.id in (select/*+ precompute_subquery */ * from table(sys.odcinumberlist(1,2,3)) c)
and v.a = t1.a;

Plan hash value: 1964829099

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |    30 |   480 |    37   (3)| 00:00:01 |
|   1 |  HASH GROUP BY                  |              |    30 |   480 |    37   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                  |              |    30 |   480 |    36   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |              |    30 |   480 |    36   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR             |              |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| XTEST1       |     3 |    24 |     3   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | SYS_C0026365 |     3 |       |     2   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN            | ITEST2       |    10 |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | XTEST2       |    10 |    80 |    11   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1"."ID"=1 OR "T1"."ID"=2 OR "T1"."ID"=3)
   7 - access("A"="T1"."A")

It can help even in most difficult cases, for example if you can’t rewrite query (in this case you can create sql patch or sql profile with “precompute_subquery”), but I wouldn’t suggest it since “precompute_subquery” is non-documented, it can be used only with simple collections and has limitation in 1000 values.
I’d suggest to use the workaround with lateral, since it’s most reliable and very simple.

Today I wanted to give a link to the description of v$sql_hint.target_level to show that no_parallel can be specified for statement or object, and though it’s pretty obvious, but surprisingly I haven’t found any articles or posts about it, so this short post describes it.
v$sql_hint.target_level is a bitset, where
1st bit set to 1 means that the hint can be specified on statement level,
2nd – on query block level,
3rd – on object level,
4th – on join level(for multiple objects).
Short example:

   select name,sql_feature
          ,class,inverse
          ,version,version_outline
          ,target_level
         ,decode(bitand(target_level,1),0,'no','yes') Statement_level
         ,decode(bitand(target_level,2),0,'no','yes') Query_block_level
         ,decode(bitand(target_level,4),0,'no','yes') Object_level
         ,decode(bitand(target_level,8),0,'no','yes') Join_level
   from v$sql_hint h;

with hints as (
   select name,sql_feature
          ,class,inverse
          ,version,version_outline
          ,target_level
         ,decode(bitand(target_level,1),0,'no','yes') Statement_level
         ,decode(bitand(target_level,2),0,'no','yes') Query_block_level
         ,decode(bitand(target_level,4),0,'no','yes') Object_level
         ,decode(bitand(target_level,8),0,'no','yes') Join_level
   from v$sql_hint h
)
select *
from hints
where statement_level='yes'
  and to_number(regexp_substr(version,'^\d+')) >= 18
order by version;

Result:

NAME              SQL_FEATURE     CLASS                VERSION  TARGET_LEVEL STATEMENT_LEVEL QUERY_BLOCK_LEVEL OBJECT_LEVEL JOIN_LEVEL
----------------- --------------- -------------------- -------- ------------ --------------- ----------------- ------------ ----------
PDB_LOCAL_ONLY    QKSFM_DML       PDB_LOCAL_ONLY       18.1.0              1 yes             no                no           no
SUPPRESS_LOAD     QKSFM_DDL       SUPPRESS_LOAD        18.1.0              1 yes             no                no           no
SYSTEM_STATS      QKSFM_ALL       SYSTEM_STATS         18.1.0              1 yes             no                no           no
MEMOPTIMIZE_WRITE QKSFM_EXECUTION MEMOPTIMIZE_WRITE    18.1.0              1 yes             no                no           no
SKIP_PROXY        QKSFM_ALL       SKIP_PROXY           18.1.0              1 yes             no                no           no
CURRENT_INSTANCE  QKSFM_ALL       CURRENT_INSTANCE     18.1.0              1 yes             no                no           no
JSON_LENGTH       QKSFM_EXECUTION JSON_LENGTH          19.1.0              1 yes             no                no           no
QUARANTINE        QKSFM_EXECUTION QUARANTINE           19.1.0              1 yes             no                no           no

Sometimes it might be useful to analyze top time-consuming filter and access predicates from ASH, especially in cases when db load is spread evenly enough by different queries and top segments doesn’t show anything special, except usual things like “some tables are requested more often than others”.
Of course, we can start from analysis of SYS.COL_USAGE$: col_usage.sql

col owner format a30
col oname format a30 heading "Object name"
col cname format a30 heading "Column name"
accept owner_mask prompt "Enter owner mask: ";
accept tab_name prompt "Enter tab_name mask: ";
accept col_name prompt "Enter col_name mask: ";

SELECT a.username              as owner
      ,o.name                  as oname
      ,c.name                  as cname
      ,u.equality_preds        as equality_preds
      ,u.equijoin_preds        as equijoin_preds
      ,u.nonequijoin_preds     as nonequijoin_preds
      ,u.range_preds           as range_preds
      ,u.like_preds            as like_preds
      ,u.null_preds            as null_preds
      ,to_char(u.timestamp, 'yyyy-mm-dd hh24:mi:ss') when
FROM   
       sys.col_usage$ u
     , sys.obj$       o
     , sys.col$       c
     , all_users      a
WHERE  a.user_id = o.owner#
AND    u.obj#    = o.obj#
AND    u.obj#    = c.obj#
AND    u.intcol# = c.col#
AND    a.username like upper('&owner_mask')
AND    o.name     like upper('&tab_name')
AND    c.name     like upper('&col_name')
ORDER  BY a.username, o.name, c.name
;
col owner clear;
col oname clear;
col cname clear;
undef tab_name col_name owner_mask;

But it’s not enough, for example it doesn’t show predicates combinations. In this case we can use v$active_session_history and v$sql_plan:

with 
 ash as (
   select 
      sql_id
     ,plan_hash_value
     ,table_name
     ,alias
     ,ACCESS_PREDICATES
     ,FILTER_PREDICATES
     ,count(*) cnt
   from (
      select 
         h.sql_id
        ,h.SQL_PLAN_HASH_VALUE plan_hash_value
        ,decode(p.OPERATION
                 ,'TABLE ACCESS',p.OBJECT_OWNER||'.'||p.OBJECT_NAME
                 ,(select i.TABLE_OWNER||'.'||i.TABLE_NAME from dba_indexes i where i.OWNER=p.OBJECT_OWNER and i.index_name=p.OBJECT_NAME)
               ) table_name
        ,OBJECT_ALIAS ALIAS
        ,p.ACCESS_PREDICATES
        ,p.FILTER_PREDICATES
      -- поля, которые могут быть полезны для анализа в других разрезах:
      --  ,h.sql_plan_operation
      --  ,h.sql_plan_options
      --  ,decode(h.session_state,'ON CPU','ON CPU',h.event) event
      --  ,h.current_obj#
      from v$active_session_history h
          ,v$sql_plan p
      where h.sql_opname='SELECT'
        and h.IN_SQL_EXECUTION='Y'
        and h.sql_plan_operation in ('INDEX','TABLE ACCESS')
        and p.SQL_ID = h.sql_id
        and p.CHILD_NUMBER = h.SQL_CHILD_NUMBER
        and p.ID = h.SQL_PLAN_LINE_ID
        -- если захотим за последние 3 часа:
        -- and h.sample_time >= systimestamp - interval '3' hour
   )
   -- если захотим анализируем предикаты только одной таблицы:
   -- where table_name='&OWNER.&TABNAME'
   group by 
      sql_id
     ,plan_hash_value
     ,table_name
     ,alias
     ,ACCESS_PREDICATES
     ,FILTER_PREDICATES
)
,agg_by_alias as (
   select
      table_name
     ,regexp_substr(ALIAS,'^[^@]+') ALIAS
     ,listagg(ACCESS_PREDICATES,' ') within group(order by ACCESS_PREDICATES) ACCESS_PREDICATES
     ,listagg(FILTER_PREDICATES,' ') within group(order by FILTER_PREDICATES) FILTER_PREDICATES
     ,sum(cnt) cnt
   from ash
   group by 
      sql_id
     ,plan_hash_value
     ,table_name
     ,alias
)
,agg as (
   select 
       table_name
      ,'ALIAS' alias
      ,replace(access_predicates,'"'||alias||'".','"ALIAS".') access_predicates
      ,replace(filter_predicates,'"'||alias||'".','"ALIAS".') filter_predicates
      ,sum(cnt) cnt
   from agg_by_alias 
   group by 
       table_name
      ,replace(access_predicates,'"'||alias||'".','"ALIAS".') 
      ,replace(filter_predicates,'"'||alias||'".','"ALIAS".') 
)
,cols as (
   select 
       table_name
      ,cols
      ,access_predicates
      ,filter_predicates
      ,sum(cnt)over(partition by table_name,cols) total_by_cols
      ,cnt
   from agg
       ,xmltable(
          'string-join(for $c in /ROWSET/ROW/COL order by $c return $c,",")'
          passing 
             xmltype(
                cursor(
                   (select distinct
                       nvl(
                       regexp_substr(
                          access_predicates||' '||filter_predicates
                         ,'("'||alias||'"\.|[^.]|^)"([A-Z0-9#_$]+)"([^.]|$)'
                         ,1
                         ,level
                         ,'i',2
                       ),' ')
                       col
                    from dual
                    connect by 
                       level<=regexp_count(
                                 access_predicates||' '||filter_predicates
                                ,'("'||alias||'"\.|[^.]|^)"([A-Z0-9#_$]+)"([^.]|$)'
                              )
                   )
               ))
          columns cols varchar2(400) path '.'
       )(+)
   order by total_by_cols desc, table_name, cnt desc
)
select 
   table_name
  ,cols
  ,sum(cnt)over(partition by table_name,cols) total_by_cols
  ,access_predicates
  ,filter_predicates
  ,cnt
from cols
where rownum<=50
order by total_by_cols desc, table_name, cnt desc;

As you can see it shows top 50 predicates and their columns for last 3 hours. Despite the fact that ASH stores just sampled data, its results are representative enough for high-load databases.
Just few details:

• Column “COLS” shows “search columns”, and total_by_cols – their number of occurrences
• I think it’s obvious, that this info is not unambiguous marker of the problem, because for example few full table scans can misrepresent the statistics, so sometimes you will need to analyze such queries deeper (v$sqlstats,dba_hist_sqlstat)
• We need to group data by OBJECT_ALIAS within SQL_ID and plan_hash_value, because in case of index access with lookup to table(“table access by rowid”) some predicates are in the row with index access and others are in the row with table access.
  

Depending on the needs, we can modify this query to analyze ASH data by different dimensions, for example with additional analysis of partitioning or wait events.

Compare the results of the following query with the clause “fetch first 2 rows only”

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
                                 sys.ku$_objnumpair(1,1),
                                 sys.ku$_objnumpair(1,2),
                                 sys.ku$_objnumpair(1,3),
                                 sys.ku$_objnumpair(3,1),
                                 sys.ku$_objnumpair(3,2),
                                 sys.ku$_objnumpair(3,3)
                                 )))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
  *
from t,
     lateral(select t1.a,t2.b
             from t1,t2 
             where t1.a = t2.a 
               and t1.a = t.id
             order by t2.b
             fetch first 2 rows only
             )(+)
order by id;

        ID          A          B
---------- ---------- ----------
         1          1          1
         1          3          1
         2          1          1
         2          3          1
         3          1          1
         3          3          1
         4          1          1
         4          3          1
         5          1          1
         5          3          1
         6          1          1
         6          3          1
         7          1          1
         7          3          1

14 rows selected.

with this one (i’ve just commented out the line with “fetch-first-rows-only”:

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
                                 sys.ku$_objnumpair(1,1),
                                 sys.ku$_objnumpair(1,2),
                                 sys.ku$_objnumpair(1,3),
                                 sys.ku$_objnumpair(3,1),
                                 sys.ku$_objnumpair(3,2),
                                 sys.ku$_objnumpair(3,3)
                                 )))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
  *
from t,
     lateral(select t1.a,t2.b
             from t1,t2 
             where t1.a = t2.a 
               and t1.a = t.id
             order by t2.b
--             fetch first 2 rows only
             )(+)
order by id;

        ID          A          B
---------- ---------- ----------
         1          1          2
         1          1          3
         1          1          1
         2
         3          3          2
         3          3          1
         3          3          3
         4
         5
         6
         7

11 rows selected.

Obviously, the first query should return less rows than second one, but we can see that it returned more rows and join predicate “and t1.a = t.id” was ignored, because A and B are not empty and “A” is not equal to t.ID.

Everyone knows that rownum in inline views blocks many query transformations, for example pushing/pulling predicates, scalar subquery unnesting, etc, and many people use it for such purposes as a workaround to avoid unwanted transformations(or even CBO bugs).

select * 
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
  and column_value = 3;


COLUMN_VALUE
------------
           3
           3

select * 
from (select *
      from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
      where rownum <= 2
     )
where column_value = 3;

no rows selected

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
  *
from t,
     lateral(select/*+ no_decorrelate */ rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id
            )(+)
order by 1,2;

        ID         RN
---------- ----------
         1          1
         1          2
         2
         3          1
         3          2

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
  *
from t,
     lateral(select rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id
            )(+)
order by 1,2;

        ID         RN
---------- ----------
         1          1
         1          2
         2
         3          3
         3          4

Of course, we can draw conclusions even from these results: we can see that in case of decorrelation(query with hint) rownum was calculated before the join. But to be sure we can check optimizer’s trace 10053:

******* UNPARSED QUERY IS *******
SELECT VALUE(KOKBF$2) "ID", "VW_DCL_76980902"."RN" "RN"
  FROM TABLE("ODCINUMBERLIST"(1, 2, 3)) "KOKBF$2",
       (SELECT ROWNUM "RN_0", VALUE(KOKBF$0) "ITEM_3"
          FROM TABLE("ODCINUMBERLIST"(1, 3)) "KOKBF$0",
               TABLE("ODCINUMBERLIST"(1, 1, 3, 3)) "KOKBF$1"
         WHERE VALUE(KOKBF$0) = VALUE(KOKBF$1)
        ) "VW_DCL_76980902"
 WHERE "VW_DCL_76980902"."ITEM_3"(+) = VALUE(KOKBF$2)
 ORDER BY VALUE(KOKBF$2), "VW_DCL_76980902"."RN"

*************************

select
  *
from t,
     lateral(select rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id)(+)
order by 1,2;

was transformed to

select
  t.id, dcl.rn
from t,
     (select rownum rn 
      from t1,t2 
      where t1.a=t2.b) dcl
where dcl.a(+) = t.id
order by 1,2;

And it confirms that rownum was calculated on the different dataset (t1-t2 join) without join filter by table t.
I created SR with Severity 1 (SR #3-19117219271) more than a month ago, but unfortunately Oracle development do not want to fix this bug and moreover they say that is not a bug. So I think this is a dangerous precedent and probably soon we will not be able to be sure in the calculation of rownum and old fixes…

Just short note: If your imported Oracle Linux image hangs on boot in the Oracle cloud, just set GRUB_DISABLE_UUID=”true” in /etc/default/grub