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

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)

Trip to BGOUG Conference Pravets 10.-13.11.2016

It was a day in this autumn. I had put a marker to my calendar. Bulgarian Oracle User Group conference 2016 call for papers deadline was that day. Toon Koppelaars will be there. I could talk about ideas behind SQL Assertion implementation. Vote here if you have not yet voted yet. 5000 votes already. I did not know how could I fit the trip to my calendar. I did not know it even then but decided to submit. My presentation "Hub Insert ORA-00001" got accepted. And after a while I started booking flights to Sofia. There was a possibility to choose such flight that I did not have to start my journey too early in the morning and I would get in time to reach the speakers dinner.

While preparing my slides I noticed that my abstract I promised to talk about parallelism. Concurrency issues was the ones I was prepared for. Well just need to add content to the presentation. Parallelism on top of SQL calls cause concurrency issues and parallelism under a SQL clause should speed up the execution. Had to figure out a way to fit a parallel SQL execution example to talk about in some minutes. This is the time when you learn and find out new stuff. While gathering information you learn new stuff. This time one thing to unlearn from 11G days was how 12c parallel dml is able to populate same segments from different processes. Further reading in a oracle optimizer blog post.

The morning of leaving from home came. My first flight to München will be delayed. The latter flight had to be moved to evening flight. I will miss the speakers dinner. Arriving to Sofia 10pm. If I had arrived with my planned flight, conference organizer transportation would have been available. Had to take a taxi. I choose the yellow official cab company next to the station. Not a pleasant journey to Pravets. Taxi driver stopping three times. Once on a dark motorway side about should I pay 100EUR to the driver that he would continue the trip. I gave some money and the rest of the 60km journey continued. Meter was not running anymore. Arrived to the hotel. Somehow I was not sleepy even thou travelling 15 hours already. Went for an one hour walk around the hotel.

Morning walk around a lake wearing short pants. While I left home there was five centimeters snow and -8 celsius cold. Seminar registration, breakfast and on with the show. I got to see good talks. Julian Dontcheff tweaking his laptop virtual machine database super_fast "with a parameter". Toon explaining thickdb approach. Flame graphs explained. Network speed and layers influence performance. Somethings about Oracle in the Cloud. Pluggable databases used in development cycle and also what will they brake. Surprisingly not so much fuss about in-memory option. Full rooms for some sessions. My session had plenty of space. Thanks for those who attended. Hopefully you got something out of it.

In addition to the talks I heard one major reason to attend these kind of seminars is to meet people and get to talk about issues. This conference had a well planned schedule for that. At least 15 minutes pause in between each session. Here are some.

Even thou I did not get to see OBIEE presentations by Gianni Ceresa or Christian Berg it was nice meeting you.

I gave some minor feedback to Martin Widlake about hist talks how Oracle works animations.

Michal Šimoník "join talk" got a slide about qube join.

Straight from Nigel Bayliss, Optimizer Product Manager I got encouragement to my suspicion that my struggling with OLTP system parse times will have some other issues also than the known 12c adaptivity problems. Seems like we found something just yesterday a day after the seminar. Maybe a place to another blog post.

I did not get to talk with Toon about SQL assertions. But at the Sunday breakfast changed some words with Bryn Llewellyn. The vote page is not promising assertion word to be implemented. Will there be some more weight to parsing time? And as those men are influencing the implementation will it be supported at first phases behind the scenes of thickdb approach. We will see. Hopefully sooner than later. So vote.

There was talks also with Neil Chandler and many others.

After the presentations and before dinner time I had also time to go to a Finnish Sauna and watch ice hockey. Tappara winning two matches. The flight home was more enjoyable than going there as I had got business class tickets at the same price than economy tickets would have cost. I was quite relaxed. I read almost a full book by Miika Nousiainen that I purchased for the trip.

On a way home nice talks with Tim Hall and Gianni while waiting at airport.

Thank you Milena Gerova and BGOUG staff for letting me to be a speaker in your event. This was the first time I did a presentation in foreign user group other than Ougf.

Installing Oracle 12.1.0.2 on Red Hat 7

This is not a complete guide how to do the installation. Just a note how to get a RPM-GPG-KEY-oracle file to Red Hat 7. Go to oracle-base for installation notes on oel7.

Installation documentation is mentioning: "Starting with Oracle Database 12c Release 1 (12.1.0.2), Oracle Linux 7 and Red Hat Enterprise Linux 7 are supported on Linux x86-64 systems." So do not try to install 12.1.0.1 on Red Hat 7. The case when you have SE one licence and not yet buying SE2 license. Just to mention 11.2 is supported to be installed on Red Hat Enterprise Linux 7.

Download the information about Oracle public yum repo https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-downloading-yum-repo.html

cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol7.repo

Try to install preinstall package and get an error.

yum install oracle-rdbms-server-12cR1-preinstall

...

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

GPG key retrieval failed: [Errno 14] curl#37 - "Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle"

You need the file mentioned here https://docs.oracle.com/cd/E37670_01/E39381/html/ol_import_gpg.html

wget http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol7 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

And you will succeed installing:

yum install oracle-rdbms-server-12cR1-preinstall

SUM over time -Elements

A while ago wrote how to calculate sum over time. During overlapping time intervals the resulting sum is produced there. But how about if one needs the elements that the sum is generated from?

2015-07-25 2015-07-26 1
2015-07-26 2015-07-28 2
2015-07-27 2015-07-30 3

The testdata is generated in td view.

with td as (
 select trunc(sysdate) + mod(level, 4) validFrom
      , trunc(sysdate) + mod(level, 8) * 2 validTill
      , level a
   from dual
connect by level < 4
), b as (
  select row_number()over(order by case r when 1 then validFrom else validTill end,r desc) rn
       , a
       , r
   , case r when 1 then validFrom else validTill end validFrom
   , sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
   , sum(case r when 1 then 1 else -1 end) over (order by case r when 1 then validFrom else validTill end,r desc) cntover
   from td, (
  select rownum r from dual connect by level < 3
  )
),c (rn,a,r,validfrom,sumover,cntover,vals) as (
 select rn,a,r,validfrom,sumover,cntover,mdsys.SDO_NUMTAB(a) from b where rn = 1
union all
select b.rn,b.a,b.r,b.validFrom,b.sumover,b.cntover,case b.r when 1 then c.vals multiset union mdsys.SDO_NUMTAB(b.a) else c.vals multiset except mdsys.SDO_NUMTAB(b.a) end
  from b,c where b.rn = c.rn+1 
),d as (select max(cntover)over(partition by validFrom) mx,a,r,validFrom,sumover,cntover,(select count(*) from table(vals)) n,vals, count(*)over(partition by validFrom) cn 
  from c 
  where cntover=(select count(*) from table(vals))
) 
select validFrom
     , nvl(lead(validFrom) over (order by validFrom)
         , to_date('22000101','yyyymmdd')) validTill
     , sumover
     , (select listagg(column_value,'+')within group(order by column_value) from table(vals)) vals
  from d
 where cntover=mx
order by validFrom;

2015-07-25 2015-07-26 1 1
2015-07-26 2015-07-27 2 2
2015-07-27 2015-07-28 5 2+3
2015-07-28 2015-07-30 3 3
2015-07-30 2200-01-01 0