Oracle Scratchpad

June 30, 2013

12c Funny

Filed under: 12c,Oracle,Troubleshooting — Jonathan Lewis @ 7:10 pm BST Jun 30,2013

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

4 Comments »

  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 | Reply

  2. 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 ( http://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 | Reply

  3. […] 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 | Reply

  4. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,084 other followers