The Tom Kyte Blog: Every day...

Thursday, December 15, 2005

Every day...

Every day I learn something new about Oracle I did not know the day before…

12 Comments:

melanie caffrey said....: Would seem particularly relevant to small data sets.

I wonder whether, with larger data sets, the rounding is still as much of an issue.

But definitely interesting.

I'm also curious as to what Siim's impetus for adding a higher precision to the plan table in the first place was.; Thu Dec 15, 01:50:00 PM EST
Anonymous said....: He didn't do it - he asked me how TOAD was doing it. So I said:
"Surely not"
Then I said
"Oh, it must be running code which converts cpu_cost to single-block equivalents"
Finally I said:
"Well, what a surprise, it's just doing explain plan then select - so the inserted values must be non-integer. Let's try changing the column definitions to see what happens."

10.2 still has integer columns, by the way.

Jonathan Lewis; Thu Dec 15, 04:05:00 PM EST
Alberto Dell'Era said....: It'll be relevant for LARGE data sets - eg a nested-loop where the driving(outer) table cardinality is very high, and the inner table costs are not integers - the rounding error will be magnified and make you wonder about the CBO calculations (as Jonathan shows on page 312 of his CBO book - probably this may explain the last "217 out of 7M" error, small in that test case with only 320 rows but not in general with eg tens of thousands rows).; Thu Dec 15, 05:47:00 PM EST
Anonymous said....: I wish Oracle would make these algorithms documented so we won't have to spend precious time guessing or trying to reverse engineer how Oracle works. Anyways, is there a need to know this level of detail?; Thu Dec 15, 07:11:00 PM EST
Gabe said....: The rounding surely will [ever so slightly] affect what gets reported through the plan_table … but it won’t affect the internal CBO calculations. I can understand Alberto’s argument that compounded rounding, in general, can affect the result of any mathematical algorithm. But here, the rounding occurring when inserting the plan_table row for some nested execution step won’t influence in any shape or form the overall reported cost … the overall reported cost itself will suffer that rounding effect because of the column accepting only integer values, but that’s about it.

So I don’t see any connection between the definition of the plan_table and the size of data sets. Unless, I’m missing something … ?!?!; Thu Dec 15, 08:06:00 PM EST
Alberto Dell'Era said....: Sure the overall cost is the same, but (paraphrazing Jonathan's book on page 312), "everybody knows" that in a nested loop:

join_cost = outer_cost
+ outer_card * inner_cost

now, say, outer_card=1000000 and inner_cost=1.34, and outer_cost=1.

You'll see in the plan
outer_cost = 1
join_cost = 1340001
outer_card = 1000000
inner_cost = round(1.34)=1

the numbers on the screen do not match; where does the 340000 additional cost come from ? If you can have higher precision instead (the higher, the better), you have less "misteries" to worry about.

Obviously, the larger the outer_card, the higher the rounding error impact.; Fri Dec 16, 07:47:00 AM EST
melanie caffrey said....: Keep in mind though, that Jonathan also notes, in that same chapter, that this is cause for greater alarm in 9i (where the numbers tend to round up) than it is in 10g (where the numbers should round to the nearest integer).

Curiously enough, after changing the cpu_cost and cost columns in my plan_table$ in 10.2.0.1 to have a greater precision of (38, 10), I do not see a greater precision when I perform a straight select from the plan_table$.

The rounding seems to take place just before or upon insert. So, irrespective of how those columns are defined, the end result appears to be stored as an integer.

Using the following values:

cpuspeed = 225
sreadtim = 16.471

I've plugged these into the formulas on the previous page, using Jonathan's same query, and should receive the following result (with my new version of the plan_table$):

14.1237191

Instead I receive a value of 14.

So not only has the optimizer rounded before inserting (regardless of my column definitions) but it appears to have rounded to the nearest integer, instead of rounding up.

This is good.; Fri Dec 16, 10:08:00 AM EST
Anonymous said....: Melanie,
That flat 14 looks a little unlikely. If you're talking about the test scripts, remember that I disable CPU costing at the top of script to ensure repeatability in the demos.
You may need to restart the session to get 10.2 using CPU costing again before checking what happens - I just tried a quick 10.2 test and got the same N d.p. output.

Jonathan Lewis; Fri Dec 16, 02:39:00 PM EST
melanie caffrey said....: Oooof. Of course.

That was it. I did *not* restart my session.

Thanks much, Jonathan.; Fri Dec 16, 03:22:00 PM EST
Anonymous said....: My error - I think I must have been using the 10.1 instance by mistake. I've been re-running the test to check the 10046 level 12, and it's doing nothing but integer values on the insert to the plan_table. So maybe the 10.1 feature is a bug rather than an enhancement.

Jonathan Lewis; Fri Dec 16, 04:36:00 PM EST
Caroline said....: I wonder if there is a wider problem with rounding - after a query from a colleague I created a little test test case and got some interesting results - not what I was expecting

Oracle 11.2.0.2 64 bit

drop table t1;
create table t1 ( case1_number number
, case2_38_12 number(38,12)
, case3_38_12x1 number(20,12)
, case_4_38_12x100 number(38,12)) ;
insert into t1 values ( round(177729.230000000010,2)
,round(177729.230000000010,2)
,round(177729.230000000010,2)
,100 * round(177729.230000000010,2));
select * from t1 ;

CASE1_NUMBER CASE2_38_12 CASE3_38_12X1 CASE_4_38_12X100
177729.23 177729.230000000010 177729.230000000010 17772923.000000000000

(Apologies for ugly output )- it seems that depending what you do - the rounding does not even happen.

Am reading further...; Wed Oct 05, 01:38:00 PM EDT
Thomas Kyte said....: @Caroline, I cannot reproduce, are you sure about that:

ops$tkyte%ORA11GR2> drop table t1;

Table dropped.

ops$tkyte%ORA11GR2> create table t1 ( case1_number number
2 , case2_38_12 number(38,12)
3 , case3_38_12x1 number(20,12)
4 , case_4_38_12x100 number(38,12)) ;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t1 values
2 ( round(177729.230000000010,2) ,
3 round(177729.230000000010,2) ,
4 round(177729.230000000010,2) ,100 * round(177729.230000000010,2)
5 );

1 row created.

ops$tkyte%ORA11GR2> set numformat 9999999999999.999999999999999
ops$tkyte%ORA11GR2> select * from t1 ;

CASE1_NUMBER CASE2_38_12 CASE3_38_12X1
------------------------------ ------------------------------ ------------------------------
CASE_4_38_12X100
------------------------------
177729.230000000000000 177729.230000000000000 177729.230000000000000
17772923.000000000000000; Wed Oct 05, 02:07:00 PM EDT

The Tom Kyte Blog

Thursday, December 15, 2005

Every day...

12 Comments:

About Me

Previous Posts