Oracle : Reduce your parsing times

All the queries that you execute in an Oracle database are parsed and then executed. However, is a nonsense(and a time wastefulness) to parse over and over the same query, even if executed by different users.

So, to avoid this, Oracle uses a 'Shared pool' area where all the cursors are cached after being parsed.

How it works? It's easy:

1. Your query is hashed;
2. Oracle searchs shared pool for the matching hash value;
3. Is it there? If so, execute the cursor;
4. Otherwise parse your query, hash it and put it in shared pool for future executions;

To take advantage of this feature of Oracle engine, we have to take care for:

1. Hashing is executed over all your query, so Case is important.

Select * from emp is different from Select * from EMP and therefore you will not take advantage of shared pool in your second execution; So, it's important that your developers team agree in Naming and Case conventions to take greater advantage of Oracle Shared Pool.

1. Where clauses are hashed too;

Select * from emp where emp_no = 1 is different from Select * from emp where emp_no = 2, use global variables and procedures whenever you can.

If you follow this rules, you will not solve all the performance problems from your application but will give a little step toward the perfection.

Oracle : SQLCODE and SQLERRM in Inserts

Today I came across an unexpected problem when executing an exception block inside a procedure :

My code was something like this :

insert into dc_errors (error_message,error_date) values (SQLERRM, SYSDATE);

and I allways obtain an annoying PL/SQL: ORA-00984: column not allowed here.

To solve this problem I've changed my code into :

v_exception := substr(SQLERRM, 1, 256);
insert into dc_errors (error_message,error_date) values (v_exception, SYSDATE);

with v_exception as varchar2(256) .