XTended Oracle SQL
pySyncOracleStandby – Simple sync service for Oracle manual standby
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
Format SQL or PL/SQL directly in Oracle database
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.
1. load appropriate java library into OracleYou 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.
2. set java permissionsThen 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', '' );3. create java functions
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; } } /4. Create PL/SQL package for it
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

Funny friday Oracle SQL quiz: query running N seconds
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;
Simple function returning Parallel slave info
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
Android Oracle Client 2.0
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:
PL/SQL functions and statement level consistency
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.
Just short note for myself: OJPPD limitations
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.
My presentations from RuOUG meetups
I forgot to share my files from presentations, so I’m going to keep them here:
SQL: Fast ways to count unique characters in the string
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
Oracle scheduler: how to find jobs with wrong nls_env
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;
Reading and analyzing trace file contents using just SQL
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;
Laterals: is (+) documented for laterals?
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
flashback_transaction_query: fixed table full table scan
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
SQL: Split periods
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' );
Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions
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)
And compare with this:
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.
v$sql_hint.target_level
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
Top time-consuming predicates from ASH
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.
Another bug with lateral
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.
Lateral view decorrelation(VW_DCL) causes wrong results with rownum
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).
If we pull the predicate “column_value = 3” from the following query to higher level
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
we will get different results:
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
Doc ID 62340.1
But we recently encountered a bug with it: lateral view with ROWNUM returns wrong results in case of lateral view decorrelation.
Compare results of this query with and without no_decorrelation hint:
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" *************************
I’ll modify it a bit just to make it more readable:
we can see that
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…
Oracle Linux hangs after “probing EDD” in Oracle Cloud
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