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