Rafu on db

2019-01-09

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
;

2018-10-23

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 
;

2018-03-08

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
;

2017-10-18

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.

2017-08-22

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

2017-06-27

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.

2017-06-22

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.

About Me

My photo
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.