Forum Stats

  • 3,733,973 Users
  • 2,246,852 Discussions
  • 7,856,957 Comments

Discussions

Benchmarks on select statements

lesak
lesak Member Posts: 532 Blue Ribbon
edited September 2008 in General Database Discussions
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.

Best Answer

  • 108476
    108476 Member Posts: 2,184
    Accepted Answer
    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

Answers

  • Robert Geier
    Robert Geier Member Posts: 2,989
    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
    lesak Member Posts: 532 Blue Ribbon
    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
    108476 Member Posts: 2,184
    Accepted Answer
    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
    lesak Member Posts: 532 Blue Ribbon
    Thank you for your answer.

    Best,
    Tut ;)
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited September 2008
    Could you explain how the Don's post is the correct answer ?

    Nicolas.
This discussion has been closed.