adaptive cursor sharing and DBMS_SQL

A recent post in the OTN mentioned that DBMS_SQL does not use bind peeking for binded variables. I couldn’t believe that, so I decided to do some tests for myself. The findings are strange…

This is potentially relevant for Apex developers, since the Apex engine uses DBMS_SQL. I still have to do further testing to check the behaviour in Apex.

First I setup some test to show bind peeking and adaptive cursor behaviour using normal statements in SQL*Plus or SQL Developer. After that we move to dynamic SQL, especially DBMS_SQL, and try the same again.

scenario setup

create skewed testdata

--drop table demo_big;
create table demo_big as
select level as id, 
       case when mod(level,10000)=0 
            then 'VALID' 
            else 'INVALID' 
       end as status
from dual
connect by level <= 1000000;

desc demo_big;

Name   Null Type
------ ---- -----------
ID          NUMBER
STATUS      VARCHAR2(7)

select status, count(*) 
from demo_big 
group by rollup(status);
STATUS     COUNT(*)
INVALID    999900
VALID      100
           1000000

So we have a few VALID values and a lot of INVALID ones.

Even if we have only two different values an index will be useful on this column. The data distribution is so skewed that any access trying to read the VALID values would profit from an index. However if we access the INVALID column we don’t want to use the index and instead want a full table scan.

-- create indexes on all the important columns
create unique index demo_big_id_ix on demo_big(id);
create index demo_big_status_ix on demo_big(status);

create statistical data(histograms)

First we create the statistics so that the optimizers knows what is in that table and how the data looks like.

-- create statistics and test histogram
execute dbms_stats.gather_table_stats(user, 'DEMO_BIG', method_opt=>'for all indexed columns size skewonly');

Then we check the data dictionary checks to see what has been created so far.
The hist_numtochar2 function is copied from Martin Widlake (Source: https://mwidlake.wordpress.com/2009/08/11/). It just helps to do a crude translation of the numerical histogram bucket endpoints. The code of the function can be found at the end of this post.

I don’t show the results from all selects but the last one. The other selects are here just as references. They are helpful to see what kind of statistics are in place.

select table_name, num_rows, blocks, last_analyzed
from user_tables
where table_name = 'DEMO_BIG';

select table_name, column_name, num_distinct, histogram, num_buckets, sample_size 
from user_tab_columns
where table_name = 'DEMO_BIG';

select *
from user_histograms
where table_name = 'DEMO_BIG' and column_name = 'STATUS';

select table_name, column_name, endpoint_number, endpoint_value, hist_numtochar2(endpoint_value) as translated_value
from user_histograms
where table_name = 'DEMO_BIG' and column_name = 'STATUS';

Here we see a frequency histogram with two buckets for the column STATUS.

TABLE     COLUMN  ENDPOINT_NUMBER    ENDPOINT_VALUE           TRANSLATED_VALUE
DEMO_BIG  STATUS  999900     380626532452853000000000000000000000    INVALJ*
DEMO_BIG  STATUS  1000000    447861930473196000000000000000000000    VALID

The first bucket holds 999900 values where status= INVALID.
The next bucket holds 1000000-999900 = 100 where status = VALID.

This of cause matches exactly what we created. So the statistical info in the dictionary is absolutly correct.

Tests

Now that our setup is in place, we can do some basic testing to see different plans.

check execution plan with LITERALS

-- test different cursor/execution plan using plain selects
select count(*) from demo_big where status = 'VALID';
select * from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |                    |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| DEMO_BIG_STATUS_IX |   100 |   800 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("STATUS"='VALID')
select count(*) from demo_big where status = 'INVALID';
select * from table(dbms_xplan.display_cursor);
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |   701 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DEMO_BIG |   999K|  7811K|   701   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"='INVALID')

Perfect! As expected one does an index access / index range scan, the other does a full table scan.

check execution plan with BIND parameters

select count(*) from demo_big where status = :P_ENTER_VALID;
select * from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |                    |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| DEMO_BIG_STATUS_IX |   100 |   800 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("STATUS"=:P_ENTER_VALID)
select count(*) from demo_big where status = :P_ENTER_INVALID;
select * from table(dbms_xplan.display_cursor);
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |   701 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DEMO_BIG |   999K|  7811K|   701   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"=:P_ENTER_INVALID)

The two statements are not identical because the name of the bind parameter is different. Because of that we get two different cursors. Each with a different execution plan.
This test shows that bind peeking works. During the hard parse phase the value of the binded parameter was checked (peeked) so that the correct estimations for the resulting rows/cardinalities were made. Which led in turn to the correct plan for each of the two different statements. However this first parameter “freezes” the execution plan. So that if we change the binded value, then the same plan is reused.

This behaviour was enhanced in 11g with the introduction of adaptive cursor sharing and got steadily improved since then.

To test adaptive behaviour we run the first query again a few times (at least 4 times). But this time we do not pass VALID, but instead INVALID as a parameter.

After that we can see a new child cursor 1 for the sql_id “7rjdcm7v7hfrs”.

select is_bind_sensitive, is_bind_aware, sql_id, child_number, sql_text
from v$sql
where upper(sql_text) like 'SELECT%FROM DEMO_BIG WHERE%'  and sql_text not like '%v$sql%'
;
IS_BIND    IS_BIND SQL_ID        CHILD   SQL_TEXT
_SENSITIVE _AWARE                _NUMBER
Y          N       7rjdcm7v7hfrs 0       select count(*) from demo_big where status = :P_ENTER_VALID
Y          Y       7rjdcm7v7hfrs 1       select count(*) from demo_big where status = :P_ENTER_VALID
Y          N       5zkmtfj331xmc 0       select count(*) from demo_big where status = :P_ENTER_INVALID
select * from table(dbms_xplan.display_cursor('7rjdcm7v7hfrs',0));
----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |                    |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| DEMO_BIG_STATUS_IX |   100 |   800 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("STATUS"=:P_ENTER_VALID)

select * from table(dbms_xplan.display_cursor('7rjdcm7v7hfrs',1));
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |   701 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DEMO_BIG |   999K|  7811K|   701   (2)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("STATUS"=:P_ENTER_VALID)

This is adaptive behaviour. After a few bad tries a second execution plan is created for the same cursor and used. How many tries are needed? Often it changes on the third try. But it can happen that more are needed.

Test with DBMS_SQL

Now comes the more difficult part. Setup a small plsql block to use DBMS_SQL to run the same statement again using binded parameters.

-- testcase for BIND peeking/aware using DBMS_SQL
declare
  curid    NUMBER;
  ret      INTEGER;
  sql_stmt VARCHAR2(200);
begin
  sql_stmt := 'select count(*) from demo_big where status = :P_STATUS';

  -- get cursor handle
  curid := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);

  DBMS_SQL.BIND_VARIABLE(curid, 'P_STATUS', 'VALID');
  ret := DBMS_SQL.EXECUTE_and_fetch(curid);

  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(curid, 'P_STATUS', 'INVALID');
  for i in 1..5 loop
    ret := DBMS_SQL.EXECUTE_and_fetch(curid);
  end loop;

DBMS_SQL.close_cursor(curid);
end;
/

The v$sql view has two interesting columns.
IS_BIND_SENSITIVE shows cursors where the execution plan can evolve.
IS_BIND_AWARE shows child cursors where a new plan was created, meaning that the cursor was evolved.

select is_bind_sensitive, is_bind_aware, sql_id, child_number, sql_text
from v$sql
where upper(sql_text) like 'SELECT%FROM DEMO_BIG WHERE%'
and sql_text not like '%v$sql%'
;
IS_BIND_SENSITIVE    IS_BIND_AWARE    SQL_ID    CHILD_NUMBER    SQL_TEXT
Y    N    7rjdcm7v7hfrs    0    select count(*) from demo_big where status = :P_ENTER_VALID
Y    Y    7rjdcm7v7hfrs    1    select count(*) from demo_big where status = :P_ENTER_VALID
N    N    3kpu54a461gkm    0    select count(*) from demo_big where status = :P_STATUS
N    N    3kpu54a461gkm    1    select count(*) from demo_big where status = :P_STATUS
Y    N    5zkmtfj331xmc    0    select count(*) from demo_big where status = :P_ENTER_INVALID
N    N    fjjm63y7c6puq    0    select count(*) from demo_big where status = :P_STATUS2
N    N    1qx03gdh8712m    0    select count(*) from demo_big where status = 'INVALID'
N    N    2jm3371mug58t    0    select count(*) from demo_big where status = 'VALID'

The two child cursors

-- find the cursor id
select sql_id, child_number, bucket_id, count, is_bind_sensitive, is_bind_aware, sql_text
from v$sql s
left join v$sql_cs_histogram h using (sql_id, child_number)
where upper(s.sql_text) like 'SELECT%FROM DEMO_BIG WHERE%'
and s.sql_text not like '%v$sql%'
;

-- check the execution plan for both child cursors
select * from table(dbms_xplan.display_cursor('3kpu54a461gkm',0));
select * from table(dbms_xplan.display_cursor('3kpu54a461gkm',1));

-- see the plans in the SGA
select * from v$sql_plan where sql_id = '3kpu54a461gkm';
select * from v$sql_plan where sql_id = 'fjjm63y7c6puq';

Now the strange thing is: The first cursor is using a FULL table scan. But the first execution was done using the VALID value and should have resulted in the index range scan. The second child cursor does not even have an execution plan!

NOTE: cannot fetch plan for SQL_ID: 3kpu54a461gkm, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER; 
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

What is going on here? v$sql has a column EXECUTIONS which tells us how often this child cursor was called. It is always 0 for the child 1 from the DBMS_SQL cursor!

I did several more tests using DBMS_SQL. Even a case where the cursor was closed and opened several times. All with the same result.

Interpreting the results

I’m still not yet exactly sure what is going on there. It seems as if bind peeking and adaptive cursor sharing does not work with DBMS_SQL. But why do we see then two child cursors? It seems as if the different parameter values at least have the effect that a new child is created. And this happens only when there is a need for a different execution plan. But where is the plan for that? I still have some doubts. Maybe the execution plan in v$sql is lying is this case? Since DBMS_SQL goes deep into the internals it might be that some of the normal behaviours are not reflected in some of the views.

The cursor itself is in the private SQL workarea and I never checked that. Another approach would be to setup a scenario where we can measure the perormance difference. The test case I used was too small to see a desicive difference between the two possible plans.

Also we have to remember that the need for DBMS_SQL is rare. A normal select with binded parameters is certainly not a case where need dynamic SQL. A more typical case would be a cursor | statement where we do not know at compile time what columns are returned. Then we can use DBMS_SQL to analyse the structure of such a cursor and react on that.

However if we build some kind of dynamic frameworks and think about using DBMS_SQL we should rethink our strategy. Maybe it is easier to provide all the possible cases as plsql apis and thereby compiling during creation, instead of building the statement in a completly dynamic fashion but suffering some essential drawbacks.

Recommendations

1) Avoid DBMS_SQL, consider to use native SQL (execute_immediate) instead
2) If you have a skewed data distribution, make sure your plans are bind_sensitive
3) If you can guarantee an even data distribution, consider to add the NO_BIND_AWARE hint. This should be needed only in some extrem situations (very high performance requirements or cursor cache issues)

Appendix

The function that I used previously:

create or replace function hist_numtochar2(p_num number
,p_trunc varchar2 :='Y') return varchar2
-- Author: Martin Widlake
-- Source: https://mwidlake.wordpress.com/2009/08/11/
is
  m_vc varchar2(15);
  m_n number :=0;
  m_n1 number;
  m_loop number :=7;
begin
  m_n :=p_num;
  if length(to_char(m_n))>36 then
    --dbms_output.put_line ('input too short');
    m_vc:='num format err';
  else
    if p_trunc !='Y' then
      m_loop :=15;
    else
      m_n:=m_n+power(256,9);
    end if;
    --dbms_output.put_line(to_char(m_N,'999,999,999,999,999,999,999,999,999,999,999,999'));
    for i in 1..m_loop loop
      m_n1:=trunc(m_n/(power(256,15-i)));
      --    dbms_output.put_line(to_char(m_n1));
      if m_n1!=0 then m_vc:=m_vc||chr(m_n1);
      end if;
      dbms_output.put_line(m_vc);
      m_n:=m_n-(m_n1*power(256,15-i));
    end loop;
  end if;
  return m_vc;
end;
/

OTN Apprecition Day: the OTN forum

The SQL and PLSQL forum

Today is OTN Apprecitation day so I decided to write a short article about my favourite Oracle feature. It is the OTN SQL and PLSQL forum! Reading and posting on this forum made me a better developer.  I also frequently visit other forums like Database General, Apex and lately the Oracle JET, but not as intensively as the SQL and PLSQL forum.

