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 ;
Rafu on db
2023-09-14
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.
2023-02-03
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 ;
2020-09-08
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
2019-10-07
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;
2019-10-03
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;
2019-10-02
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 ;
2019-09-09
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;
Subscribe to:
Posts (Atom)
About Me
- Rafu
- I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.