Re: Woraround A: for performance problem on view joining several tables
Date: Sat, 12 Jul 2014 10:33:39 +0100
Message-ID: <CFE6BD5C.190A8%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_16273483predicate 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 Sat Jul 12 2014 - 11:33:39 CEST