Others, who read Jonathan Lewis's great book - they can use them rationally, to understand WHY the optimizer did what it did. The costs of two different queries cannot be compared, not judgement as to performance may be made, you - you should definitely consider them random numbers. Geez.īut you know what - EVEN IF THEY WERE, IT TOTALLY PROVES MY POINT, factually I might add - not by belief.Īnyway, it is not bizzare - it is (as I said) to be expected. the costs that variable x set timing set autotrace select * from big_table aĠ SELECT STATEMENT Optimizer=CHOOSE (Cost=431 Card=54951 Bytes=5495100)ġ 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=431 Card=54951 Bytes=5495100)Ģ 1 INDEX (RANGE SCAN) OF 'OBJECT_NAME_IDX' (NON-UNIQUE) (Cost=52 Card=54951)ġ02352990 bytes sent via SQL*Net to clientĨ147825 bytes received via SQL*Net from clientġ101008 rows select /*+ full( a ) */ * from big_table aġ 0 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=431 Card=54951 Bytes=5495100)ġ28246913 bytes sent via SQL*Net to clientįrankly, I don't think anyone has accused me of "faking" a result to make a point before. answer, well, apparently you cannot really compare them. why are the performance characteristics so radically different? (hint - it is not a bug, repeat, it is not a bug. The queries do the same thing, they have the same cost.
(hey, i have a case study of that in my book actually - what happens when you assume something is true and it is false.) YOU ARE PROJECTING YOUR THOUGHTS OF HOW YOU WOULD HAVE DONE IT - instead of understanding how it is actually done. IT IS NOT A BUG, IT IS A DESIGN FEATURE, THE WAY IT WORKS. YOU CANNOT COMPARE THEM AND SAY "THIS WILL BE FASTER" THE COST HAS NOTHING WHATSOEVER TO DO WITH THE RUNTIME PERFORMANCE OF A QUERY. What we expect is the optimizer to permute all possibleĬombinations and pick up the plan with the least cost." "Comparing the same query using COST is perfect legitimate because that is what Remember v$sysstat is SYSTEM statistic, autotrace uses v$sessstat SESSION statistics. Physical reads select 8116-7612 from shows that whilst the query itself tool 76,412 logical IO's, there were only about 500 physical IO's performed - both via autotrace reporting as well as v$sysstat querying. Physical reads set autotrace traceonly select count(*) from all_objects Ĥ25 bytes received via SQL*Net from clientġ rows set autotrace select name, value from v$sysstat where name = 'physical reads' On a single user select name, value from v$sysstat where name = 'physical reads' Physical reads on v$sysstat is physical IO as well. It might have adjusted the over cost for the plan you wanted to be lower then the plan we did without the hint, or it may have made the plan we did without the hint extremely expensive (artificially inflated its cost).ĭon't compare them, you cannot - they might as well be random numbers. Why? Because the environment with the hint had us adjust the internal numbers we assign to various operations - in an attempt to make your hint "look like" the best way to go. Now, add a hint that makes the query run faster and check out its cost - it might be higher, might be lower then the first. The "cost" is just some artificial number we arrived at to select a query given a certain environment.
Many things influence the "cost" of a query. Those costs are specific to those queries. We cannot however take those 2 queries and compare the relative cost of each to the other. If we get another query - another similar query - we go through the same steps, build lots of plans, assign a cost, pick the one with the lowest cost from that set. These costs (which are ONLY visible to the optimizer) can be compared as they are for the same exact SQL using the same exact environment. At the end, we apply a function to derive the total cost of that query for each plan. Each step of the plan is assigned some relative cost. When we get a query, we come up with lots of plans. You cannot compare the cost of 2 queries with eachother.
0 kommentar(er)
