Benchmarks on select statements

lesak

Hi,

I'm during preparing test on select statement to check what clause cause the most slow down. Therefore I prepare 3 select statement:
1) select which transform all columns in source table for oracle's functions like: substr, rpad, decode, nvl,upper, mod, greatest, length, power, instr etc..
2) select with big where clause (about 7 lines) which take from 2 tables
3) select which have where, group by, having and order by clauses
Perhaps I will have first result today. But I am very interested what is your experience in this subject?? Which case cause the biggest slow down?

Best.

108476

Hi Tut,

what is your experience in this subject??

Every database is different, but in general:

1) select which transform all columns in source table for oracle's functions like: substr, rpad, decode, nvl,upper, mod, greatest, length, power, instr etc..

Very low overhead.

2) select with big where clause (about 7 lines) which take from 2 tables

Long time to parse, and sometime Oracle does not get the cardinality right and joins the tables in the wrong order. To fix this issue:

1 - apply histograms. I have my notes here: http://www.dba-oracle.com/art_otn_cbo_p4.htm

2 - Use an ORDERED hint to enforce the best table join order: I have my notes here: http://www.dba-oracle.com/t_table_join_order.htm

3) select which have where, group by, having and order by clauses

Just make sure that you have a large enough PGA to void sorts to disk (sort_area_size, pga_aggregate_target)

Hope this answers your questions . . .

Donald K. Burleson
Oracle Press author
Author of "Oracle Tuning: The Definitive Reference":
http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm

Robert Geier

Why are you doing this ? The results you get will only be for the exact tables, indexes, data, and select statements that you test on, so will be useless to apply to any other case.

Including unnecessary functions and sorts in your select statement will waste CPU and IO resources, so only include the minimum to do the job.

In general the most wasteful processes are physical reads, and disk sorts.

lesak

Yes, I know but I based on real database select statements. Additionally I use oryginal data for tests. So I think it show me where is a bottleneck.

108476

Hi Tut,

what is your experience in this subject??

Every database is different, but in general:

1) select which transform all columns in source table for oracle's functions like: substr, rpad, decode, nvl,upper, mod, greatest, length, power, instr etc..

Very low overhead.

2) select with big where clause (about 7 lines) which take from 2 tables

Long time to parse, and sometime Oracle does not get the cardinality right and joins the tables in the wrong order. To fix this issue:

1 - apply histograms. I have my notes here: http://www.dba-oracle.com/art_otn_cbo_p4.htm

2 - Use an ORDERED hint to enforce the best table join order: I have my notes here: http://www.dba-oracle.com/t_table_join_order.htm

3) select which have where, group by, having and order by clauses

Just make sure that you have a large enough PGA to void sorts to disk (sort_area_size, pga_aggregate_target)

Hope this answers your questions . . .

Donald K. Burleson
Oracle Press author
Author of "Oracle Tuning: The Definitive Reference":
http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm

lesak

Thank you for your answer.

Best,
Tut

Nicolas Gasparotto

Don,

Use an ORDERED hint to enforce the best table join order

I'm wondering why it is not the default one then... Is it the magic hint ?
Very dangerous advice. Use hint makes Oracle ignore the optimizer, and makes your query plan fixed for... ever, regardless the statistics and tables changes.
Furthermore, here below a good sentence about the [ORDERED hint|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50601] :
"You might want to use the ORDERED hint to specify a join order if you know something that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could."
For me, that doesn't mean we enforce the best table join, or do we have to return in RBO mode whereas the ORDERED were the default one, and forget all the advantages of the CBO ?

Nicolas.

Nicolas Gasparotto

Could you explain how the Don's post is the correct answer ?

Nicolas.