PL/SQL Objects Memory Usage Problem

Hi All,
I have a serious problem with PL/SQL Performance.
In one PL/SQL procedure I am calling a lot of PL/SQL Object Types and collections.
When this procedure is executed for 2000 rows it's look fine and work fast - 70 seconds.
But when I start it for 5000 rows, then the lot of object calls in this procedure make execution very slow, about 10 minutes. I supposed that the problem is the memory usage for PL/SQL objects, which may have some limitations.
But I don't found any materials about how the memory, used from Oracle for PL/SQL objects, can be extended.
In Oracle9i Reference I found two init parameters, related to object memory:
- OBJECT_CACHE_OPTIMAL_SIZE
- OBJECT_CACHE_MAX_SIZE_PERCENT
But they can be used only from some Objects API (Ask Tom Thread), in our case we just test one procedure from PL/SQL Developer Tool
Can you guys, send me your thoughts about this problem.
I don't think that if I post the whole source code it will be helpful. The main actions in the procedure are: reading one BLOB field (which is some ASCII file uploaded in the table) line by line and executing a lot validations through PL/SQL Object Types.
I don't mean extending of SHARED_POOL_SIZE or PGA_AGGREGATE_TARGET, just wondering is there some tips of how to make Oracle to use memory in better way when he work with large PL/SQL collections

Thank you in advance!

For any of you who is glad to help - please find the attached report from DBMS_PROFILER. The function is executed for 2000 lines in one BLOB field. Total execution time is 69 seconds.
The measures in excel file are: column C - execution time in seconds, column D - execution times, column A - execution unit, column E - execution source.
I will appreciate any ideas!
Thank you!

When object types/collections size is set to a large value, then pga needs more memory. The amount of sessions pga memory is fixed based on the values of pga_aggregate_target and processes during the instance startup. Reduce the processes value and rerun the pl/sql proc with batch size of 5000. And see the perf.

In the next run, remove pga_aggregate_target, and test the pl/sql proc.

Tamil

Yes, I have already set the value for PGA_AGGREGATE_TARGET parameter to 400 MB, but the behaviour of this function is still the same, when 2000 lines parsed - 70 seconds, for 5000 lines - 10 minutes
This database is for test purposes and there is no additional connections which can consume this memory. For example the data in the tested BLOB field is 3 mb in size, when it is located in the ASCII file. So, I think that 400 mb are enough for any sort and etc activities through this session.
The behaviour of the function with lower and with larger pga session memory is exact the same.

Did you test w/o PGA_AGG... parameter?

Tamil

Yes, I test it.
The behaviour of the function is the same

check for session wait event.. when you execute that proc for 5000 rows to process.

Hi all,
The problem was resolved.
If anyone is interested what is the problem in this situation can read our workaround:
- there is a lot calls to member subprograms in PL/SQL objects
- these member subprograms have some defined attributes
- bacause they are defined with default IN OUT parameters and without NOCOPY keyword, the behaviour for every calling of one object after executing of this subprogram, all attributes to be copied in memory
- this causes a large overhead
- the solution is to be used the NOCOPY keyword

See the PL/SQL User's Guide and Reference 10g, Chapter 11 - Tuning PL/SQL Applications for Performance:
"If you use OUT or IN OUT parameters, PL/SQL adds some performance overhead to ensure correct behavior in case of exceptions (assigning a value to the OUT parameter, then exiting the subprogram because of an unhandled exception, so that the OUT parameter keeps its original value).

If your program does not depend on OUT parameters keeping their values in such situations, you can add the NOCOPY keyword to the parameter declarations, so the parameters are declared OUT NOCOPY or IN OUT NOCOPY.

This technique can give significant speedup if you are passing back large amounts of data in OUT parameters, such as collections, big VARCHAR2 values, or LOBs.

This technique also applies to member subprograms of object types. If these subprograms modify attributes of the object type, all the attributes are copied when the subprogram ends. To avoid this overhead, you can explicitly declare the first parameter of the member subprogram as SELF IN OUT NOCOPY, instead of relying on PL/SQL's implicit declaration SELF IN OUT."

After using the NOCOPY keyword, the behaviour of the function is the following:
- for 2000 lines - 20 seconds
- for 5000 lines - 71 seconds