Dear AskTOM-Team,

It would be great if you can share your opinion on anydata-datatype.

Background to my question is, I'm using anydata-Type in conjunction with dbms_sql. To handle a dynamic-sql with their correct datatypes.
After a few tests I found out, that the anydata.access* / anydata.get* methods cause a significant time overhead.
I've seen Bug 17487865 but our environment is 12.1.0.2 on AIX 6.1 - v$version:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

and livesql is running 12.2.0.1.
So according to support.oracle this bug should be fixed in both versions.

I've provided some test-cases on livesql - they all use "index by varchar2(128)", which is close to our production-implementation.

It would be great if you can have a look at these, and either let me know if this is the time-overhead I've to live with when using anydata.
Or if this isn't the correct usage of anydata. Or if I've missed a note/bug in support.oracle.

Here are the links to my testcases:
.) Prerequisites
Help-Package to measure (cpu_)time - help.pkg:
https://livesql.oracle.com/apex/livesql/s/fgw9d652y2pduicuhfope1b90

Custom Object-Type for one testcase - my_datatype:
https://livesql.oracle.com/apex/livesql/s/fgxi5zeg51kmmkqj4o8le31eb

.) Test-Cases
The testcases are - hopefully - rather simple, they always fill an array with 1mio entries, and than access it again.
Anydata-index my varchar2(128) - any.vc2:
https://livesql.oracle.com/apex/livesql/s/fgxspb4ssakdm1nbl9leplhn5

My-custom-object-type - my.type.vc2:
https://livesql.oracle.com/apex/livesql/s/fgxi5zeg2uqh0gqoulo6vws8z

Record within pl/sql - my.vc2:
https://livesql.oracle.com/apex/livesql/s/fgxspb4sr43ij901j2khf3z41


Here are the times on livesql for each step (fill / access) for each test-case.
The column "d_step" shows the time in sec. needed for this step (dbms_utility.get_time)
Anydata:

 step      start   d_step      cpu    d_cpu  text
 ----    -------  -------  -------  -------  -------------------
   0.     0.0000   0.0000   0.0000   0.0000  BEGIN
   1.    10.2500  10.2500   8.8600   8.8600  fill anydata
   2.    19.7400   9.4900  18.2100   9.3500  loop GET anydata
   3.    30.6500  10.9100  29.0400  10.8300  loop ACCESS anydata
   4.    30.6500   0.0000  29.0400   0.0000  END

As you can see, it took ~9sec to access the 1mio entries with anydata.Get* and nearly 11sec with anydata.Access* methods.
In all my tests Get* is always slightly faster than Access*.

Custom-object-type:

 step      start   d_step      cpu    d_cpu  text
 ----    -------  -------  -------  -------  ------------
   0.     0.0000   0.0000   0.0000   0.0000  BEGIN
   1.     9.9700   9.9700   6.0500   6.0500  fill my type
   2.    11.5500   1.5800   7.6300   1.5800  loop my type
   3.    11.5500   0.0000   7.6300   0.0000  END

Only took 1.5 sec to loop over the a custom-object-type.

Record within pl/sql:

 step      start   d_step      cpu    d_cpu  text
 ----    -------  -------  -------  -------  -----------
   0.     0.0000   0.0000   0.0000   0.0000  BEGIN
   1.     9.2200   9.2200   5.2700   5.2700  fill mydata
   2.    10.0600   0.8400   6.1000   0.8300  loop mydata
   3.    10.0600   0.0000   6.1000   0.0000  END

And it only took .8sec to loop over a record/table.

The Custom-Object-Type test is rather rough, to be honest I didn't put too much effort into this test. I was just curious how it would compare to the other two.

I thought that the record would be the fastest method, but I didn't expect ~10sec for anydata.

Our AIX is almost 2 times slower than livesql - but the ration is the same.
For comparison here are the times (d_step) for the loop-steps only :

Anydata:
16.7200  loop GET anydata
19.1300  loop ACCESS anydata

Custom-object-type:
3.0500   loop my type

pl/sql-record:
1.5100   loop mydata

As mentioned above, I'd like to know if the usage of anydata would cause such a timing-overhead, or if this is the wrong usage of anydata at all.

Please take all the time you need to look into these testcases, and please let me know if there is any additional information I can provide.
No need to hurry, because - you know - nobody cares if a batch-job during the night runs for 10 minutes or 1 hour...
And in the end I'll probably rewrite everything to a pl/sql-record/table.

If you like, I've also done tests with "index by pls_integer" and "nested tables", but there is no significant timing difference, so I thought I spare these cases.

Looking forward to your answer, and thank you in advance not only for looking into my question, but for all your time and knowledge you've provided to the oracle-community over the years!

best regards,
Thomas

SQL> exec any_test1
=== ANYDATA VARCHAR2 ===
step      start   d_step      cpu    d_cpu  text
----    -------  -------  -------  -------  ---------------------
0.     0.0000   0.0000   0.0000   0.0000  BEGIN
1.     6.6900   6.6900   4.3400   4.3400  fill anydata
2.    12.4000   5.7100  10.0600   5.7200  loop GET anydata
3.    19.1100   6.7100  16.7700   6.7100  loop ACCESS anydata
4.    19.1100   0.0000  16.7700   0.0000  END

PL/SQL procedure successfully completed.

SQL> exec any_test2
=== MY TYPE VARCHAR2 ===
step      start   d_step      cpu    d_cpu  text
----    -------  -------  -------  -------  ---------------------
0.     0.0000   0.0000   0.0000   0.0000  BEGIN
1.     9.8000   9.8000   3.2800   3.2800  fill my type
2.    10.9200   1.1200   4.4100   1.1300  loop my type
3.    10.9200   0.0000   4.4100   0.0000  END

PL/SQL procedure successfully completed.

SQL> create table t ( a int, x sys.anydata );

Table created.

SQL> insert into t values ( 1, sys.anyData.convertNumber(5) );

1 row created.

SQL> insert into t values ( 2, sys.anyData.convertDate(sysdate) );

1 row created.

SQL> insert into t values ( 3, sys.anyData.convertVarchar2('hello world') );

1 row created.

SQL> insert into t values ( 4, sys.anyData.convertVarchar2('hello world hello world hello world') );

1 row created.

SQL> select a, vsize(x) from t;

         A   VSIZE(X)
---------- ----------
         1         48
         2         53
         3         57
         4         81