If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > db2 runstats

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Drunkard
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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 ..




Quote:
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
Reply With Quote
  #4 (permalink)  
Old
Drunkard
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On