Category Archives: Oracle Tuning

Oracle Tuning notes

Executing Linux command from Oracle PLSQL using Java Stored Procedure

In this blog I am going to use a JAVA stored procedure to execute Linux commands from PLSQL. In my earlier post we I have used a C program to achieve the same solution. But there are various methods to utilize it. To execute any OS command from Oracle PLSQL we have to create a […]
Read More »

Using EXISTS subqueries might be inefficient

Using EXISTS subqueries can be inefficient, the subquery executes for each and every row against the outer query’s table. In the below example used on EXISTS subqueries which is non compliant way of writing SQL: A compliant solution would be like as below:

Do not use Bulk collect without a LIMIT clause in Cursors

Normally when we use a cursor to fetch all the records at once we make use of BULK COLLECT. Without using a LIMIT clause will take all the records returned by the cursor and which will lead to extreme physical memory tiredness or we can say memory exhaustion.  So it’s always better to use LIMIT […]
Read More »

With a given scope do not use the variable more than once in PLSQL

It’s always good that we should not use a variable more than once with a given scope, as only one declaration of a variable is allowed in Oracle. If we write more variable then we are going to get PLSQL error i.e. PLS-00371 will be raised during runtime. The Non-Compliant way of writing the code is […]
Read More »

Use COUNT on indexed column or asterisk or 1

It is always prefered to use the count on indexed column or asterisk (*) or by representing it by one (1) for any table. For showing the example all the below query will give the same output but there will be a difference in the execution plan. Let us take the example of EMP table […]
Read More »

EXISTS vs DISTINCT

It is alwasys prefered not to use DISTINCT qualifier on the SELECT statement where we need to determine the information from owner end means where one to many relationship exists. For exmaple if we take departments from EMP table, then we see that departments has one to many relationship with employees column in EMP table. […]
Read More »

Using DECODE to Reduce Processing:

By using DECODE statement we can avoid scanning the same rows repeatedly or to join the same table repetitively. For let us take an example by to get the count and sum of salary for employee name like SMITH and from department 20. To achieve this we can use the below query : SQL>SELECT COUNT […]
Read More »

Escape calculation on Indexed columns

If the indexed column is been utilized on a function in the WHERE condition then the optimizer will not use the index on the column. So avoid calculations on indexed columns, so the best way to handel this is by applying function and concatinate it to the indexed column. But only MIN and MAX functions […]
Read More »

Instead of OR use UNION:

In a WHERE clause it is always advisable to use UNION in place of OR verb because using OR on a indexed column will perform a full-table scan instead of indexed retrieval by the oracle optimizer. So if the column is not indexed then use OR verb in a WHERE condition or clause and if […]
Read More »

Use UNION ALL instead of UNION:

CPU consumption are more when there is any SORT operation, so they are very expensive. The UNOIN operation sorts the result which eliminate the rows within the sub-query or removes the duplicates from the result set. Unless it is required to eliminate duplicate rows from the result set then it is preferred to use UNION […]
Read More »