Showing posts with label Tuning. Show all posts
Showing posts with label Tuning. Show all posts

How to design a database - The untold way

Rule #1
The design of a database must only be thought after the through analysis of the user requirements.

Rule #2
Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1.

Rule #3
Never implement as per theory, but base your design on practical thought. Always adhere Rule #2.

What does this mean?
I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements.

Rule #1
The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes.

This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database.

Rule #2
This rule again double verifies the rule #1. The design must be using only using such oracle functionalities which are exactly required. Otherwise if a new feature is used without additional thought on it, it will affect the application performance and also may throw the application as useless.

For example, for an order entry screen there is one master-detail table identified as per user requirements. The normal way of going for design for such a scenario is for designing two tables with parent key (primary key) and child keys (foreign keys). But if we design the same structure say with Nested tables, it will affect the way the data is stored. It will also be very difficult for MIS applications which require just the child tables.

Rule #3
This rule reminds of the theory we learn when studying the database concepts. For example Normalization. We have all learned that all tables must be normalized. And normally we all adhere to this rule. But this is just a guideline rather than a rule.

For example in a small application (it is left to the user about what a small application is and what is a large application) the performance might not be of a big effect. But in large applications, this will prove to be fatal. We will take the case of normalization itself. If all the tables are normalized then oracle will be storing data in more than one tables for a transaction. This will cause more overhead for the database to fetch the data, maintain index storage, maintain data storage etc.

It is not though meant that normalization is bad. But normalization of tables which can be avoided should not be normalized. Do not think of your database design in which normal form, wheras think how well it can help the user in maintaining their data.

Thus the database design cirulates around these:
1. Collect necessary user requirements
2. Only after collecting user requirements think of database design
3. Design the database in such a way that it is user oriented (data oriented) rather than theory oriented
4. You need not use all the features that oracle offers.

This ends part 1 of Tuning guide. Next parts will be updated soon. Watch this space.

Tuning a LIKE-clause to use Index

The LIKE-clause can ignore indexes, causing queries to run forever while doing full table scans. This document describes how to tune such SQL statements by making use of Oracle Text or reverse key indexes.

Tuning the ‘LIKE’ Clause:

Generally, search arguments in the WHERE clause such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" prevents Oracle from using an index to perform the search (however, not always).

If you use LIKE in your WHERE clause, try to specify one or more leading characters if at all possible. For example, use LIKE 'm%' and not LIKE '%m'. If you specify a leading character, Oracle has a better chance of being able to use an index to perform the query - this will increase performance and reduce the load on the database server.

To avoid such full table scans, consider the following scenarios:

Case 1: Tuning the LIKE-clause by using Oracle Text indexes

Requirements:

A. Install and configure Oracle's TEXT (done as part of the installation process).

B. Check whether Oracle TEXT is installed by looking for the 'CTXSYS' schema.

The problem

I. Create a test table:

create table t as select * from tab;
CREATE INDEX normal_index ON t
(TABLE_NAME) NOPARALLEL;
 
SQL> select TABLE_NAME from t where TABLE_NAME LIKE '%SEG%';
TABLE_NAME
------------------------------
D_MKTSEG
SEG$

II. Run a query to demonstrate that it will do a full table scan:

SQL> set autotrace traceonly explain
SQL> select TABLE_NAME from t where TABLE_NAME LIKE '%SEG%';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'

III. Drop the index:

SQL> drop index normal_index;
 
Index dropped.

Solution:

I. Create an Oracle Text index on the columns that you would like to search:

SQL> create index xyz_oracle_txt_idx on t(TABLE_NAME) INDEXTYPE IS CTXSYS.CONTEXT;
Index created.

II. Change the LIKE-clause to an CONTAINS-clause - WHERE CONTAINS(TABLE_NAME, '%SEG%') > 0;

SQL> select TABLE_NAME from t WHERE CONTAINS(TABLE_NAME, '%SEG%')>0;
 
