Skip to main content

Change in ALL_ARGUMENTS as of 18c: no more composite "explosion"

The Oracle catalog contains hundreds of views (generally referred to as "data dictionary views") that provide information about the objects stored in the database (tables, views, PL/SQL program units, etc.). Several of these views are extremely helpful to PL/SQL developers in analyzing and managing their code.

Here are a few examples:
  • ALL_OBJECTS - information about all database objects which can be accessed by the current user.
  • ALL_ARGUMENTS - information about every argument of every packaged subprogram and schema-level program unit for which the current user has EXECUTE authority.
  • ALL_IDENTIFIERS - information about identifiers in program units, gathered by the Oracle Database 11g PL/Scope feature.
In this blog post, I explore a change in behavior for ALL_ARGUMENTS (and its USER* and DBA* variants) as of Oracle Database 18c.

ALL_ARGUMENTS lists the arguments of the procedures and functions that are accessible to the current user. USER_ARGUMENTS, as you might know or guess, offers information about those arguments defined in procedures and functions in the currently connected schema.

This view gives developers access to all sorts of interesting information, allowing them to create queries to perform checks on their code.

You can, for example, determine if any arguments of an "undesireable" type, such as CHAR or LONG. You can also find out if a subprogram is a procedure or a function.

You can also determine if a subprogram is a function or a procedure (though this is now also possible through PL/Scope and ALL_IDENTIFIERS). Here's an OracleLiveSQL script showing this usage.

As you likely know, an argument could be a scalar (number, date, string. etc.) or a composite (consisting of multiple "pieces", such as a record or object type). Up until Oracle Database 12c Release 2, the way that ALL_ARGUMENTS recursively expanded composites. In other words, that view contained one row for the argument itself and then one row for each attribute of an object type or field of a record. You can see this below.

I create a table, then a function that accepts a record as an in parameter.
CREATE TABLE plch_employees
(
   employee_id     INTEGER
 , last_name       VARCHAR2 (100)
 , first_name      VARCHAR2 (100)
 , date_of_birth   DATE
 , hire_date       DATE
 , salary          NUMBER
)
/

CREATE OR REPLACE FUNCTION plch_get_info (
   employee_in    IN plch_employees%ROWTYPE
 , info_type_in   IN PLS_INTEGER
 , for_date_in    IN DATE)
   RETURN BOOLEAN
   AUTHID DEFINER
IS
BEGIN
   RETURN NULL;
END;
/

/* Verify choice correctness */

SELECT COUNT (*) arg_count
  FROM user_arguments 
 WHERE object_name = 'PLCH_GET_INFO' 
/

ARG_COUNT
---------
10
Run this came code in 18c and above, and the count of rows is just 4. The following script shows a bit more detail.
SQL> 
select OBJECT_NAME,ARGUMENT_NAME,POSITION
  2    FROM user_arguments
  3   WHERE object_name = 'PLCH_GET_INFO'
  4  /
 
OBJECT_NAME          ARGUMENT_NAME          POSITION
-------------------- -------------------- ----------
PLCH_GET_INFO                                      0
PLCH_GET_INFO        EMPLOYEE_IN                   1
PLCH_GET_INFO        INFO_TYPE_IN                  2
PLCH_GET_INFO        FOR_DATE_IN                   3
 
4 rows selected.
 
12.2
 
SQL> select OBJECT_NAME,ARGUMENT_NAME,POSITION
  2    FROM user_arguments
  3   WHERE object_name = 'PLCH_GET_INFO'
  4  /
 
OBJECT_NAME          ARGUMENT_NAME          POSITION
-------------------- -------------------- ----------
PLCH_GET_INFO                                      0
PLCH_GET_INFO        EMPLOYEE_IN                   1
PLCH_GET_INFO        EMPLOYEE_ID                   1
PLCH_GET_INFO        LAST_NAME                     2
PLCH_GET_INFO        FIRST_NAME                    3
PLCH_GET_INFO        DATE_OF_BIRTH                 4
PLCH_GET_INFO        HIRE_DATE                     5
PLCH_GET_INFO        SALARY                        6
PLCH_GET_INFO        INFO_TYPE_IN                  2
PLCH_GET_INFO        FOR_DATE_IN                   3

If you relied on that expanded data in ALL_ARGUMENTS in any of your QA scripts, you will now need to join with one of the following views: ALL_PLSQL_TYPES, ALL_PLSQL_TYPE_ATTRS, and ALL_PLSQL_COLL_TYPES.