My OTN forum handle is Sven W.

screen-shot-2016-10-11-at-23-57-04

greetings/honorable mentions

BluShadow – for moderating the forum and creating the FAQ list

Frank Kulash – for always answering in a nice and calm way insistently leading the OP to the final solution.

Billy Verrennye – for makeing me rethink old habits (like naming conventions) and for providing excellent and well thought source code examples.

William Robertson – for always beeing spot on

Odie_63 – For answering some of my questions, for example by taking apart the internal meaning of ROWIDs for external tables.

Boneist and ApexBine – for makeing their statements in a male dominated industry

noticable threads

About naming conventions in PLSQL

Coding Standards and Code Critique Request

features / ideas

PLSQL 101: Datatypes – DATE

About Ansi Joins

Introduction to regular expressions

SQL Assertions / declarative multi-row constraints

other

The 10 database commandmends

Are databases still nice and quick and simple to use like they once were?

Fun stuff from the past

Developers sometimes can be funny and sometimes they just need to boil of some steam.

Here is a collection of thread snippets from the past of the forum. Some years ago I collected memorable posts, but I don’t do this anymore. So the collection is slighty outdated now, but I tried to add a few recent quotes as well.

“OP” is used when I cite the “Original Poster” without giving the real forum handle. Otherwise usually only the first name for some of the well known members are used. Comments to the original quote from myself are in italic. Different threads are separated by a line.

Best of Forum 2007


OP> want a procedure or a block which will give a tree like structure using loops and cursors
3360> Why? Do you have a requirement to make this as slow as possible?


Dave Hemming> select ‘don”t stop me now, I”m having such a good time’ from dual

Special thanks to Dave! This is one of my favourite Queen songs.


APC> Late breaking newsflash: users are not developers!


Damorgan> Writing “working on tables” is as informative as writing “using keyboard and mouse.” See your instructor.


OP> SO CAN YOU PLEASE ASSIST ME ON THAT .

APC> On most keyboards the CAPSLOCK key is halfway down the lefthand side. Please learn to use it.


Billy> The features and flexibility and power of Oracle is NO substitution for a solid relational design.


Sentinel>

insert into table (column) values ('I have John''s shoes');

Sentinel>Of course what I’m doing with his shoes is a completely different story.
John Spencer> Since I only have one pair, I had to go to work barefoot this morning 🙂
John Spencer> Shoeless John


Damorgan> Without a context your posting is just a waste of perfectly good electrons.


OP>Is it possible to do something like this from a running program ?

Billy>Is it possible to jump from an aircraft at 5000 feet? Yes.

Billy>Of course, this has to be questioned as when it is done without a parachute, the changes of survival are very very slim. Never mind that if you’re the pilot, you are sending that plane down.

Billy>Yes, columns can be renamed dynamically from a running program. But it makes as much sense as jumping from a perfectly capable plane without a parachute.


APC> In general computing is about precision and removing ambiguity. That’s why the industry is full of pedants. Maddeningly, there is a direct correlation between pedantry and good programming.


Best of forum 2008


Billy>Be careful about making conclusions using observation only. Simple example. Observe how the WARP_SPEED hint makes the SQL go faster:

SQL> set timing on
SQL> select count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:09.60
SQL> select /*+ WARP_SPEED */ count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:00.50
SQL>

The empirical conclusion is that the WARP_SPEED hint made the query faster by 90%.

This conclusion (based on observation) is incorrect. The real reason why the 2nd query is faster is that it made substantially less physical I/O than the 1st query. The 1st query loaded a lot of data needed into the buffer cache. The 2nd query found that data there and had no need to perform the same expensive and slow physical I/Os that the 1st query did. Nor is there a WARP_SPEED hint.

So be very careful on making assumptions and basing conclusions solely on observation.


Sven> If you provide some example data and your insert statement as everybody suggested, we could give much better solutions without guessing all around.

Hans> But then we would only average one reply per question. And we would not get to spend as much time on the forums, getting to know each other so well.


Billy>Users make incredibly poor Oracle gods. That is what the DBA role is – godlike in Oracle and should be treated with care and respect and given only to those persons responsible for actual database administration. And no, users cannot administrate an Oracle database either.


WhiteHat>Hi all,

The Powers that Be at my work have decided to cut back on the number of different systems we have by re-writing a lot of them from scratch and combining functionality in order to reduce downtime caused by ETL processes and the like. so as a result I’m trying to implement the unified theory of everything in my stored proc and I can’t get it to work. Specifically I’m having difficulties combining quantum mechanics and general relativity into a single SQL statement. I’m getting ORA-06502: numeric or value error: String theory conversion error at line 3523

Is this possible in oracle v150.2.0.5 or will I have to upgrade to 153gR2?

being friday afternoon, my brain isn’t really in gear so I’m certain I’ve overlooked something simple. I suspect my basic architecture assumptions are incorrect but not sure. any advice?

Cheers,
WH.
Dave>It’s possible you’re trying to imply a quantum function to a relativistic variable. You’ll need to explicitly CAST it first.

Of course, I definitely think you need to show us your code. 🙂
Leo>Thats not always necessary as especially the quantum function sometimes can be decrypted itself by Oracle.

But definitely we need the code from line 3517.3 until line 3527.8


Billy>If this was ancient times, and you wrote this code to run on any of my databases, I would have handed your over to the SQL Inquisition for showing you the error of your ways.


Sarma>OP already told it is just an exercise for him on PL/SQL. In short, home-work.
Billy>Oh… I see.. You mean like attending Police College and committing, as home work, crimes like armed robbery, assault with a dangerous weapon, vehicular manslaughter, arson, and so on.
Billy>Yeah, I can see how this can teach you how to enforce the law.. NOT!


Justin>It’s generally helpful to specify the actual exception you’re getting rather than just saying “raises an exception”. Oracle error numbers and error strings are exceptionally useful debugging tools.


Michael O>Isn’t this the Oracle Support Forum where all wishes are granted?


Unknown>It is a basic problem that we face too here in forums. How do we show The Good Stuff of Oracle to a SQL-Server fanboy that cannot bare to empty his cup of SQL-Server in order to taste some Oracle?

Or dealing with a Java zealot that has been bitten badly by the J2EE religion, and sees Oracle as a mere persistence layer.. and not good for anything else?

Some people are so convinced that they are so absolutely right, they cannot even entertain the idea of something alternative.. never mind the idea that they just may be horribly wrong.


WhiteHat>[clippy]

Hi! it looks like you’re trying to use Oracle!

Do you:
( ) want to INSERT data to a table
( ) want to UPDATE existing data in a table
( ) ALTER the structure of the table
( ) search the internet for other queries

[clippy]

It’s not clear what you’re trying to do:
as we understand it it seems like:
you have a newly created table and you want to make it so there’s data in it is this correct?


OP> what if i will have thousands of record.i cant write
them all.
Dave> BANGS HEAD ON DESK
Dave> Instead of the select … from dual union select … from dual… perhaps you could use YOUR OWN TABLE.
OP> yeah i told you that i got it already so i dont need to bang head on desk……thanx neways


OP> Can you just tell me which is the best oracle performance tuning tool in the market? It should be free download.
Guido> It’s name is BRAIN (Biological Resource for All Informations Needs). If you really need to download that you should opt for another career path, I guess. 😉


OP>PL DESCRIBE U’R TABLE WORD.
Billy>Use proper English and not IM SPEAK as this is a technical forum and not some SMS teenage chat room.
padders>Please note however that it is considered acceptable to refer to someone’s ‘leet SQL skillz’.
Dave>Although it’s worth first establishing a reputation that clearly indicates that you do not think “irony” means “similar to iron”.


OP>i have run the package and it will take execution time more than 1 hour, how can i redure the execution time? any one help on this issue.
Matt>Remove all the code from the package.


shoblock> I really wish people would read the responses before they complain that they
aren’t working as desired.
APC> Aw c’mon. Next you’ll be wishing people would look stuff up in the documentation instead of straightaway posting questions here.


Laurent> of course regexp could save ink when printed


OP>i’ve try to add commit; but doesn’t work.

Dave>Glad to see you picked up on the need for a more complete explanation than “doesn’t work”.
Dave>Oh wait, you didn’t.

Someoneelse>That’s a new error in 11g:
Someoneelse>ORA-00042 DOESN’T WORK


Someonelse>IF Using_SQL_Server THEN
Someonelse> EXIT Oracle_Forums;
Someonelse>END IF;


Keith>If thats not clear, I’ll join the hitting head against the brickwall gang.


Billy> Do you fix the symptoms? Or do you fix the problem?
Padders>Erm. The problem I think. Aren’t we supposed to hit the symptoms with the lead pipe?


OP>Thread with title like “urgent help in sql plz ”
Billy>STOP!!

For that you need to fill in the “It Is Truly Urgent” form via the request link on the Oracle Forum main page. In triplicate. Submit it to the moderator. Wait for an urgency verification key to be supplied by the moderator. And only then can you post your urgent posting by attaching the urgency key to it for verification purposes.

Since you did not do it, your account is being reviewed for a possible 6 month suspension. You will also be prohibited from practicing Oracle during that time as you have illustrated the lack of common sense by posting this totally uncalled for and unwarranted “urgent” posting in this forum. And not applying common sense when using Oracle can cause serious injury to your database, cause serious damage to the scalability and performance of your applications, and may just cheese off your Oracle DBA resulting in a lead pipe being taken to your knee caps.


William>A right outer join is just a normal outer join written backwards to confuse everyone


More forum fun


John Stegeman> Last time I checked (1 minute ago), there is no “PL/SQL for SIM cards”


John Stegeman> Or even a entry in the mystical magical caverns of the registry, if none of those are set.
Ed Stevens> Please!  I’ll do anything!  I wash your car!  I’ll mow your lawn! Just don’t send me to the registry!


Someoneelse> We are under attack!
The Database General forum is being flooded with spam!
Here are some of the userids:  …
What the hell, is this a new feature of Jive?

jgarry> You want Jive to pump up social media, Jive pumps up social media.

jgarry> On other places I’ve been surprised by being blocked for too much posting.
I’m really not a robot!  It’s hard to tune that limit right, and some people may compose things beforehand.
But worse, spammers would consider it damage and route around it, with whatever they need to do to have numerous logons.
Like when I knocked some fuzzy balls off the umbrella next to my pool and little black widow spiders scattered everywhere.

Dude!> There is already a feature in place that does not allow people to post one message right after another without waiting for a while; 5 min. if I remember correctly.

BluShadow> It’s 30 seconds Dude!, not 5 minutes.

Dude!> Ah well, time is relative

KayK> all you need is a DeLorean


Dude!> How long will it take until everything implodes?
Billy>  Everything? I assume you are limiting “everything” to our solar system?
In that case, around 5 billion years from now, our sun will run out if fuel, shed its outer layers, and implodes into a white dwarf. Unfortunately it is too small to become a black hole. Which would be a kewl thing. Size some time matters.
Everything as in the universe? Guestimate is a 100 or so trillion years – depending on the theory you deem most likely (of which there are more than a few) describes the end of the universe. Implosion is just one of the theories. Perhaps an Asimov’s The Last Question end and beginning?

Dude!> I don’t worry so much about 5 billion years from now — not even history of the past 100 years is correct.


Billy> Disk space is cheaper than the effort to rebuild tables and indexes in order to reclaim space – and to support this effort as SOP.


William> So ‘QTR’ means ‘Quarter’? What is this, Twitter?


Jonathan Lewis> I got to the end to the first line (after the Hi) and thought: “we’re going to see a match_recognize() solution from Stew Ashton here”.

He was right.


“Re: What is the difference between select count(1) from tab and select count(*) from tab;”

Well after some short ramblings about performance and table sizes the gurus discussion went on to the right track.

Dave> One press on the shift key on my keyboard
William> “count(1)” is a nonstandard variation that takes more keystrokes and requires the parser to substitute “*” in place of the “1”, while making the person who wrote the query look foolish.
If you want an approximate result for a large data set quickly, have a look at the SAMPLE clause, e.g.

select count(*) * 20 from somebigtable sample(5);

