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”.
Hi Jonathan,
Do you have example of code where slowdown function didn’t work ?
I did some simple tests during weekend – http://oracleprof.blogspot.ie/2013/06/plsql-function-in-sql-short-example.html –
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.
regards,
Marcin
Comment by Marcin Przepiorowski — July 1, 2013 @ 8:02 am BST Jul 1,2013 |
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 ( 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.
Comment by Jonathan Lewis — July 1, 2013 @ 4:17 pm BST Jul 1,2013 |
[…] 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 |
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 |
[…] 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 |
[…] 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 |