Rafu on db


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.

with function begin_transaction return varchar2 is 
  return dbms_transaction.LOCAL_TRANSACTION_ID(TRUE);
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);


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

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.