Frank> Actually, on my keyboard, ‘1’ takes fewer keystrokes (depending on how you count) than ‘*’.  To type ‘1’, I just press the ‘1’ key, but to type ‘*’ I have to hold down the SHIFT key and then press the ‘8’ key.
Even though it’s that much harder to type, “COUNT (*)” is still better than “COUNT (1)”, for the reasons you mentioned.
Jonathan> You may be taking too narrow a view on the problem – although the correct view may, of course, be keyboard-dependent.  You need to step back from the 1/* dichotomy and consider the effect of parentheses:on the problem.

On my keyboard (*) requires me to do:  {shift} 980 {release}   (a total of 4 keystrokes – or 5 finger movements)

but (1) requires me to do: {shift} 9 {release} 1 {shift} 0 {release}  (a total of 5 keystrokes – or 6 finger movements)

Note also that if you are a “classical typist” your are probably going to use {left shift}, which means a large movement to the 1, unless you use a numeric keypad – in which case the 9 requires you to make a large lateral movement with your right hand (which can then stay in place until after the 8 stroke, of course).

Youngsters these days! Just don’t think things through properly!   (;)
William> Perhaps the round bracket keys are not shifted on some keyboards? I don’t think I’ve ever seen that though.
rp0428>Can you provide a specific reference to ANY of your books or blogs that cover an advanced topic such as this?

Sometimes ‘youngsters’ can benefit from seeing the explanation in context with some example code, trace files and execution plans.
Ospin> Just to inform for people with spain keyboards, this keyboards has “(” in shift+8 and “)” in shift+9, so is quit bit easy type “(8)”, so less finger movements and same results
John> To really figure this out, we probably need sql_trace for brains and bodies – when is Oracle going to wake up and put a bunch of SQL coders under a functional MRI scanner and do metabolic analysis to determine the precise effort involved?
However, i’ll say this: even if select(1) was an order of magnitude easier to type than select (*) (which it’s not), the dissonance and mental stress caused by seeing select(1) is probably enough to kill a few million brain cells of my own (not to mention people who come after me and have to read my code)…


All time classics:
Frameworkia – the NEW PLSQL development standard

News for developers from #OOW16 about 12.2

The following information I obtained during various sessions during OOW16. Sometimes they resemble just an impression I got while a certain feature was mentioned during a speak. So use the information with caution.

new db version 12.2

So the 12.2 version is out there now, but most of us won’t be able to use it very soon. Why? Because Oracle choose to do a cloud first approach. That means 12.2 is available for the Oracle Cloud but not for other installations yet. And my guess would be that first enterprise edition will get it and standard edition (SE2) might even get it a bit later.

Still there are many exciting new features in it and it makes sense to get ourselves familiar with them. So here are my favourites with a slight focus on developer relevance.

There is documentation about 12.2 new features out already, but it is extremely hard to find. It is hidden in the docs for the new Exadata Express Cloud service. So here is the quick link to see the new features guide.

To summarise many of the new features focus on improving availability of the database. Essentially giving developers and DBAs more options to keep the applications running even when encountering errors or while changes are takes place.

The second set of enhancements seem to be things that further extend capabilities of features that were added in 12.1.

And of cause a lot of performance improving possibilities had been added (this seems to by typical for an R2 version).

longer identifiers (128 chars)

Identifiers can now be put to 128 chars long. This applies for almost all objects including table, column, index and constraint names. Long awaited and finally there.

I suggest not to overdo it at the beginning, I suspect that many external tools that work with the Oracle database might not fully support such long identifiers yet. And of cause some common sense when naming your tables and columns should be applied as well. For example please do not repeat the table name again in the column name. However it will help greatly when you apply naming conventions on foreign key constraints or indexes.

There seems to be a new plsql constant Ora_Max_Name_Len that holds the maximum possible value in your database. It can be used at compile time to set the sizes of variables. There is an example in the plsql section later.

SQL functions

improved LISTAGG

LISTAGG now has an option not to error out when the list gets too long. Instead the list will be cut off and a default value (ellipsis “…”) is added. Additionally we can add the number of found results.

To do so add the new overflow clause, for example:

ON OVERFLOW TRUNCATE WITH COUNT

Unfortunatly LISTAGG is still not able to build distinct lists. If you want that, vote for this feature in the OTN database ideas space: https://community.oracle.com/ideas/12533

conversions with error handling

All conversion functions have now an added new clause that decides how an error is handled, when the conversion fails because of a formatting error. CAST, TO_NUMBER, TO_DATE and all the other TO_xxx functions got this improvement. The only exception is TO_CHAR. Because everything can be converted into char.

CAST_IN_DB12_2.gif

examples:

CAST(prod_id AS NUMBER DEFAULT 0 ON CONVERSION ERROR)
TO_DATE(last_processed_day DEFAULT date'2016-01-01' ON CONVERSION ERROR, 'DD-MON-RRRR','nls_date_language=AMERICAN')

So in the case where the conversion would result in an error, we can provide an alternative default value instead. This is highly useful!

Additionally there is a new function VALIDATE_CONVERSION which can be used to find values in a column where conversion would not be possible. It returns 0 for invalid values and 1 for correct ones.

new approximation functions

In 12R1 we already got APPROX_COUNT_DISTINCT
new are APPROX_PERCENTILE, APPROX_MEDIAN
These approximation functions can now be used in materialized views!
And there is a parameter that is able to switch the exact functions to the approximate versions.

alter session set approx_for_aggregation = 'TRUE';

Also new are APPROX_COUNT_DISTINCT_DETAIL, APPROX_COUNT_DISTINCT_AGG and TO_APPROX_COUNT_DISTINCT allowing to build hierarchies with those aggregation functions. Something we were not able to do in the past without rerunning the function on each aggregation level.

case insensitive linguistic queries

It is now possible to do searches using a case insensitive option, e.g.  BINARY_CI for various functions like LIKE. Those functions are able to use stemming. Not much detail about it yet, but it sounds like it can be used instead of putting UPPER around the columns.  At the moment I have no further information about possible performance impacts of that.

greatly enhanced JSON support

better simplified JSON

There is a complex JSON syntax using all the JSON functions and a simplified JSON syntax using dot notation. In general a lot of work was done to enhance the simplified JSON syntax. Like you can access elements of an array now.

JSON_EXISTS with predicates

If seen short examples where “and” expressions/filters using an && operator were done inside some SQL statement using JSON_EXISTS. Essentially JSON_EXISTS allows to add filters to the path expression using a solid set of operators.

build JSON with SQL/JSON

Using a similar syntax as the older sql/xml functions (XMLELEMENT, XMLAGG) we now have some new SQL functions to help us createing a JSON document. The output of those functions is VARCHAR2.

JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG

 

Views on JSON documents / Data Guide

The Data Guide allows to analyse JSON documents and automatically build views and virtual columns for elements in our JSON object.

The data dictionary has a list of all columns with an enabled data guide

USER|ALL|DBA_JSON_DATAGUIDE

We can access the dataguide in SQL with the functions JSON_DATAGUIDE or JSON_HIERDATAGUIDE or in PLSQL with the function DBMS_JSON.GET_INDEX_DATAGUIDE.

The plsql functions need an json search index  to work. If it exists then those functions should be preferred, since they work on persisted index data. If the json documents in the columns do all have a completly different structure, then it might be better to use the SQL functions and not use a json search index.

See also: Multiple Data Guides per Document Set

Based upon the data guide more operations are possible. For example we can easily create views and/or virtual columns that extract information from the JSON document as relational data.

To create a virtual column that shows data from our json column in a relational way we can use  DBMS_JSON.addVC.

To create a view that exposes json document data in a table like structure, we can use DBMS_JSON.createViewonDemand. This is based upon a JSON_TABLE function.

 

JSON search index

example

CREATE SEARCH INDEX po_dg_only_idx ON j_purchaseorder (po_document) FOR JSON;

 

GeoJSON support

If the JSON includes coordinates that are  conform with the GeoJSON standard, then it is possible to do geolocation searches on that JSON document.

GeoJSON also is supported during Spatial Queries and can be converted directly into Spatial Geometry.

more JSON enhancements

  • the JSON sql functions are now available in plsql as well => especially IS JSON will be useful
  • highly improved JSON search index
  • new predefined plsql object types JSON_OBJECT_T, JSON_ARRAY_T, JSON_ELEMENT_T, JSON_KEYLIST_T, JSON_SCALAR_T

MV enhancements

ON STATEMENT refresh for MV

We had ON DEMAND and ON COMMIT before. Now Materialised Views can be refreshed after DML changes to the base tables without having the need to wait for the commit

refresh statistics view

This was long overdue! We are now able to see statistics when the materialised view was refreshed. The name of the data dictionary view is not completely clear now, but I suspect DBMS_MVREF_STATS.

There is also a new package DBMS_MVIEW_STATS that can be used to organise the collection and the cleanup of those statistics.

improvements for partitioned objects

  • MOVE TABLE, SPLIT PARTITION and some other partitioned operations can be done online. That means they will not disrupt ongoing DML operations. And this includes automatic index maintenance.
  • CREATE TABLE FOR EXCHANGE prepares a non partitioned table to be partitioned.

 

superfast analytics

Join Groups

It is now possible to define columns as join groups. A JOIN GROUP is a new database object and it greatly increases performance of Join and analytical queries over those columns.

In Memory expressions

An INMEMORY expression is a special kind of virtual column that has an additional INMEMORY attribute attached to it. By that attribute special optimisations kick in that speed up access to this expression. This method is also used by some of the JSON optimisations.

A data dictionary view is added to help finding expressions that could profit from such virtual columns.

dba|all|user_expression_statistics

Analytical views

Analytical views are an easy way to define dimensional hierarchies and how they are rolled up. An Analytical View is a new object type.

This seems to be a completely new feature. Slightly based on the older dimensional cube possibilities. I’m not sure if this will be available for all editions later or if it will be an additional cost feature.

 

PL/SQL stuff

pragma deprecate

This feature was surprising to me, but I find it hugely interesting.

You can declare now plsql methods as deprecated by using this pragma. The “only” thing that happens is, if such a function is used you will get a compiler warning (PLW-something).

syntax example

function myFunc return varchar2
is
   pragma deprecate 'myFunc is deprecated. Use yourFunc instead!';
begin
  return 'x';
end myFunc;

So far I didn’t miss that feature, but I immediately have some projects in mind where I would use it.

static expressions instead of literals

All areas where a literal is to be used, can now be replaced by a so-called “static expression”. e.g.

declare
 myTab varchar2( Ora_Max_Name_Len + 2);
 myObject varchar2(2* (Ora_Max_Name_Len + 2));
begin
 myTab := '"Table"';
 myObject := '"ThisSchema".' || myTab ;
 ...

This works only as long as the expression can be resolved at compilation time.

As such it will not improve or change writing dynamic queries, however there is some impact for deployment scripts. So whenever you needed to use manually written compiler directives, this might be a new alternative.

minor changes

  • ACCESSIBLE BY for sub modules
  • bind plsql only datatypes to dbms_sql => this essentially just finishes what 12.1.0.2 already allowed for anonymous blocks and native SQL (execute immediate).
  • some enhancements for PL/scope: SQL_ID for static sql statements, reports where native SQL is used
  • slight enhancements for dbms_hprof: can now include SQL_IDs,  improved reporting   for sub cursors

new code coverage tool

It splits code into blocks and tells the developer if and how often those blocks are used during some sample run. Code that is not reached is coloured differently (red).

Blocks can be marked with a pragma

pragma coverage('NOT_FEASIBLE');

So that this block is not marked when it is not covered. The tool is currently only a new DBMS package, but it will be integrated into the next SQL Developer version.

dbms_plsql_coverage_….

 

developer.oracle.com

It is Oracles new landing page for developers. Strongly influenced by Steven Feuerstein.

screen-shot-2016-09-20-at-12-21-18

debugger enhancements

Can execute a sql now when the debugger encounters a breakpoint. Not implemented yet directly in SQL Developer, but it will be there in a future version. In general the new SQL developer already supports the enhanced debugging capabilities of the database, but not all is possible yet.

The debugger also is not available for the cloud yet. The protocol used, is not suited for cloud operations.

EBR (edition based redefinition)

Editioned objects no longer in use can be cleaned up automatically in the background

All we have to do is to drop the edition. Even in cases where this was not possible before.

CBO

CBO improved adaptive handling

The adaptive features of the CBO that are already in the database can now be better controlled. And the defaults were set in a way, that upgrades from 11g result in very similar behaviour.

OPTIMIZER_ADAPTIVE_FEATURES = TRUE or FALSE => deprecated

replaced by two new parameters

OPTIMIZER_ADAPTIVE_PLANS => default = TRUE

OPTIMIZER_ADAPTIVE_STATISTICS => default = FALSE

So if you switch from 12.1 to 12.2 and if you had OPTIMIZER_ADAPTIVE_FEATURES=TRUE then you might want to set the second parameter to TRUE again. If you switch from 11g to 12.2 you probably want the defaults.

The dbms_stats package got some improvements that go together with it.

Dbms_stats: auto_stat_extensions

At the moment not much is known about that yet.

Mview query rewrite

The CBO can now rewrite a query to a materialised view even if the view is stale at that moment. Materialized view logs are considered to return the correct results.

more CBO based things

  • new histogram types
  • GTT use session private statistics by default
  • automated SQL plan management
    Even after DB upgrade the capture of the old plan can be done by setting the parameter OPTIMIZER_FEATURES_ENABLED = ‘11.2.0.4’
    Plans can then evolve later
  • copy optimiser metadata from pre production to prod.
    EXPDP/IMPDP or dbms_stats.transfer_stats

application containers

Instead of just having a CDB and several PDBs in it, we can now define an Application Container. This serves as a kind of general repository for all PDBs that are part of that application container.

This is highly interesting.

The typical use case seems to be that you would roll out your application to various customers (in the cloud) by using a PDB for each customer. However all the common objects would be added into the application container, so that changes / enhancements can be rolled out to all customers at once or one after the other. The application container keeps track of you upgrade scripts an is able to “replay” those scripts in the specific PDBs. Only that is is not a replay of the script, instead the objects are linked from the PDB to the Application Container. I think this is the same mechanism as currently the CDB objects/metadata are made available on the PDBs.

Objects can be shared from the Application Container to the PDB by using the SHARING clause in the create statement.

SHARE NONE|METADATA|OBJECT

SHARE METADATA would only share the object definition, while SHARE OBJECT would also share the data.

It is possible to combine application containers with edition based redefinition. Essentially it seems as if the editions are copied from the Application Container to the PDB as all other Objects are copied/linked. The Application container just keeps track of what needs to be installed in a specific PDB.

shards

A new very special way to have locally distributed database. Seems to cover the same concept as shards in some noSql DBs. If the application is distributed over a set of databases, you can now declare them as sharded somehow. And if you define a table as sharded then its data will be split to the different databases. You use a shard key (very similar to a partitioned key) and that key defines where the data is going.

Exadata Express Cloud service

Exadata Express Cloud Service

Essentially you get a PDB with a few GB of space on an Exadata Machine running Oracle EE for only 175$ per month. Pricelist here.

SQL Developer version 4.1.5 supports the Exacta Express Cloud service. You can drag and drop objects (tables, etc.) directly onto the connection and get it moved there.

The Exadata Express Service includes Apex , ORDS and SODA. So it can also serve as a kind of JSON document storage using rest interface.

 

ORDS

Jetty is now supported for production mode. That means you can now run ORDS in standalone mode also for a production environment.

Since ORDS is not part of the database this does not depend on using database version 12.2.

 

SQL parse time issue – use “minified hinted outlines”

problem description

Recently I encountered a simple looking query that returned a very small result set, but used to take more than 5 seconds time to execute. When executing it a second time it was fast (less than 0.01 second). When adding a comment then the execution time was bad again (5 seconds). Adding a comment forces the optimizer to parse the query again. However cached blocks can be reused. So this simple test gives a good indication where the time is spent.

The query was simple looking at first but turned out to be a complex set of views within views with nested subqueries, union all combinations and so on. However the execution plan was all nested loops, the filter criteria was correctly pushed down through all the sub layers.

reason

The problem was that the complex sql took a very long time to hard parse.
Executing the plan was fast, but creating the execution plan was eating up the time. The CBO had trouble finding the best execution plan. The more tables are joined, the more different join orders are possible and need to be considered. This is called permutations (see tuning section below). It can take a considerably long time.

 

example

This was the query

SELECT
r.labwarebarcode
      ,r.orderid
      ,r.orderlineitemid
      ,r.streamid
      ,r.status
      ,r.lineitemcomment
      ,r.bat_identifier
      ,r.labwareitemid
      ,r.xposition
      ,r.yposition
      ,r.layoutid
      ,case when
         count(*) over (partition by r.streamid )                                        -- Number of wells
         =                                                                               -- equals
         count(case when r.status = 'SUCCESS' then 1 end) over (partition by r.streamid) -- number of successfull wells
         then 'FULFILLED'
         else 'NOT'
         end  as platestatus
from v_lnk_check_part_order_results r
where orderid = :orderid
order by streamid, labwareitemid;

Now how can we demonstrate that the parsing time is the problem?

To get a clear picture would be to trace your statement and run a TKPROF report for it. This is often something that is very hard to do for developers, because they do not have access to the OS and are not able without DBA privs to enable and disable the tracing and above all to run the TKPROF command. Here is a tutorial from John Watson that shows how to trace and use tkprof.

The way described at the beginning only gives us an indication. First run the statement several times until the timings are stable. This means the data blocks are cached in the buffer cache. Then slightly modify the statement, for example by adding a comment, adding a meaningless where condition (2=2) or removing one column from the output. This will lead to a new cursor and to a new hard parse. Run the same statement a second time and it should be fast again. The difference in execution time gives an approximization for the time needed to hard parse.

It is not perfect, but is a solid “educated guess”. I didn’t see an easy way in SQL developer to show those statistics using typical developer privs only.

the solution

Adding the following hint to the select solved the issue

/*+
LEADING(OLIS@SEL$11 ERRORS@SEL$11 WFI@SEL$2 EXTRAINFO@SEL$11 ILI@SEL$19 PL@SEL$27 ICH@SEL$17 SOL@SEL$25 BAT@SEL$21 CPD@SEL$23
CWFI@SEL$3 SOA@SEL$4 LI@SEL$3
POS@SEL$8 SOA@SEL$7 CWFI@SEL$6 LI@SEL$6
SOA@SEL$13 CE@SEL$13
OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)
ALL_ROWS
*/

