Author Archives: Sayan Malakshinov

Simple function returning Parallel slave info

Posted on by Posted in oracle, parallel, query optimizing, SQL, statistics, troubleshooting 1,581 Page views Leave a comment

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

Posted on by Posted in Android, java, oracle, SQL 1,783 Page views Leave a comment

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

Posted on by Posted in deterministic functions, oracle, PL/SQL, PL/SQL optimization, query optimizing, SQL 1,893 Page views Leave a comment

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 a simple PL/SQL function that returns the result of the query select b from test where a=:input_var

But lets check what does 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, all values in the column OP are equal to the values of B, while, in turn, function F1 returns inconsistent values.

My presentations from RuOUG meetups

Posted on by Posted in oracle 1,549 Page views Leave a comment

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

8-sql-2596013-Использование-некоторых-новых-возможностей-SQLCBO-в-12сDownload
Intra-block-row-chaining(RuOUG)Download
Intra-block-row-chaining(RuOUG).pptxDownload
Когда-выбирается-план-с-большим-COSTDownload
Sayan Malakshinov. CBO – Query Transformations(RuOUG)Download

SQL: Fast ways to count unique characters in the string

Posted on by Posted in oracle, PL/SQL optimization, query optimizing, SQL 2,168 Page views Leave a comment

Test data:

create table t_str as
select round(dbms_random.value(1e10,9e10)) str from dual connect by level&lt;=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

Reading and analyzing trace file contents using just SQL

Posted on by Posted in 12.2, oracle, trace, troubleshooting 1,665 Page views Leave a comment

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?

Posted on by Posted in bug, CBO, documentation, oracle 1,538 Page views Leave a comment

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

Posted on by Posted in hints, oracle, query optimizing 1,967 Page views Leave a comment

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