Back from Tallinn, Estonia

This morning I arrived back from a trip to Tallinn. Oracle Estonia had given me the opportunity to present my SQL Masterclass seminar at their training center in Tallinn, on Thursday and Friday. Thank you all those who spent two days hearing me. Here is a short story about my trip including some photos.

I arrived at Tallinn airport around 1PM local time on Wednesday. My hotel room was located at the 18th floor and I had this splendid view on the old city of Tallinn:



I spent the rest of the afternoon taking a city tour by bus, learning a few things about the history of Tallinn and strolling for an hour or two through the old city. Here are a few of the photos that I took during that stroll:












The seminar itself was on Thursday and Friday. I had spent a huge amount of time preparing the seminar. One of my goals was to present as many recognizable situations as possible that the attendees can apply at their daily job, so I was very pleased to receive some feedback that they indeed found several immediately applicable queries. There is still room for improvement though as a few parts were too short (I had planned one-and-a-half hour per part) and I had an error or two in my scripts that I had overlooked. Hopefully I will get a chance to fix those and present my seminar again somewhere. The feeling at the end was very positive though. The powerpoint and the scripts can be found at the "Presentations and papers" page.

This is a picture of the building where Oracle Estonia and its training center are located:



PS: As expected, during the preparation of the seminar I learnt a couple of new things. A bug with partitioned outer joins and something about connect-by-filtering. I will blog about them soon.

Instrumentation packages

At CIBER we are doing a series of knowledge sessions where several colleagues are doing a one-hour presentation about one or two package of their choice. We have already heard several very interesting presentations about DBMS_HPROF, DBMS_PARALLEL_EXECUTE, UTL_DBWS, UTL_TCP and DBMS_FGA. And this evening a colleague will do a presentation about DBMS_PREPROCESSOR and DBMS_WARNING, and I will do one about instrumentation related packages LOGGER and DBMS_APPLICATION_INFO. I have added my presentation and the scripts to my Presentations and Papers page, and for convenience here as well:

Keynote presentation instrumentation packages

Powerpoint presentation instrumentation packages

Demonstration scripts instrumentation packages

Warning: they are in Dutch.

EDIT: I just received a message that the sessions will be rescheduled to a later date because of a serious number of cancellations. Because the preparation is finished, I have decided to publish them anyway.

OGh APEX day 2011

Yesterday I attended OGh's APEX day. Previous year was already pretty successful with 150 people attending. This year the maximum of 150 people was reached in only a few days, so we scaled up the event to a maximum of 250 people, and in the end we still had to disappoint several people. And this year we had several sponsors as well: Oracle, Ciber, Transfer Solutions and Itium. These must be signs that APEX is becoming increasingly popular here in the Netherlands.

The day started with three plenary sessions. The first one was by Tony Andrews and Nigel Blair who told us about their huge APEX project for Northgate Revenues and Benefits. They sell software to local counsils and their application contained approximately 1500 Forms. They had moved from character mode, to Oracle Forms to Oracle Webforms with their application. Their users complained about Webforms of which the user interface deviated too much from their normal internet experience. The sales win rate dropped from 1 out of 2 to 1 out of 4 or less. With their new APEX application they focussed on the user interface again and are now back at winning 50 to 60% of the deals. It was also good to see how Tony solved several technical problems of which some are now part of APEX 4. This session was a great show case that APEX is certainly very much capable for large, enterprise level applications.

The next session was Hilary Farrell's session about APEX 4.1. Hilary is part of the APEX development team. It always amazes me how much great work this team gets done with such a relatively small team. The last years I think I've seen almost every team member present somewhere: all highly competent and passionate people. And Hilary is no exception. Lots of small improvements are on their way in APEX 4.1, of which I think I'm going to like the improved error handling the most. You can read more about the new version in this statement of direction.

Next up were Dimitri Gielis and John Scott with "APEX Evangelists way of working in APEX Projects". They showed how they handled several aspects of a new APEX project. From Ant scripts to generate a fresh projectstructure in Subversion. Continuous integration, their "Glitchi" project management APEX-tool and Balsamiq, with which they design the user interfaces together with the client.

In the afternoon I saw Jasper Alblas' session demonstrating Eneco's new debtor application. I have worked for more than six years at Eneco, so I was eager to see this session. Very informative session and a nice looking application. Well done, Jasper!

The second afternoon session I attended was John Scott's "Building APEX 4 Plugins". I always love John's presentations. He always presents with humour and makes seemingly complex subjects appear simple so you'll end up thinking "hey, I can do that myself without too much ado". I especially liked his Google Translate demos.

