Here’s a quirky little thing I discovered about 5 minutes after installing 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:
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 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 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 (which 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” )).