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

From: Seth Miller <sethmiller.sm_at_gmail.com>
Date: Thu, 4 Sep 2014 15:13:34 -0500
Message-ID: <CAEueRAV1b3E4s=XuoED8Ye4QW1mtUDzeUJYD7vwLLkUDziFJpA_at_mail.gmail.com>

Just to clarify, you are trying to select from a view that is made up of a union of two different queries (both of which contain functions) of a view that is made up of a union between two different tables (I really hope they are tables).

Normally, I'm not the guy to dismiss a performance issue but nesting views more often than not incapacitates the optimizer. Just rewrite your query to either select directly from the tables or from the SOA.HICARTERA view.

From Oracle Documentation:

*Views can speed up and simplify application design. A simple view definition can mask data **model complexity from the programmers whose priorities are to retrieve, display, collect, and **store data. Views are often used to provide simple row and column-level access restrictions.*

*However, though views provide clean programming interfaces, they can cause suboptimal, **resource-intensive queries when nested too deeply. The worst type of view use is creating **joins on views that reference other views, which in turn reference other views. In many cases, **developers can satisfy the query directly from the table without using a view. Because of their*
*inherent properties, views usually make it difficult for the optimizer to generate the optimal **execution plan.*

Seth Miller

On Thu, Sep 4, 2014 at 2:00 PM, Juan Carlos Reyes Pacheco < jcdrpllist_at_gmail.com> wrote:

