Who came up with the term "DIANA node" and how did they figure 6,000,000 LOC is approximately 67108864 (2**26) DIANA nodes? [duplicate]

Question

In the shared memory pool, a package spec, object type spec, standalone subprogram, or anonymous block is limited to 67108864 (2**26) DIANA nodes which correspond to tokens such as identifiers, keywords, operators, and so on.

What does DIANA stand for? I know the full form. But what is it actually?

I already referred Who is diana, and why won't she let my database objects compile? so don't mark this as duplicate.

I want to know what this concept is, who coined this term etc,.. And if possible how they tell us. "This allows for ~6,000,000 lines of code unless you exceed limits imposed by the PL/SQL compiler", how do they arrive at that value?

Answer 1

As per Oracle documentation,

PL/SQL is based on the programming language Ada. PL/SQL uses a variant of Descriptive Intermediate Attributed Notation for Ada (DIANA), a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA is used internally by compilers and other tools.

At compile time, PL/SQL source code is translated into machine-readable m-code. Both the DIANA and m-code for a procedure or package are stored in the database. At run time, they are loaded into the shared memory pool. The DIANA is used to compile dependent procedures; the m-code is simply executed.

Unfortunately, you cannot estimate the number of DIANA nodes from the parsed size. Two program units with the same parsed size might require 1500 and 2000 DIANA nodes, respectively because, for example, the second unit contains more complex SQL statements.

Ask tom says

More on DIANA node calcualtions, read this book "Ada-Europe '93: 12th Ada-Europe International Conference, "Ada Sans Frontieres", Paris, France, June 14-18, 1993. Proceedings"

The following support note covers this topic well...

Article-ID:         <Note:62603.1>
Folder:             PLSQL
Topic:              General Information Articles
Title:              'PLS-123 Program too Large' - Size Limitations on PLSQL 
                    Packages
Document-Type:      BULLETIN
Impact:             MEDIUM
Skill-Level:        NOVICE
Server-Version:     07 to 08
Updated-Date:       13-JUN-2000 17:41:01
References:         

Overview

This article contains information on PL/SQL package size limitations. When limits are reached, you receive the following error:

PLS-123 Program too large

Size Limitations on PL/SQL Packages

In releases prior to 8.1.3, large programs resulted in the PLS-123 error. This occurred because of genuine limits in the compiler; not as a result of a bug.

When compiling a PL/SQL unit, the compiler builds a parse tree. The maximum size of a PL/SQL unit is determined by the size of the parse tree. A maximum number of diana nodes exists in this tree.

Up to 7.3, you could have 2 * * 14 (16K) diana nodes, and from 8.0 to 8.1.3, 2 * * 15 (32K) diana nodes were allowed. With 8.1.3, this limit has been relaxed so that you can now have 2 * * 26 (i.e., 64M) diana nodes in this tree for package and type bodies.

Source Code Limits

While there is no easy way to translate the limits in terms of lines of source code, it has been our observation that there have been approximately 5 to 10 nodes per line of source code. Prior to 8.1.3, the compiler could cleanly compile up to about 3,000 lines of code.

Starting with 8.1.3, the limit was relaxed for package bodies and type bodies which can now have approximately up to about 6,000,000 lines of code.

Notes: This new limit applies only to package bodies and type bodies. Also, you may now start hitting some other compiler limits before you hit this particular compiler limit.

In terms of source code size, assume that tokens (identifiers, operators, functions, etc.), are on average four characters long. Then, the maximum would be:

   Up to 7.3:         4 * (2 * * 14)=64K
   From 8.0 to 8.1.3: 4 * (2 * * 15)=128K
   With 8.1.3:        4 * (2 * * 25)=256M

This is a rough estimate. If your code has many spaces, long identifiers, etc., you may end up with source code larger than this. You may also end up with source code smaller than this if your sources use very short identifiers, etc.

Note that this is per program unit, so package bodies are most likely to encounter this limit.

How to Check the Current Size of a Package

To check the size of a package, the closest related number you can use is PARSED_SIZE in the data dictionary view USER_OBJECT_SIZE. This value provides the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables and is NOT the size in the shared pool.

The size of the DIANA portion of PL/SQL code (used during compilation) is MUCH bigger in the shared pool than it is in the system table.

For example, you may begin experiencing problems with a 64K limit when the PARSED_SIZE in USER_OBJECT_SIZE is no more than 50K.

For a package, the parsed size or size of the DIANA makes sense only for the whole object, not separately for the specification and body.

If you select parsed_size for a package, you receive separate source and code sizes for the specification and body, but only a meaningful parsed size for the whole object which is output on the line for the package specification. A 0 is output for the parsed_size on the line for the package body.

The following example demonstrates this behaviour:

CREATE OR REPLACE PACKAGE example AS  
  PROCEDURE dummy1;  
END example;  
/  
CREATE OR REPLACE PACKAGE BODY example AS  
  PROCEDURE dummy1 IS  
  BEGIN  
    NULL;  
  END;  
END;  
/  

SQL> start t1.sql;  

Package created.  


Package body created.  

SQL> select parsed_size from user_object_size where name='EXAMPLE';  


PARSED_SIZE  
-----------  
        185  
          0  


SQL> select * from user_object_size where name='EXAMPLE';  

  .....

Oracle stores both DIANA and MCODE in the database. MCODE is the actual code that runs, while DIANA for a particular library unit X contains information that is needed to compile procedures using library unit X.

The following are several notes:

a) DIANA is represented in IDL. The linear version of IDL is stored on disk. The actual parse tree is built up and stored in the shared pool. This is why the size of DIANA in the shared pool is typically larger than on disk.

b) DIANA for called procedures is required in the shared pool only when you create procedures. In production systems, there is no need for DIANA in the shared pool (but only for the MCODE).

c) Starting with release 7.2, the DIANA for package bodies is thrown away, not used, and not stored in the database. This is why the PARSED_SIZE (i.e. size of DIANA) of PACKAGE BODIES is 0.

Therefore, large procedures and functions should always be defined within packages!

A package is stored in DIANA in the database, just like a procedure. A package can be used to break the dependency chain however, perhaps making this go away. It is my belief that ALL production (real) code should be in a package, never in a standalone procedure or function.