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.