Maybe slightly OT at this point, dynamic sampling on TABLE() is disabled by default and needs a fix to be enabled to kick in (some details in doc id 6708183.8) On Fri, Dec 19, 2014 at 2:43 PM, Stefan Koehler <contact@xxxxxxxx> wrote: > Hi Jeff, > thank you for the code. Now the root cause is pretty obvious (and can be > proved), however your cardinality estimates for "t_values as num_tab" are > based on bind peeking and not dynamic sampling. I modified your code > slightly to run it in my test environment (11.2.0.3.6) and make it > reproducible > for everyone. > > > SQL> create table t as select * from dba_objects; > SQL> exec dbms_stats.gather_table_stats('TEST','T'); > SQL> create or replace type num_tab is table of number; > > SQL> create or replace procedure sp_test2(p_values in varchar2) > is > l_str varchar2(4000); > t_values num_tab := num_tab(); > o_cursor sys_refcursor; > > begin > > l_str := 'select num_tab (' || p_values || ') from dual'; > EXECUTE IMMEDIATE l_str INTO t_values; > > open o_cursor for select object_name from t where data_object_id in > (select column_value from table(cast( > ))); > close o_cursor; > end; > / > > > ***** The first run (hard parse) with 9 values ****** > SQL> alter system flush shared_pool; > SQL> exec sp_test2('1,2,3,4,5,6,7,8,9'); > SQL> select sql_id, child_number, executions, sql_text from v$sql where > sql_text like '%COLUMN_VALUE%'; > SQL_ID CHILD_NUMBER EXECUTIONS SQL_TEXT > ------------- ------------ ---------- > > ------------------------------------------------------------------------------------------------------------------------ > 3jzz7uj5q24mn 0 1 SELECT OBJECT_NAME FROM T WHERE > DATA_OBJECT_ID IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B1 AS NUM_TAB))) > > Plan hash value: 121332218 > > ----------------------------------------------------------------------------------------------------------------------- > | Id | Operation | Name | E-Rows |E-Bytes| Cost > (%CPU)| E-Time | OMem | 1Mem | Used-Mem | > > ----------------------------------------------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | | | 247 > (100)| | | | | > |* 1 | HASH JOIN RIGHT SEMI | | 8 | 232 | > 247 (1)| 00:00:06 | 1206K| 1206K| | > | 2 | COLLECTION ITERATOR PICKLER FETCH| | 9 | 18 | > 22 (0)| 00:00:01 | | | | > | 3 | TABLE ACCESS FULL | T | 74530 | 1965K| > 225 (1)| 00:00:05 | | | | > > ----------------------------------------------------------------------------------------------------------------------- > > > The cardinality estimate for the 't_values as num_tab' is correct, but if > you look closely at the CBO trace it is based on bind peeking - not dynamic > sampling. > > … > ******************************************* > Peeked values of the binds in SQL statement > ******************************************* > ----- Bind Info (kkscoacd) ----- > Bind#0 > oacdty=122 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 > oacflg=00 fl2=1206001 frm=00 csi=00 siz=4000 off=0 > toid ptr value=79C6B298 length=16 > 0A913E7E69E00741E0530E38A8C0EAE7 > kxsbbbfp=7faccb264060 bln=4000 avl=16 flg=15 > value=Unhandled datatype (122) found in kxsbndinf > Dump of memory from 0x00007FACCB264060 to 0x00007FACCB264070 > 7FACCB264060 0BA16820 00000000 CB264070 00007FAC [ h......p@&.....] > > Final query after transformations:******* UNPARSED QUERY IS ******* > SELECT "T"."OBJECT_NAME" "OBJECT_NAME" FROM TABLE(CAST(:B1 AS "NUM_TAB") ) > "KOKBF$0","TEST"."T" "T" WHERE "T"."DATA_OBJECT_ID"=VALUE(KOKBF$0) > … > *************************************** > BASE STATISTICAL INFORMATION > *********************** > Table Stats:: > Table: T Alias: T > #Rows: 74530 #Blks: 1087 AvgRowLen: 98.00 ChainCnt: 0.00 > *********************** > Table Stats:: > Table: KOKBF$0 Alias: KOKBF$0 (NOT ANALYZED) > #Rows: 9 #Blks: 100 AvgRowLen: 100.00 ChainCnt: 0.00 > … > > > ***** The second run (soft parse) with 6 values ****** > SQL> exec sp_test2('1,2,3,4,5,6'); > SQL> select sql_id, child_number, executions, sql_text from v$sql where > sql_text like '%COLUMN_VALUE%'; > SQL_ID CHILD_NUMBER EXECUTIONS SQL_TEXT > ------------- ------------ ---------- > > ------------------------------------------------------------------------------------------------------------------------ > 3jzz7uj5q24mn 0 2 SELECT OBJECT_NAME FROM T WHERE > DATA_OBJECT_ID IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B1 AS NUM_TAB))) > > Plan hash value: 121332218 > > ----------------------------------------------------------------------------------------------------------------------- > | Id | Operation | Name | E-Rows |E-Bytes| Cost > (%CPU)| E-Time | OMem | 1Mem | Used-Mem | > > ----------------------------------------------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | | | 247 > (100)| | | | | > |* 1 | HASH JOIN RIGHT SEMI | | 8 | 232 | > 247 (1)| 00:00:06 | 1206K| 1206K| | > | 2 | COLLECTION ITERATOR PICKLER FETCH| | 9 | 18 | > 22 (0)| 00:00:01 | | | | > | 3 | TABLE ACCESS FULL | T | 74530 | 1965K| > 225 (1)| 00:00:05 | | | | > > ----------------------------------------------------------------------------------------------------------------------- > > The "old" plan is still used (generated with the 9 peeked values) and > maybe insufficient in consequence. > > > A GTT can possibly fix this issue (depends on settings and code usage). > The GTT consequences can be minimized by the Tom Kyte hack ( > > https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4135403700346803387 > ). With Oracle 12c you can use the GTT even more efficient in > your case (init parameter temp_undo_enabled - > http://www.dbi-services.com/index.php/blog/entry/can-we-disable-logging-for-dml > ). > > Another option may be to use a literal list in the num_tab list (e.g. like > "select column_value from table(num_tab(1,2,3));"), but this depends on > your procedure usage (hard parse!). Undocumented hints should be avoided > as much as possible in productive code. > > However based on your given information i would go for the GTT (with work > around in 11g, if the redo for undo is an issue) and then use the 12c > capabilities later on. > > Best Regards > Stefan Koehler > > Oracle performance consultant and researcher > Homepage: http://www.soocs.de > Twitter: @OracleSK > > > > Jeff C <backseatdba@xxxxxxxxx> hat am 19. Dezember 2014 um 01:40 > geschrieben: > > > > Sorry I am running EE 11.2.0.2. > > Here is a simple example of what I am talking about. > > > > create or replace type num_tab is table of number; > > > > create replace sp_test(p_values in varchar2, > > o_cursor out sys_refcursor) > > is > > l_str varchar2(4000); > > t_values num_tab := num_tab(); > > begin > > > > l_str := 'select num_tab (' || p_values || ') from dual'; > > > > EXECUTE IMMEDIATE l_str INTO t_values; > > > > open o_cursor for > > select empno, ename, job, mgr, hiredate, sal > > from scott.emp > > where empno in (select column_value from table(cast(t_values as > num_tab))); > > > > end; > > > > I have tried the dynamic sampling hint but with no improvements. > -- > //www.freelists.org/webpage/oracle-l > > >