Best way to maximize cursor reuse in oracle

Reusing a cursor means compiling a SQL statement just once, but executing it more than once. When a SQL statement is compiled, the database checks the syntax and chooses a query path. By doing this only once, but executing this statement several times, you can obtain an improvement in performance.

It is good practice to limit the number of times a cursor has to be parsed the optimal number of parses is one, of course. One option to achieve this ideal would be to preparse every possible cursor your application might ever execute. That way, every cursor would already be waiting in the shared pool when your application starts up. However, this approach would be extremely difficult to maintain in a large application or in one that allows ad hoc queries. Thus, it is better to take the hit the first time a cursor executes and then ensure that it is reused whenever possible later on.
Oracle uses a complex algorithm to decide whether a cursor that is about to be executed may reuse an already compiled version from the shared pool.

• One of the best ways to take advantage of automatically reformatting cursors to promote reuse is to put them into PL/SQL.The PL/SQL compiler reformats the mismatched cursors, thus producing a single cursor in the shared pool. On the surface, this may seem to be a very small improvement, but implemented across an application, it can produce big performance gains because the shared pool latches have fewer cursors to keep track of.
• PL/SQL can virtually eliminate all hard parses just by moving the cursors into a stored procedure.
• The PL/SQL compiler promotes reuse by being more forgiving regarding the structure of cursors.
• Once a cursor is compiled into a PL/SQL procedure, package, or function, it is automatically considered valid and parsed as long as the procedure, package, or function remains valid.
• PL/SQL compiler makes some extra effort to facilitate cursor reuse by checking for small differences like extra whitespace, uppercase versus lowercase, and line breaks.
• Another factor to consider when planning for cursor reuse is the use of literal values.
• A setting is available for the CURSOR_SHARING parameter : FORCE. And it does just that force cursors to be shared strictly on the basis of the text they contain after translating literals into bind variables. This method blindly performs literal substitution and forces the CBO to create an execution plan based on it. This is not always optimal, because the CBO might make a better decision if it knew the explicit value.

Beyond matching the text of a cursor, there are several other factors influencing cursor reusefor example, optimizer statistics and Globalization Support (previously National Language Support, or NLS) setting mismatches. In such cases, it's not enough to simply match ASCII values.

Note: The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.1
References

• http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tpcr/task_ReusingaCursor-c07aed.html
• http://www.vsevolod.com/magz/ORplSQLl/0596005873/oracleplsqldba-chp-2-sect-1.html
• Oracle PL/SQL for DBA By Arup Nanda, Steven Feuerstein

to_char or to_date which one to use

Our developper ask me often which on to use between to_char and to_date, i think they should

• Use to_char to DISPLAY a date datatype 
• Use to_date to STORE a date datatype in a row

to_char converts a date to a varchar and to_date converts a varchar to a date

query the database for the user’s information

If you want to obtain environment and session information such as the name and IP address of the
current user so that the values can be stored into local variables for logging purposes you can make use of the SYS_CONTEXT built-in function to query the database for the user’s information. Once you
have obtained the information, then store it into a local variable. At that point, you can do whatever
you’d like with it, such as save it in a logging table.

DECLARE
  username      varchar2(100);
  ip_address    varchar2(100); 

BEGIN
  SELECT SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','IP_ADDRESS')
  INTO username, ip_address
  FROM DUAL;

  DBMS_OUTPUT.PUT_LINE('The connected user is: ' || username || ', and the IP address
 is ' ||
                                                      ip_address);
END;