Oracle Data Guard Role Change History

Oracle documentation lists oracle-data-guard-in-oracle-database-views. Having diagnostics pack purchased and available one may find information of role changes in data quard also from DBA_HIST_DATABASE_INSTANCE view. No need to grep alert log.

select instance_name
     , to_char(lead(startup_time)over(order by startup_time)-startup_time,'dd hh24:mi:ss') durat
     , case when lead(host_name)over(order by startup_time) != host_name then 'ROLECHANGE' end switc
     , host_name
     , i.* 
  from DBA_HIST_DATABASE_INSTANCE i
 order by i.startup_time desc
;

Sqlstat History

Oracle Diagnosics pack purchased? AWR reports reveal many aspects of database usage and behavior. In addition to Enterprise Manager Cloud control pages the dba_hist_* views are available. Here is a script that visualizes most elapsed_time and shows 18 figures of sql clauses used and captured in dba_hist_sqlstat view.

select trim(rpad(' ',ceil(t.elapsed_time/t.mxe*len),'*'))||trim(rpad(' ',ceil((t.mxe-t.elapsed_time)/t.mxe*len),'-')) vis
     , t.*
  from (
select s.*
     , max(s.elapsed_time)over() mxe
     , 80 len
from (
select min(trunc(sn.begin_interval_time,'hh24')) bg
     , max(trunc(sn.end_interval_time,'hh24')) en
     , max(sn.snap_id) snap_id
     , ss.parsing_schema_name
     , ss.module
--     , ss.action
--     , ss.sql_id, (select sql_text from dba_hist_sqltext t where ss.sql_id = t.sql_id and rownum = 1) sql_text
     , sum(ss.elapsed_time_delta) elapsed_time
     , sum(ss.sharable_mem) sharable_mem
     , sum(ss.version_count) version
     , sum(ss.fetches_delta) fetches
     , sum(ss.end_of_fetch_count_delta) end_of_fetch_count
     , sum(ss.sorts_delta) sorts
     , sum(ss.executions_delta) executions
     , sum(ss.loads_delta) loads
     , sum(ss.parse_calls_delta) parse_calls
     , sum(ss.buffer_gets_delta) buffer_gets
     , sum(ss.rows_processed_delta) rows_processed
     , sum(ss.cpu_time_delta) cpu_time
     , sum(ss.iowait_delta) iowait
     , sum(ss.plsexec_time_delta) plsexec_time
     , sum(ss.physical_read_requests_delta) physical_read_requests
     , sum(ss.physical_read_bytes_delta) physical_read_bytes
     , sum(ss.physical_write_requests_delta) physical_write_requests
     , sum(ss.physical_write_bytes_delta) physical_write_bytes
  from dba_hist_sqlstat ss
     , dba_hist_snapshot sn 
 where ss.snap_id = sn.snap_id
   and ss.instance_number = sn.instance_number
   and ss.dbid = ss.dbid
   and sn.end_interval_time > trunc(sysdate)
 group by ss.parsing_schema_name
     , ss.module
--     , ss.action
--     , ss.sql_id
     , sn.snap_id
) s ) t
order by module,vis
;

Access Path Suggestor and Generator -Mariadb

Earlier I have published accesspath suggestors for Oracle and Postgresql. Those are interpreting accesspath bidirectionally. Here is one for Mariadb. I quess it suits also for Mysql. I have not tried. This traverses the foreign key tree only in one direction. So the dependencies from a :roottable to :goaltablename in a :tableschema are reported. Also a query for the whole accesspath is generated.

with recursive fks as (
SELECT constraint_name,
       table_name,
       column_name,
       referenced_table_name,
       referenced_column_name
  FROM information_schema.key_column_usage
 WHERE table_schema = :tableschema 
   AND referenced_column_name IS NOT NULL
), pths as (
 select 1 lvl,
        cast(concat('/',table_name) as varchar(4000)) pth,
        constraint_name,
        table_name,
        column_name,
        referenced_table_name,
        referenced_column_name,
        cast(concat(referenced_table_name,', ',table_name,' ',constraint_name) as varchar(4000)) fro,
        cast(concat(referenced_table_name,'.',referenced_column_name,' = ',constraint_name,'.',column_name) as varchar(4000)) joi
   from fks
  where referenced_table_name = :roottablename
  union all
 select r.lvl+1,
        concat(r.pth,'/',c.table_name),
        c.constraint_name,
        c.table_name,
        c.column_name,
        c.referenced_table_name,
        c.referenced_column_name,
        concat(r.fro,', ',c.table_name,' ',c.constraint_name),
        concat(r.joi,' and ',r.constraint_name,'.',c.referenced_column_name,' = ',c.constraint_name,'.',c.column_name)
   from pths r, fks c
  where r.table_name  = c.referenced_table_name
) cycle table_name, referenced_table_name restrict
select concat('select * from ',fro,' where ',joi,';') sq
     , pths.pth
  from pths
 where table_name = :goaltablename
