What is DBMS_PIPE? 

DBMS_PIPE is a package provided by Oracle that allows two or more sessions in the same instance to communicate.  

The DBMS_PIPE package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required.. 

Types: 

There are two types of pipes 

Implicit Pipes- These are created automatically when a message is sent with an unknown pipe name using the SEND_MESSAGE function. Implicit pipes disappear when they are empty. 

Explicit Pipes- These are created using the CREATE_PIPE function. Explicit created pipes must be removed using the REMOVE_PIPE function. 

Security: 

Depending upon your security requirements, you may choose to use either a public or a private pipe. 

Public Pipes- You may create a public pipe either implicitly or explicitly. For implicit public pipes, the pipe is automatically created when it is referenced for the first time, and it disappears when it no longer contains data. Because the pipe descriptor is stored in the SGA, there is some space usage overhead until the empty pipe is aged out of the cache. 

 You can create an explicit public pipe by calling the CREATE_PIPE function with the private flag set to FALSE. You must deallocate explicitly-created pipes by calling the REMOVE_PIPE function. 

The domain of a public pipe is the schema in which it was created, either explicitly or implicitly. 

Private Pipes- You explicitly create a private pipe by calling the CREATE_PIPE  function. Once created, the private pipe persists in shared memory until you explicitly deallocate it by calling the REMOVE_PIPE  function. A private pipe is also deallocated when the database instance is shut down. 

You cannot create a private pipe if an implicit pipe exists in memory and has the same name as the private pipe you are trying to create. In this case, CREATE_PIPE returns an error. 

Access to a private pipe is restricted to: 

• Sessions running under the same userid as the creator of the pipe
• Stored subprograms executing in the same userid privilege domain as the pipe creator
• Users connected as SYSDBA or INTERNAL

An attempt by any other user to send or receive messages on the pipe, or to remove the pipe, results in an immediate error. Any attempt by another user to create a pipe with the same name also causes an error. 

In short we can say that oracle pipes are similar in concept to the pipes used in UNIX, but Oracle pipes are not implemented using the operating system pipe mechanisms. It is called a pipe because it connects two (or more) sessions, and messages are queued up inside, just like a pipe. Each session can take the next received item out of a pipe, or insert the next item to send. Anybody (with access) can insert or remove something from the pipe, in any order. Messages can only be removed and read once – two people can’t remove the same message from a pipe. 

In more real terms, these pipes are buffers in the system global area (the SGA). Messages are prepared for loading using PACK_MESSAGE, loaded into a pipe using SEND_MESSAGE, and read similarly (RECEIVE_MESSAGE then UNPACK_MESSAGE). 

Required grant to the user: 

GRANT EXECUTE ON SYS.DBMS_PIPE TO [user]  

The following example used the CREATE_PIPE function to create explicit public and private pipes. 

Public and Private Pipe

Let us see, does above code realy create pipes for us. To displays information about the pipes, we use V$DB_PIPES view. 

View Pipes

The following example uses REMOVE_PIPE function to remove the pipes, created previously. 

Remove Pipe

Let us see, does above code realy removes pipes that we created recently. 

Display Pipe

Writing and Reading Pipes

Each public pipe works asynchronously. Any number of schema users can write to a public pipe, as long as they have EXECUTE permission on the DBMS_PIPE package, and they know the name of the public pipe. However, once buffered information is read by one user, it is emptied from the buffer, and is not available for other readers of the same pipe. The sending session builds a message using one or more calls to the PACK_MESSAGE procedure. This procedure adds the message to the session’s local message buffer. The information in this buffer is sent by calling the SEND_MESSAGE function, designating the pipe name to be used to send the message. When SEND_MESSAGE is called, all messages that have been stacked in the local buffer are sent. 

 A process that wants to receive a message calls the RECEIVE_MESSAGE function, designating the pipe name from which to receive the message. The process then calls the UNPACK_MESSAGE procedure to access each of the items in the message. 

 For this test, open up two sessions as the SAME user on the SAME instance. Make sure your user has access to the DBMS_PIPE package. 

We are going to have the first instance create the pipe, send in an SQL command, and have the second instance retrieve that message and execute it. 

 Below is the step by step procedure to implement communication between two sessions 

 1) Create a Pipe: Depending upon the need create pubic of private pipe. For this we use DBMS_PIPE.CREATE_PIPE Function. 

Create Pipe

 2) Checking the Pipes in the Database, by using v$db_pipes view. 

View Pipe

3) Sending message through session #1. For this we use PACK_MESSAGE and SEND_MESSAGE functions of the DBMS_PIPE package. 

Send Message

