Logon trigger to change CURSOR_SHARING=FORCE

Bind variable placeholders are used to calculate the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage. They can also be used for enforcing a SQL profile for SQL statement.

Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.

CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID      VARCHAR2(30)                 NOT NULL,
  MACHINE     VARCHAR2(200),
  PROGRAM    VARCHAR2(200),
  CREATE_DT  DATE                            DEFAULT sysdate
)
TABLESPACE USER01;

CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
  v_db_user      VARCHAR2(30);
  v_machine     VARCHAR2(200);
  v_program     VARCHAR2(200);
  v_os_user      VARCHAR2(30);
  v_count          INTEGER;
BEGIN
  v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
  v_machine  := SYS_CONTEXT('USERENV', 'HOST');
  v_program   := SYS_CONTEXT('USERENV', 'MODULE');
  v_os_user := SYS_CONTEXT('USERENV','OS_USER');

  SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
  WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
  AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
  ;  -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases

  IF v_count >=1 THEN
     EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
     INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
               VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
  END IF;   -- LOGIN_AUDIT is another table.
 END;
/

Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;