Re: Woraround A: for performance problem on view joining several tables
Date: Wed, 3 Sep 2014 11:28:20 -0400
Message-ID: <CAGYrQyt9oHG7BzkPzMbiPcRxfWP3MbOkr-ZKE4kcCG=KQcSY8Q_at_mail.gmail.com>
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-lReceived on Wed Sep 03 2014 - 17:28:20 CEST