Cost-Based Oracle Fundamentals (Expert's Voice in Oracle) by Jonathan Lewis

By Jonathan Lewis

The insights that Jonathan presents into the workings of the cost-based optimizer will make a DBA a greater clothier, and a Developer a greater SQL coder. either teams becomes greater troubleshooters - Thomas Kyte, vice chairman (Public Sector), Oracle company The query, "Why isn't really Oracle utilizing my index?" needs to be the most renowned (or maybe unpopular) questions ever requested at the Oracle support boards. you've got picked precisely the correct columns, you have got them within the excellent order, you've gotten computed records, you have checked for null columns&emdash;and the optimizer flatly refuses to take advantage of your index except you trace it. What may very well be going improper? If you may have suffered the disappointment of gazing the optimizer do anything thoroughly weird and wonderful whilst the easiest execution plan is completely visible, or spent hours or days attempting to make the optimizer do what you will want it to do, then this can be the booklet you would like. you are going to come to understand how the optimizer "thinks," comprehend why it makes errors, and realize the information styles that make it move awry. With this knowledge at your fingertips, you are going to store an incredible period of time on designing and trouble-shooting your SQL. The cost-based optimizer is just a bit of code that features a version of ways Oracle databases paintings. through employing this version to the statistics approximately your facts, the optimizer attempts to successfully convert your question into an executable plan. regrettably, the version cannot be ideal, your records cannot be ideal, and the ensuing execution plan will be faraway from excellent. In Cost-Based Oracle basics, the 1st publication in a sequence of 3, Jonathan Lewis&emdash;one of the key professionals during this field&emdash;describes the main primary components of the version, what the optimizer does along with your information, and why issues get it wrong. With this data, you may be capable of repair whole areas of difficulty, not only unmarried SQL statements, by way of adjusting the version or developing extra honest data.

Show description

Read Online or Download Cost-Based Oracle Fundamentals (Expert's Voice in Oracle) PDF

Similar oracle books

Oracle PL/SQL Programming 4Th Edition

За последние 10 лет книга Oracle PL/SQL Programming издательства O'Reilly's стала бестселлером среди книг по PL/SQL, процедурному языку базы данных Oracle. Снабженная примерами и полезными рекомендациями книнга является незаменимой как новичкам, так и гуру, как разработчикам на Oracle varieties, так и администраторам - используйте PL/SQL на полную мощь.

Oracle Streams 11g Data Replication

Grasp Oracle Streams 11g Replication allow real-time details entry and information sharing throughout your disbursed framework utilizing the professional info during this Oracle Press advisor. Oracle Streams 11g facts Replication explains how one can manage and administer a unified company information sharing infrastructure.

Oracle Application Express 4 Recipes

Oracle software show four Recipes offers an example-based method of studying software exhibit - the ground-breaking, quick software improvement platform incorporated with each Oracle Database license. The recipes layout is perfect for the quick-study who simply desires an outstanding instance or to kick begin their pondering and get pointed within the correct course.

Extra info for Cost-Based Oracle Fundamentals (Expert's Voice in Oracle)

Sample text

In other words, the optimizer’s execution plan may not be exactly the run-time execution path, and the speed of the run-time execution path may be affected by an unlucky choice of data. You will see examples of this in Chapter 9. Summary The main purpose of this very short chapter is to warn you that there are no quick and snappy answers to questions like Why did Oracle do X? At the very least, you have three optimizer modes and two (possibly three) major versions of Oracle to check before you start to formulate an answer.

This is because the default value in 10g for the parameter optimizer_dynamic_sampling is 2 (which means use dynamic sampling on any table without statistics), rather than 1, as it was in 9i. Another parameter associated with the optimizer_mode is optimizer_goal. ora). So What Is the Cost? One of the commonest questions about the CBO on the Internet is What does the cost represent? This is usually followed by comments like According to explain plan, the cost of doing a hash join for this query is 7 million and the cost of a nested loop is 42—but the hash join completes in 3 seconds and the nested loop takes 14 hours.

Oracle can do all sorts of transformations to your SQL before optimizing it. This means that an execution plan you were expecting to see may have been eliminated by a transformation before the optimizer started to do any costing. On the positive side, though, the basic arithmetic is always the same. Cunning refinements, little tweaks, new features, and dirty tricks are thrown in to confuse the issue—but 95% of everything that the optimizer does can be described and explained reasonably accurately in just a few chapters.

Download PDF sample

Rated 4.60 of 5 – based on 8 votes