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.

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

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 ( https://jonathanlewis.wordpress.com/2007/05/16/vsession_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.

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