;

Aggregating Timestamp Ranges with Postgresql

Having timestamp ranges and need to combine those from several lines. Here is a rehearsal to create an user defined aggregate function range_agg with postgresql. Range_agg handles tsrange datatyped input parameter and returns an array of those. At the end there is an usage example with unnest(range_agg(aa)) to receive aggregated and generated ranges. Array is needed because aggregation of ranges can produce several ranges when input ranges do not overlap or meet each other. Ranges and ranges_final functions are used and implements the behavior of the range_agg(tsrange) aggregate.

create or replace function ranges(tsrange[],tsrange)
 returns tsrange[] as 
$$
  select array_append($1,$2);
$$ language 'sql' strict;

create or replace function ranges_final(tsrange[])
 returns tsrange[] as 
$$
with times as (
select st, en, max(newst) over(order by st,en) ledge
  from  (
 select st, en, case when st <= max(le) over(order by st,en) then null else st end as newst
   from (
  select st, en, lag(en) over(order by st, en) le
    from (
   select distinct lower(ra) st, upper(ra) en 
     from (
    select unnest($1) ra
    ) s0
   ) s1
  ) s2
 ) s3
), ranges as (
select ledge st, max(en) en
  from times
 group by ledge
 order by ledge
)
select array_agg(tsrange(st,en))
  from ranges;
$$ language 'sql' strict;

create or replace aggregate range_agg(tsrange)
(
    sfunc = ranges,
    stype = tsrange[], 
    finalfunc = ranges_final,
    initcond = '{}'    
);

select unnest(range_agg(ra)) ra from (
-- 06 - 07
select tsrange(current_date+time'06:00', current_date+time'07:00') ra
  union all
-- 08 - 10
select tsrange(current_date+time'08:00', current_date+time'09:00') ra
  union all
select tsrange(current_date+time'09:00', current_date+time'10:00') ra
  union all
-- 11 - 14
select tsrange(current_date+time'11:00', current_date+time'13:00') ra
  union all
select tsrange(current_date+time'12:00', current_date+time'14:00') ra
) aaa
;

["2020-09-08 06:00:00","2020-09-08 07:00:00")
["2020-09-08 08:00:00","2020-09-08 10:00:00")
["2020-09-08 11:00:00","2020-09-08 14:00:00")

Documentation links: rangetypes, functions-array, functions-aggregate, sql-createaggregate and xaggr

Unindex Postgresql

The issue of foreign keys without indexing was the starting point of my blog. Just browsing and I found a postgresql version of unindex query. Copied the query here. Visit the original authors page for more explanation and an example. Expecially liked the commented code /*the first index columns must be the same as the key columns, but order doesn't matter*/.

SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table,
       'create index '||c.conname||'_idx on '||c.conrelid::regclass||'('||string_agg(a.attname, ',' ORDER BY x.n)||');' idx
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        /* is there a matching index for the constraint? */
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid
           /* the first index columns must be the same as the
              key columns, but order doesn't matter */
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               @> c.conkey)
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;

Access Path Suggestor - Postgresql

Here is an improved access path suggestor from postgresql metadata. Based from my Oracle version. In addition to visualized path alternative this generates a query based on the found access path. Could be used in getting familiar with a new data model. Should work also with foreign keys with multiple columns. Cycle detection missing. Just give names of the tables whose path alternatives you are interested in as :foo and :bar binds.

WITH RECURSIVE fks as (
SELECT cc.fk_schematable,
       cc.fk_table,
       string_agg(a.attname, ',' ORDER BY x.n) AS fk_columns,
       cc.conname AS constraint_name,
       cc.uk_schematable,
       cc.uk_table,
       string_agg(b.attname, ',' ORDER BY y.n) AS pk_columns,
       string_agg(cc.uk_table||'.'||b.attname||' = '||cc.fk_table||'.'||a.attname, ' and ' ORDER BY y.n) AS joi
  FROM (
  SELECT c.conrelid::regclass as uk_schematable,
         pk.relname uk_table,
         c.conname,
         c.confrelid::regclass as fk_schematable,
         fk.relname fk_table,
         c.conkey,
         c.confkey,
         c.conrelid,
         c.confrelid
    FROM pg_catalog.pg_constraint c
   INNER JOIN pg_catalog.pg_class pk ON c.conrelid!=0 AND c.conrelid=pk.oid
   INNER JOIN pg_catalog.pg_class fk ON c.confrelid!=0 AND c.confrelid=fk.oid
   WHERE c.contype = 'f'
  ) cc
   /* enumerated key column numbers per foreign key */
 CROSS JOIN LATERAL
      unnest(cc.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each foreign key column */
  JOIN pg_catalog.pg_attribute a
    ON a.attnum = x.attnum
   AND a.attrelid = cc.conrelid
 CROSS JOIN LATERAL
      unnest(cc.confkey) WITH ORDINALITY AS y(attnum, n)
   /* name for each unique key column */
  JOIN pg_catalog.pg_attribute b
    ON b.attnum = y.attnum
   AND b.attrelid = cc.confrelid
 WHERE x.n=y.n 
 GROUP BY cc.fk_schematable, cc.fk_table, cc.conname, cc.uk_schematable, cc.uk_table
), pths as (
SELECT uk_schematable as o1,uk_table as t1,constraint_name,fk_schematable as o2,fk_table as t2,'-<' dir, joi
  FROM fks
 UNION ALL
SELECT fk_schematable as o1,fk_table as t1,constraint_name,uk_schematable as o2,uk_table as t2,'>-' dir, joi
  FROM fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth,fro,joi) as (
SELECT o1,t1,constraint_name,o2,t2,dir,1 lvl,o1||dir||o2 pth, o1||' '||t1||', '||o2||' '||t2, joi
  FROM pths
 WHERE t1 = :foo
 UNION ALL 
SELECT s.o1,s.t1,s.constraint_name,s.o2,s.t2
     , s.dir,lvl+1
     , prio.pth||s.dir||s.o2
     , prio.fro||', '||s.o2||' '||s.t2
     , prio.joi||' and '||s.joi
 FROM rcte prio INNER JOIN pths s ON prio.o2=s.o1 AND prio.constraint_name != s.constraint_name
WHERE prio.lvl < 30
)
SELECT pth,'select * from '||fro||' where '||joi||';' sq
  FROM rcte
 WHERE t2 = :baz
 ORDER BY lvl,pth
;

Testing

create table foo(i int primary key);

create table bar(j int primary key, i int references foo);

create table baz(k int primary key, j int references bar);

pth             sq
foo>-bar>-baz select * from foo foo, bar bar, baz baz where bar.i = foo.i and baz.j = bar.j;

Pascal Matrix - Wishes Come True

Years ago I made a wish in my blog that an analytic function should work in a recursive query. My approach to generate Pascal Matrix seems to work with Oracle 19c database.

with n (u) as (
select 1 from dual
union all
select n.u+1 
  from n
 where n.u < 8
), q as (
select n.u v, m.u w 
  from n, n m 
), r (v,w,s,d,e) as (
select v,w, v,w,sum(w)over(order by w)
  from q
 where v = 1
union all
select q.v,q.w
      ,r.d
      ,r.e
      ,sum(r.e)over(order by r.w)
  from r 
 inner join q 
    on r.w=q.w and r.v+1=q.v
)
select v,w,s
  from r
;

Alter view and dba_dependencies

Adding constraints to a view. Maybe better to consider recreating. Dependencies are populated more complete that way.

create table huba(a int primary key);

create table hubb(b int primary key);

create table link(a int references huba, b int references hubb);


create or replace view fact as (select a,b from link);

alter view fact add constraint f_a_fk foreign key (a) references huba disable;

alter view fact add constraint f_b_fk foreign key (b) references hubb disable;

select referenced_name from dba_dependencies where name = 'FACT';

--HUBB
--LINK

create or replace view fact (
    a
  , b
  , constraint f_a_fk foreign key (a) references huba disable
  , constraint f_b_fk foreign key (b) references hubb disable
)as (select a,b from link);

select referenced_name from dba_dependencies where name = 'FACT';

--HUBA
--HUBB
--LINK

drop table huba cascade constraints purge;

drop table hubb cascade constraints purge;

drop view fact;

Errors With Depth First Hierarchical Query and Table Of Types

Struggling with a hierarchical query and table of types. Getting ORA-00600: internal error code, arguments: [koxsi2sz1] and [rworupo.1] errors with 12.1.0.2 version.

WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH breadth FIRST BY operand SET order1
SELECT * FROM Factorial
;

Breadth first and everything is fine. But changing to depth first the problems occur.

WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH depth FIRST BY operand SET order1
SELECT * FROM Factorial
;

With 18.4 version the error message is more tolerable ORA-00932: inconsistent datatypes: expected UDT got SYS.ODCINUMBERLIST. But no success with the results.

Getting around the problem is to create a NumberListWrapper wrapper type for the array type. The workaround seems to work at least with 12.2 and 18.4 versions.

CREATE OR REPLACE TYPE NumberListWrapper AS OBJECT (
    numbertable sys.odciNumberlist,
    MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC
);
/

CREATE OR REPLACE TYPE BODY NumberListWrapper AS
    MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC IS
    BEGIN
        RETURN 1;
    END;
END;
/


WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, NumberListWrapper(sys.odcinumberlist(1,2)) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH DEPTH FIRST BY operand SET order1
SELECT * FROM Factorial
;

Select For Update Locks Joined Rows

Select for update locks the joined rows even thou columns from the table are not in the select list. The rows used in a predicate sub query are not locked. Here is an example. First preparing tables:

drop table bar;

drop table foo;

create table foo as select column_value i from table(sys.odcinumberlist(1,2,3,4));

alter table foo add constraint foo_pk primary key (i);

create table bar as select i,i j from foo;

alter table bar add constraint bar_pk primary key(j);

alter table bar add constraint bar_foo_fk foreign key(i) references foo;

Selecting for update. The foo table used in exists predicate is not locked.

select j from bar where j = 1 and exists (select 0 from foo where foo.i=bar.i) for update;

--only the row in BAR table is locked

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) BAR

rollback;

When joining is used, also the joined row in FOO table is locked

select bar.j from bar inner join foo on foo.i=bar.i where j = 1 for update;

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) FOO
--Row-X (SX) BAR

rollback;

Access Path Suggestor

Reverse engineering an existing schema or creating a new sql query. Join access paths are often following foreign keys. Here is a query that searches foreign key dependency paths between two tables.

with fks as(
select pk.owner o1, pk.table_name t1, fk.constraint_name, fk.owner o2, fk.table_name t2
  from all_constraints fk, all_constraints pk
 where fk.r_owner= pk.owner
   and fk.r_constraint_name = pk.constraint_name
   and fk.constraint_type = 'R' 
), pths as (
 select o1,t1,constraint_name,o2,t2,'-<' dir
   from fks
 union all
 select o2,t2,constraint_name,o1,t1,'>-' dir
   from fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth) as (
 select o1,t1,constraint_name,o2,t2,dir,1,o1||'.'||t1||dir||constraint_name||'-'||o2||'.'||t2
   from pths
 where o1 = :owner1
   and t1 = :table1
 union all 
 select s.o1,s.t1,s.constraint_name,s.o2,s.t2
      , s.dir,lvl+1
      , prio.pth||s.dir||case when s.constraint_name not like 'SYS\_%' escape '\' then s.constraint_name||'-' end||s.o2||'.'||s.t2
  from rcte prio inner join pths s on prio.o2=s.o1 and prio.t2=s.t1 and prio.constraint_name != s.constraint_name
 where prio.lvl < 7
) cycle o1,t1 set cycle to 1 default 0
 select lvl,cycle,pth
   from rcte
 where o2 = :owner2
   and t2 = :table2
  order by lvl,pth
;

Finding shortest paths in lenght from a hierarchy

A slight modification to my previous post. This way it is possible to find shortest paths from all start nodes in a graph to a specified end point.

with tre as (
select 0 parent, 1 child, 1 len from dual union all
select 1 parent, 2 child, 2 len from dual union all
select 2 parent, 3 child, 3 len from dual union all
select 3 parent, 1 child, 4 len from dual union all
select 3 parent, 4 child, 5 len from dual union all
select 4 parent, 5 child, 6 len from dual union all
select 5 parent, 6 child, 7 len from dual union all
select 2 parent, 5 child, 20 len from dual
), rcte( root, parent, child, len, lvl, foundpth, pthlen, minpthlen, pth) as (
select t.parent
     , t.parent
     , t.child
     , t.len
     , 1 lvl
     , case when t.child = :endnode then 1 else 0 end foundpth
     , t.len pthlen
     , case when t.child = :endnode then t.len else 9999999 end minpthlen
     , t.parent||'-'||t.child pth
  from tre t
 where t.parent != :endnode
union all
select r.root
     , t.parent
     , t.child
     , t.len
     , r.lvl+1 lvl
     , case when t.child = :endnode then 1 else 0 end
     , r.pthlen+t.len pthlen
     , min(case when t.child = :endnode then r.pthlen+t.len else 9999999 end)over(partition by r.root) minpthlen
     , r.pth||'-'||t.child pth
  from tre t, rcte r
 where t.parent = r.child 
   and r.pthlen + t.len < r.minpthlen 
  ) search breadth first by parent,child set ordr
    cycle parent,child set cycle to 1 default 0
