I would like to clarify the "generalization" that you should not use bind variables in a data warehouse. I'll try to be very exact. I wish they hadn't printed that "rule of thumb".

You should avoid a bind variable in the very unique situation where you have

o a column that you have generated histograms for via the analyze command AND

o it is known by you that the values in the column are very skewed AND

o people frequently query on that column AND

o you desire different plans to be generate for different values.

For example, the column in question contains the numbers 1 .. 100. 0.01% of the rows have a value of 1, 0.01% have a value of 2 (and so up upto 99). 99.01% of the rows have a value of 100 in this column. In this very special case, you would like the optimizer to use an index if the predicate contains "where that_column = some_number" and some_number is between 1 and 99. You DO NOT want the optimizer to use an index if some_number is 100 as scanning the index would be painfully slow in this case. If you just submit a query:

select * from that_table where some_column = :x

the optimizer has to guess and would either generate a plan that used an index or not -- making this plan optimial only for queries with x between 1 and 99 OR x = 100 but not both. In this VERY special case, you would attempt to avoid the use of bind variables.

Short of this very very special case, you want to use bind variables. This very very special case is not limited to a data warehouse -- it can happen in any database but it might be more common to a data warehouse. Also -- if your data warehouse runs tons of queries -- you might consider using bind variables even in this case for the queries in the range x between 1 and 99 and having the special case "100" be different to avoid chewing up space in your shared pool.


Now, to answer the questions...

1) no, the whole concept of the shared pool is to avoid the regeneration of query plans -- we "shared" them, many people are concurrently executing them. If we changed the plan based on the bind variables -- we could not be sharing a single image of the plan, we would each need our own.

You need an entirely new statement (or different environment with the same statement, like if you submit the query from a session with optimizer goal set to all_rows and someone else submits it with the optimizer goal set to first_rows, you might get 2 copies of the statement in the shared pool with different plans).

2) a hard parse is a parse of a query that was not found in the shared pool. We have to parse, qualify (figure out the emp means scott.emp), authorize (can you select from scott.emp), and finally optimize.

a soft parse is when we find the query in the shared pool. We hash the sql statement, search by the hash, verify the query applies to us and reuse it. We skip many of the steps above as they have already been done for us.

a reparse I guess is when you parse the same statement over and over. It is wasteful in a single program and could be avoided. For example, instead of coding:

for i in 1 .. 10 loop
execute immediate 'insert into t values ( :x)' using i;
end loop;

you could use dbms_sql to:

open_cursor
parse the insert
for i in 1 .. 10 loop
bind i
execute the statement
end loop
close the cursor

the first loop would have upto 10 soft parses. The second has 1.

3) at the bind step. when we goto execute. It happens AFTER optimization. Histograms can be used but their value is limited with bind variables.

4) only when you ask for it with the for columns.

5) see the method_opt parameter to the GATHER_* routines. You can specify this yes.

6) dbms_stats can be used to populate the data dictionary tables used by the CBO or it can populate tables outside of the data dictionary (to be transfered into the data dictionary later)