The last session I attended was Hans Wijnacker's Golfscores.eu session. Again a well delivered session about an APEX-application that keeps track of golfscores at tournaments. If you are organizing a golf tournament, you can hire them to take care of the IT. I saw Hans faced some similar challenges that I've faced with my own APEX application for Tourpoules (currently offline by the way, waiting for 11g XE to be released ...).

I had a great time and I want to thank OGh and especially Natalie Rohlof, who organized the event, for the fantastic day. I hope Learco and me will get the chance to setup a program for a third edition in 2012.

Open cursor paranoia

Most PL/SQL developers will likely have witnessed this phenomenon several times during their career. But only in other people's code, of course :-). I'm talking about PL/SQL code where every program unit ends like this:

exception
when others then
  if c%isopen
  then
    close c;
  end if;
  raise;
end;

where lines 3 to 6 are repeated for every cursor in the block above.

Proponents of open cursor paranoia justify it as defensive programming. Even when you open-fetch-close a cursor, the fetch could raise an exception and the close statement would not execute, leaving the cursor open, they say. A next execution would raise the dreaded "ORA-06511: PL/SQL: cursor already open".

Let's examine this claim. First by defining about which type of cursor we're talking about, since the term "cursor" is an overloaded one. Here I am talking about explicit session cursors in PL/SQL. The ones you declare and manage yourself. Now suppose you have this package:

SQL> create package pkg
  2  as
  3    procedure p;
  4  end pkg;
  5  /

Package created.

SQL> create package body pkg
  2  as
  3    procedure p
  4    is
  5      cursor c is select dummy from dual;
  6    begin
  7      open c;
  8    end p;
  9  end pkg;
 10  /

Package body created.

What will the next piece of code do? Will it raise an ORA-06511: cursor already open?

SQL> begin
  2    pkg.p;
  3    pkg.p;
  4  end;
  5  /

No it doesn't:

PL/SQL procedure successfully completed.

An explicit cursor variable has a scope, just like every other variable. Its scope here is the procedure. When the procedure ends, the cursor variable is gone and you can safely do an "open c" again. Note that PL/SQL has a PL/SQL cursor cache which keeps the cursor silently open in the background, so that a reopen of this cursor will not cause a reparse, not even a soft parse:

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL'
  4  /

CURSOR_TYPE
----------------------------------------------------------------
PL/SQL CURSOR CACHED

1 row selected.

By the way, in 11g, Oracle has added 3 extra columns to the V$OPEN_CURSOR view. The new column CURSOR_TYPE is very informative as you can see above.

The cursor type is PL/SQL CURSOR CACHED, because the SESSION_CACHED_CURSORS parameter is on its default of 50 and the cursor was closed in the PL/SQL code. When you switch this parameter off by setting it to 0, the entry disappears from V$OPEN_CURSOR, and the cursor is closed in the background as well:

SQL> alter session set session_cached_cursors = 0
  2  /

Session altered.

SQL> begin
  2    pkg.p;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL'
  4  /

no rows selected

SQL> alter session set session_cached_cursors = 50
  2  /

Session altered.

Is there really no way that closing open cursors in the exception handler is useful? Maybe it once made sense in an earlier version? If you know this was the case once, please leave a comment. And it could be useful when the scope of the cursor is bigger than just the procedure, for example when it's the declared globally in the package. In this case the package state holds the state of the cursor variable, so with this package:

SQL> create or replace package body pkg
  2  as
  3    cursor c is select dummy from dual
  4    ;
  5    procedure p
  6    is
  7    begin
  8      open c;
  9    end p;
 10  end pkg;
 11  /

Package body created.

, you do get the ORA-06511:

SQL> begin
  2    pkg.p;
  3    pkg.p;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "RWIJK.PKG", line 3
ORA-06512: at "RWIJK.PKG", line 8
ORA-06512: at line 3

And the cursor_type attribute of v$open_cursor now says:

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL '
  4  /

CURSOR_TYPE
----------------------------------------------------------------
OPEN-PL/SQL

1 row selected.

But this raises the question why you would ever want to have a globally defined cursor. Recently I've seen more than one application with so called cursor packages. Packages containing only cursors in their specification, as helper packages for the other non cursor packages in the application. A truly bad idea, not only for not hiding the implementation details from the caller, but also because once the package is called, the package has a state and the cursor variables don't get closed automatically, making the application vulnerable for ORA-06511's. Although, I can imagine a cursor package in a situation where a mid-tier application wants to handle record sets without having to deal with the SQL. I don't have experience with this scenario myself though.

