Oracle Scratchpad

June 30, 2013

12c Funny

Filed under: 12c,Oracle,Troubleshooting — Jonathan Lewis @ 7:10 pm BST Jun 30,2013

Here’s a quirky little thing I discovered about 5 minutes after installing the 12c Beta 1 (cut-n-pasted from SQL*Plus):

create or replace view v$my_stats
        v$mystat        ms,
        v$statname      sn
        sn.statistic# = ms.statistic#
 14  ;
create or replace view v$my_stats
ERROR at line 1:
ORA-00999: invalid view name

You can’t create views with names that start with v$ or gv$ in the sys schema. Presumably to eliminate the risk of someone’s clever view definition from overwriting and disabling one of the distributed dynamic performance views by accident.

Fortunately even the sys account allows you to create synonyms that start with v$ and gv$ – and the last lines of all my “cunning snapshot” scripts already create synonyms for my views, so I didn’t have to do a lot of work to upgrade my scripts.

Inline (“with”) functions

Having fixed up the views for my snapshot code, I then realised that there’s a new feature of 12c that might make some of my snapshot packages redundant – in line (or “with”) pl/sql functions. I’ve described the use of “slowdown” functions in the past, but with 12c I don’t need to create a separate row_wait function – I can define it on the fly, and use it inline.

Here’s an example, using the v$event_histogram view:

rem     Program:        12c_with_function.sql
rem     Dated:          June 2013
rem     Author:         J.P.Lewis

column wait_time_milli  format 999,999,999
column wait_count       format 999,999,999
column event            format a42

define m_event = 'db file sequential read'

        function wait_row (
                i_secs          number,
                i_return        number
        ) return number
                return i_return;
        event, wait_time_milli, sum(wait_count) wait_count
from    (
                event, wait_time_milli, -1 * wait_count wait_count
        from    v$event_histogram
        where   event = '&m_event'
        union all
                null, wait_row(10, -1), null
        from    dual
        union all
                event, wait_time_milli, wait_count
        from    v$event_histogram
        where   event = '&m_event'
        wait_time_milli != -1
group by
        event, wait_time_milli
order by
        event, wait_time_milli

EVENT                                      WAIT_TIME_MILLI   WAIT_COUNT
------------------------------------------ --------------- ------------
db file sequential read                                  1          411
db file sequential read                                  2           11
db file sequential read                                  4            4
db file sequential read                                  8           33
db file sequential read                                 16           37
db file sequential read                                 32           10
db file sequential read                                 64            5
db file sequential read                                128            0
db file sequential read                                256            0
db file sequential read                                512            0
db file sequential read                              1,024            0

You’ll notice that I’ve used the “/” to run the SQL statement; things get a little confusing with SQL*Plus and the oddity of having semi-colons inside the SQL statement (see footnote 1) when you start to use in-line functions – so to avoid confusion you might want to get into the habit of doing a “set sqlterminator off” at the start of each script and then using the “/” after each SQL statement to execute it.

Inevitably there are reasons why this “union all” coding strategy won’t work everywhere you might hope – even in 11g you could get fooled by the appearance of deterministic functions; but now in 12c you could also get fooled in some cases if you try running parallel queries when all the branches of a union all can be made to run concurrently (see the /*+ PQ_CONCURRENT_UNION(@qb_name) */ hint (see footnote 2).


Footnote 1: Technically the “‘;” is supposed to be a seperator, it’s a historical quirk that Oracle Corp. also allowed it’s appearance at the end of an SQL statement to be interpreted as an instruction to execute the statement.

Footnote 2: The pq_conncurrent_union()  hint is currently subject to bug no. 15851422: the hint has to address the query block which, for a “top-level” union all, will probably be “set$1”.



  1. Hi Jonathan,

    Do you have example of code where slowdown function didn’t work ?
    I did some simple tests during weekend –
    and inline PL/SQL with sleep inside works OK for me.

    I had a problem with arraysize set to 1 – it works for 1st row only, all subsequent rows are return in pairs when SQL is used.


    Comment by Marcin Przepiorowski — July 1, 2013 @ 8:02 am BST Jul 1,2013 | Reply

  2. Marcin,

    I don’t know what happened to my edits, but a great chunk of the post disappeared at some point – luckily it was available in an autosave, so I think I’ve got everything back.
    I’m not expecting the slowdown function itself to fail – but I am cautious about Oracle “over-optimising” if you use it more than once in a single query, and doing some sort of function-call caching. The type of example I had in mind was the sort of thing I did to emulate v$session_wait_history ( ) some years ago.

    I haven’t yet created an example where determinism causes a problem – in fact when I created the equivalent of my session wait history view in 12c it looked as if I couldn’t make the inline view deterministic anyway (at least, not across the branches of the union all).

    Your oddity with SQL*Plus may be related to the way that it doesn’t allow you to set arraysize to 1 – or rather, it lets you set it, but then ignores your requirement and runs with an arraysize of 2. If you enable SQL trace you can see that there’s a first fetch of 1 row and every subsequent fetch gets two rows at a time.

    Comment by Jonathan Lewis — July 1, 2013 @ 4:17 pm BST Jul 1,2013 | Reply

  3. […] a comment from Marcin Przepiorowski on my last post, it crossed my mind to check whether “with” functions can be […]

    Pingback by 12c Determinism | Oracle Scratchpad — July 1, 2013 @ 4:55 pm BST Jul 1,2013 | Reply

  4. For more information, take a look at Tim Hall’s article – which also mentions the 12c “pragma udf” that you can apply to a “normal” function – resulting in performance that appears to be quicker than inline functions ! (And which may also give you the benefit of allowing you to define the function as deterministic.)

    Comment by Jonathan Lewis — July 3, 2013 @ 11:52 am BST Jul 3,2013 | Reply

  5. […] Here’s the modelling query, with a little infrastructure to examine the workload. Note that this will only run on 12c because of the inline PL/SQL function I’ve used for slowing the query down. […]

    Pingback by Result Cache 2 | Oracle Scratchpad — September 28, 2015 @ 8:50 am BST Sep 28,2015 | Reply

  6. […] simple SQL statements to takes deltas of dynamic performance views. The example I supplied was for v$event_histogram but I’ve just been prompted by a question on ODC to supply a couple more – […]

    Pingback by 12c Snapshots | Oracle Scratchpad — March 6, 2019 @ 10:35 am GMT Mar 6,2019 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: