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!!