DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Optimization

  1. #1
    Join Date
    Mar 2001
    Posts
    131

    Unhappy


    Can any one tell me what is cost based optimization of DB.
    How can we do on database.....?
    What is DBA's role in that ?

    This we want for better response of the database.

    Please explain in detail.

    Thanks

  2. #2
    Join Date
    Jul 2000
    Posts
    243
    Hi

    look at the oracle documentation for database tunning. the issue id to big to be answerd onthis furom.

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    In Oracle there is an Optimiser which helps u to optimise ur queries.The Optimiser mode in most of the cases is CHOOSE(Default).CHOOSE means that Oracle will first find out all the execution plans for the query and it will choose the least cost execution plan.CHOOSE will come into effect only whn u have analysed ur tables used in the query.
    There is also a RULE based optimiser which will look at thre sql query and will choose its own execution plan.Apart from this there are other optimiser modes such as FIRST_ROWS,ALL_ROWS......
    The best mode to operate ur database is in CHOOSE mode and if u are using this mode (by default) try to analyze all ur tables which helps u to improve the performance.
    In case of any help please be free to write to me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  4. #4
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    CBO

    Hi, 2nd May 2001 15:30 hrs chennai

    if you need to know what is cost based optmizer first
    let me tell you what is a major difference between Rule and cost based optimizer.

    Rule Based:

    I)Uses a Ranking system
    II)Syntax driven.

    Cost Based:

    I)Chooses least cost Path
    II)Statistics driven.

    ===

    As already mentioned in the above postings if the default CHOOSE sees that if the Statistics are not available or generated due to or by Using Analyze command(The Generated statistics are updated in DBA_INDEXES,DBA_TABLES ect....) then it will use rule based optimizer.

    The rule based has complete set of rules for ranking access paths.Only exp people know to understand this rules and rank accordingly.

    In the cost based optmizer it examines each statements and all possible access path to the data.It then calculates the resource cost of each access path and chooses the least path i.e less expensive.The CBO is based on number of logical reads.

    Also many of the oracle new features like partitioning etc are supported only by CBO.

    Cheers

    padmam

    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Padman's response is pretty much on-target.

    Rohitsn's is not. CHOOSE, as Padman sort-of said , will:

    - See if any of the objects in the query have statistics.
    - If they do, the CBO will be used
    - If not, the RBO will be used.

    But as shawish_sababa said, this is a *very* large and complex issue - do not go into it blindly. You will want to do a lot of reading before jumping into this!

    - Chris

  6. #6
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    More on CBO

    Hi Upesh, 2nd May 2001 19:00 hrs chennai

    The links at the bootom covers more on CBO as many have said in this thread its not easy to explain all about CBO.

    http://safari1.oreilly.com/table.asp...radba&snode=71

    http://www.akadia.com/services/orati...mizer/optm.htm

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

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


Click Here to Expand Forum to Full Width