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;