Performance Improvement - Part 2: Hard parse is not bad

Performance Improvement - Part 2: Hard parse is not bad

In previous post, we have seen the soft parsing is doing less operation comparing with hard parsing.

Please have a look on below post before read this one.
http://karthikeyanbaskaran.blogspot.com/2010/07/performance-improvement-part-1-parsing.html

The Hard parse includes the parse, optimize, generation of the plan for the query and soft parse skips optimize and generation of the plan. So we decided that hard parse is not good for application performance.

Some times soft parsing is having performance issue. The below scripts proves that.
SQL@ORA10G> create table t
2 as
3 select case when rownum = 1
4 then 1 else 99 end id, a.*
5 from all_objects a;

Table created.

SQL@ORA10G> create index t_idx on t(id);

Index created.


SQL@ORA10G> analyze table t compute statistics;

Table analyzed.


The WHERE ID=1 condition will return one record and WHERE ID=99 will return all of the rest (about 40,000 records). Also, the optimizer is very aware of this fact, we can definitely see different plans for different inputs, as we are passing the literals in the WHERE clause.

SQL@ORA10G> set autotrace traceonly explain
SQL@ORA10G> select * from t where id = 1;

Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=141)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=141)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)

SQL@ORA10G> select * from t where id = 99;

Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=137 Card=34323 Bytes=4839543)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=137 Card=34323 Bytes=4839543)





From Oracle9i Database Release 1 through Oracle Database 10g Release 2, Oracle Database will wait until the cursor is opened to do the actual optimization of the query—it will wait for the bind variable value to be supplied by the application before figuring out the right way to optimize the query. This is called bind variable peeking, when the optimizer first looks at the bind values and then optimizes the query.


In this case, however, depending on which inputs are used to first run the query, the database will either choose a full scan or an index range scan plus table access by index rowid. And in Oracle9i Database Release 1 through Oracle Database 10g Release 2, that is the plan that will be used to execute the SELECT * FROM t WHERE ID = :ID query, regardless of the subsequent bind values, until the query is hard-parsed and optimized again.

SQL@ORA10G> exec :id := 99

PL/SQL procedure successfully completed.

SQL@ORA10G> select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=137 Card=20391 Bytes=1814799)

1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=137 Card=20391 Bytes=1814799)




So we started off with ID=99 as the bind, and the optimizer chose a full scan. Therefore, regardless of the bind value, the database will execute a full scan from now on. For example:
SQL@ORA10G> exec :id :=1

PL/SQL procedure successfully completed.

SQL@ORA10G> select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=137 Card=20391 Bytes=1814799)

1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=137 Card=20391 Bytes=1814799)



The above result demonstrates that it is not going for the index range scan or table access by index rowid. In this case, the logical I/Os (Consistent gets) are more because of the FULL table scan instead of the index scan.

The result proves the “poorly performing query”.