Odds are that when you see "if c%isopen then close c; end if;" constructs being used abundantly throughout the code, most of them can happily be removed.

Runtime error ORA-01031: insufficient privileges

After a new version of software was installed in production, the end users reported a runtime error: ORA-01031: insufficient privileges, when selecting from a view. The developers of the code were investigating the problem and half way through, they asked me to have a look at the problem. I saw a function from schema3, which was used in a view in schema2, which was used by schema1. I had just finished learning their situation, when they had tried granting an EXECUTE-privilege on the function with grant option, which solved the error. The privilege with grant option was quickly granted in production and everything went back to normal and all interest in the problem vanished. But I was puzzled: how could that missing grant option lead to the error at runtime. It should not have been possible to grant the SELECT-privilege to schema1 at compile/install time, or at least I thought so. So I did a little investigation.

SQL> create user rwijk3 identified by rwijk3
  2  /

User created.

SQL> grant create session, create procedure to rwijk3
  2  /

Grant succeeded.

SQL> create user rwijk2 identified by rwijk2
  2  /

User created.

SQL> grant create session, create view to rwijk2
  2  /

Grant succeeded.

SQL> create user rwijk1 identified by rwijk1
  2  /

User created.

SQL> grant create session to rwijk1
  2  /

Grant succeeded.

SQL> conn rwijk3/rwijk3@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> create function f return number
  2  is
  3  begin
  4    return 42;
  5  end f;
  6  /

Function created.

SQL> grant execute on f to rwijk2
  2  /

Grant succeeded.

SQL> conn rwijk2/rwijk2@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> create view v
  2  as
  3  select rwijk3.f() x
  4    from dual
  5  /

View created.

SQL> grant select on v to rwijk1
  2  /
grant select on v to rwijk1
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'RWIJK3.F'

This is the scenario I expected. Granting the select privilege leads to an error message at compile/install time. But our scenario was a little different than this clean install: the view already existed without the function call and the privilege without the grant option was already granted. Like this:

SQL> show user
USER is "RWIJK2"
SQL> drop view v
  2  /

View dropped.

SQL> create view v
  2  as
  3  select 42 x
  4    from dual
  5  /

View created.

SQL> grant select on v to rwijk1
  2  /

Grant succeeded.

And user rwijk1 could happily query the view of course:

SQL> conn rwijk1/rwijk1@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> select x
  2    from rwijk2.v
  3  /

         X
----------
        42

1 row selected.

The new version introduced the function call:

SQL> create or replace view v
  2  as
  3  select rwijk3.f() x
  4    from dual
  5  /

View created.

Which now succeeds, even though select privileges on this view were already granted to schema1. Oracle didn't raise an ORA-01720, like it did in the fresh install scenario. So Oracle does not perform a check whether all already granted privileges remain effective. It also doesn't remove the privileges as user RWIJK1 can describe the view because of the SELECT privilege without the grant option:

SQL> conn rwijk1/rwijk1@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> desc rwijk2.v
 Name                                    Null?    Type
 --------------------------------------- -------- ---------------------------
 X                                                NUMBER

It just issues a runtime error message when you issue a select against the view:

SQL> select x
  2    from rwijk2.v
  3  /
  from rwijk2.v
              *
ERROR at line 2:
ORA-01031: insufficient privileges

An ORA-01031 during the parse phase, which you can also see in the trace file:

PARSE ERROR #47952083822528:len=25 dep=0 uid=114 oct=3 lid=114 tim=1299371043385365 err=1031
select x
  from rwijk2.v

When doing a "CREATE OR REPLACE VIEW", and the view is used inside a program unit, like a package, procedure or function, these dependent program units are marked invalid so they need to be compiled again against the new view definition.

When doing a "CREATE OR REPLACE VIEW", and the view has instead of triggers defined against the view, the triggers are removed when the view definition is replaced. And you need to install the instead of triggers again.

And when doing a "CREATE OR REPLACE VIEW", and privileges on this view were granted before replacing the view definition, these privileges are not re-evaluated when the view definition is replaced.

Questions about the result cache

Last year I have presented a couple of times about the result cache. You can download this presentation from my new Presentations and papers tabpage, by the way. After each of those sessions I received several good questions. To some questions I didn't know the answer, and for some other questions I did, but I hadn't tested it so I wasn't absolutely sure. I promised to address those questions with a separate blogpost, so this post is long overdue, but I finally took some time to investigate them all and here are the results on version 11.2.0.2.

