Wednesday, April 28, 2010

Something new I learned about estimated cardinalities...

I've used pipelined functions and other table functions many times in the past. One of the drawbacks with using them is the fact that the optimizer has no clue what the estimated cardinality will be.

Another thing that has bothered me - is the question "why doesn't the optimizer learn from its mistakes". If it estimated a certain step in a given plan would return 5,000 rows - and it discovers through experience "it only returns 5 rows" - why doesn't it 'learn' from that.

Well, 11g is addressing both of these. I was aware of the 2nd issue being addressed - features like adaptive cursor sharing are all about that.

But I stumbled on a new feature (first available in 11.1 releases) that started affecting table functions (in 11.2) - that feature is called 'cardinality feedback'.

Normally, when you have a pipelined function - the estimated cardinality is computed based on your blocksize - the default number of rows that will come from it are based on your database block size. I have an 8k block size so....


ops$tkyte%ORA11GR2> create or replace type str2tblType
as table of varchar2(30);
2 /

Type created.

ops$tkyte%ORA11GR2> create or replace
2 function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
3 return str2tblType
4 PIPELINED
5 as
6 l_str long default p_str || p_delim;
7 l_n number;
8 begin
9 loop
10 l_n := instr( l_str, p_delim );
11 exit when (nvl(l_n,0) = 0);
12 pipe row ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
13 l_str := substr( l_str, l_n+1 );
14 end loop;
15 return;
16 end;
17 /

Function created.