, pths as (
select root,child,lvl,pthlen,pth,cycle,foundpth,row_number()over(partition by root order by pthlen, lvl, pth) rn
  from rcte
 where foundpth = 1
)
select * 
  from pths 
 where rn=1
 order by root,pthlen, lvl, pth 
;

Finding shortest path in lenght from a hierarchy

Using an analytical function to find out when to stop browsing a hierarchy. Here is an example without further explanation what is happening.

with tre as (
select 0 parent, 1 child, 1 len from dual union all
select 1 parent, 2 child, 2 len from dual union all
select 2 parent, 3 child, 3 len from dual union all
select 3 parent, 1 child, 4 len from dual union all
select 3 parent, 4 child, 5 len from dual union all
select 4 parent, 5 child, 6 len from dual union all
select 5 parent, 6 child, 7 len from dual union all
select 2 parent, 5 child, 20 len from dual
), rcte( root, parent, child, len, lvl, foundpth, pthlen, minpthlen, pth) as (
select t.parent
     , t.parent
     , t.child
     , t.len
     , 1 lvl
     , case when t.child = :endnode then 1 else 0 end foundpth
     , t.len pthlen
     , case when t.child = :endnode then t.len else 9999999 end minpthlen
     , t.parent||'-'||t.child pth
  from tre t
 where t.parent = :startnode
   and t.parent != :endnode
union all
select r.root
     , t.parent
     , t.child
     , t.len
     , r.lvl+1 lvl
     , case when t.child = :endnode then 1 else 0 end
     , r.pthlen+t.len pthlen
     , min(case when t.child = :endnode then r.pthlen+t.len else 9999999 end)over() minpthlen
     , r.pth||'-'||t.child pth
  from tre t, rcte r
 where t.parent = r.child 
   and r.pthlen + t.len < r.minpthlen 
  ) search breadth first by parent,child set ordr
    cycle parent,child set cycle to 1 default 0
select *
  from rcte
 where foundpth = 1
 order by minpthlen, lvl, pth 
 fetch first row only
;

A slow SQL using TEMP, when and how much.

A really slow SQL clause using a lot of temp space. How much and when? Here is a query that reports hourly maximum usage of temp from ASH.

select dy
     , hr
     , coalesce(tmp,0) tmp
     , coalesce(tm,' ') tm
  from (
  select trunc(sample_time,'hh24') samplehr
        , max(tempgb) tmp
        , rpad(' ',max(tempgb)/mx*80,'*') tm
   from ( 
   select sample_time
        , tempgb
        , max(tempgb)over() mx
     from (
    select sample_time
         , sample_id
         , trunc(sum(temp_space_allocated)/1024/1024/104) tempgb
      from dba_hist_active_sess_history 
     where sql_id like coalesce(:sql_id,'%')
     group by sample_time,sample_id
         )
        )
   group by trunc(sample_time,'hh24') 
          , mx
      ) tmps 
 right outer join (
 select hr - level/24 hr 
      , to_char(hr - level/24,'dy','NLS_DATE_LANGUAGE = AMERICAN') dy
   from (
  select trunc(min(sample_time),'hh24') mi
       , trunc(sysdate,'hh24') hr
    from dba_hist_active_sess_history
       ) connect by level < (hr - mi)*24
      ) hours 
    on hours.hr=tmps.samplehr
order by hr desc
;

If you need more detailed information just browse the views as I have done earlier.

Tables that a query is touching

Here is a query that tells tables that a query is using. Give a sql_id as a parameter.

  select distinct t.owner, t.table_name, t.degree, t.num_rows, t.last_analyzed, t.partitioned
    from v$sql_plan p, dba_tables t 
   where p.sql_id = :sql_id  
     and (p.object_owner,p.object_name) in (
            select t.owner,t.table_name 
              from dual
            union all
            select owner,index_name
              from dba_indexes i
             where i.table_owner = t.owner 
               and i.table_name = t.table_name )
   order by t.owner, t.table_name
;

Earlier I have posted SQL queries to find
-Tables that a view or a procedure is using: http://rafudb.blogspot.fi/2013/04/tables-used.html
-Information about queries that have been touching a table. Diagnostics pack needed http://rafudb.blogspot.fi/2014/11/ash-mining-slow-queries.html

