-
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
-
Hi
look at the oracle documentation for database tunning. the issue id to big to be answerd onthis furom.
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|