Use NOCOPY to Improve Performance

The NOCOPY parameter hint tells the PL/SQL compiler to pass OUT and IN-OUT parameters "by reference" (meaning: do not copy in, nor copy out). The result is better performance and reduced PGA consumption. 

Two Ways of Passing Argument to Function/Procedure

1. Call by Value
2. Call by Reference

Call by value

In call by value,

·       a copy of actual arguments is passed to formal arguments of the called procedure or function and any change made to the formal arguments in the called procedure or function have no effect on the values of actual arguments in the calling procedure or function, which leads to lot of CPU and memory consumption.

·       In call by value, actual arguments will remain safe, they cannot be modified accidentally.

Call by reference

In call by reference,

·       The location (address) of actual arguments is passed to formal arguments of the called procedure or function. This means by accessing the addresses of actual arguments we can alter them within from the called procedure or function.

·       In call by reference, alteration to actual arguments is possible within from called procedure or function; therefore the code must handle arguments carefully else you get unexpected results.

Summary of Call by Value and Call by Reference:

Point	Call by Value	Call by Reference
Copy	Duplicate Copy of Original Parameter is Passed	Actual Copy of Original Parameter is Passed
Modification	No effect on Original Parameter after modifying parameter in procedure or function	Original Parameter gets affected if value of parameter changed inside procedure or function

Parameter

Name of the formal parameter that we are declaring, which we can reference in the executable part of the subprogram.

IN, OUT, IN OUT

Note:

Avoid using OUT and IN OUT for function parameters. The purpose of a function is to take zero or more parameters and return a single value. Functions must be free from side effects, which change the values of variables not local to the subprogram.

NOCOPY

Requests that the compiler pass the corresponding actual parameter by reference instead of value. Each time the subprogram is invoked, the optimizer decides, silently, whether to obey or disregard NOCOPY.

NOCOPY is a hint and Oracle does not guarantee a parameter will be passed by reference when explicitly mentioned. Here are some places where this is not possible (The compiler ignores NOCOPY in these cases)

·       The actual parameter must be implicitly converted to the data type of the formal parameter.

·       The actual parameter is the element of a collection.

·       The actual parameter is a scalar variable with the NOT NULL constraint.

·       The actual parameter is a scalar numeric variable with a range, size, scale, or precision constraint.

·       The actual and formal parameters are records, one or both was declared with %ROWTYPE or %TYPE, and constraints on corresponding fields differ.

·       The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor FOR LOOP statement, and constraints on corresponding fields differ.

·       The subprogram is invoked through a database link or as an external subprogram.

·       When the call is a remote procedure call

·       When the actual parameter being passed is an expression

·       When there is an implicit conversion involved

There may be other situations where Oracle may decide a call by value over a call by reference. Since this is not clearly specified, it is advisable not to build any process logic on this feature when exceptions being raised in the called routine are being trapped in the calling routine.

Note:

The preceding list might change in a subsequent release.

What is a NOCOPY parameter? Where it is used?

NOCOPY is a hint given to the compiler, This is used with the declaration of OUT and IN OUT parameter. Indicating that the parameter is passed as a reference and hence actual value should not be copied in to the block and vice versa. The processing will be done accessing data from the original variable. (Which otherwise, oracle copies the data from the parameter variable into the block and then copies it back to the variable after processing. This would put extra burdon on the server if the parameters are of large collections/sizes).

To get around this, package variables were being used to pass values around. Though serviceable as an alternative to prevent multiple buffers and copy overhead, it resulted in higher maintenance cost.

From Oracle 8i onwards, the NOCOPY parameter hint has been introduced for OUT and IN OUT parameters. Using this hint tells Oracle to make a call by reference. Use this hint when there is no need to preserve the original value (in case the called routine raises an exception).

Oracle's internal benchmark testing shows improvements of 30% to 200% for PL/SQL tables being passed as parameters. NOCOPY is the ideal hint for OUT and IN OUT parameters when the original value is not to be preserved (as is generally the case).

Here's an example:

