I decided to hop over to DBA Zine to read Jonathans paper as anything that smells like hidden info, undocumented info or similar interests me, especially if Jonathan has written it as he is an excellent author.
Jonathans paper is called "Hidden Benefits of 10g" and discusses some of the lesser known facts about Oracle 10g Release 1. Jonathan starts off with an interesting view point that most people tend to decide to upgrade based on the high profile press details of the features that may be useful. In this paper Jonathan has decided to discuss some of the less well known features but features that are also very useful and should help customers of Oracle decide whether to upgrade or not.
Jonathan talks about optimizer improvements, hash joins and outer join optimizations. He then looks at improvements in index range scans to remove redundant reads of table blocks. Jonathan then talks about the fact that Oracle has now in 10g finally added a proper optimizing compiler for PL/SQL. he then looks at a simple example of single row processing and shows how in 10g R1 it is promoted to array processes and why it could be an issue. He also talks about the fact the optimizer tries much harder to standardise the SQL by massaging the SQL text by upper casing it and removing white space.
One little snippet of interest to me was the note about the optimization level of the compiler that can be changed with an ALTER SESSION command as follows:
alter session set plsql_optimize_level = 1;
This paper reminded me of a paper I read some time back when i was interested in the inner workings of PL/SQL. The paper called "PL/SQL just got faster" is superb and well worth reading for those interested in real technical details. This paper describes the inner workings of PL/SQL, the Virtual machine, P-Code or M-Code and the Diana. It describes in some details the changes to the compiler and also to the native compilation facilities in Oracle. This paper gives more background to Jonathans as it talks about the new optimizing compiler and how it works. This paper is well worth reading for those interested in detail and internals. The paper can be found at the link above as a download via a zip file. You can access the zip directly from this link.