Results 1 to 4 of 4

Thread: db2 runstats

  1. #1
    Join Date
    Aug 2003
    Posts
    21

    db2 runstats

    What is the equivalent of db2 runstats in oracle ? is there any?

    if not, how does oracle do query optimization

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    A full answer to your question would run into many volumes. I would suggest Oracle technet or Google for a good place to start reading about Oracle's query optimisation.

    There are many different ways that Oracle does it, but the short and usual answer is that it looks at the predicates, considers available indexes and the selectivity for satisfying those predicates and forms an execution plan based on that.

    Ie, a table has a million rows.
    -Column1 is indexed.
    -Column2 is indexed.

    Your query says select something from table where colum1 = 'A' and column2 = 'B'.

    By looking at the indexes for Colum1 and Colum2 Oracle thinks that using Colum1='A' on the column1 index would return about 100,000 rows - then it would have to 'filter' them in memory to get all the Column2 = 'B'.

    It then looks at the column2 index and determines that using that index for value='B' would return about 50 rows. It would then have to 'filter' them in memory for column1 = 'A'.

    Usually, in memory 'filters' are going to be quicker than disk reads so it would opt to use column2 index.

    This is about as simplistic as saying a petrol engine works by burning fuel.

    I don't know what db2 runstats are but the name suggests post query analysis, whereas your question is related to pre-query execution plans.

    I truly don't mean to sound dismissive, but query optimisation on Oracle is a huge area, I don't think your question could be answered in any satisfactory form in a single post on here.

    Hth
    Bill

  3. #3
    Join Date
    Aug 2003
    Posts
    21
    Bill... Thanks for taking time to explain ...

    In db2, runstats command collects information about tables and indexes and stores them in database system tables ... for example, the number of rows in a table, number of pages used by the table, number of free pages etc. Index(b-tree) statistics will be number of levels, number of leaf pages, first key cardinality, second key cardinality etc ...

    It is based on these the optimizer decides a specific access path ... For example, if a table has million rows now but the runstats was done when the table had 50000 rows, the access path will be optimized based on the statistics available and not on the current actual table data...

    I would like to know if there is a command in Oracle to collect such statistics to help the optimizer decide on an access plan ... If there are no statistics, then what is the basis on which Oracle decides , ie how does Oracle know that the table has a million rows, index on column one can return 100,000 rows for 'A' etc ..

    Thanks for your valuable time ..




    Originally posted by billm
    A full answer to your question would run into many volumes. I would suggest Oracle technet or Google for a good place to start reading about Oracle's query optimisation.

    There are many different ways that Oracle does it, but the short and usual answer is that it looks at the predicates, considers available indexes and the selectivity for satisfying those predicates and forms an execution plan based on that.

    Ie, a table has a million rows.
    -Column1 is indexed.
    -Column2 is indexed.

    Your query says select something from table where colum1 = 'A' and column2 = 'B'.

    By looking at the indexes for Colum1 and Colum2 Oracle thinks that using Colum1='A' on the column1 index would return about 100,000 rows - then it would have to 'filter' them in memory to get all the Column2 = 'B'.

    It then looks at the column2 index and determines that using that index for value='B' would return about 50 rows. It would then have to 'filter' them in memory for column1 = 'A'.

    Usually, in memory 'filters' are going to be quicker than disk reads so it would opt to use column2 index.

    This is about as simplistic as saying a petrol engine works by burning fuel.

    I don't know what db2 runstats are but the name suggests post query analysis, whereas your question is related to pre-query execution plans.

    I truly don't mean to sound dismissive, but query optimisation on Oracle is a huge area, I don't think your question could be answered in any satisfactory form in a single post on here.

    Hth
    Bill

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes Oracle gathers statistics in a similar manner. They are simply referred to as table, index or schema statistics.

    There are a couple of ways to get these statistics up to date..
    - the analyze table command
    - the dbms_stats package

    analyze table is the simplest as shown below but there also other options for it. You should also be aware that Oracle has different optimiser modes which may ignore the statistics (rule based or cost based optimisation). They can also be overriden on a per query basis (optimiser hints) or instace wide global hints.

    analyze table <tablename> [compute|estimate] statistics [for all indexes]

    I wrote an introduction to Oracle SQL tuning, maybe it will be of use
    http://www.billmagee.co.uk/oracle/sqltune/index.html

    Hth
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •