Re: where clause filter from an array or use temp table?
Date: Fri, 19 Dec 2014 15:08:43 +0100
Message-ID: <CAAnDMS0vnvoLxbpE8FJD1MPBga6baR_+TNBkkz_8fwTtCY4vfA_at_mail.gmail.com>
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-lReceived on Fri Dec 19 2014 - 15:08:43 CET