create or replace procedure cpy_chk (pi_val     in             varchar2,

                po_dat     out    nocopy  date,

                pio_status in out nocopy  varchar2) is

begin

   po_dat := sysdate;

   pio_status := pio_status || 'amar is testing';

end;

The nocopy.sql script compares the performance of both methods by passing a populated collection as a parameter.

nocopy.sql

SET SERVEROUTPUT ON
DECLARE
   TYPE     t_tab IS TABLE OF VARCHAR2(32767);
   l_tab    t_tab := t_tab();
   l_start  NUMBER;

   PROCEDURE in_out (p_tab  IN OUT  t_tab) IS
   BEGIN
     NULL;
   END;  

   PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab) IS
   BEGIN
     NULL;
   END;
BEGIN
   l_tab.extend;
   l_tab(1) := '1234567890123456789012345678901234567890';
   l_tab.extend(999999, 1);  -- Copy element 1 into 2..1000000

   -- Time normal IN OUT
   l_start := DBMS_UTILITY.get_time;

   in_out(l_tab);

   DBMS_OUTPUT.put_line('IN OUT       : ' ||
                        (DBMS_UTILITY.get_time - l_start));

   -- Time IN OUT NOCOPY
   l_start := DBMS_UTILITY.get_time;

   in_out_nocopy(l_tab);  -- pass IN OUT NOCOPY parameter

   DBMS_OUTPUT.put_line('IN OUT NOCOPY: ' ||
                        (DBMS_UTILITY.get_time - l_start));
END;
/

The output of the script clearly demonstrates the performance improvements possible when using the NOCOPY hint.

SQL> @nocopy.sql

IN OUT       : 122
IN OUT NOCOPY: 0

PL/SQL procedure successfully completed.

The use of the NOCOPY hint does come with some drawbacks, as it can result in parameter aliasing when global variables are passed as parameters to subprograms.  A parameter alias occurs when two variable names point to the same memory location.  The update_global.sql script shows how aliasing can occur if a global variable is accessed directly from a procedure when it has also been passed as a NOCOPY parameter.

update_global.sql

SET SERVEROUTPUT ON
DECLARE
  l_global  NUMBER := 10; 

  PROCEDURE update_global (p_number  IN OUT NOCOPY  NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('Started update_global');
    DBMS_OUTPUT.put_line('p_number=' || p_number);
    p_number := 20;
    DBMS_OUTPUT.put_line('p_number := 20 : l_global=' || l_global);
    l_global := 30;
    DBMS_OUTPUT.put_line('l_global := 30 : l_global=' || l_global);
    DBMS_OUTPUT.put_line('Finished update_global');
  END;
BEGIN
   DBMS_OUTPUT.put_line('Start: l_global=' || l_global);

   update_global(l_global);

   DBMS_OUTPUT.put_line('End: l_global=' || l_global);
END;
/

The results from this script are displayed below.

SQL> @update_global.sql
Start: l_global=10
Started update_global
p_number=10
p_number := 20 : l_global=20
l_global := 30 : l_global=30
Finished update_global
End: l_global=30

PL/SQL procedure successfully completed.

This shows that the value of the global variable is being set using both methods.  It isn’t a problem if this is the intention of the programmer, but it can lead to problems if the programmer believes they have assigned values to two separate variables. Aliasing can also occur when the same actual parameter is passed multiple times to a procedure or function, as shown by the multiple_params.sql script.

multiple_params.sql

SET SERVEROUTPUT ON
DECLARE
  l_global  NUMBER := 10;

  PROCEDURE multiple_params (p_in             IN             NUMBER,
                             p_in_out         IN OUT         NUMBER,
                             p_in_out_nocopy  IN OUT NOCOPY  NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('Started multiple_params');
    DBMS_OUTPUT.put_line('p_in=' || p_in);
    p_in_out := 20;
    DBMS_OUTPUT.put_line('p_in_out := 20 : p_in=' || p_in);
    p_in_out_nocopy := 30;
    DBMS_OUTPUT.put_line('p_in_out_nocopy := 30 : p_in=' || p_in);
    DBMS_OUTPUT.put_line('Finished multiple_params');
  END multiple_params;
BEGIN
   DBMS_OUTPUT.put_line('Start: l_global=' || l_global);

   multiple_params(l_global, l_global, l_global);

   DBMS_OUTPUT.put_line('End: l_global=' || l_global);
END;
/

The result of this script is shown below.

SQL> @multiple_params.sql
Start: l_global=10
Started multiple_params
p_in=10
p_in_out := 20 : p_in=10
p_in_out_nocopy := 30 : p_in=30
Finished multiple_params
End: l_global=20

PL/SQL procedure successfully completed.

I hope you all have enjoyed reading this article. Comments are welcome....

In-memory database

An in-memory database (IMDB; also main memory database system or MMDB or memory resident database) is a database management system that primarily relies on main memory  for computer data storage. It is contrasted with database management systems that employ a disk storage mechanism. Main memory databases are faster than disk-optimized databases since the internal optimization algorithms are simpler and execute fewer CPU instructions. Accessing data in memory eliminates seek time when querying the data, which provides faster and more predictable performance than disk.

An in-memory database (IMDB, also known as a main memory database or MMDB) is a database whose data is stored in main memory to facilitate faster response times. Source data is loaded into system memory in a compressed, non-relational format. In-memory databases streamline the work involved in processing queries. 

An IMDB is one type of analytic database, which is a read-only system that stores historical data on metrics for business intelligence/business analytics (BI/BA) applications, typically as part of a data warehouse or data mart. These systems allow users to run queries and reports on the information contained, which is regularly updated to incorporate recent transaction data from an organization’s operational systems.

In addition to providing extremely fast query response times, in-memory analytics can reduce or eliminate the need for data indexing and storing pre-aggregated data in OLAPcubes or aggregate tables.  This capacity reduces IT costs and allows faster implementation of BI/BA applications.

Three developments in recent years have made in-memory analytics increasingly feasible: 64-bit computing, multi-core servers and lower RAM prices. 

Applications where response time is critical, such as those running telecommunications network equipment and mobile advertising networks, often use main-memory databases. IMDBs have gained a lot of traction, especially in the data analytics space, starting in the mid-2000s - mainly due to less expensive RAM.

With the introduction of non-volatile random access memory technology, in-memory databases will be able to run at full speed and maintain data in the event of power failure.

There is an obvious performance benefit in the reduced latency in-memory database solutions bring, even over heavily cached systems, which can only optimise database read requests.

But in-memory databases are subtler than that. This is because they provide an opportunity to optimise the way data is managed compared to traditional databases on disk-based media.

When all data is kept in memory, the need to deal with issues arising from the use of traditional spinning disks disappears. This means, for example, there is no need to maintain additional cache copies of data and manage synchronisation between them. 

Row Format vs. Column Format

Oracle Database has traditionally stored data in a row format. In a row format database, each new transaction or record stored in the database is represented as a new row in a table. That row is made up of multiple columns, with each column representing a different attribute about that record. A row format is ideal for online transaction systems, as it allows quick access to all of the columns in a record since all of the data for a given record are kept together in-memory and on-storage.

A column format database stores each of the attributes about a transaction or record in a separate column structure. A column format is ideal for analytics, as it allows for faster data retrieval when only a few columns are selected but the query accesses a large portion of the data set.

But what happens when a DML operation (insert, update or delete) occurs on each format? A row format is incredibly efficient for processing DML as it manipulates an entire record in one operation i.e. insert a row, update a row or delete a row. A column format is not so efficient at processing row-wise DML: In order to insert or delete a single record in a column format all of the columnar structures in the table must be changed.

Up until now you have been forced to pick just one format and suffer the tradeoff of either sub-optimal OLTP or sub-optimal analytics performance.

Oracle Database In-Memory (Database In-Memory) provides the best of both worlds by allowing data to be simultaneously populated in both an in-memory row format (the buffer cache) and a new in-memory column format.

Note that the dual-format architecture does not double memory requirements. The in-memory column format should be sized to accommodate the objects that must be stored in memory, but the buffer cache has been optimized for decades to run effectively with a much smaller size than the size of the database.

In practice it is expected that the dual-format architecture will impose less than a 20% overhead in terms of total memory requirements. This is a small price to pay for optimal performance at all times for all workloads.

Figure 1. Oracle’s unique dual-format architecture

With Oracle’s unique approach, there remains a single copy of the table on storage, so there are no additional storage costs or synchronization issues. The database maintains full transactional consistency between the row and the columnar formats, just as it maintains consistency between tables and indexes. The Oracle Optimizer is fully aware of the column format: It automatically routes analytic queries to the column format and OLTP operations to the row format, ensuring outstanding performance and complete data consistency for all workloads without any application changes.

List of in-memory databases

Name	Developer	Availability	License	Description/Notes
Oracle Coherence	Oracle Corporation		Proprietary with developer download	For Java, relational, distributed
Oracle Exalytics	Oracle Corporation		Proprietary	Appliance
Oracle RDBMS	Oracle Corporation	2014	Proprietary	RDBMS Oracle 12c contains an option for in-memory technology (additional licenses required).

I hope you all have enjoyed reading this article. Comments are welcome....

Minimizing Dependent Recompilations Oracle Database 11g

• Adding a column to a table does not invalidate its dependent objects.

• Adding a PL/SQL unit to a package does not invalidate dependent objects.

• Fine-grain dependencies are tracked automatically.

• No configuration is required.

Earlier Oracle Database releases record dependency metadata—for example, that PL/SQL unit P depends on PL/SQL unit F, or that the V view depends on the T table—with the precision of the whole object. This means that dependent objects are sometimes invalidated without logical requirement. 

Starting with Oracle Database 11g, we have access to records that describe more precise dependency metadata. This is called fine-grain dependencies and it is on automatically.

For example, if the V view depends only on the A and B columns in the T table, and column D is added to the T table, the validity of the V view is not logically affected.

Nevertheless, before Oracle Database, Release 11.1, the V view is invalidated by the addition of the D column to the T table. With Oracle Database, Release 11.1, adding the D column to the T table does not invalidate the V view. Similarly, if procedure P depends only on elements E1 and E2 within a package, adding the E6 element (to the end of a package to avoid changing slot numbers or entry point numbers of existing top-level elements) to the package does not invalidate the P procedure.

Reducing the invalidation of dependent objects in response to changes to the objects on which they depend increases application availability, both in the development environment and during online application upgrade.

I hope you all have enjoyed reading this article. Comments are welcome....

Online Redefinition Enhancements In Oracle Database 11g

• Online table redefinition supports the following:

–      Tables with materialized views and view logs

–      Triggers with ordering dependency

• Online redefinition does not systematically invalidate dependent objects.

Oracle Database 11g supports online redefinition for tables with materialized views and view

logs. In addition, online redefinition supports triggers with the FOLLOWS or PRECEDES clause,which establishes an ordering dependency between the triggers.

In previous database versions, all directly and indirectly dependent views and PL/SQL packages

would be invalidated after an online redefinition or other DDL operations.

These views and PL/SQL packages would automatically be recompiled whenever they are next invoked. If there are a lot of dependent PL/SQL packages and views, the cost of the revalidation or recompilation can be significant.

In Oracle Database 11g, views, synonyms, and other table-dependent objects (with the exception

of triggers) that are not logically affected by the redefinition, are not invalidated.

So, for example,

If referenced column names and types are the same after the redefinition, then they are not

invalidated.

This optimization is “transparent,” that is, it is turned on by default.

Another example: If the redefinition drops a column, only those procedures and views that

reference the column are invalidated. The other dependent procedures and views remain valid.

Note that all triggers on a table being redefined are invalidated (as the redefinition can potentially change the internal column numbers and data types), but they are automatically revalidated with the next DML execution to the table.

I hope you all have enjoyed reading this article. Comments are welcome....