Re: Woraround A: for performance problem on view joining several tables

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_at_gmail.com> 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_at_vishalgupta.com>:
>
>> 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_at_hotmail.com>
>> Reply-To: <iggy_fernandez_at_hotmail.com>
>> Date: Friday, 11 July 2014 23:48
>> To: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>, "jcdrpllist_at_gmail.com"
>> <jcdrpllist_at_gmail.com>, ORACLE-L <oracle-l_at_freelists.org>
>>
>> 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_at_jlcomp.demon.co.uk
>> To: jcdrpllist_at_gmail.com; oracle-l_at_freelists.org
>> 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
>> _at_jloracle
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
>> behalf of Juan Carlos Reyes Pacheco [jcdrpllist_at_gmail.com]
>> *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
>>
>>
>

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