We can pass parameters to PL/SQL procedures using one of two methods:
BY VALUE - the default value for parameters IN OUT, OUT. A copy of the passed parameter is made, any calculations in the procedure are performed on this copy, and finally, the value is copied back to the original variable.
BY REFERENCE - if we use NOCOPY's hint - Oracle instead of copying the variable - will use a reference to the original variable.
In the case of an exception in the procedure - the value of the variable passed BY VALUE is restored to the state before the procedure was started, and in the case of a variable passed BY REFERENCE - the value of the variable is not restored, and has the value as at the time of the exception.
A general good practice is to use the NOCOPY hint to optimize PGA memory usage. But what is the actual impact of NOCOPY's hint on memory usage? Let's check.
The test was inspired by a post on the Oracle-base blog. I adapted the test to my data and added a few new test cases.
I conducted the tests on Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production on the HR scheme. All scripts can be downloaded from the links at the end of the post.
The results of the first tests seemed to confirm the general opinion on how the NOCOPY hint affects the usage of PGA memory. I ran two tests:
DEFAULT TEST
test where I pass the global collection (declared in the package spec) to the procedure using the IN OUT parameter.
NOCOPY TEST
test in which I pass the global collection (declared in the package specification) to the procedure using the IN OUT parameter with an additional NOCOPY hint.
+=========================+===========+===========+======+========+
| TEST | PGA_IN | PGA | TIME | COUNT |
+=========================+===========+===========+======+========+
| DEFAULT | 323026944 | 323026944 | 57 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY | 65536 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
PGA_IN - the usage of PGA memory in the procedure to which we pass the collection with the parameter IN OUT.
PGA - usage of PGA memory at the end of the process.
As you can see from the tests' results after adding the hint of NOCOPY - the usage of PGA dropped drastically from 361758720 to 65536, both at the time of starting the procedure and at the end of the process. Processing time also decreased from 57 to 0 hundredths of a second.
So far, so good.
But what if we don't want to use the global/public collection but want to declare this collection in a procedure? Does it affect test results? After all, why would they?
But let's check.
LOCAL TEST
the collection declared in the procedure is passed to the sub-procedure using the IN OUT parameter.
NOCOPY LOCAL TEST
the collection declared in the procedure is passed to the sub-procedure with the NOCOPY hint.
+=========================+===========+===========+======+========+
| TEST | PGA_IN | PGA | TIME | COUNT |
+=========================+===========+===========+======+========+
| DEFAULT | 323026944 | 323026944 | 57 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY | 65536 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| LOCAL | 131072 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY LOCAL | 131072 | 65536 | 2 | 535000 |
+-------------------------+-----------+-----------+------+--------+
Wow! The results of both tests are no different!
Although when it comes to PGA usage - the value is twice as high as with the NOCOPY test with a global collection, it is still definitely less than the test with a global collection without a hint (DEFAULT test), regardless of whether we use the NOCOPY hint or not!
The processing time is 0.
Interestingly, PGA usage in the subprocess is twice as high as at the end of the process, regardless of how the collection is passed to the procedure.
It's getting interesting. Well, let's go on!
The next tests are:
ROW BY ROW TEST
row by row (%rowtype) from the global collection is passed using the IN OUT parameter.
NOCOPY ROW BY ROW TEST
row by row (%rowtype) from the global collection is passed using the IN OUT NOCOPY parameter
LOCAL ROW BY ROW TEST
row by row (%rowtype) from the local collection is passed using the IN OUT parameter.
TEST LOCAL NOCOPY ROW BY ROW
row by row (%rowtype) from the local collection is passed using the IN OUT NOCOPY parameter
+=========================+===========+===========+======+========+
| TEST | PGA_IN | PGA | TIME | COUNT |
+=========================+===========+===========+======+========+
| DEFAULT | 323026944 | 323026944 | 57 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY | 65536 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| LOCAL | 131072 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY LOCAL | 131072 | 65536 | 2 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| ROW BY ROW | | 65536 | 33 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY ROW BY ROW | | 65536 | 33 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| LOCAL ROW BY ROW | | 131072 | 34 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY LOCAL ROW BY ROW | | 131072 | 33 | 535000 |
+-------------------------+-----------+-----------+------+--------+
The results are very interesting.
ROW PO ROW testing on a global collection has the same PGA usage as a global collection test with a NOCOPY hint - whether we use the NOCOPY hint in the ROW BY ROW process or not! However, the processing time has increased significantly, although it is still half the time of the DEFAULT test - a global collection with no trace of NOCOPY.
And ROW BY ROW processing on a local collection has the same PGA usage as the local collection test (LOCAL and NOCOPY LOCAL), but measured in a procedure! If we look at the times - the times are significantly increased.
To sum up:
If in the process we use and pass a globally declared collection - then using the NOCOPY hint should significantly reduce the PGA memory usage as well as the processing time.
In other cases, it doesn't matter whether we use the NOCOPY hint or not. It matters more whether we declare the collection globally or locally, and whether we pass the collection to the subroutine row by row - or as a collection.
Scripts:
Run all tests in separate sessions.
Objects and package.
Komentarze
Prześlij komentarz