If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Conistant Gets and Physical reads are at block level. When you read with first 10 rows depending upon in which block those rows exists you get your consitant gets and physical reads. If you look for first 100 you do not need to (expect to) read new blocks again if you have em in the same blocks as the first 10 rows. But yet to Optimizer process 100 instead 10 so, you see cost variation.

At the point in time of the optimizer making this cost, it doesn't know in which the block the rows will be in.. It finds that out when it FTS's or Index Reads...

So all the rows could be in 1 block or it may need to access 100 block to bring back the first 100 rows, and then access them and more again and again...

Also the optimizer has no knowledge if these blocks are cached in memory or they will require I/O.

My question is why is the cost increased when the physical and consistent reads are reduced.. I understand that the cost is an arbitrary figure that doesn't mean anything in real terms, but what apart from physical reads, consistent reads and sorts is contributing to this calculation...

Both are doing FTS and Index Scans and are joining with Nested Loops so why the difference...

If I understand your comment right, you say being the same optimizer execution path (same indexes use, nested loops use) for the query that returns 100 rows and 1 million rows there should not be cost difference. How can it be ?

Do you think amount of data for the query will not affect ? yes it will.

See the excerpt from 9i Perf&Tune Guide here:

The CBO can optimize a SQL statement for fast response when the parameter OPTIMIZER_MODE is set to FIRST_ROWS_n, where n is 1, 10, 100, or 1000, or FIRST_ROWS. A hint FIRST_ROWS(n), where n is any positive integer, or FIRST_ROWS can be used to optimize an individual SQL statement for fast response.

Fast-response optimization is suitable for online users, such as those using Oracle Forms or Web access. Typically, online users are interested in seeing the first few rows and seldom look at the entire query result, especially when the result size is large. For such users, it makes sense to optimize the query to produce the first few rows as quickly as possible, even if the time to produce the entire query result is not minimized.

With fast-response optimization, the CBO generates a plan with the lowest cost to produce the first row or the first few rows. The CBO employs two different fast-response optimizations, referred to here as the old and new methods. The old method is used with the FIRST_ROWS hint or parameter value. With the old method, the CBO uses a mixture of costs and rules to produce a plan. It is retained for backward compatibility reasons.

The new method is totally based on costs, and it is sensitive to the value of n. With small values of n, the CBO tends to generate plans that consist of nested loop joins with index lookups. With large values of n, the CBO tends to generate plans that consist of hash joins and full table scans.

The value of n should be chosen based on the online user requirement and depends specifically on how the result is displayed to the user. Generally, Oracle Forms users see the result one row at a time and they are typically interested in seeing the first few screens. Other online users see the result one group of rows at a time.

With the fast-response method, the CBO explores different plans and computes the cost to produce the first n rows for each. It picks the plan that produces the first n rows at lowest cost. Remember that with fast-response optimization, a plan that produces the first n rows at lowest cost might not be the optimal plan to produce the entire result. If the requirement is to obtain the entire result of a query, then fast-response optimization should not be used. Instead use the ALL_ROWS parameter value or hint.

More on COST

The cost represents units of work or resource used. The CBO uses disk I/O, CPU usage, and memory usage as units of work. So, the cost used by the CBO represents an estimate of the number of disk I/Os and the amount of CPU and memory used in performing an operation. The operation can be scanning a table, accessing rows from a table by using an index, joining two tables together, or sorting a row set. The cost of a query plan is the number of work units that are expected to be incurred when the query is executed and its result produced.

The access path determines the number of units of work required to get data from a base table. The access path can be a table scan, a fast full index scan, or an index scan. During table scan or fast full index scan, multiple blocks are read from the disk in a single I/O operation. Therefore, the cost of a table scan or a fast full index scan depends on the number of blocks to be scanned and the multiblock read count value. The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys. The cost of fetching rows using rowids depends on the index clustering factor.

Although the clustering factor is a property of the index, the clustering factor actually relates to the spread of similar indexed column values within data blocks in the table. A lower clustering factor indicates that the individual rows are concentrated within fewer blocks in the table. Conversely, a high clustering factor indicates that the individual rows are scattered more randomly across blocks in the table. Therefore, a high clustering factor means that it costs more to use a range scan to fetch rows by rowid, because more blocks in the table need to be visited to return the data.

Not really what I was meaning - I understand that the amount of rows returned will affect the cost - but both of these statements will return the same amount of rows - the only difference being is that one plan is optimised to return the first 10 rows fastest and the second it optimised to return the first 100 rows fastest - so it's not really a case of how many more a case of which will get you back the number you asked for fastest.

I also get that the optimiser mode has been changed to prefer response over throughput.

I guess this is more of an intellectual exercise that a problem solve. I just can't get my head round the idea of what is contributing to the cost...

As you quite rightly stated the execution plans are slightly different and I'm not disputing that this will have an impact on the costs but going back to the stats....

quote:
--------------------------------------------------------------------------------
Originally posted by alison
I just can't get my head round the idea of what is contributing to the cost...
--------------------------------------------------------------------------
Factor n in ur case..
----

Do you mean number, is that number of rows retrieved (the same for both runs - or the first_rows_n number?

---

And yes the plans are very different - I was just trying to simplify things to get to the answer that I wanted.

As I stated this is an intellectual exercise I was wondering why Oracle considered the first plan to have a lower cost than the second, considering that the first plan has to do significantly more disk I/O and consistent gets.

As regards to your response

Well first one is worst..who is telling that's better?

Oracle is telling me that that one is better because it has a cost of 22 rather than 61.