Re: where clause filter from an array or use temp table?
Date: Fri, 19 Dec 2014 14:43:55 +0100 (CET)
Message-ID: <223295527.482486.1418996636008.open-xchange_at_app01.ox.hosteurope.de>
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-lReceived on Fri Dec 19 2014 - 14:43:55 CET