Rafu on db

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

2017-06-20

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    ----------------------------------------------------------------------------

2016-12-01

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)

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.