0

When building and tuning a query in Oracle, speed is generally the main concern for the developer. However, in tuning a particular query, I recently tried the FIRST_ROWS and NO_CPU_COSTING hints and an execution plan was generated that is 80% faster than the previous plan in execution time, but at a 300% higher cost. There is very little I/O in the execution plan, and it appears that all the additional cost comes from a nested loop outer join between two views.

This query is paginated, so I will only ever need the first few hundred rows. The lack of significant I/O leads me to think that this query will not be cache-dependent, and at first glance it seems like the way to go. However, since I've never seen a query increase in speed and cost so much at the same time, I'm not sure what the drawbacks to using this query might be. Are there any?

APC
  • 144,005
  • 19
  • 170
  • 281
monitorjbl
  • 4,280
  • 3
  • 36
  • 45
  • the two hints you mentioned are very nested loop oriented - NL is useful for retrieving the first N rows and it's a pretty simple approach there for very cheap CPU wise. the alternative is merge sort for example - mainly used for joining two large tables. it's CPU expensive because it requires a sort. hash join is also CPU expensive because it requires applying a hash function. i don't know specifically about your case but most systems are disk bound and not CPU bound so decreasing the amount of disk reads cause lower cost. – haki Apr 22 '13 at 16:23
  • Actually, I think accuracy is more important than speed. – Dan Bracuk Apr 22 '13 at 16:27
  • I have worked on systems with more than 500,000 rows in a table. I think speed is a must or it can hurt the whole system. – Shawn Altman Apr 22 '13 at 16:35
  • Do you mean it is 80% faster when fetching **all** rows, or when fetching the first screenful? If fetching just the first screenful then that is exactly as expected - the total cost for the full query is higher, but the elapsed time for the first few rows in shorter. – Tony Andrews Apr 22 '13 at 16:38
  • @TonyAndrews The query itself is paginated, so the resulset is relatively small, under a few hundred rows in the worst case. – monitorjbl Apr 22 '13 at 16:44
  • How did you get the cost - from Explain Plan or from a SQL Trace? It appears to me that Explain Plan doesn't take pagination into account, it shows the same cost as if fetching all rows, which would overstate the cost for your query. – Tony Andrews Apr 22 '13 at 17:00
  • @TonyAndrews It is from an Explain Plan. I was not aware that this was a limitation, do you have a source? I know that if you just have a ROWNUM limiter, the plan will show a COUNT STOP KEY step to indicate that the cursor does not read all rows. – monitorjbl Apr 22 '13 at 17:21
  • I don't have a source, it is just what I have found while using explain plan in Oracle 11.2.0.3.0. The "cost" is extortionate even though the "rows" value is 1! – Tony Andrews Apr 22 '13 at 17:35

1 Answers1

4

This is pretty typical of a query with an equijoin that is optimised to use a hash join when the full data set is required, and a nested loop when only the first few rows are needed, or where a sort is used for an order by on the full date set where an index can be more efficiently used for a subset.

Of course if the optimiser is not aware that you are only going to use a subset of the rows then it is not giving the cost for the query that you will actually execute, as it includes the cost for all the nested loop operations that are never going to execute.

However, there is nothing incorrect about the estimated cost, it just is what it is. If you want a more meaningful figure for your own understanding then use a rownum limit.

By the way, FIRST_ROWS is deprecated in favour of first_rows(1), first_rows(10), first_rows(100) or first_rows(1000).

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • You are correct! Apparently, that did not show up on the Oracle page I was looking at, but the FIRST_ROWS hint has indeed been deprecated. However, you are quoting the OPTIMIZER_MODE setting. The FIRST_ROWS hint has been deprecated in favor of FIRST_ROWS(n). I switched to FIRST_ROWS(1) and the plan showed a more reasonable cost. If you'll update your answer, I'll accept it :) – monitorjbl Apr 22 '13 at 19:19