Rafu on db


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


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 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 (, Oracle Linux 7 and Red Hat Enterprise Linux 7 are supported on Linux x86-64 systems." So do not try to install 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 


ASH Mining Slow Queries

"Database is slow." It was fast three days ago. We know the problem table but no queries are informed to the dba. Luckily we have diagnostics pack purchased. Time to start finding the slowest queries touching the table.
with sqid as (
  select /*+materialize*/
         distinct sql_id 
    from dba_hist_sql_plan p, dba_tables t 
   where t.owner = :table_owner
     and t.table_name = :table_name
     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
select /*+leading(s)*/ 
       trunc(h.sample_time) dt
     , h.sql_id
     , max(h.sample_time-h.sql_exec_start) dur
     , min(h.sample_time) mins
     , max(h.sample_time) maxs
     , count(distinct h.sql_plan_hash_value) cntpln
     , collect(distinct h.sql_plan_hash_value) plns
     , count(distinct h.sql_exec_id) cntexec
     , count(distinct h.session_id) cntsess
     , collect(distinct h.event) events
     , (select dbms_lob.substr(t.sql_text,2000) 
          from dba_hist_sqltext t 
         where t.sql_id = h.sql_id) txt
  from dba_hist_active_sess_history h, sqid s
 where sample_time > trunc(sysdate)-7 
   and h.sql_id = s.sql_id
group by trunc(h.sample_time),h.sql_id
order by max(dur)over(partition by h.sql_id) desc, dt desc



select t.owner
     , t.table_name
     , p.column_value pname
     , dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) pvalue
     , case when dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) != dbms_stats.get_prefs(p.column_value) then dbms_stats.get_prefs(p.column_value) end pvaluedefault
 from dba_tables t,
 where owner = :owner
   and table_name = :table_name



Gouping consecutive dates

I just read about Tabitosan method on Oracle PL/SQL forum.
Rob Van Wijk has written also about the method recently.

Here is just another example of it.
Something happening on random times. Need to know sequences of dates. Need to get a grouping information on rows, if there exist rows on yesterday or tomorrow.

with dates as (
select TIMESTAMP'2014-04-15 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-16 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 03:00:00' dt from dual
select dt,TRUNC(dt)-DENSE_RANK()over(order by trunc(dt)) grp
from dates

2014-04-15 01:00:00    2014-04-14
2014-04-15 02:00:00    2014-04-14
2014-04-15 03:00:00    2014-04-14
2014-04-16 01:00:00    2014-04-14
2014-04-17 02:00:00    2014-04-14
2014-04-17 03:00:00    2014-04-14
2014-04-19 01:00:00    2014-04-15
2014-04-19 02:00:00    2014-04-15
2014-04-21 02:00:00    2014-04-16
2014-04-21 03:00:00    2014-04-16

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.