What this hint does and how to get it is explained below.

I call it the “minified hinted outline” approach. Although “Extrem lead hinting” was a close runner up.

tuning effort

We already identified the hard parsing as the point where the CBO spends its time. And more specifically it is the point where the CBO creates multiple execution plans and compares them.


Source:Oracle Docs – Query Optimizer Concepts

To understand the problem that the CBO faces, here is a table that lists the number of potential table combinations that the CBO needs to consider. The permutations go up exponentially with the number of tables.

permutation considerations

# tables join permutations to consider
2 2! = 2, table A => table B & table B => table A
3 3! = 6, A=>B=>C & A=>C=>B & B=>A=>C & B=>C=>A & C=>A=>B & C=>B=>A
4 4! = 24
5 5! = 120
6 6! = 720
7 7! = 5040
15 15! = 1,307,674,368,000

There is a maximum number of permutations that the CBO considers.
This maximum number is 2000 (default since 11g). It can be influenced by the hidden parameter _optimizer_max_permutations. In older database version this was a normal init.ora parameter, but it was deprecated in 9i and replaced by the “hidden” parameter (http://oracleinaction.com/undocumented-params-11g/
). It might be that this maximum number is per query block. The documentation is not perfectly clear about it. A query block can be an independent subquery, a non-merged/inline view or a WITH clause section. So for ten query blocks, it might be that the maximum number of permutations to consider is 20000. I didn’t test that hypothesis but there are some indications that this happened in this specific case.

As we can see from the list this magical number of 2000 permutations is already reached when 7 tables are joined. After that the optimizer will stop looking at other combinations. Btw: that is why the table order in the FROM clause can still make a difference! So put the main tables at the beginning of the FROM clause, so that the CBO considers them correctly in its access path permutations.

general approaches

  • Stored outlines and SQL plan management
  • global table hints

stored outlines

A stored outline will store the execution plan for one specific SQL statement. They are mostly used for plan stability, however a nice side effect is, that the hard parse phase is shortend when a stored outline is found.
A good introduction about stored outlines can be found in the 10g documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14211/outlines.htm#i30836 . Stored outlines are available since 9i at least. The modern version of a stored outline is a SQL plan baseline and SQL plan management (SPM).

Further reading:
Oracle white paper: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf#22

Blog “Insight into the workings of the Optimizer”: https://blogs.oracle.com/optimizer/entry/how_to_use_sql_plan

global table hints

Essentially what stored outlines do is to set a couple of hints.

We can do this directly ourselfs by using global table hints.
The normal way to provide a hint, is to add it to the section where it is relevant for. But when views are involved, you probably won’t want to change the view definitions and cutter them with hints. First of all the views might belong to some other schema. Also they are used in different points in the application, so a hint that my be good for one specific SQL might not be good for another SQL using the same view.

Global table hints come to the rescue! A global table hint is able to “inject” a hint into the deeper layers of a view.

Quick and not so dirty solution

As already mentioned adding the following hint to the select solved the issue

/*+
LEADING(OLIS@SEL$11 ERRORS@SEL$11 WFI@SEL$2 EXTRAINFO@SEL$11 ILI@SEL$19 PL@SEL$27 ICH@SEL$17 SOL@SEL$25 BAT@SEL$21 CPD@SEL$23
CWFI@SEL$3 SOA@SEL$4 LI@SEL$3
POS@SEL$8 SOA@SEL$7 CWFI@SEL$6 LI@SEL$6
SOA@SEL$13 CE@SEL$13
OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)
ALL_ROWS
*/

All we need to get such a hint is clever usage of the Oracle SQL Developer.

how to

Using SQL developer we can check the execution plan for our query (F10).
In the lower section of the plan there is a section “Other XML” and in this section is a long list of HINTs. This section can be exported.

OR we can use the explain plan command and after that do this:

select *
from table(dbms_xplan.display(
             format=>'BASIC ALIAS OUTLINE'
          ));

“OUTLINE” will show the complete sql outline. Which is the sum of all hints that need to be applied to get this specific plan.
“ALIAS” will show the query block names that are used.
“BASIC” is just to focus on the relevant parts for this example.

A solid first test strategy is to copy and paste the complete outline section into the sql statement and see if it is now fast when using all the hints.

However this list is way to long and we can eliminate step by step most hints. I would recommend to keep only the leading hints. Remember, we just want to reduce the complexity for the high number of table permutations. The leading hints (and a few others like merge/no_merge) are responsible for freezing the join orders. Make sure to get all leading hints. If one of them is missing, the whole order of execution might break.

If the query is still reasonably fast then we can work with them only. In my case the execution time slightly increased to 0.15 seconds. Which is still way below the full 5 seconds when calling it without a hint.

The result may look like this:

LEADING(@SEL$6A1B73E7 CWFI@SEL$3 SOA@SEL$4 LI@SEL$3)
LEADING(@SEL$AB668856 POS@SEL$8 SOA@SEL$7 CWFI@SEL$6 LI@SEL$6)
LEADING(@SEL$5B04CACE SOA@SEL$13 CE@SEL$13)
LEADING(@SEL$FC92CB92 OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)
LEADING(@SEL$5461C9FE OLIS@SEL$11 ERRORS@SEL$11 WFI@SEL$2 EXTRAINFO@SEL$11 ILI@SEL$19 PL@SEL$27 ICH@SEL$17 SOL@SEL$25 BAT@SEL$21 CPD@SEL$23)
ALL_ROWS

Ok lets look more closely at one of the leading hints that we have:
LEADING(@SEL$5B04CACE SOA@SEL$13 CE@SEL$13)

@SEL$5B04CACE is a named query block. Where oracle itself had choosen the name. We can see those query block names when using the “ALIAS” setting during explain plan creation. I believe those query block names could potentially change when useing the same statement on a different database (e.g. moving the code from DEV to TEST DB). Therefore the goal is to get rid of those aliases for query blocks.

SOA@SEL$13 is the table(or view) alias “SOA” and SEL$13 is the 13th SELECT command in our query. Including the outermost select call that we issue ourselfs. So the table order is SOA before CE inside the query block “SEL$5B04CACE”.

We can then combine all those leading hints into one single leading hint. The order is relevant. Sometimes it needs a little testing to find the proper order of the hints. This is because the starting points, the query block names need to be removed when only one hint is used. In my case the “@SEL$5461C9FE” was the outermost block (the select itself), so the leading tables that came after that block were the first ones to consider. This outermost block can be identified by studying the ALIAS section of the execution plan.

Fazit

“minified hinted outlines” are a quick solution for a very specific type of problem. Typically when using complex queries on views we might want to consider such a solution. It is a quick solution as an alternativ to stored outlines or SPM.

drawbacks

This solution freezes parts of the execution plan. But in a less invasive way than a real stored outline would do. For example we do not say which index to use, instead the CBO still decides about the best way how to access a specific table. It is just that the order of the tables and subquery blocks is hinted and by that frozen.

If the select or the views change, then the leading hint needs to be regenerated. However this concept was working sucessfully even when moving the query including hint from test to a production environment.

Integrate Oracle JET into Apex 5.0

Introduction

For Apex 5.1 it is announced that the new chart library will be Oracle JET. It is possible to use Oracle Jet already in Apex 5.0. How to integrate it is the scope of this article.

