Friday, December 14, 2007

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
Block Size = 1K bytes
Maximum Cache Size = 1056K bytes (1056 blocks)
Maximum Result Size = 52K bytes (52 blocks)
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:

-------- -----------------------------------------------------------------
0/0 PLW-06014: Het genereren van native code is uitgeschakeld omdat

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 /

------------------------------ ------------ ------------------------------ -------------------- ------------------------------


  1. As for the problem mentioned in (2)
    regarding the reference-partitioned tables, I would like to thank you for pointing it out and want you to know that the problem has been fixed and the fix should show up in an upcoming release.

  2. Eugene, thanks for letting me know.
    I'm glad to hear it has been picked up and fixed.