Consistant gets and the cost..

[alison]

I was wondering if you could clarify something.

When I change the optimizer mode from first_rows_10 to first_rows_100 the cost
of the plan goes up, even though the consistant_gets and physical reads goes
down.

I do see that the cardinallity gets higher but still not sure why the cost goes
up instead of down?

SYSTEM-ORCL->alter session set optimizer_mode = first_rows_10;

Session altered.

Elapsed: 00:00:00.00
SYSTEM-ORCL->select * from customers c, sales s
2 where c.cust_id = s.cust_id
3 and cust_last_name = 'Smith'
4 and s.time_id <= '31-DEC-98';

359 rows selected.

Elapsed: 00:06:01.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=22 Card=10 Bytes=2890)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=22 Card=1
Bytes=130)
2 1 NESTED LOOPS (Cost=22 Card=10 Bytes=2890)
3 2 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (FULL) OF 'SALES' (Cost=3 Card=201 Bytes=31959)
5 2 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'CUST_CUST_LAST_NAME_IDX'
(NON-UNIQUE) (Cost=1 Card=641)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
877531 consistent gets
2731 physical reads
0 redo size
21137 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
359 rows processed

SYSTEM-ORCL->rem Now with FIRST_ROWS_100
SYSTEM-ORCL->pause

SYSTEM-ORCL->alter session set optimizer_mode = first_rows_100;

Session altered.

Elapsed: 00:00:00.00
SYSTEM-ORCL->select * from customers c, sales s
2 where c.cust_id = s.cust_id
3 and cust_last_name = 'Smith'
4 and s.time_id <= '31-DEC-98';

359 rows selected.

Elapsed: 00:00:01.09

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=61 Card=100 Bytes=15900)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (Cost=61)
2 1 NESTED LOOPS (Cost=61 Card=100 Bytes=15900)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=5 Card=1
Bytes=130)
4 3 INDEX (RANGE SCAN) OF 'CUST_CUST_LAST_NAME_IDX' (NON-UNIQUE)
(Cost=3 Card=1)
5 2 PARTITION RANGE (ITERATOR)
6 5 BITMAP CONVERSION (TO ROWIDS)
7 6 BITMAP AND
8 7 BITMAP MERGE
9 8 BITMAP INDEX (RANGE SCAN) OF 'SALES_TIME_BIX'
10 7 BITMAP INDEX (SINGLE VALUE) OF 'SALES_CUST_BIX'




Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
3191 consistent gets
497 physical reads
0 redo size
20130 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
359 rows processed 877531 consistent gets[/B][/B] [B]

[sreddy]

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.

This is my analogy. May be right or may be wrong.

[alison]

Thanks for the follow-up but I'm not quite sure what you are saying?

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

Thanks for your continued help

Allie

[sreddy]

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.

[sreddy]

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.

[alison]

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

877531 consistent gets vs. 3191 consistent gets
2731 physical reads vs. 497 physical reads
359 rows processed vs. 359 rows processed

Why would the first one be considered a better plan???

Cheers A

[sreddy]

I wish I would anser your question but even that takes me to spend some time on Optimizer Internals (not just easy task just read pages and come to conclusions).

When you find free time just read P&T Guide Optimizer section(I will once again 9i , read sometime ago on 8i in 2003 when I had terrible problems with 8173 optimizer).

[abhaysk]

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

Originally posted by alison
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

Do u think its a slight diff? -- Its hell lotta..

Originally posted by alison
but going back to the stats....

877531 consistent gets vs. 3191 consistent gets
2731 physical reads vs. 497 physical reads
359 rows processed vs. 359 rows processed

Why would the first one be considered a better plan???

Cheers A

Well first one is worst..who is telling thats better?

Abhay.

[slimdave]

It wouldbe interesting to see the execution plan/costs/stats 'n'all for an "all rows" optimized version of this query.

[alison]

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.

Plan 1 num_rows_10
Cost 22
877531 consistent gets
2731 physical reads
359 rows processed

Plan 2 num_rows_100
Cost 61
3191 consistent gets
497 physical reads
359 rows processed


Thanks for your continued interest...