TABLE_NAME
------------------------------
D_MKTSEG
SEG$

III. Look at the SQL plan:

SQL> set autotrace traceonly explain
SQL> select TABLE_NAME from t WHERE CONTAINS(TABLE_NAME, '%SEG%')>0;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=24)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          24)
 
   2    1     DOMAIN INDEX OF 'XYZ_ORACLE_TXT_IDX' (Cost=0)

Much better!

Case 2: Tuning the LIKE-clause by using reverse key indexes

Another trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index - and then programmatically reverse the LIKE-clause to read "LIKE 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.

Steps:

1. Create reverse key index on columns that will be searched. For example, create a reverse key index on Cust_Name of the customer table:

CREATE INDEX Cust_Name_reverese_idx
ON customer(Cust_Name)
REVERSE;

2. Programmatically reverse the SQL LIKE-clause to read '%saliV%':

Existing Query:

SELECT * 
FROM customer
WHERE Cust_Name LIKE '%Vilas%'

New Query:

SELECT * 
FROM customer
WHERE Cust_Name LIKE '%saliV%';
 

SQL Tuning Guidelines for Oracle - Simple yet Effective!

Author: Kathy Gleeson

In this article I will discuss some SQL tuning guidelines that are simple but effective. SQL is the heart of the Oracle system. You can use many different SQL statements to attain the same result. It is often the case that only one statement will be the most efficient option in a given state of affairs. The guidelines below include information about whether one form of the statement is always more efficient or whether each statement is an alternative and the efficiency will differ depending on your application.

>>Oracle processing of SQL:

Oracle processes SQL in two steps:

  1. Parsing
  2. Execution.

Tuning may speed up your SQL by reducing either parsing or execution or both. Tuning SQL should only be done after your code is working correctly. Beware that there is an inevitable tug-of-war between writing efficient SQL and understandable SQL.

>>Oracle SQL Tuning - Identical SQL:

SQL cannot be shared within Oracle unless it is absolutely identical. Statements must match exactly in case, white space and underlying schema objects to be shared within Oracle's memory. Oracle avoids the parsing step for each subsequent use of an identical statement.

sql> SELECT NAME FROM S_CUSTOMER WHERE ID = 212; statement to match
sql> SELECT NAME FROM s_customer WHERE ID = 212; lower case
sql> SELECT NAME FROM S_CUSTOMER WHERE ID=212; white space
sql> SELECT NAME
FROM S_CUSTOMER
WHERE ID=212; white space

>>Oracle SQL Tuning - Standard SQL:

Use SQL standards within an application. Rules like the following are easy to implement and will allow more sharing within Oracle's memory.

- Use a single case for all SQL verbs
-
Begin all SQL verbs on a new line
- Align the verbs right or left within the initial SQL verb
- Separate all words with a single space

>>Oracle SQL Tuning - Bind variables:

Use bind variables. The values of bind variables need not to be the same for two statements to be considered identical. Bind variables are not substituted until a statement has been successfully parsed.

Sharable SQL

Non-sharable SQL

SELECT * FROM emp
WHERE emp_no = :B1; Bind value: 123

SELECT * FROM emp
WHERE emp_no = 123;

SELECT * FROM emp
WHERE emp_no = :B1; Bind value: 987

SELECT * FROM emp
WHERE emp_no = 987;

>>Oracle SQL Tuning - Table Aliases:

Use table aliases and a standard approach to table aliases. If two identical SQL statements differ because an identical table has two different aliases, then the SQL is different and will not be shared.

>>Oracle SQL Tuning - Prefix column names by Aliases:

Prefix all column names by their aliases when more than one table is involved in a query. This decreases the parse time and prevents future syntax errors if someone adds a column to one of the tables with the same name as a column in another table. (ORA-00918: Column Ambiguously Defined)

>>Oracle SQL Tuning - WHERE Clause:

Beware of WHERE clauses which do not use indexes at all. Even if there is an index over a column that is referenced by a WHERE clause included in this section, Oracle will ignore the index. All of WHERE clauses can be re-written to use an index while returning the same values. In other words, don't perform operations on database objects referenced in the WHERE clause.

SELECT account_name, trans_date, amount
WHERE SUBSTR (account_name,1,7) = 'CAPITAL';   

SELECT account_name, trans_date, amount
WHERE account_name LIKE 'CAPITAL%';

SELECT account_name, trans_date, amount
WHERE account_name || account_type = 'AMEXA';   

SELECT account_name, trans_date, amount
WHERE account_name = 'AMEX'
AND account_type = 'A';

SELECT account_name, trans_date, amount
WHERE TRUNC (trans_date) = TRUNC ( SYSDATE );   

SELECT account_name, trans_date, amount
WHERE trans_date BETWEEN TRUNC ( SYSDATE ) AND TRUNC ( SYSDATE ) + .99999;

SELECT account_name, trans_date, amount
WHERE account_name = NVL ( :acc_name, account_name);   

SELECT account_name, trans_date, amount
WHERE account_name LIKE NVL ( :acc_name, '%');

SELECT account_name, trans_date, amount
WHERE amount + 3000 < 5000;   

SELECT account_name, trans_date, amount
WHERE amount != 0;   

SELECT account_name, trans_date, amount
WHERE amount < 2000;

SELECT account_name, trans_date, amount
WHERE amount > 0;

SELECT account_name, trans_date, amount
WHERE amount NOT = 0;   

SELECT account_name, trans_date, amount
FROM TRANSACTION
WHERE amount > 0;  

>>Oracle SQL Tuning - Tuning the views:

Don't forget to tune views. Views are SELECT statements and can be tuned in just the same way as any other type of SELECT statement can be. All tuning applicable to any SQL statement are equally applicable to views.

>>Oracle SQL Tuning - HAVING Clause:

Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

Using HAVING Clause

Not Using HAVING Clause

SELECT region, AVG (loc_size)
FROM location
GROUP BY region
HAVING region != ' SYDNEY '
AND region != ' PERTH ';   

SELECT region, AVG (loc_size)
FROM location
WHERE region != ' SYDNEY '
AND region != ' PERTH ';
GROUP BY region;

>>Oracle SQL Tuning - Table Lookups:

Minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SELECTs or multicolumn UPDATEs.

Separate Subqueries

Combined Subqueries

SELECT emp_name
FROM emp
WHERE emp_cat = ( SELECT MAX (category)
FROM emp_categories)
AND emp_range = ( SELECT MAX (sal_range)
FROM emp_categories)
AND emp_dept = 0020;

SELECT emp_name
FROM emp
WHERE (emp_cat, sal_range)
= ( SELECT MAX (category), MAX (sal_range)
FROM emp_categories)
AND emp_dept = 0020;

>>Oracle SQL Tuning - Multiple Table Joins:

Consider the alternatives like EXISTS, IN and table joins when doing multiple table joins. None of these are consistently faster; it depends on your data. If there is a poor performer here, it's likely the IN clause. This query returns the employee names from each department in department category 'A'.

EXISTS

IN

Table Joins

SELECT emp_name
FROM emp E
WHERE EXISTS (
SELECT 'X' FROM dept
WHERE dept_no = E.dept_no
AND dept_cat = 'A');

SELECT emp_name
FROM emp E
WHERE dept_no IN
( SELECT dept_no FROM dept
WHERE dept_no = E.dept_no
AND dept_cat = 'A');

SELECT emp_name
FROM dept D, emp E
WHERE E.dept_no = D.dept_no
AND D.dept_cat = 'A';

>>Oracle SQL Tuning - DISTINCT vs. EXISTS:

Avoid joins that require the DISTINCT qualifier on the SELECT list in queries which are used to determine information at the owner end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer realizes when the subquery has been satisfied once, there is no need to proceed further and the next matching row can be fetched.
Below query returns all department numbers and names which have at least one employee.