Passing a PL/SQL Boolean Parameter in a SQL clause

Oracle SQL does not have a boolean data type. Here is an example how to pass a PL/SQL boolean parameter to a function in a SQL statement. This is using 12c version ability to declare a function in with part of a query.

rollback;

with function begin_transaction return varchar2 is 
 begin
  dbms_lock.sleep(2);
  return dbms_transaction.LOCAL_TRANSACTION_ID(TRUE);
 end;
select systimestamp beforetime
     , dbms_transaction.LOCAL_TRANSACTION_ID not_in_a_transaction
     , begin_transaction
     , dbms_transaction.LOCAL_TRANSACTION_ID inside_a_transaction
     , systimestamp aftertime
  from dual
;

beforetime                not_in_a_transaction  begin_transaction  inside_a_transaction  aftertime
27.06.2017 16:44:23,134   (null)                5.24.3524          5.24.3524             27.06.2017 16:44:23,134

SQL function systimestamp is returning consistent results inside a cursor. Columns beforetime and aftertime returns the same time allthou pl/sql function call to declared begin_transaction is coded to take two seconds in between. PL/SQL function calls inside a SQL clause have some order in which they are executed as one can see form this example results. The first call of dbms_transaction.LOCAL_TRANSACTION_ID for not_in_a_transaction returns null. Second call for dbms_transaction.LOCAL_TRANSACTION_ID function starts an transaction as it gets TRUE parameter. The third call returns the transaction id in inside_a_transaction.

Truncate table partition cascade

Trying to truncate partitions on a parent table that has a reference partitioned child table. Both have global indexes supporting primary keys. So during truncate also update indexes clause needs to be used. Using the documented syntax we hit ORA-14126 error. Here is an example and correction to the situation.

drop table c purge;

drop table p purge;

create table p(a int constraint a_pk primary key, b int) partition by list(b)(partition p1 values(1),partition p2 values (2));

create table c(b int constraint b_pk primary key, a not null constraint c_p_fk references p on delete cascade) partition by reference(c_p_fk);


insert into p values(1,1);
insert into p values(2,2);

insert into c values(1,1);
insert into c values(2,2);

commit;

truncate table p drop storage cascade;

alter table p truncate partition p1 drop storage update global indexes cascade;

ORA-14126: only a  may follow description(s) of resulting partitions
14126. 00000 -  "only a  may follow description(s) of resulting partitions"
*Cause:    Descriptions of partition(s) resulting from splitting of a
           table or index partition may be followed by an optional
            which applies to the entire statement and
           which, in turn, may not be followed by any other clause.
*Action:   Ensure that all partition attributes appear within the
           parenthesized list of descriptions of resulting partitions in
           ALTER TABLE/INDEX SPLIT PARTITION statement.

So the documented syntax is not working http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#i2131210 and https://docs.oracle.com/database/121/VLDBG/GUID-92748418-FB88-4A41-9CEF-E44D2D9A6464.htm

The working place for cascade word is before update indexes clause.

alter table p truncate partition p1 drop storage cascade update global indexes;

Submitted a documentation bug today.

Visualizing slow sql execution

A slow sql execution vanished from sql monitor. Take a look from awr. It was almost six hours of execution time. Which sql plan lines was the execution spending its time?

select sql_plan_line_id
     , cnt
     , mi
     , mx
     , trim(rpad(' ',((aa-sta)/nullif(aa,0))*len,'-'))
     || trim(rpad(' ',((sta-sto)/nullif(aa,0))*len,'*'))
     || trim(rpad(' ',(sto/nullif(aa,0))*len,'-')) t
from (
select 80 len
     , sql_exec_start
     , sql_plan_line_id
     , cnt
     , to_char(mis,'hh24:mi:ss') mi
     , to_char(mxs,'hh24:mi:ss') mx
     , max(mxs)over(partition by sql_exec_start)-min(mis)over(partition by sql_exec_start) dur
     , max(cast(mxs as date))over(partition by sql_exec_start)-min(cast(mis as date))over(partition by sql_exec_start) aa
     , max(cast(mxs as date))over(partition by sql_exec_start)-cast(mis as date) sta
     , max(cast(mxs as date))over(partition by sql_exec_start)-cast(mxs as date) sto
from (
select sql_exec_start
     , sql_plan_line_id
     , count(*) cnt
     , min(sample_time) mis
     , max(sample_time) mxs
  from dba_hist_active_sess_history 
 where sql_plan_hash_value = 917708421
   and sql_id = 'cnf5jz56h4swp'
   and trunc(sample_time) = date'2017-01-20'
 group by sql_exec_start,sql_plan_line_id 
)
)
order by sql_exec_start,sql_plan_line_id
;

PLANLINEID CNT    MI          MX          T      
1          2      16:21:42    16:23:02    ----------------------------------------------------------------------------
3          155    09:18:25    16:22:52    --**************************************************************************
4          8      11:37:38    16:11:21    ---------------------------************************************************-
5          1      13:03:45    13:03:45    ----------------------------------------------------------------------------
8          16     09:00:34    14:38:03    ***********************************************************-----------------
11         2      09:00:24    09:00:54    ----------------------------------------------------------------------------
12         4      09:01:04    09:01:34    ----------------------------------------------------------------------------
13         1      10:03:09    10:03:09    ----------------------------------------------------------------------------
14         36     09:11:15    14:47:54    ***********************************************************-----------------
15         697    09:02:54    14:48:24    *************************************************************---------------
16         469    09:01:44    14:48:44    *************************************************************---------------
17         2      12:43:53    14:40:23    ---------------------------------------********************-----------------
18         27     09:50:58    14:37:53    --------**************************************************------------------
19         557    09:04:24    14:48:34    *************************************************************---------------
20         235    09:02:14    14:45:54    *************************************************************---------------
21         29     09:45:38    14:42:23    -------****************************************************-----------------
22         345    14:50:24    16:20:32    --------------------------------------------------------------**************
24         10     14:48:54    14:50:44    ----------------------------------------------------------------------------
25         1      14:49:44    14:49:44    ----------------------------------------------------------------------------
26         15     14:57:55    15:05:25    ----------------------------------------------------------------------------
27         2      14:50:54    14:51:04    ----------------------------------------------------------------------------
28         1      14:57:35    14:57:35    ----------------------------------------------------------------------------
29         1      14:54:04    14:54:04    ----------------------------------------------------------------------------
30         12     14:51:54    14:53:54    ----------------------------------------------------------------------------
31         5      14:51:14    14:52:14    ----------------------------------------------------------------------------
32         11     14:55:35    14:57:25    ----------------------------------------------------------------------------
34         7      14:54:35    14:55:45    ----------------------------------------------------------------------------
35         1      14:54:14    14:54:14    ----------------------------------------------------------------------------
36         1      14:54:25    14:54:25    ----------------------------------------------------------------------------

Plan Shaping and Cardinality Miss Estimate on row_number over partition by

Recalling talks with Tim Hall I wrote in my last post. He mentionend Jonathan Lewis telling about plan shaping. Every now and then there comes a need to tell the optimizer where the query execution should be starting. Write your query from part in order and use ordered hint or most often leading hint will be enough. Hitting the need for such plan shaping comes when the optimizer sees inline views in a query returning only one row and actually there are more in execution time. If there are several missleading one liners and a cartesian join between those a quite simple query can consume significant query time. 12.1.0.2 database has a anoying bug involving top-n queries with analytic partition by part. Luckily a patch for the 21971099 bug is available for some environments. Here is a simplified test getting cardinality one. Imagine having a couple of such in your bigger query.

create table s as (
select round(level/2) n 
     , level n2 
 from dual connect by level < 10e4
)
;

select /*+gather_plan_statistics*/ n
     , n2
  from (
   select n
        , n2
        , row_number()over(partition by n order by n2 desc) rn
     from s)
 where rn <= 1
;

select * 
  from table(dbms_xplan.display_cursor(format=>'iostats last'))
;

Plan hash value: 2407482549
 
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |  50000 |00:00:00.12 |     208 |
|*  1 |  VIEW                    |      |      1 |      1 |  50000 |00:00:00.12 |     208 |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |  99999 |  50000 |00:00:00.11 |     208 |
|   3 |    TABLE ACCESS FULL     | S    |      1 |  99999 |  99999 |00:00:00.02 |     208 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN"<=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "N" ORDER BY 
              INTERNAL_FUNCTION("N2") DESC )<=1)

While waiting for the patching to happen, an alternative to bypass the problem is not to use row_number analytic function. By using rank and order by part that won't return competing winners, we get another cardinality estimate. It is as much wrong as the earlier one, but most likely will help with the cartesian join problem.

select /*+gather_plan_statistics*/ n
     , n2
  from (
   select n
        , n2
        , rank()over(partition by n order by n2 desc, rowid) rn
     from s)
 where rn <= 1
;

select * 
  from table(dbms_xplan.display_cursor(format=>'iostats last'))
;