Rationale

I don’t see that most of the projects will switch very soon to Apex 5.1. Many have just made the transition to Apex 5 and Universal Theme or are about to do so. This was usually quite some work. It is difficult to argue for making a new conversion to Apex 5.1 very soon. Even if some of the reasons (Interactive Grids!) are extremly compelling. However since Oracle JET Charts will be one of the major areas where we can expect some adaptation when migrating, it makes sense to start working with that technology as soon as possible. Using Oracle JET charts in Apex 5.0 would be a very good preparation and might smooth a future upgrade path.

In the scope of the Apex Dashboard Competition I made a try to combine Oracle Jet into Apex 5.0. There are quite a few traps and surprises when trying to do so. I plan to do a series of blog posts that all deal with the combination of Apex 5 and oracle jet.

This article will just concentrate on how to install Oracle Jet (OJ) and make it available in Apex 5.0.

License considerations

Since a few weeks Oracle Jet is open source.
See : http://www.oracle.com/technetwork/developer-tools/jet/downloads/jetlicense-2905156.html

“Anyone and Everyone can use Oracle JET. It’s an Open Source project sponsored by Oracle. It is distributed under the Universal Permissive License(UPL).”

Essentially it means we can use and distribute oracle jet as long as the appropriate license document is included in the distribution. The license document is also part of the oraclejet.zip file that we need to download.

Download sources

To install Oracle JET, we first need to download the newest version. Since we do not create a Oracle JET only application, most of the “Getting Started” docs and templates are not really relevant. The oraclejet.zip file hides under the “Oracle JavaScript Extension Toolkit : Base Distribution” link in the download page.

Download page: http://www.oracle.com/technetwork/developer-tools/jet/downloads/index.html

General documentation links:

Installation

Preparation

Prerequisites: the oraclejet.zip file and an apex 5.0 database.

I tested the whole installation process in the vanilla oracle developer virtual machine. This VM includes an Oracle EE database 12.1.0.2, Apex 5.0, ORDS 3.0.x and SQL Developer 4.1.

http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Remember it is for evaluation purposes, but not for production development!

Server Installation Process

Unfortunatly there is no CDN link that we could simply include in our apex page. Instead we need to reference the needed js and css files somehow in our Apex page.

To install Oracle JET we need access to the apex web server, where the image files are located. To do so unzip the downloaded oraclejet.zip file into a usable folder. I suggest to use the normal apex/images/libraries folder.

e.g.

/apex/images/libraries/oraclejet

apex/images is typically mapped as the image path /i/. It can later be used with a substituition variable #IMAGE_PREFIX#. To access an oraclejet file, we could include it like this #IMAGE_PREFIX#libaries/oraclejet/libs/oj/v2.0.0/ojs.js .

Unzipping the file into some accessible folder is all we need for the server installation part.

OJ configuration and integration into Universal Theme (UT)

The oj documentation states that the require.js framework is mandatory if data visualization components are used. Since the charts are the most interesting parts for Apex, we are forced to use require.js. Most of the installation problems I encountered had to do with using require.js. More details are in the “About requirejs” part. I hope that in the distribution for Apex 5.1 this dependency will be gone.

About requirejs

Require.js is a tool that is able to load needed ressources dynamically. Typically those resources are other javascript libraries. If multiple parts of an application will load the same library, require.js will only load it once including dependencies.

The Oracle JET documentation tells us how to install requirejs.

The loader that makes it easier to manage library references and is designed to improve the speed and quality of your code. Oracle JET uses RequireJS by default for the Oracle JET QuickStart Template and is required if you plan to use Oracle JET’s internationalization, data visualization components, or the oj.OAuth plugin in your application.

To use RequireJS to manage references:

  1. Download Oracle JET as described in Download the Oracle JET Zip File.
  2. Copy js/libs/oj/version/main-template.js to the js folder.
  3. In the js folder, rename main-template.js to main.js.
  4. Add the following script reference to your index.html file:
  5. Update main.js as needed to reference Oracle JET modules or your own scripts.

    For a list of Oracle JET modules and additional details about using RequireJS in your Oracle JET application, see Using RequireJS for Modular Development.

For more information about RequireJS, see http://requirejs.org.

Ok, so lets do this. Do not copy the code for the main.js from the documentation. This code is outdated and does not have all the correct paths. The reason is simple. Some libraries, like oj itself have now a newer version and the version is included in the path. If you copy the main-template.js as stated in the documentation, you should be safe. This is what I used for the main.js file. The paths are relative to the location of the main.js file itself. in this case to set the baseUrl is not needed.

requirejs.config({
  // baseUrl: '#IMAGE_PREFIX#libraries/oraclejet/js',
  // Path mappings for the logical module names
  paths: {
    'knockout': 'libs/knockout/knockout-3.4.0',
    'jquery': 'libs/jquery/jquery-2.1.3.min',
    'jqueryui-amd': 'libs/jquery/jqueryui-amd-1.11.4.min',
    'ojs': 'libs/oj/v2.0.0/min',
    'ojL10n': 'libs/oj/v2.0.0/ojL10n',
    'ojtranslations': 'libs/oj/v2.0.0/resources',
    'signals': 'libs/js-signals/signals.min',
    'text': 'libs/require/text',
    'promise': 'libs/es6-promise/promise-1.0.0.min',
    'hammerjs': 'libs/hammer/hammer-2.0.4.min',
    'ojdnd': 'libs/dnd-polyfill/dnd-polyfill-1.0.0.min',
  },
  // Shim configurations for modules that do not expose AMD
  shim: {
    'jquery': {
      exports: ['jQuery', '$']
     }
    }
  },

// This section configures the i18n plugin. It is merging the Oracle JET built-in translation
// resources with a custom translation file.
// Any resource file added, must be placed under a directory named "nls". You can use a path mapping or you can define
// a path that is relative to the location of this main.js file.
    config: {
        ojL10n: {
            merge: {
                //'ojtranslations/nls/ojtranslations': 'resources/nls/myTranslations'
            }
        }
    }
});

/**
 * A top-level require call executed by the Application.
 * Although 'ojcore' and 'knockout' would be loaded in any case (they are specified as dependencies
 * by the modules themselves), we are listing them explicitly to get the references to the 'oj' and 'ko'
 * objects in the callback.
 *
 * For a listing of which JET component modules are required for each component, see the specific component
 * demo pages in the JET cookbook.
 */

require(['ojs/ojcore', 'knockout', 'jquery', 'ojs/ojknockout', 'ojs/ojbutton', 'ojs/ojtoolbar','ojs/ojmenu','ojs/ojpictochart'], // add additional JET component modules as needed
  function(oj, ko, $) // this callback gets executed when all required modules are loaded
  {
      // add any startup code that you want here
  }
);

This file will be included in our Apex page using a js script inclusion.

<script data-main="#IMAGE_PREFIX#libraries/oraclejet/js/main" 
src="#IMAGE_PREFIX#libraries/oraclejet/js/libs/require/require.js" 
></script>

Since the script tag needs a special data-main attribute, we can not use the normal Apex 5 mechanism of adding the js.file name to the appropriate page section.

Instead we need to modify the page template. I copied the minimal one.
The result looks like this.

...
<head>
  <meta charset="utf-8">  
  <title>#TITLE#</title>
  #APEX_CSS#
  #THEME_CSS#
  #TEMPLATE_CSS#
  #THEME_STYLE_CSS#
  #APPLICATION_CSS#
  <!-- Oracle JET CSS files -->
  <link rel="stylesheet" href="#IMAGE_PREFIX#libraries/oraclejet/css/libs/oj/v2.0.0/alta/oj-alta-min.css" type="text/css"/>
  <script data-main="#IMAGE_PREFIX#libraries/oraclejet/js/main" src="#IMAGE_PREFIX#libraries/oraclejet/js/libs/require/require.js" ></script>
    
  #PAGE_CSS#  
  #FAVICONS#
  #HEAD#
  
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"/>
</head>
...

There is one more problem. Require.js does not like if a library with the same name is loaded previously without using require.js. (There are some notable exceptions that depend on using Asyncronous Module Definition Api (AMD)).

Apex 5.0 already loads the Hammer.js library in a different version than the one, that is included with the base Oracle JET distribution. Hammer.js provides the possibility to use Touch Gestures. So it is especially useful for mobile applications.

Because Apex already loaded Hammer require.js throws an error message. The only way I found to make it work, was to remove Hammer.js from the Theme. Unfortunately this can be done only by unsubscribing the Theme. After unsubscribing, we can edit the Theme and remove Hammer.js from the Javascript Files Section.

Thanks to Roel Hartmann for the tipp!

In any case you should check that the libraries were loaded successfully.

On firefox open the console (SHIFT+CTRL+K) and switch on the network traffic.

apex5_log_network

If all files loaded successfully then we are up and running.

It is time to try our first chart from the cookbook.

Using a static Cookbook Demo

So lets try to use the page and add a pictoChart to it

Link: http://www.oracle.com/webfolder/technetwork/jet/uiComponents-pictoChart-default.html

First we create a static region and add the html from the cookbook.

<div  id="picto-container">
  <div id='pc1' data-bind="ojComponent:{
      component: 'ojPictoChart',
      items: pictoChartItems,
      animationOnDisplay: 'auto',
      columnCount: 5
    }"
    style="vertical-align:middle; margin-right:15px">
  </div>
  <div style="display:inline-block; vertical-align:middle; font-weight:bold">
    <span style="color:#333333; font-size:1.1em">7 out of 10 college students</span><br>
    <span style="color:#ed6647; font-size:1.3em">have sleep problems.</span>
  </div>
</div>

Then we add some javascript functions to the page itself (global functions section).

In the javascript section we need to repeat the require call including the configuration part. This is because the libs are loaded asynchronously. On each page where we want to add some js files from Oracle Jet we would do a require call immediately before the relevant section. This time the baseUrl setting is helpful.

To enable pictocharts we need to require the ojs/ojpictochart lib.

require.config({
  baseUrl: '#IMAGE_PREFIX#libraries/oraclejet/js',
  paths: {
    'knockout': 'libs/knockout/knockout-3.4.0',
    'jquery': 'libs/jquery/jquery-2.1.3.min',
    'jqueryui-amd': 'libs/jquery/jqueryui-amd-1.11.4.min',
    'ojs': 'libs/oj/v2.0.0/min',
    'ojL10n': 'libs/oj/v2.0.0/ojL10n',
    'ojtranslations': 'libs/oj/v2.0.0/resources',
    'signals': 'libs/js-signals/signals.min',
    'text': 'libs/require/text',
    'promise': 'libs/es6-promise/promise-1.0.0.min',
    'hammerjs': 'libs/hammer/hammer-2.0.4.min',
    'ojdnd': 'libs/dnd-polyfill/dnd-polyfill-1.0.0.min'
  },
   // waitSeconds: 1,
  // Shim configurations for modules that do not expose AMD
  shim: {
    'jquery': {
      exports: ['jQuery', '$']
    }
  }
});      

require(['ojs/ojcore', 'knockout', 'jquery', 'ojs/ojknockout', 'ojs/ojpictochart'], function (oj, ko, $) {
    function PictoChartModel() {
      this.pictoChartItems = ko.observableArray([
        {name: 'Have  Sleep Problems', shape: 'human', count:7, color: '#ed6647'},
        {name: 'Sleep Well', shape: 'human', count: 3}
      ]);
    }
    var pictoChartModel = new PictoChartModel();

    $(document).ready(
      function() {
        ko.applyBindings(pictoChartModel, document.getElementById('picto-container'));
      }
    );
  });

Voilá we are done!

This is how the result looks like.
Screen Shot 2016-04-07 at 01.16.45

How to enhance the pictoCharts a bit including adding other shapes will be explained in another blog post.

Here are just two more screenshots to give you some ideas.

Screen Shot 2016-04-07 at 01.17.16Screen Shot 2016-04-07 at 00.28.13

SQL Quickie: Column usage

A question directed at me recently was if we can somehow monitor how a table is accessed useing which where conditions. The goal was to decide about index creation for such a table.

Fortunatly Oracle already collects some relevant information for this.

The sys.col_usage$ table is used by the dbms_stats package to decide which columns might profit by adding histograms. If we have DBA privs, we can access this information:

-- column usage 
select o.owner, o.object_name,  c.column_name,
       u.*
from dba_objects o
join dba_tab_columns c on c.owner = o.owner and c.table_name = o.object_name 
left join SYS.COL_USAGE$ u  on o.object_id = u.obj# and u.intcol# = c.column_id
where o.object_type = 'TABLE'
and c.owner = 'LARRY' -- enter appropriate schema here
and c.table_name = 'CLOUDNO9' -- enter appropriate table here
order by c.table_name, c.column_id;

