Poorly written application code (not using bind variables)

One of our database servers has serious problems with latching. We have very active application with hundreds of users accessing the database at the same time. That application is not written with bind variables cause of various reasons - so to summarize, application is not using bind variables.

For every submitted query Oracle checks shred pool weather that statement has been already submitted. If Oracle finds statement in shared pool it will reuse previous calculated execution plan and execute the SQL . If that is not the case, Oracle will go thorough whole process of parsing the statement and calculating the execution plan. This is "hard parse" and this could be very expensive operation for OLTP application as ours - DML's instructions will last much longer.

Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory values, which can bring multi-user system to performance problems in hundreds of copies of program are trying to hard parse statements at the same time.



Bind variables are "substitution" variables that are used in place of literals and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.
It is highly recommended to use bind variables when coding application.


So we have highly active application with accessing database without bind variables.

This is the main cause of latch contention problems that we often experience on this database.

Best thing you can do in this situation is to rewrite application with using bind variables, but that takes time and we had to do something in the meantime to keep application running.

To help ourselves we changed parameter CURSOR_SHARING from EXACT to FORCE.

Statements that differ only in literals can be forced to share a cursor. If we are using cursor sharing force, Oracle checks the shared pool for identical or similar statements and forces similar statements to share execution plan. Cursor sharing force will significantly improve performance with reducing hard parses and latch contention, but it will need more work for soft parsing - to find similar statements in the shared pool.
It is important to mention that cursor sharing could not be the replacement for poorly written application code. We could have many optimizer related problems and bad execution plans using cursor_sharing=similar/force.

I found useful example in book "Oracle PL/SQL Tuning", by Dr. Timothy S. Hall

CREATE TABLE bind_variables
(
code VARCHAR2(10)
);

BEGIN
FOR i IN 1 .. 10
LOOP
BEGIN
-- Without bind variables
EXECUTE immediate
'insert into bind_variables (code) values('''
||i||''')';
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
END LOOP;
-- With bind variables
FOR i IN 1 .. 10
LOOP
BEGIN
EXECUTE immediate
'insert into bind_variables (code) values (:B1)'
USING TO_CHAR(i);
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
END LOOP;
COMMIT;
END;
/


-- Selecting the results!

SELECT  sql_text,
executions
FROM    v$sql
WHERE   instr(sql_text,
'insert into bind_variable')>0
AND instr(sql_text,'execute')   =0
ORDER BY sql_text
/

CURSOR_SHARING=EXACT

SQL_TEXT EXECUTIONS
------------------------------------------------------------ ----------
insert into bind_variables (code) values (:B1) 10
insert into bind_variables (code) values('1') 1
insert into bind_variables (code) values('10') 1
insert into bind_variables (code) values('2') 1
insert into bind_variables (code) values('3') 1
insert into bind_variables (code) values('4') 1
insert into bind_variables (code) values('5') 1
insert into bind_variables (code) values('6') 1
insert into bind_variables (code) values('7') 1
insert into bind_variables (code) values('8') 1
insert into bind_variables (code) values('9') 1

11 rows selected.

system@XE> alter system set cursor_sharing=force;

System altered.

system@XE> alter system flush shared_pool;

System altered.

Execute procedure and query again...

CURSOR_SHARING=FORCE

SQL_TEXT EXECUTIONS
------------------------------------------------------------ ----------
insert into bind_variables (code) values (:B1) 10
insert into bind_variables (code) values(:"SYS_B_0") 10

2 rows selected.

In first example you could see how Oracle uses bind variables for reusing sql statements.

Next, after cursor_sharing=force, you could see that ten inserts using literals have been converted into single statement using :"SYS_B_0" which has executed ten times. The statement what was already using bind variables was unaltered and executed ten times.

From this example you could see that cursor_sharing=force can't bee replacement for not using bind variables.

Cursor sharing could help us to keep application running, but developers should work on correcting badly written code.


After setting currsor_sharing=force latch contention happened much less but didn't disappeared and we still had problems. Sometimes in peak hours we had serious latch contention with "cache buffer chains" latches in queues and CPU highly used. In short we had serious performance problems with application.

We find out that there are two things that could be done to back application in normal functioning. One is restarting the instance, which is not the most desirable way of fixing problems, and the other is flushing shared pool.


After executing "ALTER SYSTEM FLUSH SHARED POOL" CPU load dropped and latch contention disappeared. Application backs to normal functioning with acceptable performance.


Purpose of shared pool is to cache executable versions of SQL and PL/SQL statements. This allows multiple executions of the same SQL or PL/SQL code to be performed without hard parsing, which results with significant reductions in CPU, memory, and latch usage. We should aviod application designs that result in large number of users issuing dynamic, unshared SQL statements.

If we execute "alter system flush shared pool" that clears all data from the shared pool in the SGA. We clear cached dictionary information and shared sql and pl/sql areas for sql statements, stored procedures, functions, packages, and triggers. This statement does not clear shared SQL and PL/SQL areas for items that are curently being executed, and objects that are pinned in memory with DBMS_SHARED_POOL.KEEP.


I've searched Metalink for bugs and problems related to setting cursor_sharing=force and found out some bugs and problems related to that setting. It is very possible that we are having problems with shared pool because of cursor_sharing=force setting. Probably, new patchset could resolve some of the problems with cursor_sharing bugs but we do not have intention to apply new patchset before developers correct bad application code.

Use bind variables anytime you can and your application will have better performance and scalability, if not - hope for the best :)


RELATED DOCUMENTS:
http://www.akadia.com/services/ora_bind_variables.html
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/memory.htm#i34608