Juan, You may have already included it but I couldn't find the DDL for the view. Can you please post it? Seth Miller On Wed, Sep 3, 2014 at 10:28 AM, Juan Carlos Reyes Pacheco < jcdrpllist@xxxxxxxxx> wrote: > Thank you a lot Vishal, but it's not working for my situation :), I'll see > another solution. > > if you are interested in understanding the problem I explain you otherwise > don't worry. 11.2.0.3 patch 15 > > DIFFERENT EXECUTION PATHS IN TABLES INDEPENDENTLY THAN THROUGH A VIEW WITH > UNION ALL > ------------------------------------------------------------- > > CARTERA: Table asset with of today information > HICARTERA view joining to historic CARTERA TABLES > HICARTERA_RW recent historic data > HICARETRA_RO older historic data in readonly tablespace. > > CARTERA: don't have date column, it uses a package variable with the > current date > HICARTERA has a date column > HICARTERA_RW has a date column > HICARETRA_RO has a date column > > cartera_total join cartera + hicartera > > Executing separatedly in tables > I execute a query only using cartera 0,00s > I execute a query only using hicartera_rw 0,00s > I execute a query only using hicartera_ro 0,00s > > I execute on views > > When I use the view instead (hicartera, cartera_total) they are join 15s, > because the optimizer decides to use full scan > > The execution paths, if you see when using the view the optimizer uses > full table scan meanwhile when making the same query in every table of the > view (that uses a union all) the optimizer executes a full table scan. > Execution path using view > > SQL Statement from editor: > > > SELECT 1 FROM > SOATRANSACCIONES A, SOA.hicartera B, SOA.VENTA_PACTO, SOA.EMISION D > WHERE STR_CODCART = hca_CODCART AND hca_FECHA = TRUNC(STR_FEHORA) AND > STR_CODCART = VEP_CODCART(+) AND STR_NRO_OP_ORIGEN = > VEP_NRO_OPERACIONVEN(+) > AND EMN_CODINST = STR_INST AND EMN_SERIE = STR_SERIE AND > STR_OPERACION <> > 'RCP' AND STR_NRO_OPERACION = 73282 AND STR_CODCLI = 0 ORDER BY > STR_NRO_OPERACION > > ------------------------------------------------------------ > > Statement Id=7 Type=VIEW > Cost=29181 TimeStamp=03-09-14::11::18:37 > > (1) SELECT STATEMENT ALL_ROWS > Est. Rows: 1 Cost: 29.184 > (12) NESTED LOOPS > Est. Rows: 1 Cost: 29.184 > (10) NESTED LOOPS > Est. Rows: 1 Cost: 29.183 > (5) NESTED LOOPS OUTER > Est. Rows: 1 Cost: 2 > (3) TABLE TABLE ACCESS BY INDEX ROWID > SOA.SOATRANSACCIONES [Analyzed] > (3) Blocks: 5.165 Est. Rows: 1 of 82.406 Cost: 2 > Tablespace: TBL_USERS > (2) INDEX (UNIQUE) INDEX UNIQUE SCAN > SOA.CST_STR_NRO_OPERACION_CODCLI [Analyzed] > Est. Rows: 1 Cost: 1 > (4) INDEX (UNIQUE) INDEX UNIQUE SCAN > SOA.CST_CODCAR_NROOPVEN [Analyzed] > Est. Rows: 13.978 > (9) VIEW VIEW SOA.HICARTERA > Est. Rows: 1 Cost: 29.181 > * (8) UNION-ALL* > * (6) TABLE TABLE ACCESS FULL SOA.HICARTERA_RW > [Analyzed] * > * (6) Blocks: 88.881 Est. Rows: 2.183.791 of > 2.183.791 Cost: 24.009 * > * Tablespace: TBL_USERS* > * (7) TABLE TABLE ACCESS FULL SOA.HICARTERA_RO > [Analyzed] * > * (7) Blocks: 19.105 Est. Rows: 536.364 of 536.364 > Cost: 5.172 * > * Tablespace: TBL_USERS* > (11) INDEX (UNIQUE) INDEX RANGE SCAN > SOA.CST_CODIFICACION_SERIE [Analyzed] > Est. Rows: 1 Cost: 1 > > Execution path using tables > SQL Statement from editor: > > > SELECT 1 FROM > SOATRANSACCIONES A, SOA.hicartera_rw B, SOA.VENTA_PACTO, SOA.EMISION D > WHERE STR_CODCART = hca_CODCART AND hca_FECHA = TRUNC(STR_FEHORA) AND > STR_CODCART = VEP_CODCART(+) AND STR_NRO_OP_ORIGEN = > VEP_NRO_OPERACIONVEN(+) > AND EMN_CODINST = STR_INST AND EMN_SERIE = STR_SERIE AND > STR_OPERACION <> > 'RCP' AND STR_NRO_OPERACION = 73282 AND STR_CODCLI = 0 ORDER BY > STR_NRO_OPERACION > > ------------------------------------------------------------ > > Statement Id=7 Type=INDEX > Cost=1 TimeStamp=03-09-14::11::20:05 > > (1) SELECT STATEMENT ALL_ROWS > Est. Rows: 1 Cost: 4 > (9) NESTED LOOPS > Est. Rows: 1 Cost: 4 > (7) NESTED LOOPS > Est. Rows: 1 Cost: 3 > (5) NESTED LOOPS OUTER > Est. Rows: 1 Cost: 2 > (3) TABLE TABLE ACCESS BY INDEX ROWID > SOA.SOATRANSACCIONES [Analyzed] > (3) Blocks: 5.165 Est. Rows: 1 of 82.406 Cost: 2 > Tablespace: TBL_USERS > (2) INDEX (UNIQUE) INDEX UNIQUE SCAN > SOA.CST_STR_NRO_OPERACION_CODCLI [Analyzed] > Est. Rows: 1 Cost: 1 > (4) INDEX (UNIQUE) INDEX UNIQUE SCAN > SOA.CST_CODCAR_NROOPVEN [Analyzed] > Est. Rows: 13.978 > * (6) INDEX (UNIQUE) INDEX UNIQUE SCAN > SOA.IDX_HCA_CODCART_RO_E2 [Analyzed] * > Est. Rows: 2.183.791 Cost: 1 > (8) INDEX (UNIQUE) INDEX RANGE SCAN SOA.CST_CODIFICACION_SERIE > [Analyzed] > Est. Rows: 1 Cost: 1 > > SQL Statement from editor: > > > SELECT 1 FROM > SOATRANSACCIONES A, SOA.hicartera_ro B, SOA.VENTA_PACTO, SOA.EMISION D > WHERE STR_CODCART = hca_CODCART AND hca_FECHA = TRUNC(STR_FEHORA) AND > STR_CODCART = VEP_CODCART(+) AND STR_NRO_OP_ORIGEN = > VEP_NRO_OPERACIONVEN(+) > AND EMN_CODINST = STR_INST AND EMN_SERIE = STR_SERIE AND > STR_OPERACION <> > 'RCP' AND STR_NRO_OPERACION = 73282 AND STR_CODCLI = 0 ORDER BY > STR_NRO_OPERACION > > ------------------------------------------------------------ > > Statement Id=7 Type=INDEX > Cost=1 TimeStamp=03-09-14::11::20:36 > > (1) SELECT STATEMENT ALL_ROWS > Est. Rows: 1 Cost: 4 > (9) NESTED LOOPS > Est. Rows: 1 Cost: 4 > (7) NESTED LOOPS > Est. Rows: 1 Cost: 3 > (5) NESTED LOOPS OUTER > Est. Rows: 1 Cost: 2 > (3) TABLE TABLE ACCESS BY INDEX ROWID > SOA.SOATRANSACCIONES [Analyzed] > (3) Blocks: 5.165 Est. Rows: 1 of 82.406 Cost: 2 > Tablespace: TBL_USERS > (2) INDEX (UNIQUE) INDEX UNIQUE SCAN > SOA.CST_STR_NRO_OPERACION_CODCLI [Analyzed] > Est. Rows: 1 Cost: 1 > * (4) INDEX (UNIQUE) INDEX UNIQUE SCAN > SOA.CST_CODCAR_NROOPVEN [Analyzed] * > Est. Rows: 13.978 > (6) INDEX (UNIQUE) INDEX UNIQUE SCAN SOA.CST_HCA_CODCART_E > [Analyzed] > Est. Rows: 536.364 Cost: 1 > (8) INDEX (UNIQUE) INDEX RANGE SCAN SOA.CST_CODIFICACION_SERIE > [Analyzed] > Est. Rows: 1 Cost: 1 > > :) > > > > 2014-07-12 5:33 GMT-04:00 Vishal Gupta <vishal@xxxxxxxxxxxxxxx>: > >> Try by setting following in on 11.2.0.4. >> >> alter session set "_fix_control" ='12341619:OFF'; >> Or >> Using hint /*+ OPT_PARAM('_fix_control','12341619:OFF') */ >> >> >> On 11.2.0.3 >> alter session set "_fix_control" ='11814428:OFF'; >> Or >> Using hint /*+ OPT_PARAM('_fix_control','11814428:OFF') */ >> >> >> >> Optimizer >> Feature >> BUG Value Default Enable Event SQL Feature >> Description >> ---------- ----- ------- --------- -------- >> ----------------------------------- >> ------------------------------------------------------------------------------------------ >> 11814428 1 1 11.2.0.3 0 QKSFM_CARDINALITY_11814428 >> use union all view stats for colgroup cardinality sanity check >> 11668189 1 1 11.2.0.3 0 QKSFM_PQ_11668189 >> parallelize top-level union all if PDDL or PDML >> 11881047 1 1 11.2.0.3 0 QKSFM_PQ_11881047 >> non top-level union is parallel if at least one branch is parall >> 13543207 1 1 11.2.0.4 0 QKSFM_TRANSFORMATION_13543207 >> predicate pushdown in some union[all] branch(es) where valid >> 12944193 1 1 11.2.0.4 0 QKSFM_OLD_PUSH_PRED_12944193 >> Allow push into union view with remote table in the first branch >> 12341619 1 1 11.2.0.4 0 QKSFM_OLD_PUSH_PRED_12341619 >> improve view cardinality estimation in OJPPD for UNION [ALL] vie >> 14467202 1 1 11.2.0.4 0 QKSFM_CARDINALITY_14467202 >> Initialize selectivity for union all views >> 14147762 1 1 11.2.0.4 0 QKSFM_CURSOR_SHARING_14147762 >> disable cardinality feedback for union-all inside PJP view >> 14707009 1 1 11.2.0.4 0 QKSFM_OLD_PUSH_PRED_14707009 >> avoid early push of FTS filters into union view >> 12557401 1 1 11.2.0.4 0 QKSFM_CURSOR_SHARING_12557401 >> disable cardinality feedback for union-all JPPD >> 16273483 1 1 11.2.0.4 0 >> QKSFM_FILTER_PUSH_PRED_16273483 predicate pushdown only in valid union >> branches >> >> Regards, >> Vishal Gupta >> >> From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx> >> Reply-To: <iggy_fernandez@xxxxxxxxxxx> >> Date: Friday, 11 July 2014 23:48 >> To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>, "jcdrpllist@xxxxxxxxx" >> <jcdrpllist@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx> >> >> Subject: RE: Woraround A: for performance problem on view joining >> several tables >> >> +1 >> >> Partition elimination in partitioned views is a working feature but >> Carlos has not given us enough information (full view definition, full SQL >> query, dbms_xplan.display_cursor listing) for us to figure out whether it >> is working out for him. >> >> Iggy >> >> >> ------------------------------ >> From: jonathan@xxxxxxxxxxxxxxxxxx >> To: jcdrpllist@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx >> Subject: RE: Woraround A: for performance problem on view joining several >> tables >> Date: Thu, 10 Jul 2014 23:25:14 +0000 >> >> >> >> It's a little hard to tell from your choice of display tool, but it looks >> like your view is actually: >> >> select from table1 >> union all >> ( >> select from table2 >> union all >> select from table3 >> ) >> >> where the second union all is a stored view rather than in-line view. >> Who knows what other variations on the basic theme you've included >> without giving us clues. >> >> You may find that if you ensure that Oracle KNOWS that your driver table >> will return one row then you can avoid the function - the simplest test >> would be to add "where rownum = 1" to the subquery. >> >> >> >> >> >> Regards >> Jonathan Lewis >> http://jonathanlewis.wordpress.com >> @jloracle >> ------------------------------ >> *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on >> behalf of Juan Carlos Reyes Pacheco [jcdrpllist@xxxxxxxxx] >> *Sent:* 10 July 2014 19:36 >> *To:* ORACLE-L >> *Subject:* Woraround A: for performance problem on view joining several >> tables >> >> Thank you. >> If you are interested. >> >> I don't have too much time and I still couldn't get work the Partitioned >> UNION-ALL views >> meanwhile I found a work around to solved in 11.2.0.3 patch 15, meanwhile >> I try the other solution. >> >> The workaround is replacing int the where with a function instead of a >> column >> I used another table for the date, and the problem persists, so the >> problem is not the table itself but the optimizer. I gather full statitics, >> etc. >> >> select * from view(union all of some tables) a, one_row_date_table b >> where a.date_column=b.date_column >> takes 14s >> >> select * from view(union all of some tables) a >> where a.date_column = (SELECT b.date_column FROM one_row_date_table b) >> takes 14s >> >> select * from view(union all of some tables) a >> where a.date_column = functiongetdate >> >> >> functiongetdate >> is >> begin >> SELECT b.date_column FROM one_row_date_table b >> return b.date_column >> end; >> >> :) >> >> If you are interested I put both execution plans. >> >> DATE COLUMN=DATE COLUMN >> >> ------------------------------------------------------------ >> >> Statement Id=15 Type=UNION-ALL >> Cost=0 TimeStamp=10-07-14::14::25:00 >> >> (1) SELECT STATEMENT ALL_ROWS >> Est. Rows: 5.314.311 Cost: 30.052 >> (18) SORT GROUP BY >> Est. Rows: 5.314.311 Cost: 30.052 >> (17) HASH JOIN >> Est. Rows: 5.314.311 Cost: 29.282 >> (9) NESTED LOOPS >> Est. Rows: 34 Cost: 33 >> (7) NESTED LOOPS >> Est. Rows: 1 Cost: 2 >> (5) NESTED LOOPS >> Est. Rows: 1 Cost: 2 >> (3) TABLE TABLE ACCESS BY INDEX ROWID >> DAZ.UTL_MULTIEMPRESA_EMPRESA [Analyzed] >> (3) Blocks: 5 Est. Rows: 1 of 1 Cost: 1 >> Tablespace: TBL_USERS >> (2) INDEX (UNIQUE) INDEX UNIQUE SCAN >> DAZ.CST_USR_SIGLA [Analyzed] >> Est. Rows: 1 >> (4) INDEX (UNIQUE) INDEX RANGE SCAN >> DAZ.IDX_MEM_FECHA_HOY [Analyzed] >> Est. Rows: 1 Cost: 1 >> (6) INDEX (UNIQUE) INDEX UNIQUE SCAN >> DAZ.CST_IDI_CODIGO_45 [Analyzed] >> Est. Rows: 1 >> (8) TABLE TABLE ACCESS FULL SOA.CLIENTES_ME >> [Analyzed] >> (8) Blocks: 110 Est. Rows: 34 of 1.971 Cost: 31 >> Tablespace: TBL_USERS >> (16) VIEW VIEW SOA.CARTERA_TOTAL_RAW >> Est. Rows: 1.848.456 Cost: 29.172 >> (15) UNION-ALL >> (10) INDEX INDEX RANGE SCAN >> SOA.CARTERA_IDX$$_03C10050 [Analyzed] >> Est. Rows: 15 Cost: 4 >> (14) VIEW VIEW SOA.HICARTERA >> Est. Rows: 1.848.441 Cost: 29.168 >> (13) UNION-ALL >> (11) TABLE TABLE ACCESS FULL >> SOA.HICARTERA_RW [Analyzed] >> (11) Blocks: 88.881 Est. Rows: 1.729.179 >> of 2.183.791 Cost: 24.025 >> Tablespace: TBL_USERS >> (12) TABLE TABLE ACCESS FULL >> SOA.HICARTERA_RO [Analyzed] >> (12) Blocks: 19.105 Est. Rows: 119.262 >> of 536.364 Cost: 5.143 >> Tablespace: TBL_USERS >> >> >> DATE COLUMN=FUNCTION RETURNING DATE >> >> ------------------------------------------------------------ >> >> Statement Id=15 Type=INDEX >> Cost=4 TimeStamp=10-07-14::14::24:26 >> >> (1) SELECT STATEMENT ALL_ROWS >> Est. Rows: 27.669 Cost: 3.278 >> (22) SORT GROUP BY >> Est. Rows: 27.669 Cost: 3.278 >> (21) MERGE JOIN CARTESIAN >> Est. Rows: 27.669 Cost: 3.274 >> (9) NESTED LOOPS >> Est. Rows: 34 Cost: 32 >> (7) NESTED LOOPS >> Est. Rows: 1 Cost: 1 >> (5) NESTED LOOPS >> Est. Rows: 1 Cost: 1 >> (3) TABLE TABLE ACCESS BY INDEX ROWID >> DAZ.UTL_MULTIEMPRESA_EMPRESA [Analyzed] >> (3) Blocks: 5 Est. Rows: 1 of 1 Cost: 1 >> Tablespace: TBL_USERS >> (2) INDEX (UNIQUE) INDEX UNIQUE SCAN >> DAZ.CST_USR_SIGLA [Analyzed] >> Est. Rows: 1 >> (4) INDEX (UNIQUE) INDEX UNIQUE SCAN >> DAZ.CST_MEM_CODIGO [Analyzed] >> Est. Rows: 1 >> (6) INDEX (UNIQUE) INDEX UNIQUE SCAN >> DAZ.CST_IDI_CODIGO_45 [Analyzed] >> Est. Rows: 1 >> (8) TABLE TABLE ACCESS FULL SOA.CLIENTES_ME >> [Analyzed] >> (8) Blocks: 110 Est. Rows: 34 of 1.971 Cost: 31 >> Tablespace: TBL_USERS >> (20) BUFFER SORT >> Est. Rows: 802 Cost: 3.246 >> (19) VIEW VIEW SOA.CARTERA_TOTAL_RAW >> Est. Rows: 802 Cost: 95 >> (18) UNION-ALL >> (11) FILTER >> (10) INDEX INDEX RANGE SCAN >> SOA.CARTERA_IDX$$_03C10050 [Analyzed] >> Est. Rows: 15 Cost: 4 >> (17) VIEW VIEW SOA.HICARTERA >> Est. Rows: 787 Cost: 91 >> (16) UNION-ALL >> (13) TABLE TABLE ACCESS BY INDEX >> ROWID SOA.HICARTERA_RW [Analyzed] >> (13) Blocks: 88.881 Est. Rows: 427 >> of 2.183.791 Cost: 31 >> Tablespace: TBL_USERS >> (12) INDEX INDEX RANGE SCAN >> SOA.IDX_HCA_LIQ_RO [Analyzed] >> Est. Rows: 544 Cost: 4 >> (15) TABLE TABLE ACCESS BY INDEX >> ROWID SOA.HICARTERA_RO [Analyzed] >> (15) Blocks: 19.105 Est. Rows: 360 >> of 536.364 Cost: 60 >> Tablespace: TBL_USERS >> (14) INDEX INDEX RANGE SCAN >> SOA.IDX_HCA_LIQ [Analyzed] >> Est. Rows: 618 Cost: 6 >> >> >