Here are two sources of more information on this change, as provided by Jurgen, who also offers an extensive comment about his team's experience with this change below.

Changes to /ALL/USER/DBA User View and PL/SQL External Libraries

About Bug 5910872

Comments

  1. Hi Steven,

    Do you happen to know any detail about why that behavior has been changed?
    I am aware about the following bug that is related to the performance of DataPump Import operations and, generally, the amount of space occupied in the SYSTEM tablespace:
    Bug 5910872 : ARGUMENT$ DATA UNNECESSARILY DUPLICATED
    It seems to be quite a nice improvement, however, I have not seen any tests showing the performance effects of that change.
    I guess JDBC should also be affected in addition to any other operations utilizing %_ARGUMENTS views extensively (don't recall anything right now apart from JDBC metadata queries).

    Regards,
    Mikhail.

    ReplyDelete
    Replies
    1. Mikhail, I believe you are right on target. A key motivation for the change was to reduce the amount of space need to store argument information.

      Delete
  2. Hi Steven,

    we stumbled upon that pesky issue no more than month ago since we skipped 18c for good.

    Here's some input from Oracle
    ----

    https://docs.oracle.com/en/database/oracle/oracle-database/18/rnrdm/pl-sql-bugs.html#GUID-A0EB14E7-D759-46F4-B438-A5F6DB1C3761

    https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/behavior-changes-deprecated-desupport-oracle-database.html#GUID-543498D6-3799-4217-9BE3-4BB8630FC32D

    The changes where made because of compilation performance/memory limitation issues on table ARGUMENT$.
    Just search for bug id 5910872:

    https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=u4vhb0pq8_4&_afrLoop=525552462352302

    https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=u4vhb0pq8_4&_afrLoop=525552462352302

    ----

    Our data access interface "Oracle OCI <-> C++" is not able to resolve nested datatypes (data type level > 0) anymore because "OCIDescribeAny()" is based on ARGUMENT$ which only represents level 0 data types now.
    We internally only support the fetch of table type data (collections) holding single scalar datatypes but also this is now not working out of the box with OCIDescribeAny() anymore.

    Also all of our dynamic SQL generating PLSQL procedures which rely on the complete xxx_ARGUMENTS views don't work anymore as intended.
    Furthermore even DBMS_DESCRIBE does not work like it used to because it's based on OCIDescribeAny().

    It's noteable that there exists an Oracle EVENT which reverts the new ARGUMENT$ behaviour. Full recompilation is necessary after setting the event.
    => events='10946, level 65536'

    But setting an event to force backward compatibility could hit you very hard in future Oracle releases lacking that special event.
    Additionally no one knows what other side effect this event will piggyback - just like every patch/bundle/set does.

    Because of this reasons I wrote a pipelined function which emulates a fully resolved xxx_ARGUMENTS fetch. I created a local ALL_ARGUMENTS view so that no further PLSQL code changes are necessary. The query performance on my own ALL_ARGUMENTS is unfortunately under par. We now consider to cache the fully resolved result. Maybe a materialized view will help here.

    Our data access interface using OCI is now also rewritten and resolves now at least to data type level 1.


    Beware that there even exists an Oracle 12.1 "bugfix" which already introduces this new behavoir!

    BR,
    Jürgen

    ReplyDelete
    Replies
    1. Jürgen, thank you so much for providing all this detailed information. I am sorry this change had such a dramatic impact for you.

      Two questions:

      1. Have you notified Oracle Support of any of these issues? I will also pass this along to the PL/SQL team.

      2. Any possibility that you could and would share your pipelined table function implementation on, say, Oracle LiveSQL (livesql.oracle.com) for others to use?

      Many thanks, Steven

      Delete
    2. Hello Steven,

      so far we haven't opened a SR on MOS regarding that issue since Oracle stated in their documentary that this is indeed a intended behaviour change for 18c.
      Should I open one despite that fact?
      It really makes me wonder that there only exists just a handful of complaints because of that hefty change on Google or MOS search.
      Seems no one really uses this views or haven't really noticed the changes so far.

      I could share my code but I've to remove the code pieces which rely on our base PLSQL code.

      BR,
      Jürgen

      Delete
    3. You are right that it is a known and documented change, so definitely will not be considered a bug. And they will not be changing it back.

      I expect that most people who use all_arguments do so "lightly" and either didn't pay attention to the recursive expansion or didn't rely on it.

      Delete

Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…