And this is how the result could look like

OWNER	OBJECT_NAME	COLUMN_NAME	OBJ#	INTCOL#	EQUALITY_PREDS	EQUIJOIN_PREDS	NONEQUIJOIN_PREDS	RANGE_PREDS	LIKE_PREDS	NULL_PREDS	TIMESTAMP
LARRY	CLOUDNO9	COL1		68200	1	725	5	0	0	1	0	10.03.2016 15:42
LARRY	CLOUDNO9	COL2									
LARRY	CLOUDNO9	COL3									
LARRY	CLOUDNO9	COL4									
LARRY	CLOUDNO9	COL5									
LARRY	CLOUDNO9	COL6		68200	6	0	1	0	0	0	0	19.04.2012 13:33
LARRY	CLOUDNO9	COL7		68200	7	0	1	0	0	0	0	19.04.2012 13:33
LARRY	CLOUDNO9	COL8		68200	8	0	1	0	0	0	2	25.06.2014 10:28
LARRY	CLOUDNO9	COL9		68200	9	326	3	0	0	1	0	24.02.2016 09:13
LARRY	CLOUDNO9	COL10		68200	10	316	1	0	0	0	0	24.02.2016 09:13
LARRY	CLOUDNO9	COL11		68200	11	315	1	0	0	0	0	24.02.2016 09:13
LARRY	CLOUDNO9	ID		68200	12	891	1674	0	0	0	0	10.03.2016 17:57
LARRY	CLOUDNO9	FK1_ID		68200	13	2236	3	1	0	0	0	10.03.2016 19:43
LARRY	CLOUDNO9	FK2_ID		68200	14	1	508	0	0	0	3	10.03.2016 17:57
LARRY	CLOUDNO9	CHANGED									
LARRY	CLOUDNO9	CHANGED_BY									
LARRY	CLOUDNO9	COL12		68200	17	0	161	0	0	0	161	10.03.2016 14:57

The different columns are statistic counts with some obvious meaning

  • EQUALITY_PREDS = equality predicates
    for example a where condition as this

    col1 = 'ABC'
  • EQUIJOIN_PREDS = column used by an equi join
    for example a join as this

    ...from a join b on a.id = b.a_id

    IN / NOT IN comparisons often also count as equi joins

    ...from a 
    where a.id not in (select b.a_id from b)
  • NONEQUIJOIN_PREDS = column used by a non equi join
    for example a join as this

    ...from a join b on a.col1 between b.col1 and b.col2
  • RANGE_PREDS = column used by a range comparison
    for example a where condition as this

    where a.col_date >= trunc(sysdate)-5
  • LIKE_PREDS = column used by a like comparison
    for example a where condition as this

    where a.col1 like 'AMORE%'

    Any kind of like expression counts.

  • NULL_PREDS = column compared with IS NULL
    for example a where condition as this

    where a.col1 is null

    IS NOT NULL is not tracked. And only for columns that are optional.

  • TIMESTAMP = tells us when this column was last used using one of those methods

Doing such statements the information is first stored and collected somewhere in the SGA. We can force it into this table by flushing it using

execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

Recommendation

We can add a simple index check to the previous select.
This helps to quickly identify columns that might profit from an index.
Obvious candidates would be columns where no index exists but with a high count of EQUALITY_PREDS+EQUIJOIN_PREDS, but also RANGE_PREDS.

-- column usage + index check
select o.owner, o.object_name,  c.column_name,
       case when exists (select null 
                          from dba_ind_columns i 
                          where i.table_owner = c.owner 
                          and i.table_name = c.table_name 
                          and i.column_name = c.column_name) 
        then 'YES' else 'NO' 
       end  as index_exists,
       u.*
from dba_objects o
join dba_tab_columns c on c.owner = o.owner and c.table_name = o.object_name 
left join SYS.COL_USAGE$ u  on o.object_id = u.obj# and u.intcol# = c.column_id
where o.object_type = 'TABLE'
and c.owner = 'LARRY' 
and c.table_name = 'CLOUDNO9'
order by c.table_name, c.column_id;

And if you want to quickly scan your whole schema try this version.

-- column usage without index for the whole schema 
select * 
from (
  -- column usage + index check
  select o.owner, c.table_name,  c.column_name, 
         case when exists (select null 
                            from dba_ind_columns i 
                            where i.table_owner = c.owner 
                            and i.table_name = c.table_name 
                            and i.column_name = c.column_name) 
          then 'YES' else 'NO' 
         end  as index_exists,
         c.num_distinct, c.nullable, 
         u.*
  from dba_objects o
  join dba_tab_columns c on c.owner = o.owner and c.table_name = o.object_name 
  left join SYS.COL_USAGE$ u  on o.object_id = u.obj# and u.intcol# = c.column_id
  where o.object_type = 'TABLE'
  and c.owner = 'LARRY' 
  --and c.table_name = 'XXX'
  )
where index_exists = 'NO' 
and equality_preds+equijoin_preds+range_preds > 100 
and equality_preds+equijoin_preds+range_preds > like_preds+null_preds
and num_distinct > 1
order by equality_preds+equijoin_preds+range_preds desc, table_name, intcol#;

References

Maria Colgan mentioned this table in her blog about column histograms:
https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt

Ulrike Schwinn wrote a nice article for the German community about table and column monitoring:
https://apex.oracle.com/pls/apex/germancommunities/dbacommunity/tipp/1261/index.html

Sequence and Audit columns with Apex 5 and 12c

Introduction

In the good ol’ times database triggers were used to populate database columns like primary identifiers or audit columns.

Here is an example of such a trigger.
For sake of simplicity I concentrate on the insert trigger.

create table swe_demo (id number primary key
                      ,col1 number
                      ,col2 varchar2(30)
                      ,inserted_date date not null
                      ,inserted_from varchar2(30) not null);

create sequence swe_demo_seq cache 10000;

create or replace trigger swe_demo_bri_trg
  BEFORE INSERT ON swe_demo
  FOR EACH ROW
BEGIN
   -- record needs always a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

   -- timestamp of the last changes
   :new.inserted_date := SYSDATE;
   :new.inserted_from := COALESCE(v('APP_USER'), user);
END swe_demo_bri_trg;
/

What does it do?

  • The ID column is filled with a sequence value.
  • The inserted_date column is filled with sysdate.
  • The inserted_from column is filled with the current apex user or if that is not set, then the connected user is choosen.

It is time to revisit that functionality and see which new and maybe better conceptes are offered when the newest technologies are in place.

This was tested against Apex 5.0.1 and Oracle 12.1.0.1 .
It should work also in Apex 5.0.3 and Oracle 12.1.0.2

Solution

If you are just looking for the final code – here is my “perfect” solution.

create sequence swe_demo_seq cache 10000;
create table swe_demo
  (id number default swe_demo_seq.nextval primary key
   ,col1 number
   ,col2 varchar2(30)
   ,inserted_date date default sysdate not null
   ,inserted_from varchar2(30) default coalesce(
          sys_context('APEX$SESSION','app_user')
         ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
         ,sys_context('userenv','session_user')
         )
      not null);

The inserted_from value is now more accurate.

You might have noticed that the need for an insert trigger is gone.
This increases performance considerably.

To insert 1 mill rows the old version needed more than a minute. This can be considered fast.
Elapsed: 00:01:12.904

The new version needs less than 6 seconds. This is a blizzard!
Elapsed: 00:00:05.598

I admit that the test was specifically set up to show a major difference. Feel free to set up your own more realistic test cases.

Still 12 times faster is really nice.

The test code to get this result can be seen in chapter TC4.

Considerations and test cases

How to find out about the current user

This line

COALESCE(v('APP_USER'), user)

from the example is really bad.

v(‘APP_USER’) is a bit slow, although not as slow as I suspected. It will not work on databases without apex installations. “v” is just a public synonym for an apex function that looks up session specific values in the apex repository. Surprisingly the “user” function is a much bigger drag. See TC1 below for performance comparisons.

In older apex versions a good way to find out who was logged in, was to read the client_identifier from the userenv context.

sys_context('userenv','client_identifier')

The structure of this did change between the different apex versions. In Apex 5 it now holds the APP_USER and the Session ID, e.g. SVEN:0123456789

A context is an extremly efficient way to read global session or application data.

Apex 5 introduced a new context with the namespace “APEX$SESSION”. This context is populated by the apex engine (APEX_050000.WWV_FLOW_SESSION_CONTEXT) and holds app_user, app_session and workspace_id. There is a good chance that future versions will add more parameters to this namespace.

See also: http://jeffkemponoracle.com/2015/11/24/apex-5-application-context/

To read the logged in user we fetch SYS_CONTEXT(‘APEX$SESSION’,’APP_USER’).
To read the user if there is something non-apex running, we should read the client_identifer. But there is more to consider.

database links

In many apex projects we do not insert into the local apex database, but into some remote project schema. This is often the case when I just add some reporting or monitoring solution in apex, without interfering with the real project database too much. So lets compare how the different contexts behave when done local vs. remote (over a db link).

12c introduced some changes to the userenv namespace. Especially the “current_user” is now deprecated.
Here is a list of all parameters that belong to the userenv namespace:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#g1513460

The interesting ones are:

  • client_identifier
    Returns an identifier that is set by the application through the DBMS_SESSION.SET_IDENTIFIER procedure, the OCI attribute OCI_ATTR_CLIENT_IDENTIFIER, or the Java class Oracle.jdbc.OracleConnection.setClientIdentifier. This attribute is used by various database components to identify lightweight application users who authenticate as the same database user.
  • current_schema
    Name of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement.
  • current_user
    Deprecated – Use the SESSION_USER parameter instead.
  • session_user
    For enterprises users, returns the schema. For other users, returns the database user name by which the current user is authenticated. This value remains the same throughout the duration of the session.
  • dblink_info
    Returns the source of a database link session. Specifically, it returns a string of the form:SOURCE_GLOBAL_NAME=dblink_src_global_name, DBLINK_NAME=dblink_name, SOURCE_AUDIT_SESSIONID=dblink_src_audit_sessionid
  • authenticated_identity
    Returns the identity used in authentication. In the list that follows, the type of user is followed by the value returned: …

For testing purposes I just made a loopback link to the same database but a different schema. The testscript can be found in section TC2.

The following tests were done:
1) An insert from an apex page into a view that included a db link to REMOTE_B
2) An insert from an apex page, directly into a local view (LOCAL_A) which was mapped to demo.swe_demo.
5) A direct database insert in the view with the DB link (remote insert)
6) A direct database insert in the view without the db link
7) an insert from a DBA “IAMDBA” using alter session set current_schema

This was the result

Id Some Text Inserted Date Inserted From App User Apex Session Client Identifier Current Schema Current User User Session User Authenticated Identity Dblink Info
1 apex remote insert 22-FEB-16 SVEN SVEN:4628609689353 DEMO DEMO REMOTE_B REMOTE_B REMOTE_B SOURCE_GLOBAL_NAME=DB_A.11623201
2 apex local insert 22-FEB-16 SVEN SVEN SVEN SVEN:4628609689353 DEMO DEMO APEX_PUBLIC_USER APEX_PUBLIC_USER APEX_PUBLIC_USER
5 direct remote insert 22-FEB-16 REMOTE_B DEMO DEMO REMOTE_B REMOTE_B REMOTE_B
6 direct local insert 22-FEB-16 LOCAL_A DEMO DEMO LOCAL_A LOCAL_A LOCAL_A
7 direct insert current_schema 22-FEB-16 IAMDBA DEMO DEMO IAMDBA IAMDBA IAMDBA

I did some more tests, for example using definer and invoker rights procedures, but they didn’t reveal any important differences.

The findings

APEX$SESSION is not populated via db link, but CLIENT_IDENTIFIER is.
Other than those two SESSION_USER has the best information and is always populated.

Therefore in an apex near environment the best expression to find out who inserted some record would be

coalesce(
   sys_context('APEX$SESSION','app_user')
   ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
   ,sys_context('userenv','session_user')
   )

One should also notice that current_user returns the same values as current_session. This is contrary to the documentation in older database versions. And Oracles suggestion to use session_user instead is certainly correct, but you should be aware that then a different user might be stored than before.

Authenticated_Identity promises to be interesting in very specific security environments.

12c identity columns and default values

With 12c we can consider to use an identity column as our primary key. Also it is now possible to use a sequence as a default value for the column.

The base syntax for an identity column is

id number generated as identity

The base syntax for a default value column is

id number default swe_demo_seq.nextval

