12c Funny

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
as
select
        ms.sid,
        sn.statistic#,
        sn.name,
        sn.class,
        ms.value
from
        v$mystat        ms,
        v$statname      sn
where
        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
rem     Program:        12c_with_function.sql
rem     Dated:          June 2013
rem     Author:         J.P.Lewis
rem

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'

with
        function wait_row (
                i_secs          number,
                i_return        number
        ) return number
        is
        begin
                dbms_lock.sleep(i_secs);
                return i_return;
        end;
select
        event, wait_time_milli, sum(wait_count) wait_count
from    (
        select
                event, wait_time_milli, -1 * wait_count wait_count
        from    v$event_histogram
        where   event = '&m_event'
        union all
        select
                null, wait_row(10, -1), null
        from    dual
        union all
        select
                event, wait_time_milli, wait_count
        from    v$event_histogram
        where   event = '&m_event'
        )
where
        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”.