SELECT DISTINCT dept_no, dept_name
FROM dept D,
emp E
WHERE D.dept_no = E.dept_no;    SELECT dept_no, dept_name
FROM dept D
WHERE EXISTS (
SELECT 'X'
FROM emp E
WHERE E.dept_no = D.dept_no);

>>Oracle SQL Tuning - UNION ALL:

Consider whether a UNION ALL will be adequate in place of a UNION . The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicate to be filtered before the first row is returned. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If your tables include no duplicate records, or you don't care if duplicates are returned, the UNION ALL is much more efficient.

UNION

UNION ALL

SELECT acct_num, balance_amt
FROM debit_transactions
WHERE tran_date = '31-DEC-95'
UNION
SELECT acct_num, balance_amt
FROM credit_transactions
WHERE tran_date = '31-DEC-95';  

SELECT acct_num, balance_amt
FROM debit_transactions
WHERE tran_date = '31-DEC-95'
UNION ALL
SELECT acct_num, balance_amt
FROM credit_transactions
WHERE tran_date = '31-DEC-95';

>>Oracle SQL Tuning - DECODE:

Consider using DECODE to avoid having to scan the same rows repetitively or join the same table repetitively. DECODE is not necessarily faster as it depends on your data and the complexity of the resulting query. Also, using DECODE requires you to change your code when new values are allowed in the field.

SELECT COUNT (*)
FROM emp
WHERE status = 'Y'
AND emp_name LIKE 'SMITH%';

SELECT COUNT (*)
FROM emp
WHERE status = 'N'
AND emp_name LIKE 'SMITH%';
SELECT COUNT ( DECODE (status, 'Y', 'X', NULL )) Y_count,
COUNT ( DECODE (status, 'N', 'X', NULL )) N_count
FROM emp
WHERE emp_name LIKE 'SMITH%';

>>Oracle SQL Tuning - Casting:

Oracle automatically performs casting or simple column type conversions when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle columns, if the type is supported in the programming language you are using.

Types of partitioned indexes

Indexes, like tables, may be partitioned. There are two possible methods to partition indexes. You may either:

 

Equi-partition the index with the table - Also known as a local index. For every table partition, there will be an index partition that indexes just that table partition. All of the entries in a given index partition point to a single table partition and all of the rows in a single table partition are represented in a single index partition.

 

Partition the index by range - Also known as a global index. Here the index is partitioned by range, and a single index partition may point to any (and all) table partitions.

 

In the locally partitioned index, the index entries in a given partition, point into exactly one table partition. The globally partitioned index diagram however, shows that the index entries in a global index may point into any or all of the table partitions.  Also, note that the number of index partitions may in fact be different than the number of table partitions.

 

Since global indexes may be partitioned by range only, you must use local indexes if you wish to have a hash or composite partitioned index. The local index will be partitioned using the same scheme as the underlying table.

Index skip scan

The index skip scan is a new execution plan in Oracle9i whereby an Oracle query can bypass the leading-edge of a concatenated index and access the inside keys of a multi-values index.

For example, consider the following concatenated index:

create index sex_emp_id on emp (sex, emp_id);

Prior to Oracle9i, this index could only be used with both sex and emp_id were present in the SQL query, or when the sex column was specified.  The following query would not be able to use the concatenated index:

select emp_id from emp where emp_id = 123;

The Oracle9i skip scan execution plan allows for the concatenated index to be used, even though sex is not specified in the SQL query.  This feature promises that there is no need to provide a second index on the emp_id column.  Oracle acknowledges that the index skip scan is not as fast as a direct index lookup, but states that the index skip scan is faster than a full-table scan (otherwise why would oracle introduce such a feature).

What Oracles does not mention is that the cardinality of the leading column has a direct impact on the speed of the index skip scan.  In our example, the first column, sex has two records (‘F’, and ‘M’).

While Oracle does not publish the internals of the index skip scan, we can infer from the execution plan that Oracle is internally generating multiple queries, thereby satisfying the query with multiple sub-queries:

SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)

   0 SORT (AGGREGATE)

        1 INDEX (SKIP SCAN) OF 'SEX_EMP_ID' (NON-UNIQUE)

Internally, Oracle9i is probably generating two queries and joining the resulting Row ID lists:

select emp_name from emp_where sex = ‘F’ and emp_id = 123

UNION

select emp_name from emp_where sex = ‘M’ and emp_id = 123;

The implications of using the index skip scan are clear:

  • Oracle skip scan execution plan performance will decrease according to the number of unique values in the high order key.   If the leading column were “state” with 50 values, Oracle would be issuing 50 index probes to retrieve the result set.

 

By Donald K. Burleson

Article courtesy: http://www.praetoriate.com/oracle_tips_skip_scan.htm

 

Comments from the author: If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm

 

 

Bind variables - The key to application performance

If you've been developing applications on Oracle for a while, you've no doubt come across the concept of «Bind Variables». Bind variables are one of those Oracle concepts that is key to application performance.

To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example:

SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;

Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself.

When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from id = 674 to id=234 and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas, which whilst it might not affect a single program running against a small set of data, can bring a multi-user system to it's knees if hundreds of copies of the program are trying to hard parse statements at the same time.

The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set.

The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substituion» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset.

Bind Variables in SQL*Plus

In SQL*Plus you can use bind variables as follows:

SQL> variable deptno number
SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;

What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed. This bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement)

Bind Variables in PL/SQL

Taking PL/SQL first of all, the good news is that PL/SQL itself takes care of most of the issues to do with bind variables, to the point where most code that you write already uses bind variables without you knowing. Take, for example, the following bit of PL/SQL:

create or replace procedure dsal(p_empno in number)
as
begin
update emp
set sal=sal*2
where empno = p_empno;
commit;
end;
/

Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable.

Dynamic SQL

In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.

Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:

create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set sal = sal*2 where empno = '||p_empno;
commit;
end;
/

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set
sal = sal*2 where empno = :x' using p_empno;
commit;
end;
/

And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.

The Performance Killer

Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:

Here is the Performance Killer ....

SQL> alter system flush shared_pool;
SQL> set serveroutput on;

declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds...' );
end;
/
101.71 Seconds...

... and here is the Performance Winner:

declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x'
using i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds...' );
end;
/
1.9 Seconds...

That is pretty dramatic. The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously.

Bind Variables in VB, Java and other applications

The next question is though, what about VB, Java and other applications that fire SQL queries against an Oracle database. How do these use bind variables? Do you have to in fact split your SQL into two statements, one to set the bind variable, and one for the statement itself?

In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables, and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database.

For example, Java has PreparedStatement, which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature.

Conclusion

Lastly, it's worth bearing in mind that there are some instances where bind variables are probably not appropriate, usually where instead of your query being executed many times a second (as with OLTP systems) your query in fact actually takes several seconds, or minutes, or hours to execute - a situation you get in decision support and data warehousing. In this instance, the time taken to hard parse your query is only a small proportion of the total query execution time, and the benefit of avoiding a hard parse is probably outweighed by the reduction in important information you're making available to the query optimizer - by substituting the actual predicate with a bind variable, you're removing the ability for the optimiser to compare your value with the data distribution in the column, which might make it opt for a full table scan or an index when this isn't appropriate. Oracle 9i helps deal with this using a feature known as bind variable peeking, which allows Oracle to look at the value behind a bind variable to help choose the best execution plan.

Another potential drawback with bind variables and data warehousing queries is that the use of bind variables disallows the potential for star transformations, taking away this powerful option for efficiently joining fact and dimension tables in a star schema.


Information retrieved from www.akadia.com