Several 11g oddities

In an earlier post about setting up journaling using flashback data archives I mentioned the risk of losing undo information when adding a constraint to the table. During the preparation for the session Oracle 11g for developers I discovered four oddities:


1) Result Cache:

In the concepts manual, there is a picture of the Result Cache in the SGA. The drawing tells us that the Result Cache is a separate memory area, just like you have the Shared Pool, Large Pool or Buffer Cache. However when executing the procedure dbms_result_cache.memory_report, the output shows this:

rwijk@ORA11G> exec dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1056K bytes (1056 blocks)
Maximum Result Size = 52K bytes (52 blocks)
[Memory]
Total Memory = 5140 bytes [0.003% of the Shared Pool]

... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

Which makes you believe that the Result Cache is part of the Shared Pool. So which one of the two is true? The important part for me to know is that it is in shared memory, but still the difference is strange.


2) Reference partitioning

Suppose the following situation: three tables in a master-detail-detail relationship. The master is range partitioned, the child table is reference partitioned, and the grandchild table is also reference partitioned. When trying to insert a row in the grandchild table that violates the foreign key you'd expect the rather well known error message ORA-02291, integrity constraint violated. The error message you receive however is this one:

ORA-14400: De ingevoerde partitiesleutel wordt niet toegewezen aan een partitie.

(translation: inserted partition key does not map to any partition)


3) Fine Grained Dependency Tracking

This feature reduces the odds that your packages become invalid, by storing the dependencies at the column level instead of table level. It works - I've seen it - but I cannot see the column dependencies anywhere. The obvious place, USER_DEPENDENCIES, does not show any column level information. The documentation does not seem to mention an alternative place where this information is stored. Hopefully I overlooked something, and I would appreciate it if someone would tell me where to find it. And I don't mean some X$-table, but in the regular USER_... views.


4) Native compilation

This one was staring at my face when I developed the demonstration script, but I missed it. Only after I was being notified by a colleague watching the session, I saw it too. When compiling a procedure natively, the PLSQL_OPTIMIZE_LEVEL has to be at least 2 according to the documentation. So in my script I set it to 1, and show that a warning is generated and the procedure is NOT natively compiled. But when querying the USER_PLSQL_OBJECT_SETTINGS view, it says that it is natively compiled:

rwijk@ORA11G> alter session set plsql_optimize_level = 1
  2  /

Sessie is gewijzigd.

rwijk@ORA11G> alter session set plsql_code_type = native
  2  /

Sessie is gewijzigd.

rwijk@ORA11G> create procedure p2
  2  is
  3  begin
  4    for i in 1..100000000
  5    loop
  6      null;
  7    end loop
  8    ;
  9    dbms_output.put_line('p2');
 10  end;
 11  /

SP2-0804: Procedure aangemaakt met compilatiewaarschuwingen

rwijk@ORA11G> show err
Fouten voor PROCEDURE P2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLW-06014: Het genereren van native code is uitgeschakeld omdat
         PLSQL_OPTIMIZE_LEVEL <= 1.

rwijk@ORA11G> select name
  2       , type
  3       , plsql_code_type
  4       , plsql_optimize_level
  5       , plsql_warnings
  6    from user_plsql_object_settings
  7   where name like 'P_'
  8   order by name
  9  /

NAME                           TYPE         PLSQL_CODE_TYPE                PLSQL_OPTIMIZE_LEVEL PLSQL_WARNINGS
------------------------------ ------------ ------------------------------ -------------------- ------------------------------
P1                             PROCEDURE    INTERPRETED                                       2 ENABLE:ALL
P2                             PROCEDURE    NATIVE                                            1 ENABLE:ALL
P3                             PROCEDURE    NATIVE                                            2 ENABLE:ALL