> Thank you Seth, there are two views, but the problem persists when is only
> one view, is only an union all.
> CREATE OR REPLACE VIEW SOA.CARTERA_TOTAL_RAW
> (
> DAZ_VERSION, CAR_FECHA, CAR_CASPRMIN_MER, CAR_CASTEOUNIDAD,
> CAR_CLASE_OPERACION,
> CAR_COD_INT, CAR_CODCAREMP, CAR_CODCART, CAR_CODCLI, CAR_CODVAL,
> CAR_CONTRAPARTE,
> CAR_CTA_DEV, CAR_CTA_DEV_VEP, CAR_CTA_INT, CAR_CTA_POS, CAR_CTA_POS_VEP,
> CAR_CUSTEO, CAR_DEBE, CAR_DEVACUM, CAR_DEVENGADO, CAR_DSC, CAR_DSM,
> CAR_ENBCA,
> CAR_ENBOR, CAR_ENBOT, CAR_ENBPR, CAR_ENBRJ, CAR_ENCR, CAR_ENCUST,
> CAR_ENRP,
> CAR_ENUSONR, CAR_FEADQ, CAR_FEMER, CAR_FEMER_MAD, CAR_FEVAL, CAR_HABER,
> CAR_INST, CAR_LCODVAL_ITERA, CAR_LIQ, CAR_MONEDA, CAR_NRO_OPERACION_VEVC,
> CAR_PLAZORP, CAR_POSICION, CAR_PRADQ, CAR_PRAPE_MER, CAR_PRD,
> CAR_PRMAX_MER,
> CAR_PRMER, CAR_PRMIN_MER, CAR_PRTEOADQ, CAR_PRVAL, CAR_PRVAL_1,
> CAR_PRVAL_2,
> CAR_PRVAL_3, CAR_PRVAL_PPP, CAR_PRVAL_TEO, CAR_PRVALHOY, CAR_SERIE,
> CAR_TCUSD,
> CAR_TDADQ, CAR_TIPOMER, CAR_TIT_EXCESO, CAR_TRADQ, CAR_TRMER, CAR_TRVAL,
> CAR_TRVAL_1, CAR_TRVAL_2, CAR_TRVAL_3, CAR_TRVAL_TEO, CAR_TRVALHOY,
> CAR_ULTFEDEV,
> CAR_STOCK, CAR_IGUALA_FORZOSA, CAR_PROV_PRMER, CAR_PRVAL_4, CAR_TRVAL_4,
> CAR_CPPFECHA, CAR_CPPPRECIO, CAR_PRVAL_PPP_INI, CAR_NRO_TITS_PPP_INI,
> CAR_AGRUPADOR_CAR,
> CAR_PRVAL_PPP2, CAR_QUIEN_CUST, CAR_STOCK0_, CAR_ESVIRF,
> CAR_VIRF_PEND_CVT,
> CAR_VIRF_PEND_RPT, CAR_VIRF_DISP, CAR_NRO_OPERACION_ADQ_VIRF
>
> )
> AS
> SELECT
> /**/ 'SOA2014051552611' /**/ DAZ_VERSION
> ,DAZ.PCK_EMPRESA.CF_ObtieneFechaSisReal CAR_FECHA
> ,car_casprmin_mer
> ,car_casteounidad
> ,car_clase_operacion
> ,car_cod_int
> ,car_codcaremp
> ,car_codcart
> ,car_codcli
> ,car_codval
> ,car_contraparte
> ,car_cta_dev
> ,car_cta_dev_vep
> ,car_cta_int
> ,car_cta_pos
> ,car_cta_pos_vep
> ,car_custeo
> ,car_debe
> ,car_devacum
> ,car_devengado
> ,car_dsc
> ,car_dsm
> ,car_enbca
> ,car_enbor
> ,car_enbot
> ,car_enbpr
> ,car_enbrj
> ,car_encr
> ,car_encust
> ,car_enrp
> ,car_enusonr
> ,car_feadq
> ,car_femer
> ,car_femer_mad
> ,car_feval
> ,car_haber
> ,car_inst
> ,car_lcodval_itera
> ,car_liq
> ,car_moneda
> ,car_nro_operacion_vevc
> ,car_plazorp
> ,car_posicion
> ,car_pradq
> ,car_prape_mer
> ,car_prd
> ,car_prmax_mer
> ,car_prmer
> ,car_prmin_mer
> ,car_prteoadq
> ,car_prval
> ,car_prval_1
> ,car_prval_2
> ,car_prval_3
> ,car_prval_ppp
> ,car_prval_teo
> ,car_prvalhoy
> ,car_serie
> ,car_tcusd
> ,car_tdadq
> ,car_tipomer
> ,car_tit_exceso
> ,car_tradq
> ,car_trmer
> ,car_trval
> ,car_trval_1
> ,car_trval_2
> ,car_trval_3
> ,car_trval_teo
> ,car_trvalhoy
> ,car_ultfedev
> ,car_stock_
> ,CAR_IGUALA_FORZOSA
> ,CAR_PROV_PRMER,
> car_prval_4, car_trval_4, car_cppfecha, car_cppprecio,
> car_prval_ppp_ini, car_nro_tits_ppp_ini,
>
> TO_CHAR(DAZ.PCK_EMPRESA.cf_obtienefechasisreal,'DDMMYYYY')||CAR_PRD||'-'||CAR_SERIE||'-'||CAR_INST||'-'||NVL(CAR_COD_INT,'~~~')||'-'||NVL(CAR_CTA_INT,'~~~')||'-'||CAR_CODCLI||'-'||CAR_CLASE_OPERACION
> CAR_AGRUPADOR_CAR,
> CAR_PRVAL_PPP2,CAR_QUIEN_CUST, CAR_STOCK0_,CAR_ESVIRF,
> CAR_VIRF_PEND_CVT, CAR_VIRF_PEND_RPT,
> CAR_VIRF_DISP,CAR_NRO_OPERACION_ADQ_VIRF
> FROM soa.cartera
>
>
>
> UNION ALL
>
> SELECT
>
> /**/ 'SOA2014051552611' /**/ DAZ_VERSION
>
> ,HCA_FECHA
> ,CAR_casprmin_mer
> ,hca_casteounidad
> ,hca_clase_operacion
> ,hca_cod_int
> ,hca_codcaremp
> ,hca_codcart
> ,hca_codcli
> ,hca_codval
> ,hca_contraparte
> ,hca_cta_dev
> ,hca_cta_dev_vep
> ,hca_cta_int
> ,hca_cta_pos
> ,hca_cta_pos_vep
> ,hca_custeo
> ,hca_debe
> ,hca_devacum
> ,hca_devengado
> ,hca_dsc
> ,hca_dsm
> ,hca_enbca
> ,hca_enbor
> ,hca_enbot
> ,hca_enbpr
> ,hca_enbrj
> ,hca_encr
> ,hca_encust
> ,hca_enrp
> ,hca_enusonr
> ,hca_feadq
> ,hca_femer
> ,hca_femer_mad
> ,hca_feval
> ,hca_haber
> ,hca_inst
> ,hca_lcodval_itera
> ,hca_liq
> ,hca_moneda
> ,hca_nro_operacion_vevc
> ,hca_plazorp
> ,hca_posicion
> ,hca_pradq
> ,car_prape_mer
> ,car_prd
> ,car_prmax_mer
> ,hca_prmer
> ,car_prmin_mer
> ,hca_prteoadq
> ,hca_prval
> ,hca_prval_1
> ,hca_prval_2
> ,hca_prval_3
> ,car_prval_ppp
> ,hca_prval_teo
> ,hca_prvalhoy
> ,hca_serie
> ,hca_tcusd
> ,hca_tdadq
> ,hca_tipomer
> ,hca_tit_exceso
> ,hca_tradq
> ,hca_trmer
> ,hca_trval
> ,hca_trval_1
> ,hca_trval_2
> ,hca_trval_3
> ,hca_trval_teo
> ,hca_trvalhoy
> ,hca_ultfedev
> ,car_stock_
> ,CAR_IGUALA_FORZOSA
> ,CAR_PROV_PRMER,
> car_prval_4, car_trval_4, car_cppfecha, car_cppprecio,
> car_prval_ppp_ini, car_nro_tits_ppp_ini,
>
> TO_CHAR(HCA_FECHA,'DDMMYYYY')||CAR_PRD||'-'||HCA_SERIE||'-'||HCA_INST||'-'||NVL(HCA_COD_INT,'~~~')||'-'||NVL(HCA_CTA_INT,'~~~')||'-'||HCA_CODCLI||'-'||HCA_CLASE_OPERACION
> CAR_AGRUPADOR_CAR,
> CAR_PRVAL_PPP2,CAR_QUIEN_CUST, CAR_STOCK0_,CAR_ESVIRF,
> CAR_VIRF_PEND_CVT, CAR_VIRF_PEND_RPT,
> CAR_VIRF_DISP,CAR_NRO_OPERACION_ADQ_VIRF
>
> FROM soa.hicartera
> -- WHERE HCA_FECHA = '09MAR2014'
> /
> CREATE OR REPLACE VIEW SOA.HICARTERA
> (
> HCA_FECHA, HCA_FEMER, HCA_PRMER, HCA_TRMER, HCA_FEVAL, HCA_PRVAL,
> HCA_TRVAL,
> HCA_CLASE_OPERACION, HCA_LIQ, HCA_CONTRAPARTE, HCA_NRO_OPERACION_VEVC,
> HCA_DSC, HCA_DSM, HCA_TCUSD, HCA_CODCART, HCA_INST, HCA_SERIE, HCA_DEBE,
> HCA_HABER, HCA_ENRP, HCA_ENCR, HCA_ENCUST, HCA_FEADQ, HCA_PRADQ,
> HCA_TRADQ,
> HCA_TDADQ, HCA_PLAZORP, HCA_CODCLI, HCA_CODVAL, HCA_TIPOMER, HCA_DEVACUM,
> HCA_DEVENGADO, HCA_POSICION, HCA_PRTEOADQ, HCA_ULTFEDEV, HCA_CUSTEO,
> HCA_TIT_EXCESO,
> HCA_COD_INT, HCA_CTA_INT, HCA_ENUSONR, HCA_ENBOT, HCA_ENBPR, HCA_ENBOR,
> HCA_ENBRJ, HCA_ENBCA, HCA_PRVALHOY, HCA_TRVALHOY, HCA_FEMER_MAD,
> HCA_CODCAREMP,
> HCA_TRVAL_TEO, HCA_PRVAL_TEO, HCA_LCODVAL_ITERA, HCA_CTA_POS, HCA_CTA_DEV,
> HCA_CTA_POS_VEP, HCA_CTA_DEV_VEP, HCA_MONEDA, DAZ_VERSION,
> CAR_RESTRINGIDO_,
> CAR_ENCR_C_, CAR_ENCR_, CAR_STOCK_, CAR_TASA_CLASE_, CAR_PRD,
> HCA_MONTO_DISPONIBLE_,
> CAR_BLOQUEOS_, CAR_PRMIN_MER, CAR_PRMAX_MER, CAR_PRAPE_MER,
> CAR_CASPRMIN_MER,
> HCA_CASTEOUNIDAD, HCA_TRVAL_1, HCA_PRVAL_1, HCA_TRVAL_2, HCA_PRVAL_2,
> HCA_TRVAL_3,
> HCA_PRVAL_3, HCA_TRVAL_4, HCA_PRVAL_4, MEE_EMPRESA, CAR_PRVAL_PPP,
> CAR_IGUALA_FORZOSA,
> HCA_CPPFECHA, HCA_CPPPRECIO, CAR_PROV_PRMER, CAR_PRVAL_4, CAR_TRVAL_4,
> CAR_CPPFECHA, CAR_CPPPRECIO, CAR_PRVAL_PPP_INI, CAR_NRO_TITS_PPP_INI,
> CAR_PRVAL_PPP2,
> CAR_PRINI_MDD, CAR_QUIEN_CUST, CAR_STOCK0_, CAR_ESVIRF, CAR_VIRF_PEND_CVT,
> CAR_VIRF_PEND_RPT, CAR_VIRF_DISP, CAR_NRO_OPERACION_ADQ_VIRF
>
> )
> AS
> SELECT
>
> HCA_FECHA,HCA_FEMER,HCA_PRMER,HCA_TRMER,HCA_FEVAL,HCA_PRVAL,HCA_TRVAL,HCA_CLASE_OPERACION,
>
> HCA_LIQ,HCA_CONTRAPARTE,HCA_NRO_OPERACION_VEVC,HCA_DSC,HCA_DSM,HCA_TCUSD,HCA_CODCART,HCA_INST,
>
> HCA_SERIE,HCA_DEBE,HCA_HABER,HCA_ENRP,HCA_ENCR,HCA_ENCUST,HCA_FEADQ,HCA_PRADQ,
>
> HCA_TRADQ,HCA_TDADQ,HCA_PLAZORP,HCA_CODCLI,HCA_CODVAL,HCA_TIPOMER,HCA_DEVACUM,HCA_DEVENGADO,
>
> HCA_POSICION,HCA_PRTEOADQ,HCA_ULTFEDEV,HCA_CUSTEO,HCA_TIT_EXCESO,HCA_COD_INT,HCA_CTA_INT,HCA_ENUSONR,
>
> HCA_ENBOT,HCA_ENBPR,HCA_ENBOR,HCA_ENBRJ,HCA_ENBCA,HCA_PRVALHOY,HCA_TRVALHOY,HCA_FEMER_MAD,
>
> HCA_CODCAREMP,HCA_TRVAL_TEO,HCA_PRVAL_TEO,HCA_LCODVAL_ITERA,HCA_CTA_POS,HCA_CTA_DEV,HCA_CTA_POS_VEP,HCA_CTA_DEV_VEP,
> HCA_MONEDA, /**/ 'SOA2014051552664' /**/
> DAZ_VERSION,CAR_RESTRINGIDO_,CAR_ENCR_C_,CAR_ENCR_,CAR_STOCK_,CAR_TASA_CLASE_,CAR_PRD,
>
> HCA_MONTO_DISPONIBLE_,CAR_BLOQUEOS_,CAR_PRMIN_MER,CAR_PRMAX_MER,CAR_PRAPE_MER,CAR_CASPRMIN_MER,HCA_CASTEOUNIDAD,
> HCA_TRVAL_1, HCA_PRVAL_1, HCA_TRVAL_2, HCA_PRVAL_2, HCA_TRVAL_3,
> HCA_PRVAL_3,HCA_TRVAL_4, HCA_PRVAL_4,
> MEE_EMPRESA,CAR_PRVAL_PPP,CAR_IGUALA_FORZOSA,
> HCA_CPPFECHA, HCA_CPPPRECIO,CAR_PROV_PRMER,
> car_prval_4, car_trval_4, car_cppfecha, car_cppprecio,
> car_prval_ppp_ini, car_nro_tits_ppp_ini,CAR_PRVAL_PPP2,
> CAR_PRINI_MDD,CAR_QUIEN_CUST,CAR_STOCK0_,CAR_ESVIRF, CAR_VIRF_PEND_CVT,
> CAR_VIRF_PEND_RPT, CAR_VIRF_DISP,CAR_NRO_OPERACION_ADQ_VIRF
> FROM SOA.HICARTERA_RW
>
> UNION ALL
>
> SELECT
>
> HCA_FECHA,HCA_FEMER,HCA_PRMER,HCA_TRMER,HCA_FEVAL,HCA_PRVAL,HCA_TRVAL,HCA_CLASE_OPERACION,
>
> HCA_LIQ,HCA_CONTRAPARTE,HCA_NRO_OPERACION_VEVC,HCA_DSC,HCA_DSM,HCA_TCUSD,HCA_CODCART,HCA_INST,
>
> HCA_SERIE,HCA_DEBE,HCA_HABER,HCA_ENRP,HCA_ENCR,HCA_ENCUST,HCA_FEADQ,HCA_PRADQ,
>
> HCA_TRADQ,HCA_TDADQ,HCA_PLAZORP,HCA_CODCLI,HCA_CODVAL,HCA_TIPOMER,HCA_DEVACUM,HCA_DEVENGADO,
>
> HCA_POSICION,HCA_PRTEOADQ,HCA_ULTFEDEV,HCA_CUSTEO,HCA_TIT_EXCESO,HCA_COD_INT,HCA_CTA_INT,HCA_ENUSONR,
>
> HCA_ENBOT,HCA_ENBPR,HCA_ENBOR,HCA_ENBRJ,HCA_ENBCA,HCA_PRVALHOY,HCA_TRVALHOY,HCA_FEMER_MAD,
>
> HCA_CODCAREMP,HCA_TRVAL_TEO,HCA_PRVAL_TEO,HCA_LCODVAL_ITERA,HCA_CTA_POS,HCA_CTA_DEV,HCA_CTA_POS_VEP,HCA_CTA_DEV_VEP,
> HCA_MONEDA,/**/ 'SOA2014051552664' /**/
> DAZ_VERSION,CAR_RESTRINGIDO_,CAR_ENCR_C_,CAR_ENCR_,CAR_STOCK_,CAR_TASA_CLASE_,CAR_PRD,
>
> HCA_MONTO_DISPONIBLE_,CAR_BLOQUEOS_,CAR_PRMIN_MER,CAR_PRMAX_MER,CAR_PRAPE_MER,CAR_CASPRMIN_MER,HCA_CASTEOUNIDAD,
> HCA_TRVAL_1, HCA_PRVAL_1, HCA_TRVAL_2, HCA_PRVAL_2, HCA_TRVAL_3,
> HCA_PRVAL_3,HCA_TRVAL_4, HCA_PRVAL_4,
> MEE_EMPRESA,CAR_PRVAL_PPP,CAR_IGUALA_FORZOSA,
> HCA_CPPFECHA, HCA_CPPPRECIO,CAR_PROV_PRMER,
> car_prval_4, car_trval_4, car_cppfecha, car_cppprecio,
> car_prval_ppp_ini, car_nro_tits_ppp_ini,CAR_PRVAL_PPP2,
> CAR_PRINI_MDD,CAR_QUIEN_CUST,CAR_STOCK0_,CAR_ESVIRF, CAR_VIRF_PEND_CVT,
> CAR_VIRF_PEND_RPT, CAR_VIRF_DISP,CAR_NRO_OPERACION_ADQ_VIRF
> FROM SOA.HICARTERA_RO
> /
>
>
>
> 2014-09-03 13:15 GMT-04:00 Seth Miller <sethmiller.sm_at_gmail.com>:
>
>> 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_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

Received on Thu Sep 04 2014 - 22:13:34 CEST

This message: [ Message body ]
Next message: Ls Cheng: "buffer advisor"
Previous message: Jonathan Lewis: "RE: v$SGA and V$SGASTAT"
Maybe in reply to: Juan Carlos Reyes Pacheco: "Re: Woraround A: for performance problem on view joining several tables"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message