ops$tkyte%ORA11GR2> column plan_table_output format a80 truncate
ops$tkyte%ORA11GR2> variable in_list varchar2(255)
ops$tkyte%ORA11GR2> exec :in_list := 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select *
2 from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2407808827

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0
| 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 29 (0
--------------------------------------------------------------------------------

.
The optimizer guesses 8168 rows. It all probability - the real number of rows is not anywhere near 8168. If we use this estimated row count in a bigger query - we'll probably end up with the wrong plan (I like to say - wrong card=wrong plan, right card=right plan - where card is cardinality).

Now, if I run the query and ask Oracle "what plan did you use", we get to see 'reality' - not an explain plan (explain plans are many times not representative of reality!) and reality said:


ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> select *
2 from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cu030hs8vrjjn, child number 1
-------------------------------------
select * from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t

Plan hash value: 2407808827

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100
| 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 29 (0
--------------------------------------------------------------------------------


13 rows selected.


Which is not any different from explain plan at this point. Reality says "I came up with a plan based on an estimated cardinality of 8168 rows"... However, the database actually *ran* the query this time - and the database has learned from its mistake:


ops$tkyte%ORA11GR2> select *
2 from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cu030hs8vrjjn, child number 2
-------------------------------------
select * from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t

Plan hash value: 2407808827

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100
| 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 6 | 12 | 29 (0
--------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement


17 rows selected.

.
Apparently, it hard parsed that query - it did not reuse the plan. We know that it hard parsed since it just created child #2 - and we can see the plan is different - the row count is much lower.

Now, in this example, onl y the estimated row counts changed - the actual plan is the same (the query is after all very simple). Will this affect real world queries?

Yes, it will :)
ops$tkyte%ORA11GR2> create table data
2 as
3 select *
4 from all_objects;

Table created.

ops$tkyte%ORA11GR2> create index data_idx on data(object_name);

Index created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'DATA' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> with T as
2 ( select distinct * from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t where rownum > 0 )
3 select * from data, t where data.object_name = t.column_value
4 /
....
10 rows selected.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID bv5ar1b8sft67, child number 0
-------------------------------------
with T as ( select distinct * from TABLE(cast( str2tbl( :in_list ) as
str2tblType) ) t where rownum > 0 ) select * from data, t where
data.object_name = t.column_value

Plan hash value: 892089582

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 319 (100)| |
|* 1 | HASH JOIN | | 13730 | 1528K| 319 (1)| 00:00:04 |
| 2 | VIEW | | 8168 | 135K| 30 (4)| 00:00:01 |
| 3 | HASH UNIQUE | | 8168 | 16336 | 30 (4)| 00:00:01 |
| 4 | COUNT | | | | | |
|* 5 | FILTER | | | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 29 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | DATA | 72236 | 6842K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")
5 - filter(ROWNUM>0)


27 rows selected.

.
Ugh, one of those (tongue in cheek) nasty full scans and hash joins (they are good - when they are appropriate, they aren't appropriate here...)

as opposed to

ops$tkyte%ORA11GR2> with T as
2 ( select distinct * from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t where rownum > 0 )
3 select * from data, t where data.object_name = t.column_value
4 /
...
10 rows selected.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID bv5ar1b8sft67, child number 1
-------------------------------------
with T as ( select distinct * from TABLE(cast( str2tbl( :in_list ) as
str2tblType) ) t where rownum > 0 ) select * from data, t where
data.object_name = t.column_value

Plan hash value: 3947981921

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 48 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 10 | 1140 | 48 (3)| 00:00:01 |
| 3 | VIEW | | 6 | 102 | 30 (4)| 00:00:01 |
| 4 | HASH UNIQUE | | 6 | 12 | 30 (4)| 00:00:01 |
| 5 | COUNT | | | | | |
|* 6 | FILTER | | | | | |
| 7 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 6 | 12 | 29 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | DATA_IDX | 2 | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | DATA | 2 | 194 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - filter(ROWNUM>0)
8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
- cardinality feedback used for this statement


33 rows selected.



Nice - it learned from the errors of its ways...


Note: this affects more than just table functions - read some of the links found on google to see other interesting examples.

Very nice.
POST A COMMENT

14 Comments:

Blogger Radoslav said....

Nice..

I had a problem with cardinality and table functions. I used to use cardinality hint (I know that's not a good practice to use undocumented hints) until I read this great article from Adrian Billington: http://www.oracle-developer.net/display.php?id=426

Wed Apr 28, 05:18:00 PM EDT  

Blogger Thomas Kyte said....

@Radoslav -

I agree, great article - but it is addressing an entirely different problem area, that of calling PLSQL from SQL in a where clause, it helps with costing that as a filter, but it doesn't help so much with the estimated cardinality of a table function.

But yes, that article looks excellent.


I've taught in the past about the cardinality hint, saying it is the only undocumented thing I really talk about - now, I'll be able to talk about it "less"

Wed Apr 28, 06:33:00 PM EDT  

Anonymous David Aldridge said....

Also worth a look: http://www.oracle-developer.net/display.php?id=427

Thu Apr 29, 04:26:00 AM EDT  

Anonymous Albert Spijkers said....

I notice I have a lot the learn about 11g/r2 eventhough I am already 11g OCP. The nicties are in the details. I will investigate the recomended blogs and the Oracle Documentation for some more insight!

Albert
DBA consulting
IT Consultant

Thu Apr 29, 06:13:00 AM EDT  

Blogger dmcghan said....

Tom,

I believe with 11g, and even 10g, you're able to omit the CAST function.

select *
from TABLE(str2tbl( :in_list )) t;

Regards,
Dan

Thu Apr 29, 06:47:00 AM EDT  

Anonymous Adrian Billington said....

@David,

Thanks for the link.

@Tom, @Radoslav,

The article mentioned by David covers nothing but cardinality for table and pipelined functions:

Setting cardinality for pipelined and table functions

It needs an update for cardinality feedback, but covers CARDINALITY, OPT_ESTIMATE, Dynamic Sampling and the Extensible Optimizer. (I think that this is probably the article that Radoslav was meaning to link to).

Regards,
Adrian

Thu Apr 29, 03:23:00 PM EDT  

Blogger Stew Ashton said....

Completely off the subject, but in a few hours asktom.oracle.com will be exactly 10 years old (judging by the oldest "first asked" date). I wish there were some way for all the beneficiaries to join in celebrating this event. I know very many have benefited from (and enjoyed) your methods, expertise, teaching skills, humor and passion. In my case, you are the main factor in a late-in-life career change that has made my work much more enjoyable and useful than before. There is no adequate way to thank you.

Sun May 02, 05:06:00 AM EDT  

Blogger Thomas Kyte said....

@Stew,

thank you very much for the kind words...

10 years...

Sun May 02, 09:58:00 AM EDT  

Blogger an said....

Hi,

Nice post and very informative. thanks for the great sharing.

Cheers

Mon May 03, 06:55:00 AM EDT  

Anonymous dario the boring said....

Great article and great feature.

Oracle learns! That seems to be good, but one day...

SQL> create trigger autonomous_update_to_avoid_mutating ...
ORA-09000: Dave, this conversation can serve no purpose anymore. Goodbye.
Disconnected
SQL> conn / as sysdba
Connected
SQL> shutdown immediate
ORA-09000: I'm sorry, Dave. I'm afraid I can't do that.
SQL> shutdown abort
ORA-09000: Daisy, Daisy, give me your answer do.

OT: great ten years.
I have made a bad habit. I google your site before documentation...
Then documentation, and later metalink (for bugs!). And the phrase "Bind variables" makes thing about "I can not state hard enough the importance of bind variables" (dixit you). Great, great site. You explain, give proofs AND solutions... There is no other site like that, for any technology, that I know. It should be part of the manual or certification courses.

Mon May 03, 07:23:00 AM EDT  

OpenID egorius said....

Tom,
I'm sort of afraid of these "learning from the errors" features because they introduce instability. What should one do in case of performance problem on a production? Just hope for the best? Try to reproduce the problem on a test instance (good luck)?

P.S. Congratulations on 10 years of asktom and thank you for your great work!

Tue May 04, 12:01:00 PM EDT  

Blogger Thomas Kyte said....

@Egoruis

it is sort of like a 'live' sql profile...

SQL Plan management can be used to bring "consistency" back - using that - we'll only use a new plan AFTER it has been verified (via testing and actually running the query in the background) - making the change somewhat safe.

But yes, some of these features (cardinality feedback, adaptive cursor sharing) do make things "change"

Tue May 04, 01:50:00 PM EDT  

Blogger Kevin said....

All of this work, starting with "dynamic_sampling", seems to be slowly working towards what has always seemed to be the optimal approach to query optimization: dynamic plans.

Specifically, a state of affairs whereby the CBO had the freedom to adjust its execution plans on-the-fly. During the execution of a query, as each step is completed, the contents are retained in memory and are sampled as a stand-alone table, and the resulting stats compared to the estimates of the original plan. Any substantial deviation from the original estimate causes the CBO to compute a new plan, using that step's result as a "table" with fresh statistics (in place of whatever joins, calculationss, etc., involved in it's derivation), and then executing the "rest" of the query using the new plan. In theory, each plan revision should contain fewer objects and thus be incrementally less computationally intensive than prior plans for that query, so this shouldn't tremendously impact the overall query execution time.

It would be the equivalent of planning a trip, and anytime you're actual path has wandered too far from your expected path, you stop to ask for directions...

Any chance anything like this might be under development?

Mon May 10, 01:10:00 PM EDT  

Blogger Thomas Kyte said....

@Kevin,

I doubt it would go as far as the "single steps within the sql execution plan" in general

There is the sunk cost concept that would be at work. Suppose a mistake was to be found in the plan - we guessed 5,000 rows and found 5. Does that mean we should start over?

The analogy of the trip does not hold water. During a trip, you don't have to go back to the origin in order to adjust your path. With a SQL query - most times (MOST times, I know there are exceptions) - you would have to basically start over.

Very hard to turn a nested loops join into a full scan hash join and vice versa.

So, I see it learning from its mistakes - but the logic you describe can only really happen when the plan has a branch in the middle and we decide which branch to take depending on some inputs to the branch (and we already do that in many cases - since 8i...)

Mon May 10, 02:42:00 PM EDT  

POST A COMMENT

<< Home