As much as I can say about Statistics in 60 minutes … - PowerPoint PPT Presentation

as much as i can say about statistics in 60 minutes n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
As much as I can say about Statistics in 60 minutes … PowerPoint Presentation
Download Presentation
As much as I can say about Statistics in 60 minutes …

play fullscreen
1 / 86
As much as I can say about Statistics in 60 minutes …
278 Views
Download Presentation
greta
Download Presentation

As much as I can say about Statistics in 60 minutes …

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. As much as I can say about Statistics in 60 minutes… Thomas Kyte http://asktom.oracle.com/

  2. Agenda • Why do we gather statistics • Statistics Basics • Other ways to get statistics • How to defeat statistics • Some things to think about

  3. Why do we gather statistics?

  4. “Cardinality”

  5. “Wrong Plan => Wrong Cardinality”

  6. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> create table t 2 as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1, 3 decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.* 4 from all_objects a 5 / Table created. ops$tkyte%ORA11GR2> create index t_idx on t(flag1,flag2); Index created. ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt=>'for all indexed columns size 254' ); 5 end; 6 / PL/SQL procedure successfully completed.

  7. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select 'select * from t', num_rows 2 from user_tables where table_name = 'T' 3 union all 4 select 'select * from t where flag1 = "N"', num_rows/2 5 from user_tables where table_name = 'T' 6 union all 7 select 'select * from t where flag2 = "N"', num_rows/2 8 from user_tables where table_name = 'T' 9 union all 10 select 'select * from t where flag1 = "N" and flag2 = "N"', num_rows/2/2 11 from user_tables where table_name = 'T'; 'SELECT*FROMT' NUM_ROWS ------------------------------------------------- ---------- select * from t 72726 select * from t where flag1 = "N" 36363 select * from t where flag2 = "N" 36363 select * from t where flag1 = "N" and flag2 = "N" 18181.5

  8. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> select * from t where flag1='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 36499 | 3635K| 301 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 36499 | 3635K| 301 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FLAG1"='N')

  9. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select * from t where flag2='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 36227 | 3608K| 301 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 36227 | 3608K| 301 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FLAG2"='N')

  10. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select * from t where flag1='N' and flag2='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18181 | 1810K| 301 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 18181 | 1810K| 301 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FLAG2"='N' AND "FLAG1"='N')

  11. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ * 2 from t where flag1='N' and flag2='N'; no rows selected

  12. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.02 | 1080 | |* 1 | TABLE ACCESS FULL| T | 1 | 18181 | 0 |00:00:00.02 | 1080 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("FLAG2"='N' AND "FLAG1"='N')) 19 rows selected.

  13. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select /*+ dynamic_sampling(t 3) */ * from t where flag1='N' and flag2='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 470836197 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 612 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 612 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FLAG1"='N' AND "FLAG2"='N') Note ----- - dynamic sampling used for this statement (level=2)

  14. Wrong Plan => Wrong Cardinality SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), NVL(SUM(C3),:"SYS_B_02") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ :"SYS_B_03" AS C1, CASE WHEN "T"."FLAG1"= :"SYS_B_04" AND "T"."FLAG2"=:"SYS_B_05" THEN :"SYS_B_06" ELSE :"SYS_B_07" END AS C2, CASE WHEN "T"."FLAG2"=:"SYS_B_08" AND "T"."FLAG1"=:"SYS_B_09“ THEN :"SYS_B_10" ELSE :"SYS_B_11" END AS C3 FROM "OPS$TKYTE"."T" SAMPLE BLOCK (:"SYS_B_12" , :"SYS_B_13") SEED (:"SYS_B_14") "T") SAMPLESUB

  15. Small Change – but think about it… ops$tkyte%ORA11GR2> create table t 2 as 3 select substr(object_name, 1, 1 ) str, all_objects.* 4 from all_objects 5 order by dbms_random.random; Table created. ops$tkyte%ORA11GR2> create index t_idx on t(str,object_name); Index created. ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt => 'for all indexed columns size 254', 5 estimate_percent=>100 ); 6 end; 7 / PL/SQL procedure successfully completed.

  16. Small Change – but think about it… ops$tkyte%ORA11GR2> select count(subobject_name) from t t1 where str = 'T'; … -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 296 (0)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 19 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 292 | 5548 | 296 (0)| 00:00:04 | |* 3 | INDEX RANGE SCAN | T_IDX | 292 | | 4 (0)| 00:00:01 | --------------------------------------------------------------------------------------

  17. Small Change – but think about it… ops$tkyte%ORA11GR2> insert into t 2 select 'T', all_objects.* 3 from all_objects 4 where rownum <= 1; 1 row created. ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt => 'for all indexed columns size 254', 5 estimate_percent=>100 ); 6 end; 7 / PL/SQL procedure successfully completed.

  18. Small Change – but think about it… ops$tkyte%ORA11GR2> select count(subobject_name) from t t2 where str = 'T'; … --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 297 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 19 | | | |* 2 | TABLE ACCESS FULL| T | 293 | 5567 | 297 (1)| 00:00:04 | ---------------------------------------------------------------------------

  19. Statistics Basics

  20. Things Change over Time • In 10g and before, it was very common to “program” our statistics gathering • Unique set of inputs for each and every table and index • In 11g, you should as often as possible allow everything except the segment name and DOP to default • Why is this? • However…

  21. You are being WATCHED! ops$tkyte%ORA11GR2> create table t 2 as 3 select a.*, 4 case when rownum < 500 5 then 1 6 else 99 7 end some_status 8 from all_objects a 9 / Table created.

  22. You are being WATCHED! ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats(user,'T'); 3 end; 4 / PL/SQL procedure successfully completed.

  23. You are being WATCHED! ops$tkyte%ORA11GR2> select histogram 2 from user_tab_cols 3 where table_name = 'T' 4 and column_name = 'SOME_STATUS'; HISTOGRAM --------------- NONE

  24. You are being WATCHED! ops$tkyte%ORA11GR2> create index t_idx on t(some_status); Index created.

  25. You are being WATCHED! ops$tkyte%ORA11GR2> select * from t where some_status = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 36115 | 3526K| 300 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 36115 | 3526K| 300 (1)| 00:00:04 | --------------------------------------------------------------------------

  26. You are being WATCHED! ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats( user, 'T' ); 3 end; 4 / PL/SQL procedure successfully completed.

  27. You are being WATCHED! ops$tkyte%ORA11GR2> select histogram 2 from user_tab_cols 3 where table_name = 'T' 4 and column_name = 'SOME_STATUS'; HISTOGRAM --------------- FREQUENCY

  28. You are being WATCHED! ops$tkyte%ORA11GR2> select * from t where some_status = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 470836197 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 539 | 53900 | 10 (0)| 00:00 | 1 | TABLE ACCESS BY INDEX ROWID| T | 539 | 53900 | 10 (0)| 00:00 |* 2 | INDEX RANGE SCAN | T_IDX | 539 | | 2 (0)| 00:00 --------------------------------------------------------------------------------

  29. You are being WATCHED! ops$tkyte%ORA11GR2> select * from t where some_status = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 71683 | 7000K| 300 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 71683 | 7000K| 300 (1)| 00:00:04 | --------------------------------------------------------------------------

  30. Index Statistics – little known fact ops$tkyte%ORA11GR2> create table t 2 as 3 select * 4 from all_objects 5 / Table created. ops$tkyte%ORA11GR2> create index t_idx on t(object_id); Index created. ops$tkyte%ORA11GR2> select num_rows, sample_size, last_analyzed 2 from user_indexes 3 where index_name = 'T_IDX'; NUM_ROWS SAMPLE_SIZE LAST_ANAL ---------- ----------- --------- 72726 72726 17-JUL-12

  31. Other Ways to Get Statistics

  32. Other ways to get statistics • DBMS_STATS.SET_xxx • If you know the statistics, just tell us • DBMS_STATS.COPY_xxx • If you have something representative, just use to them to start • Dynamic Sampling… • SQL Profiles • Extended Statistics • Cardinality Feedback • Cardinality Hint

  33. Dynamic Sampling • http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html • Google: site:www.oracle.com kyte dynamic • Already demonstrated • Levels • 2 – sample any unanalyzed table during hard parse • 3 – same as 2 but add “sample during guess” for single column guesses • 4 – same as 3 but add “sample during guess” for multi-column guesses

  34. SQL Profiles • Analyzing a query • Stores better estimated cardinalities • Gets these by using your workload against your data • Important to be done in the right environment • Are just statistics really, so yes, they can go stale • Applied at hard parse time

  35. SQL Profiles ops$tkyte@ORA11G> create or replace procedure p 2 as 3 cursor c1 4 is 5 select object_id, object_name 6 from sqlprof 7 order by object_id; 9 l_object_id sqlprof.object_id%type; 10 l_object_name sqlprof.object_name%type; 11 begin 12 open c1; 13 for i in 1 .. 10 14 loop 15 fetch c1 into l_object_id, l_object_name; 16 exit when c1%notfound; 17 -- .... 18 end loop; 19 end; 20 / Procedure created.

  36. SQL Profiles SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID call count cpu elapsed query current rows ------- ------ -------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 Execute 1 0.00 0.00 0 0 0 Fetch 10 0.07 0.10 659 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- total 12 0.07 0.10 659 0 10 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 410 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 10 SORT ORDER BY (cr=659 pr=0 pw=0 time=101152 us) 47487 TABLE ACCESS FULL SQLPROF (cr=659 pr=0 pw=0 time=47604 us)

  37. SQL Profiles ops$tkyte@ORA11G> declare 2 l_sql_id v$sql.sql_id%type; 3 begin 4 select sql_id into l_sql_id 5 from v$sql 6 where sql_text = 'SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID'; 7 dbms_output.put_line( 8 sys.dbms_sqltune.create_tuning_task 9 ( sql_id => l_sql_id, 10 task_name => 'sqlprof_query' ) || ' in place...' ); 11 dbms_sqltune.execute_tuning_task 12 ( task_name => 'sqlprof_query' ); 13 end; 14 / PL/SQL procedure successfully completed.

  38. SQL Profiles ops$tkyte@ORA11G> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sqlprof_query') 2 FROM DUAL; DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLPROF_QUERY') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION … ------------------------------------------------------------------------------- SQL ID : 3zfpa86satsm3 SQL Text: SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 99.45%) ------------------------------------------ Consider accepting the recommended SQL profile. execute :profile_name := dbms_sqltune.accept_sql_profile(task_name => 'sqlprof_query')

  39. SQL Profiles 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1044598349 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time| -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47487 | 1391K| | 546 (3)| 00:00:07 | | 1 | SORT ORDER BY | | 47487 | 1391K| 3736K| 546 (3)| 00:00:07 | | 2 | TABLE ACCESS FULL| SQLPROF | 47487 | 1391K| | 151 (2)| 00:00:02 | -------------------------------------------------------------------------------------- 2- Using SQL Profile -------------------- Plan hash value: 337606071 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10 | 300 | 3 (0)|00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| SQLPROF | 47487 | 1391K| 3 (0)|00:00:01 | | 2 | INDEX FULL SCAN | SQLPROF_PK | 10 | | 2 (0)|00:00:01 | ------------------------------------------------------------------------------------------

  40. Extended Statistics • https://blogs.oracle.com/optimizer/entry/extended_statistics • In fact, https://blogs.oracle.com/optimizer should be mandatory reading! • Create statistics on sets of columns (correlated columns) • Create statistics on expressions (functions)

  41. Extended Statistics ops$tkyte%ORA11GR2> create table t 2 as 3 select * 4 from all_objects; Table created. ops$tkyte%ORA11GR2> create index t_idx on t(owner,object_type); Index created. ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed.

  42. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where owner = 'PUBLIC' and object_type = 'JAVA CLASS'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9015 | 853K| 290 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 9015 | 853K| 290 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='PUBLIC')

  43. Extended Statistics ops$tkyte%ORA11GR2> select dbms_stats.create_extended_stats( user, 'T', '(owner,object_type)' ) x from dual; X -------------------------------------------------- SYS_STUXJ8K0YTS_5QD1O0PEA514IY ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed.

  44. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where owner = 'PUBLIC' and object_type = 'JAVA CLASS'; Execution Plan ---------------------------------------------------------- Plan hash value: 470836197 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 763 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 7 | 763 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX | 7 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='JAVA CLASS')

  45. Extended Statistics • See https://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload for how to have these column groups automatically built for you • Extended statistics on functions done exactly the same

  46. Extended Statistics ops$tkyte%ORA11GR2> create table t 2 as 3 select rownum a, -rownum b, all_objects.* 4 from all_objects; Table created. ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select 0.05 * count(*) from t; 0.05*COUNT(*) ------------- 3635.15

  47. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where (a+b)/2 > 50; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3635 | 383K| 321 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 3635 | 383K| 321 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("A"+"B")/2>50)

  48. Extended Statistics ops$tkyte%ORA11GR2> set autotrace off ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> select dbms_stats.create_extended_stats( user, 'T', '((a+b)/2)' ) x from dual; X -------------------------------------------------- SYS_STUS9G#61NMFNG0T#HK9W8062Y ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed.

  49. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where (a+b)/2 > 50; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 110 | 320 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 1 | 110 | 320 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("A"+"B")/2>50)

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