12c Funny

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