Question 1: If my result cached function is valid, and I recompile the valid function, will my cached results still become invalid?

SQL> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> create function f (p_factor in number) return number result_cache
  2  is
  3  begin
  4    return 10 * p_factor;
  5  end;
  6  /

Function created.

SQL> select f(2) from dual
  2  /

      F(2)
----------
        20

1 row selected.

SQL> select id
  2       , type
  3       , status
  4       , name
  5       , namespace
  6    from v$result_cache_objects
  7  /

        ID TYPE       STATUS    NAME                                     NAMES
---------- ---------- --------- ---------------------------------------- -----
         0 Dependency Published RWIJK.F
         1 Result     Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1   PLSQL

2 rows selected.

SQL> alter function f compile
  2  /

Function altered.

SQL> select id
  2       , type
  3       , status
  4       , name
  5       , namespace
  6    from v$result_cache_objects
  7  /

        ID TYPE       STATUS    NAME                                     NAMES
---------- ---------- --------- ---------------------------------------- -----
         0 Dependency Published RWIJK.F
         1 Result     Invalid   "RWIJK"."F"::8."F"#fac892c7867b54c6 #1   PLSQL

2 rows selected.

So the answer is yes. The results always become invalid when compiling a function.


Question 2: What happens when you execute a SQL query -of which the results are cached- that also has a result cached function in its select list?

SQL> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select /*+ result_cache */
  2         deptno
  3       , f(deptno)
  4    from dept
  5  /

    DEPTNO  F(DEPTNO)
---------- ----------
        10        100
        20        200
        30        300
        40        400

4 rows selected.

SQL> select id
  2       , type
  3       , status
  4       , name
  5       , namespace
  6    from v$result_cache_objects
  7  /

        ID TYPE       STATUS    NAME                                     NAMES
---------- ---------- --------- ---------------------------------------- -----
         1 Dependency Published RWIJK.F
         0 Dependency Published RWIJK.DEPT
         6 Result     Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1   PLSQL
         5 Result     Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1   PLSQL
         4 Result     Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1   PLSQL
         3 Result     Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1   PLSQL
         2 Result     Published select /*+ result_cache */               SQL
                                       deptno
                                     , f(deptno)
                                  from dept


7 rows selected.

So, in that case both the SQL query as well as the PL/SQL function will cache its results.


Question 3: If my table is partitioned and I query on only one partition and cache the results, will a DML statement that affects another partition invalidate my result set?

SQL> create table my_partitioned_emp
  2  ( empno  number(4) primary key
  3  , deptno number(2) not null
  4  , sal    number(4) not null
  5  )
  6  partition by range(deptno)
  7  ( partition p1 values less than (15)
  8  , partition p2 values less than (25)
  9  , partition p3 values less than (35)
 10  , partition p4 values less than (maxvalue)
 11  )
 12  /

Table created.

SQL> insert into my_partitioned_emp
  2  select empno
  3       , deptno
  4       , sal
  5    from emp
  6  /

14 rows created.

SQL> begin
  2    dbms_stats.gather_table_stats
  3    ( ownname  => user
  4    , tabname  => 'my_partitioned_emp'
  5    , partname => null
  6    , cascade  => true
  7    );
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select /*+ result_cache */
  2         sum(sal)
  3    from my_partitioned_emp
  4   where deptno = 10
  5  /

  SUM(SAL)
----------
      8750

1 row selected.

SQL> insert into my_partitioned_emp
  2  values (7777, 40, 1000)
  3  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select id
  2       , type
  3       , status
  4       , name
  5       , namespace
  6    from v$result_cache_objects
  7  /

        ID TYPE       STATUS    NAME                                     NAMES
---------- ---------- --------- ---------------------------------------- -----
         0 Dependency Published RWIJK.MY_PARTITIONED_EMP
         1 Result     Invalid   select /*+ result_cache */               SQL
                                       sum(sal)
                                  from my_partitioned_emp
                                 where deptno = 10


2 rows selected.

So yes, the results will be invalidated, even if the DML is on another partition. The granularity is at table level. I'm not sure whether finer grained dependencies for the result cache would be welcome, because it could imply a significant overhead to manage.


Question 4: Does the result cache work with analytic functions?

SQL> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select /*+ result_cache */
  2         empno
  3       , sal
  4       , sum(sal) over (partition by deptno)
  5    from emp
  6  /

     EMPNO        SAL SUM(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- -------------------------------
      7782       2450                            8750
      7839       5000                            8750
      7934       1300                            8750
      7566       2975                           10875
      7902       3000                           10875
      7876       1100                           10875
      7369        800                           10875
      7788       3000                           10875
      7521       1250                            9400
      7844       1500                            9400
      7499       1600                            9400
      7900        950                            9400
      7698       2850                            9400
      7654       1250                            9400

14 rows selected.

SQL> /

     EMPNO        SAL SUM(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- -------------------------------
      7782       2450                            8750
      7839       5000                            8750
      7934       1300                            8750
      7566       2975                           10875
      7902       3000                           10875
      7876       1100                           10875
      7369        800                           10875
      7788       3000                           10875
      7521       1250                            9400
      7844       1500                            9400
      7499       1600                            9400
      7900        950                            9400
      7698       2850                            9400
      7654       1250                            9400

14 rows selected.

SQL> select *
  2    from v$result_cache_statistics
  3  /

        ID NAME                                     VALUE
---------- ---------------------------------------- --------------------
         1 Block Size (Bytes)                       1024
         2 Block Count Maximum                      5120
         3 Block Count Current                      32
         4 Result Size Maximum (Blocks)             256
         5 Create Count Success                     1
         6 Create Count Failure                     0
         7 Find Count                               1
         8 Invalidation Count                       0
         9 Delete Count Invalid                     0
        10 Delete Count Valid                       0
        11 Hash Chain Length                        1
        12 Find Copy Count                          1

12 rows selected.

One create and one find, so it does work. I could not think of a reason why it wouldn't work, but it doesn't hurt to check.


Question 5: Does fine grained auditing work correctly in combination with the result cache?

The idea behind this question is that with the result cache, your query will skip a lot of work. Will a query that gets its results via the result cache, also skip the auditing part?

SQL> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> begin
  2    dbms_fga.add_policy
  3    ( object_schema   => user
  4    , object_name     => 'emp'
  5    , policy_name     => 'sal_policy'
  6    , audit_condition => 'sal <= 1000'
  7    , audit_column    => 'sal'
  8    );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select sal
  2    from emp
  3   where sal = 800
  4  /

       SAL
----------
       800

1 row selected.

SQL> /

       SAL
----------
       800

1 row selected.

SQL> select /*+ result_cache */
  2         sal
  3    from emp
  4   where sal <= 800
  5  /

       SAL
----------
       800

1 row selected.

SQL> /

       SAL
----------
       800

1 row selected.

SQL> select policy_name
  2       , scn
  3       , sql_text
  4    from dba_fga_audit_trail
  5   order by scn
  6  /

POLICY_NAME                           SCN
------------------------------ ----------
SQL_TEXT
--------------------------------------------------------------------------------
SAL_POLICY                        1382762
select sal
  from emp
 where sal = 800

SAL_POLICY                        1382764
select sal
  from emp
 where sal = 800

SAL_POLICY                        1382766
select /*+ result_cache */
       sal
  from emp
 where sal <= 800

SAL_POLICY                        1382768
select /*+ result_cache */
       sal
  from emp
 where sal <= 800


4 rows selected.

Both queries with the result_cache hint are there. So even though the second query got its results from the result cache, the policy recorded the query. And so the answer to this question is "Yes". This is not surprising given the execution plan:

SQL> alter session set statistics_level = all
  2  /

Session altered.

SQL> set serveroutput off
SQL> select /*+ result_cache */
  2         sal
  3    from emp
  4   where sal <= 800
  5  /

       SAL
----------
       800

1 row selected.

SQL> /

       SAL
----------
       800

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fdfbdwgq5vqnn, child number 0
-------------------------------------
select /*+ result_cache */        sal   from emp  where sal <= 800

Plan hash value: 3956160932

---------------------------------------------------------------------------
| Id  | Operation          | Name                       | Starts | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |      1 |      1 |
|   1 |  RESULT CACHE      | fxpb18zb9vn8w87wgw43w8cbyr |      1 |      1 |
|*  2 |   TABLE ACCESS FULL| EMP                        |      0 |      0 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SAL"<=800)

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -


24 rows selected.

Note that I edited the E-rows and A-time columns out of the formatted plan for nicer display.

You can see that the result cache at line 1 prevents the full scan of EMP from executing, but not line 0 -the SELECT-, where the auditing probably takes place.

SQL Masterclass in Tallinn, Estonia

On June 9 and 10, I'll be doing a SQL Masterclass seminar for Oracle University in Tallinn, Estonia.

You can find the details here.