There are several differences between the two.
An identity column essentially prevents that values are inserted into this column. So that it is ensured that always the sequence was used to populate the data. The default value column uses the sequence only if null was inserted. This is essentially the same functionality what the trigger did.

It is possible to setup an identity column to behave almost the same as the default value column.

The extended syntax for such an identity column is

id number generated by default on null as identity (cache 10000)

I slightly prefer the default value column syntax for two reasons

  1. The default value syntax is also used for the two audit columns. So it is consistent to use the same mechanism.
  2. Default value columns allow more control over the sequence object.

I plan to write an extra blog post to discuss the differences in more detail.

TC3 shows an performance comparison between trigger logic, default value and identity column. Default values and identity are equally fast. But the trigger looses by a large margin!

Various testcases

For most cases the results should be consistent in other environments too. However always test it your own. Here are the scripts that I used. They might help to setup your own test cases.

TC1) Compare performance of different expressions to read the client identifer

The following plsql block was used and run

set time on
set timing on
declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := ##EXPRESSION##;
  end loop;
end;
/

This is the output for several tested expressions.
Watch out that some expressions return different results.

I always did three runs to see if the results are consistent and copied one of the elapsed times to the table.

sys_context('userenv','client_identifier')

00:00:02.436
This is the base. It should not be possible to go faster.

substr(sys_context('userenv','client_identifier'),1
            ,instr(sys_context('userenv','client_identifier'),':')-1)

00:00:04.288
Finds the colon and returns everything before that. But returns NULL if no colon is in the client_identifer.

substr(sys_context('userenv','client_identifier'),1, 
   coalesce(nullif(instr(sys_context('userenv','client_identifier'),':'),0)-1,
 length(sys_context('userenv','client_identifier'))))

00:00:06.311
A little complex to read, isn’t it…

regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')	

00:00:03.459
This is the winner!

translate(sys_context('userenv','client_identifier'),'A:0123456789','A')

00:00:05.663
Surprisingly slow

It surprised me that the REGEXP_SUBSTR expression was the best performing. Previous comparisons between regular expressions and substr/instr combinations always went in favor of substr. It seems either the internal code was optimized by oracle. Or more likely that this specific task (simple search from the beginning of the string) is well suited for a fast regexp search.

In general all expressions were fast. Interesting side note is that REGEXP_SUBSTR and TRANSLASTE were a tiny bit slower for the second identifier, while the other expressions performed equally fast.

Some other expressions:

user 

Elapsed: 00:00:20.652
Really really slow!

sys_context('APEX$SESSION','app_user')

00:00:01.549
Blazingly fast!

v('APP_USER')

00:00:05.646
Not as slow as I thought!

TC2) Analyze the value of differnt contexts with regards to DB links

This script can be run as DBA to setup a demo user.
Make sure you do not have a schema named “demo” already in your system.


create user demo identified by demo;
grant unlimited tablespace to demo;

drop table demo.swe_demo ;
drop sequence demo.swe_demo_seq;

create table demo.swe_demo 
                      (id number primary key
                      ,some_text varchar2(30)
                      ,inserted_date           date not null
                      ,inserted_from           varchar2(30) not null
                      ,c_app_user                varchar2(30)
                      ,c_apex_session            varchar2(30)
                      ,c_client_identifier       varchar2(100)
                      ,c_current_schema          varchar2(30)
                      ,c_current_user            varchar2(30)
                      ,c_user                    varchar2(30)
                      ,c_session_user            varchar2(30)
                      ,c_authenticated_identity  varchar2(100)
                      ,c_dblink_info             varchar2(100)
                      ,c_external_name           varchar2(30)
                      );

create sequence demo.swe_demo_seq cache 10000;


create or replace trigger demo.swe_demo_bri_trg
  BEFORE INSERT ON demo.swe_demo
  FOR EACH ROW
BEGIN
   -- record needs a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

   -- timestamp of last changes 
   :new.inserted_date := SYSDATE;
   :new.c_app_user               := v('APP_USER');
   :new.c_apex_session           := sys_context('APEX$SESSION','app_user');
   :new.c_client_identifier      := sys_context('userenv','client_identifier');
   :new.c_current_schema         := sys_context('userenv','current_schema');
   :new.c_current_user           := sys_context('userenv','current_user');
   :new.c_user                   := user;
   :new.c_authenticated_identity := sys_context('userenv','authenticated_identity');
   :new.c_session_user           := sys_context('userenv','session_user');
   :new.c_dblink_info            := sys_context('userenv','dblink_info');
   :new.c_external_name          := sys_context('userenv','EXTERNAL_NAME');
   
   -- recommended:
   :new.inserted_from        := coalesce(sys_context('APEX$SESSION','app_user')
                                     ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
                                     ,sys_context('userenv','session_user')
                                     );
   
END swe_demo_bri_trg;
/

show errors

grant select, insert, update, delete on demo.swe_demo to SchemaB;

TC3) Performance comparison for populating PK columns

--------------------------------------------------------------------------------
-- swe 12c demo identity columns
--------------------------------------------------------------------------------
set time on
set timing on

-- old logic using trigger
drop table swe_demo ;
drop sequence swe_demo_seq;
create table swe_demo (id number primary key, col1 number, col2 varchar2(30));
create sequence swe_demo_seq cache 10000;

create or replace trigger swe_demo_bri_trg
  BEFORE INSERT ON swe_demo
  FOR EACH ROW
BEGIN
   -- record needs a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

END swe_demo_bri_trg;
/

-- Performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;
   
-- result
1,000,000 rows inserted.
Elapsed: 00:00:36.854

-- setup 12c logic using default value sequences
drop table swe_demo ;
drop sequence swe_demo_seq;
create sequence swe_demo_seq cache 10000;
create table swe_demo (id number default swe_demo_seq.nextval primary key, col1 number, col2 varchar2(30));


-- performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;

-- result
1,000,000 rows inserted.
Elapsed: 00:00:04.068


-- 12c logic using identity column 
drop table swe_demo ;
drop sequence swe_demo_seq;
create table swe_demo (id number generated as identity primary key, col1 number, col2 varchar2(30));

-- Performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;

--result 
1,000,000 rows inserted.
Elapsed: 00:00:08.626

There is a performance difference compared to the default value solution. This is purely based upon the sequence cache
The identity column was created with a default cache size of 20.

--Try to manually insert a row into the identiy column 
insert into swe_demo (id, col1)
values (null, -1);

SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 -  &amp;amp;quot;cannot insert into a generated always identity column&amp;amp;quot;
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.
;
-- use non default settings for the identity column
-- 12c logic using identity column 
drop table swe_demo purge;
create table swe_demo (id number generated by default on null as identity (cache 10000) primary key , col1 number, col2 varchar2(30));

-- Performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;

--result 
1,000,000 rows inserted.
Elapsed: 00:00:03.763    

-- test insert
insert into swe_demo (id, col1)
values (null, -1);

1 row inserted.

insert into swe_demo (id, col1)
values (-1, -1);

1 row inserted.

It seems as this is even a tiny bit faster than the default value solution. The test results were influenced heavily by other network or database activities.
Both execution times are very close. The top score however was done using an identity column.

TC4) Extend performance test TC3 with audit columns

--------------------------------------------------------------------------------
-- Adding audit columns to the previous example
-- Final comparison
--------------------------------------------------------------------------------
-- old logic 
drop table swe_demo purge;
drop sequence swe_demo_seq ;

create sequence swe_demo_seq cache 10000;
create table swe_demo (id number primary key
                      ,col1 number
                      ,col2 varchar2(30)
                      ,inserted_date date not null
                      ,inserted_from varchar2(30) not null);

create or replace trigger swe_demo_bri_trg
  BEFORE INSERT ON swe_demo
  FOR EACH ROW
BEGIN
   -- record needs always a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

   -- timestamp of the last changes
   :new.inserted_date := SYSDATE;
   :new.inserted_from := COALESCE(v('APP_USER'), user);
END swe_demo_bri_trg;
/

-- Performance test to insert 1 mill rows
insert into swe_demo (col1)
select level from dual connect by level <= 1000000;

1,000,000 rows inserted.
Elapsed: 00:01:12.904

-- new logic 
drop table swe_demo purge;
drop sequence swe_demo_seq;
create sequence swe_demo_seq cache 10000;
create table swe_demo
  (id number default swe_demo_seq.nextval primary key
   ,col1 number
   ,col2 varchar2(30)
   ,inserted_date date default sysdate not null
   ,inserted_from varchar2(30) default coalesce(
          sys_context('APEX$SESSION','app_user')
         , regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
         ,sys_context('userenv','session_user')
         )
      not null);

-- Performance test to insert 1 mill rows
insert into swe_demo (col1)
select level from dual connect by level <= 1000000;

1,000,000 rows inserted.
Elapsed: 00:00:05.598

Side notes

EBR considerations

A trigger is an editionable object. A default column value is not editionable. So if the default expression needs to change, then this will be done in all editions at the same time. With a trigger we are able to choose.

Possible issues

The db_link_info context did not return what the documentation said.
This might be an issue specific to the oracle db version (12.1.0.1).

Security considerations

Consider if you want to trust the client_identifier. Any session can simply set this value and _identify_ as someone else. The apex$session context can not so easily be manipulated.

fragments

When using identity columns if you drop the table the automatically generated sequence (ISEQ$…) will stay. This is needed in case the table is flashbacked from the recycle bin. Therefore I always try to remember to purge the table.

drop table swe_demo purge;

Cleanup

Cleanup code for most of the generated objects:

drop user demo cascade;
drop table swe_demo purge;
drop sequence swe_demo_seq;

identity column issue with DB links

Introduction

In oracle 12c we have some new features regarding the usage of sequences especially for primary key columns. The usage of default values and identity columns.

Both are very performant which has been shown several times already. While default values are really nice to use, identity columns have an essential drawback.

If one inserts into a remote table, then it is not possible to get the generated PK value.

Links

Problem demonstration

Assume we have two databases A_local and B_remote. On database A_local we have a database link (called DBLINK) pointing to some schema on database B_remote.

We now setup two tables on the remote database.

create sequence demo_seq;
create table demo_remoteinsert1 
   (  id number default demo_seq.nextval not null primary key
    , txt varchar2(100) not null
    );
    
create table demo_remoteinsert2 
   (  id number generated as identity primary key
    , txt varchar2(100) not null
    );

Note that table 1 uses a default value based upon the sequence DEMO_SEQ. Table 2 uses an identity column. The identity column behind the scenes is implemented using a default value and a sequence. I will show this later below.

On the local database A_local we set up some synonyms and views to have an easy access to those remote obejcts.

create synonym remote_demo_Seq for demo_seq@dblink;
create view v_demo_remoteinsert1 
  as select * from demo_remoteinsert1@dblink;
create view v_demo_remoteinsert2 
  as select * from demo_remoteinsert2@dblink;

Now we want to do an insert in the A_Local database.

set serveroutput on
declare
  v_id v_demo_remoteinsert1.id%type;
begin
  insert into v_demo_remoteinsert1 (txt)
  values ('abc')
  returning id into v_id;
  dbms_output.put_line('new ID='||v_id);
end;
/
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at line 4
22816. 00000 -  "unsupported feature with RETURNING clause"
*Cause:    RETURNING clause is currently not supported for object type
           columns, LONG columns, remote tables, INSERT with subquery,
           and INSTEAD OF Triggers.
*Action:   Use separate select statement to get the values.

It is not possible to do an insert and fetch the generated ID values using the returning clause.

Fortunately there is a simple workaround. Fetch first from the sequence, then do the insert using the sequence value.

set serveroutput on
declare
  v_id v_demo_remoteinsert1.id%type;
begin
  v_id := remote_demo_seq.nextval;  
  insert into v_demo_remoteinsert1 (id, txt)
  values (v_id, 'abc');
  dbms_output.put_line('new ID='||v_id);
end;
/
PL/SQL procedure successfully completed.

new ID=1

Now lets try the same using table 2. Remember table 2 has column ID generated as identity.

If we try the returning clause we get the same error as above.

set serveroutput on
declare
  v_id v_demo_remoteinsert2.id%type;
begin
  insert into v_demo_remoteinsert2 (id, txt)
  values (v_id, 'abc')
  returning id into v_id;
  dbms_output.put_line('new ID='||v_id);
end;
/
ORA-22816: unsupported feature with RETURNING clause
22816. 00000 -  "unsupported feature with RETURNING clause"
*Cause:    RETURNING clause is currently not supported for object type
           columns, LONG columns, remote tables, INSERT with subquery,
           and INSTEAD OF Triggers.
*Action:   Use separate select statement to get the values.