Plan hash value: 2407482549
 
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |  50000 |00:00:00.13 |     208 |
|*  1 |  VIEW                    |      |      1 |  99999 |  50000 |00:00:00.13 |     208 |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |  99999 |  50000 |00:00:00.11 |     208 |
|   3 |    TABLE ACCESS FULL     | S    |      1 |  99999 |  99999 |00:00:00.02 |     208 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN"<=1)
   2 - filter(RANK() OVER ( PARTITION BY "N" ORDER BY INTERNAL_FUNCTION("N2") DESC 
              ,ROWID)<=1)

Trip to BGOUG Conference Pravets 10.-13.11.2016

It was a day in this autumn. I had put a marker to my calendar. Bulgarian Oracle User Group conference 2016 call for papers deadline was that day. Toon Koppelaars will be there. I could talk about ideas behind SQL Assertion implementation. Vote here if you have not yet voted yet. 5000 votes already. I did not know how could I fit the trip to my calendar. I did not know it even then but decided to submit. My presentation "Hub Insert ORA-00001" got accepted. And after a while I started booking flights to Sofia. There was a possibility to choose such flight that I did not have to start my journey too early in the morning and I would get in time to reach the speakers dinner.

While preparing my slides I noticed that my abstract I promised to talk about parallelism. Concurrency issues was the ones I was prepared for. Well just need to add content to the presentation. Parallelism on top of SQL calls cause concurrency issues and parallelism under a SQL clause should speed up the execution. Had to figure out a way to fit a parallel SQL execution example to talk about in some minutes. This is the time when you learn and find out new stuff. While gathering information you learn new stuff. This time one thing to unlearn from 11G days was how 12c parallel dml is able to populate same segments from different processes. Further reading in a oracle optimizer blog post.

The morning of leaving from home came. My first flight to München will be delayed. The latter flight had to be moved to evening flight. I will miss the speakers dinner. Arriving to Sofia 10pm. If I had arrived with my planned flight, conference organizer transportation would have been available. Had to take a taxi. I choose the yellow official cab company next to the station. Not a pleasant journey to Pravets. Taxi driver stopping three times. Once on a dark motorway side about should I pay 100EUR to the driver that he would continue the trip. I gave some money and the rest of the 60km journey continued. Meter was not running anymore. Arrived to the hotel. Somehow I was not sleepy even thou travelling 15 hours already. Went for an one hour walk around the hotel.

Morning walk around a lake wearing short pants. While I left home there was five centimeters snow and -8 celsius cold. Seminar registration, breakfast and on with the show. I got to see good talks. Julian Dontcheff tweaking his laptop virtual machine database super_fast "with a parameter". Toon explaining thickdb approach. Flame graphs explained. Network speed and layers influence performance. Somethings about Oracle in the Cloud. Pluggable databases used in development cycle and also what will they brake. Surprisingly not so much fuss about in-memory option. Full rooms for some sessions. My session had plenty of space. Thanks for those who attended. Hopefully you got something out of it.

In addition to the talks I heard one major reason to attend these kind of seminars is to meet people and get to talk about issues. This conference had a well planned schedule for that. At least 15 minutes pause in between each session. Here are some.

Even thou I did not get to see OBIEE presentations by Gianni Ceresa or Christian Berg it was nice meeting you.

I gave some minor feedback to Martin Widlake about hist talks how Oracle works animations.

Michal Šimoník "join talk" got a slide about qube join.

Straight from Nigel Bayliss, Optimizer Product Manager I got encouragement to my suspicion that my struggling with OLTP system parse times will have some other issues also than the known 12c adaptivity problems. Seems like we found something just yesterday a day after the seminar. Maybe a place to another blog post.

I did not get to talk with Toon about SQL assertions. But at the Sunday breakfast changed some words with Bryn Llewellyn. The vote page is not promising assertion word to be implemented. Will there be some more weight to parsing time? And as those men are influencing the implementation will it be supported at first phases behind the scenes of thickdb approach. We will see. Hopefully sooner than later. So vote.

There was talks also with Neil Chandler and many others.

After the presentations and before dinner time I had also time to go to a Finnish Sauna and watch ice hockey. Tappara winning two matches. The flight home was more enjoyable than going there as I had got business class tickets at the same price than economy tickets would have cost. I was quite relaxed. I read almost a full book by Miika Nousiainen that I purchased for the trip.

On a way home nice talks with Tim Hall and Gianni while waiting at airport.

Thank you Milena Gerova and BGOUG staff for letting me to be a speaker in your event. This was the first time I did a presentation in foreign user group other than Ougf.