Pinning in M/R // Background Process

Pinning stored procedure/function to shared pool in 11G

execute dbms_shared_pool.keep(owner.trigger, 'R');

---------------------------------------------

Script displays instance background process information. The script works when the database is MOUNTed or OPENed.

select A.SID,A.SERIAL#,A.PROGRAM,P.PID,P.SPID,A.OSUSER, /* Who Started INSTANCE */A.TERMINAL,
A.MACHINE,A.LOGON_TIME,B.NAME,B.Description,P.PGA_USED_MEM,P.PGA_FREEABLE_MEM,P.PGA_MAX_MEM
from v$session A,v$process P,v$bgprocess B where A.PADDR=B.PADDR AND A.PADDR=P.ADDR and
A.type='BACKGROUND';

ESCAPE With LIKE Operator

ESCAPE With LIKE Operator

select ename from scott.emp where ename like '%_%';

But you will be surprised to see the results:

ENAME
------------
Will dispaly all Data in the Table

it is because _ is a wild card character. That is _ stands for any character. So the query yielded all the rows.

modified query to get the desired output as below:


select ename from scott.emp where ename like '%#_%' escape '#';

ENAME
------------
FRA_KLIN

About TNS file

What is PRESENTATION=RO in tnsnames.ora file

Check the entry in tnsnames.ora file:

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

In this article we will be discussing about the PRESENTATION clause in the entry.

Little history
The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database).

The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc.

Back to EXTPROC_CONNECTION_DATA
But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared libraries (DLLs).

PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.

The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO

TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.

JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.

It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution

RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.

Benefit of Cursor For loop for 10g

Which will run much slower than the other two, and why?

a.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
BEGIN
FOR employee_rec IN employees_cur LOOP
do_stuff (employee_rec);
END LOOP;
END;

b.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
l_employee employees%ROWTYPE;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur INTO l_employee;
EXIT WHEN employees_cur%NOTFOUND;
do_stuff (l_employee);
END LOOP;
CLOSE employees_cur;
END;

c.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT 100;
EXIT WHEN l_employees.COUNT () = 0;
FOR indx IN 1 .. l_employees.COUNT
LOOP
do_stuff (l_employees (indx));
END LOOP;
END LOOP;
CLOSE employees_cur;
END;

(b) Is the slowest. That's because on Oracle 10g and higher, the PL/SQL optimizer will automatically rewrite cursor FOR loops so that they are executed in the same way as the BULK COLLECT query.

Tuning the LIKE-clause

Tuning the LIKE-clause (by using reverse key indexes)

For tuning Like operator (like '%SON') is to create a REVERSE index - and then programmatically reverse the LIKE-clause to read LIKE 'NOS%'

Steps:

CREATE INDEX Cust_Name_reverese_idx
ON customer(Cust_Name) REVERSE;

2. Programmatically reverse the SQL LIKE-clause to read '%saliV%':

SELECT * FROM customer WHERE Cust_Name LIKE '%Vilas%'

New Query:

SELECT * FROM customer WHERE Cust_Name LIKE '%saliV%';