The workaround would be fetch from the sequence first, then do the insert.
In this case we did not directly create a sequence object. However oracle created such an object and we can find out which sequence it is. The generated sequence will be named “ISEQ$_xxxx” where xxx is a generated number. In my example it was ISEQ$$_1988882.

The correct way to find which sequence object was generated is to look at the default value for the column.

select column_name, data_default, identity_column
from user_tab_columns 
where table_name = 'DEMO_REMOTEINSERT2';
COLUMN_NAME	DATA_DEFAULT	IDENTITY_COLUMN
ID	"B_REMOTE"."ISEQ$$_1988882".nextval	YES
TXT		NO

Using that sequence name we can try to do a remote insert in A_local.

create synonym remote_demo_seq2 for ISEQ$$_1988882@dblink;

set serveroutput on
declare
  v_id v_demo_remoteinsert2.id%type;
begin
  v_id := remote_demo_seq2.nextval;  
  insert into v_demo_remoteinsert2 (id, txt)
  values (v_id, 'abc');
  dbms_output.put_line('new ID='||v_id);
end;
/
Error report -
ORA-32795: cannot insert into a generated always identity column
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.

Well we did use the associated sequence generator to provide the value, however oracle doesn’t know this and still creates an error.

workarounds

How to solve this problem?

There is no perfect solution. However depending on the environment I see two possible ways to circumvent the problem.

Workaround 1) Create a plsql api to do the insert

This will work only if you are allowed to create remote procedures and functions. Also there might be some obstacles to implement this in a very robust way. For example there should be almost no additional overhead if the table structure changes (added columns etc.).

This plsql api could also solve the issue why we need this ID returned in the first place. For example if we want to insert data into some child tables then we need the ID value to be used as a FK for the child table. The api could do it all in one go.

Workaround 2) Do a select afterwards using an unique identifier

Most tables should have a PK populated by a meaningless technical sequence value. Most tables will also have a UK based upon some meaningful business value. If such an UK exists then we can do a second select on the table using the UK values to fetch the PK value. It is cumbersome and not as performant but it works if the data model is well designed.

Workaround 3) Fetch currval in the same session

I personally dislike the usage of currval. This is the first example where I found it to be usable.

set serveroutput on
declare
  v_id v_demo_remoteinsert2.id%type;
begin
  insert into v_demo_remoteinsert2 (txt)
  values ('abc');
  select remote_demo_seq2.currval into v_id from dual;  
  dbms_output.put_line('new ID='||v_id);
end;
/
PL/SQL procedure successfully completed.

new ID=2

Note that this is not the recommended action based upon oracles own error description.

Additionally I am not 100% sure that CURRVAL will always return the correct value and that it will always return a value for our session when used via DB link.

This is from the oracle documentation:

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL. Refer to CREATE SEQUENCE for information on sequences.

Since we need to call nextval in our session first to initialize currval it should work. However I did not find an offical oracle document specifially explaining this for usage over database links.

It is hard to setup a testcase where another session inserts some values at the same time and thereby manipulating the sequence cache. I tried, but cound’t break the example. So with all caution, it could be an easy workaround to setup.

Solution: Let oracle implement the returning clause also via database links!

I created an database idea for this. Vote up here to increase the chance that this is implemented in a future version.

Every second counts!

About DAY TO SECOND INTERVAL arithmetic

This is a short collection of 30 SQL examples that deal with intervals, specifically the DAY TO SECOND interval. In some cases the result differs from what one suspects.

I’ve hidden the results, so you can read it like a quiz. First think about the expected result, then see if it matches your expectation.

There is no special explanation given. If you want more details check the documentation links at the end of the article or post a comment.

tested on a 11.2.0.4 database (using German date language settings)

Creating an interval

Example 1

select interval '90' second from dual;
Click to see result

+00 00:01:30.000000

Example 2

select interval '90' second(2) from dual;
Click to see result

+00 00:01:30.000000

Example 3

select interval '90' second(1) from dual;
Click to see result

+00 00:01:30.000000

Example 4

select interval '90' day(1) from dual;
Click to see result

ORA-01873: the leading precision of the interval is too small
*Cause: The leading precision of the interval is too small to store the
specified interval.
*Action: Increase the leading precision of the interval or specify an
interval with a smaller leading precision.

Example 5

select interval '1:30' minute to second from dual;
Click to see result

+00 00:01:30.000000

Example 6

select interval '0:90' minute to second from dual;
Click to see result

ORA-01852: seconds must be between 0 and 59

Example 7

select numtodsinterval(90, 'second') from dual;
Click to see result

+00 00:01:30.000000

Example 8

select numtodsinterval(1.5, 'minute') from dual;
Click to see result

+00 00:01:30.000000

Example 9

select to_dsinterval('90') from dual;
Click to see result

ORA-01867: the interval is invalid
*Cause: The character string you specified is not a valid interval.
*Action: Please specify a valid interval.

Example 10

select to_dsinterval('+0 00:01:30') from dual;
Click to see result

+00 00:01:30.000000

Example 11

select to_dsinterval('PT0H1M30S') from dual;
Click to see result

+00 00:01:30.000000

Example 12

select to_dsinterval('PT90S') from dual;
Click to see result

+00 00:01:30.000000

Interval-like data

Example 13

select to_timestamp('30','SS') from dual;
Click to see result

01.01.16 00:00:30,000000000

Example 14

select to_timestamp('90','SS') from dual;
Click to see result

ORA-01852: seconds must be between 0 and 59

Example 15

select to_timestamp('90','SSSSS') from dual;
Click to see result

01.01.16 00:01:30,000000000

Example 16

select to_timestamp('1:30','MI:SS') from dual;
Click to see result

01.01.16 00:01:30,000000000

Using and printing intervals

Example 17

select extract(second from interval '90' second) from dual;
Click to see result

30

Example 18

select extract(second from systimestamp) from dual;
Click to see result

57,131241

Example 19

select extract(second from sysdate) from dual;
Click to see result

ORA-30076: invalid extract field for extract source
*Cause: The extract source does not contain the specified extract field.
*Action:

Example 20

select extract(day from sysdate) from dual;
Click to see result

20

Example 21

select extract(second from numtodsinterval(90, 'second')) from dual;
Click to see result

30

Example 22

select extract(second from to_timestamp('1:30','MI:SS')) from dual;
Click to see result

30

Example 23

select to_char(interval '90' second,'SS') from dual;
Click to see result

+00 00:01:30.000000
I think this is a bug!
The formatting seems to be ignored for interval datatypes (Oracle 11.2.0.4)

Example 24

select to_char(interval '90' second,'SSSSS') from dual;
Click to see result

+00 00:01:30.000000

Example 25

select to_char(trunc(sysdate) + interval '90' second,'SS') from dual;
Click to see result

30

Example 26

select to_char(trunc(sysdate) + interval '90' second,'SSSSS') from dual;
Click to see result

00090

Example 27

select to_char(trunc(sysdate) + interval '90' second,'FMSSSSS') from dual;
Click to see result

90

Example 28

select sysdate - (sysdate - interval '90' second) from dual;
Click to see result

0,001041666666666666666666666666666666666667

Example 29

select sysdate - sysdate + interval '90' second from dual;
Click to see result

ORA-30081: invalid data type for datetime/interval arithmetic

Example 30

select numtodsinterval(sysdate - (sysdate - interval '90' second),'DAY') from dual;
Click to see result

+00 00:01:30.000000

Quirks

  • The precision for seconds seems to be ignored for interval literals (example 2).
  • TO_CHAR does not consider the formatting or intervals have no extra formatting options
  • EXTRACT uses ANSI date. ANSI date has no time component. That’s why one can extract the day from it but not the seconds (example 19/20)
  • extract on a timestamp also returns fractional seconds

Documentation links

Examining JSON functions in 12.1.0.2

In preparation of a new version of my Quickreference SQL Advanced Functions i had a closer look at the new SQL JSON possibilities which are available in Oracle DB 12.1.0.2.

Overview

There are three functions and three conditions that all deal with JSON.

  • json_value – a function that returns a single (skalar) value
  • json_query – a function that returns a json string (can be an array)
  • json_table – a function that provides table logic based upon a json document.
  • is [not] json – checks if the document is json or not
  • json_exists – checks if a certain element or structure exists in the json document
  • json_textcontains – efficient search through a json document using an oracle text index

JSON_TABLE is the most versatile of all json functions. It can do everything that json_value, json_query and json_exists can do and more than that. So if you want to learn more about the json functions in 12c then concentrate on JSON_TABLE. See also: JSON_TABLE Generalizes Other Oracle SQL Functions

Call Parameters

data is [not] json
json_exists(data, $path on_error)
json_textcontains(column, $path, searchstring)
json_query(data, $path returning query_wrapper on_error)
json_table(data, $path returning on_error COLUMNS columnlist)
json_value(data, $path returning on_error)

data:  json varchar2, clob or a blob; blobs need to add “FORMAT JSON”

$path: json search path

on_error: action when return error happens;
ERROR|NULL|EMPTY|DEFAULT literal|TRUE|FALSE ON ERROR

ERROR, NULL for all functions
TRUE|FALSE   for json_exists
EMPTY             for json_query
DEFAULT          for json_table, json_value

returning :return data type and formatting; PRETTY ASCII

query_wrapper  : WITH|WITHOUT ARRAY WRAPPER

columnlist  : json column list

EXISTS PATH             like json_exists
PATH                            like json_query or json_value
NESTED PATH           single row unnesting
FOR ORDINALITY    row numbers

Understanding JSON path expressions

All functions and conditions can use a json path expression.

A JSON_PATH expression uses dot-notation to separate steps. Each step an be an object or an array step.

Syntax $path (object or array)

$
[ { . { * | simplename | ”complexname” } }
   | 
   { [ { integer [TO integer]     
         [, integer [TO integer]
         ]…
         } 
         | *
      ]
   }
] …

Some examples for json path expressions

$.ShippingInstructions.Address.zipCode
$.a.b.c
$.a[3].b[*].c[0]

The json_table function will map columns based upon a json_path expression.
The columns can be defined using an expression resembling the JSON_VALUE, JSON_QUERY or JSON_EXISTS syntax.

 

Examples

Example 1

select *
from JSON_TABLE('{"data":{"a":[1,2,3],"b":[0,1,2],"c":"nur ein test"}}'
               ,'$.data'
       columns ("A_json" varchar2(100) format json path '$.a'
               ,"B_check" varchar2(10) EXISTS path '$.b'
               ,"C_text" varchar2(100) path '$.c'
               )) tab
;
A_JSON B_CHECK C_TEXT
[1,2,3] true nur ein test

Column A uses a json_query syntax. The “format json” directive allows to return a more complex json object.

Column B uses a json_exists syntax. If the column is declared as varchar2 then true/false is return. If it is declared as number then 1/0 is returned.

Column C uses the default json_value syntax.

Example 2

select *
from json_table('{a:[5, 10, 15]}', '$'
columns (val1 varchar2(20) format json path '$.a');
VAL1
[5,10,15]

 

select *
from json_table('{a:[5, 10, 15]}', '$'
      columns (nested path '$.a[*]' columns (val2 number path '$'))
);
VAL2
5
10
15

 

And now the combination of both columns in one query.

select *
from json_table('{a:[5, 10, 15]}', '$'
      columns (val1 varchar2(20) format json path '$.a'
              ,nested path '$.a[*]' columns (val2 number path '$'))
);

 

VAL1 VAL2
5
10
15
15

 

This result is a bug!

There is a patch available from oracle metalink, that corrects this.

patch 20080249: “JSON Patch Bundle 1”. MOS Doc 1992767.1

It is strongly recommended to install this patch when planning to work with JSON data in the database.

An error happens, but the json function does return an empty string and do not raise the error.

We can get some more info by adding the “ERROR ON ERROR” clause.

select *
from json_table('{a:[5, 10, 15]}', '$'
error on error
columns (val1 varchar2(20) format json path '$.a' ,
         nested path '$.a[*]' columns (val2 number path '$')));

ORA-40480: result cannot be returned without array wrapper

Now that we see the error message, we can try to react on it by adding “WITH ARRAY WRAPPER”. for column val1

select *
from json_table('{a:[5, 10, 15]}', '$'
error on error
columns (val1 varchar2(20) format json WITH ARRAY WRAPPER path '$.a' ,
         nested path '$.a[*]' columns (val2 number path '$')));
VAL1 VAL2
[[5] 5
,[10] 10
,[15] 15
,[]] 15

 
This result is still wrong. But at least we got some indication about what happend to our data. To solve the issue we need to install the patch or do a different kind of query.

Fazit

Using json functions in the database is easy. But just as with any other new technology we need a little time to get used to it. Also expect a few minor bugs at the beginning.

 

Other reads