Re: where clause filter from an array or use temp table?

On Fri, Dec 19, 2014 at 6:08 AM, Mauro Pagano <mauro.pagano_at_gmail.com> wrote:
>
> 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_at_soocs.de> 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_at_&.....]
>>
>> 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: _at_OracleSK
>>
>>
>> > Jeff C <backseatdba_at_gmail.com> 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.
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l