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..
rajesh@11GR2> create or replace function parse_list(p_varchar_list varchar2,p_delimiter in varcha
2 return vcarray
3 pipelined
4 as
5 begin
6 for x in ( select
7 trim( substr (txt,
8 instr (txt, p_delimiter, 1, level ) + 1,
9 instr (txt, p_delimiter, 1, level+1)
10 - instr (txt, p_delimiter, 1, level) -1 )
11 )
12 as discrete_value
13 from (select p_delimiter||p_varchar_list||p_delimiter txt from dual)
14 connect by level <= length(p_varchar_list)-length(replace(p_varchar_list,p_delimiter,'')
15 loop
16 pipe row(x.discrete_value);
17 end loop;
18 end;
19 /
Function created.
Elapsed: 00:00:00.01
rajesh@11GR2>
rajesh@11GR2> variable x varchar2(40);
rajesh@11GR2> exec :x := 'A_B_C';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> set serveroutput off;
rajesh@11GR2>
rajesh@11GR2> select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t;
COLUMN_VALUE
-------------
A
B
C
Elapsed: 00:00:00.01
rajesh@11GR2>
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID 2da7fr1xbuv36, child number 5
-------------------------------------
select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t
Plan hash value: 58440541
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST | 8168 | 16336 | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
13 rows selected.
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.
rajesh@11GR2>
rajesh@11GR2> select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t;
COLUMN_VALUE
-------------
A
B
C
Elapsed: 00:00:00.00
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID 2da7fr1xbuv36, child number 6
-------------------------------------
select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t
Plan hash value: 58440541
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST | 6 | 12 | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Note
-----
- cardinality feedback used for this statement
17 rows selected.
Elapsed: 00:00:00.07
rajesh@11GR2>
Apparently, now it hard parsed that query - it did not reuse the plan. We know that it hard parsed since it just created child #6 - and we can see the plan is different - the row count is much lower.
Now, in this example only the estimated row counts changed - The actual plan is same ( since the query is very simple ) will this affects real world application codes?
rajesh@11GR2> create table t
2 nologging
3 as
4 select * from all_objects;
Table created.
Elapsed: 00:00:05.87
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t
2 ( case when owner = 'SCOTT' then owner
3 when owner = 'APPQOSSYS' then owner
4 when owner = 'OWBSYS' then owner
5 else null end
6 ) nologging;
Index created.
Elapsed: 00:00:00.48
rajesh@11GR2>
rajesh@11GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'T',
5 estimate_percent=>100,
6 method_opt=>'for all columns size 254',
7 cascade =>true);
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.01
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> variable y varchar2(40);
rajesh@11GR2> exec :y := 'SCOTT_APPQOSSYS_OWBSYS';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> with datas as
2 (
3 select * from table ( cast( parse_list(:y,'_') as vcarray ) )
4 )
5 select t.*
6 from t , datas
7 where case when owner ='SCOTT' then owner
8 when owner ='APPQOSSYS' then owner
9 when owner ='OWBSYS' then owner
10 else null end = datas.column_value;
.....
11 rows selected.
Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6vy77gcct2fgy, child number 1
-------------------------------------
with datas as ( select * from table ( cast( parse_list(:y,'_') as
vcarray ) ) ) select t.* from t , datas where case when owner ='SCOTT'
then owner when owner ='APPQOSSYS' then owner
when owner ='OWBSYS' then owner else null end =
datas.column_value
Plan hash value: 3303169200
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1002 (100)| |
| 1 | MERGE JOIN | | 195M| 18G| 1002 (97)| 00:00:13 |
| 2 | TABLE ACCESS BY INDEX ROWID | T | 71757 | 6867K| 5 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | T_IND | 11 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 8168 | 16336 | 30 (4)| 00:00:01 |
| 5 | COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST | 8168 | 16336 | 29 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."SYS_NC00016$"=VALUE(KOKBF$))
filter("T"."SYS_NC00016$"=VALUE(KOKBF$))
27 rows selected.
Elapsed: 00:00:00.09
as opposed to
rajesh@11GR2> with datas as
2 (
3 select * from table ( cast( parse_list(:y,'_') as vcarray ) )
4 )
5 select t.*
6 from t , datas
7 where case when owner ='SCOTT' then owner
8 when owner ='APPQOSSYS' then owner
9 when owner ='OWBSYS' then owner
10 else null end = datas.column_value;
.....
11 rows selected.
Elapsed: 00:00:00.12
rajesh@11GR2>
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6vy77gcct2fgy, child number 2
-------------------------------------
with datas as ( select * from table ( cast( parse_list(:y,'_') as
vcarray ) ) ) select t.* from t , datas where case when owner ='SCOTT'
then owner when owner ='APPQOSSYS' then owner
when owner ='OWBSYS' then owner else null end =
datas.column_value
Plan hash value: 4190747592
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| |
| 1 | NESTED LOOPS | | 22 | 2200 | 35 (0)| 00:00:01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST | 6 | 12 | 29 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 4 | 392 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_IND | 1 | | 0 (0)| |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."SYS_NC00016$"=VALUE(KOKBF$))
Note
-----
- cardinality feedback used for this statement
29 rows selected.
Elapsed: 00:00:00.10
rajesh@11GR2>
No comments:
Post a Comment