Why does Oracle prefer CPU cost to less I/O while generating execution plans?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Why does Oracle prefer CPU cost to less I/O while generating execution plans?

Hybrid View

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    Why does Oracle prefer CPU cost to less I/O while generating execution plans?

    I have been much into query tuning for last 1 month. Couple of times, I have seen that though CPU cost of a plan is low, the query essentially does not consume least time using this plan. By specifying an index hint, I was able to find a plan that caused much less I/O and hence the query took much less time. A day later when Oracle seemed to have collected new statistics on underlined tables and indexes, I found that query started using new indexes which again caused query to take more time to execute.

    My question are:
    1. How can we explicitly ask oracle to use an execution plan based on least I/O?
    2. Even in oracle 11g, does oracle not automatically pick an execution plan that will cause least I/O?
    lucky

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    1-- An execution plan profile can be assigned to a particular query - the one you like.
    2-- Oracle chooses a "reasonable" execution plan depending on performance statistics, there are no warranties.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    1-- An execution plan profile can be assigned to a particular query - the one you like.
    2-- Oracle chooses a "reasonable" execution plan depending on performance statistics, there are no warranties.
    Thanks PAVB. By assigning execution profile to a query, do you mean using INDEX/NO_INDEX hints? I have tried searching assigning execution profiles to a query but the searches returned results relevant to user profiles only. Is assigning execution profile to queries a new feature that I may not be aware of?
    lucky

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by mahajanakhil198 View Post
    Thanks PAVB. By assigning execution profile to a query, do you mean using INDEX/NO_INDEX hints? I have tried searching assigning execution profiles to a query but the searches returned results relevant to user profiles only. Is assigning execution profile to queries a new feature that I may not be aware of?
    PAVB, I was able to bring down the execution time of the query that was taking long to execute, that had low CPU cost but that had high I/O. Using Oracle generated plan, its consistent gets were around 4,00,000. When I hinted query to use a specific bitmap index, the consistent gets came down to around 1,75,000. At this moment, the query started using one more index that the query was not using earlier in its execution plan. When I hinted the query not to use that extra index, the consistent gets came down dramatically to 3000. Now, this serves my purpose well and the query executes in almost 1 second - down from 20 seconds!!
    So, the wider questions remains how in this world without using hit and trial, I can figure out a plan that has least IO!!
    lucky

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by mahajanakhil198 View Post
    ... the wider questions remains how in this world without using hit and trial, I can figure out a plan that has least IO!!
    That's why performance tunning has as much of art as it has of science
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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