4) Receiving message in session #2, For this we use Receive_Message and UNPACK_Message functions of DBMS_PIPE package. 

Receive Message

Pipe Uses

The pipe functionality has several potential applications. Some of these are mentioned below: 

• External service interface: You can communicate with user-written services that are external to the RDBMS. This can be done in a (effectively) multi-threaded manner, so that several instances of the service are executing simultaneously. Additionally, the services are available asynchronously. The requestor of the service does not need to block a waiting reply. The requestor can check (with or without timeout) at a later time. The service can be written in any of the 3GL languages that Oracle supports.
• Independent transactions: The pipe can communicate to a separate session which can perform an operation in an independent transaction (such as logging an attempted security violation detected by a trigger).
• Alerters (non-transactional): You can post another process without requiring the waiting process to poll. If an “after-row” or “after-statement” trigger were to alert an application, then the application would treat this alert as an indication that the data probably changed. The application would then read the data to get the current value. Because this is an “after” trigger, the application would want to do a “select for update” to make sure it read the correct data.
• Debugging: Triggers and stored procedures can send debugging information to a pipe. Another session can keep reading out of the pipe and display it on the screen or write it to a file.
• Concentrator: This is useful for multiplexing large numbers of users over a fewer number of network connections, or improving performance by concentrating several user-transactions into one DBMS transaction.

Tips & Tricks for optimizing SQL queries.      

Many developers assume that their goal is only to write SQL statements that deliver the correct data from Oracle. But it’s not enough. In reality, formulating the SQL is only half of their job. Successful Oracle shops always require that developers ensure that their SQL accesses the database in an optimal fashion.

In my opinion optimizing SQL query is not belong to such topics, which can be completed in one discussion. So, in this article I will discuss Indexes with you.

Indexing is very basic concept in terms of query optimization.Indexes can decrease the execution time of a SQL SELECT query dramatically. Indexes are functionally, less fundamental than tables. Indexes are really just a means to reach table rows quickly. They are essential to performance, but not functionally necessary. Ideally, we should use indexes if our select query is fetching 0-5% of total number rows.

 Types of Indexes

 B-Tree Indexes

The most common and most important type of index, by far, is the B-tree index, which reflects a tree structure that is balanced (hence the B) to a constant depth from the root to the leaf blocks along every branch. B-Tree indexes are the regular type that OLTP systems make much use of. Fig below shows a three-deep B-tree, likely reflecting an index pointing to 90,000-27,000,000 rows in a table, the typical size range for three-deep B-trees.                 

A three-level B-Tree index 

Bit-Mapped Indexes

Bitmap indexing provides both substantial performance improvements and space savings over usual (B*-tree) indexes when there are few distinct values in the indexed columns. Bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

 Statements not written for Indexes.

 Some SELECT statement WHERE clauses do not use indexes at all. Most such problems are caused by having a function on an indexed column. Here I am listing those functions which should not be used on indexed columns in WHERE clause of SELECT statement, because they nullify the effect of indexing. These are

1)      Avoid NOT operator

i)                    SELECT student_num,student_name

FROM student

WHERE student_num NOT LIKE ‘9%’;

ii)                  SELECT account_name,  trans_date, amount

FROM transaction

WHERE amount != 0;

2)      Avoid SUBSTR function

SELECT account_name, trans_date, amount

FROM transaction

WHERE SUBSTR(account_name,1,7)=’CAPITAL’;

3)      Avoid concatenate function

SELECT account_name, trans_date, amount

FROM transaction

WHERE account_name||account_type=’AMEXA’;

4)      Avoid Arithmetic operators(+,-,*,/) 

SELECT account_name, trans_date, amount

FROM transaction

WHERE amount+3000<5000;

5)      Avoid indexed columns on both sides of an operator

SELECT account_name, trans_date, amount

FROM transaction

WHERE account_name=NVL(:acc_name,account_name);

6)      Avoid TRUNC function on indexed columns

SELECT account_name, trans_date, amount

FROM transaction

WHERE TRUNC(trans_date)=TRUNC(SYSDATE);

 We have two alternatives to come out of such situations

i)                    Try some other alternatives for these Select statements

ii)                  Set QUERY_REWRITE_ENABLED to TRUE and OPTIMIZATION_MODE to CHOOSE in init.ora file. Then create function index instead of simple indexes. Now above select queries will work fine.

 7)      Use same data types in both sides

Select emp_no, emp_name,salary

From emp

Where emp_type=123

*Data type of emp_type is of VARCHAR2 type.

 So if we want to write optimized SQL queries, then we should take care of above mentioned functions, operators on indexed columns.