RE: performance. 7 queries independent queries takes 3s with union all in view 98s

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 22 Oct 2015 13:57:24 -0700
Message-ID: <BLU179-W67F52BC7ECD5D1BFF24926EB270_at_phx.gbl>

Hi, Juan,
The zip file contains "EXPLAIN PLANS" which are almost completely useless by their very definition :-) The only reliable and useful query plan is one that is produced by DBMS_XPLAN.DISPLAY_CURSOR and which includes "rowsource execution statistics". However, it appears to me to see that the following common block has been factored out of each branch of the UNION-ALL view. In other words, the the execution plan of the query against the UNION ALL view is not the simple union of the execution plans of the queries against the individual branches. See https://blogs.oracle.com/optimizer/entry/optimizer_transformations_join_factorization for an explanation of join factorization.

               (98)  NESTED LOOPS                     Est. Rows: 1  Cost: 2                   (96)  NESTED LOOPS                         Est. Rows: 1  Cost: 2                       (94)  TABLE TABLE ACCESS BY INDEX ROWID DAZ.UTL_MULTIEMPRESA_EMPRESA  [Analyzed]                        (94)   Blocks: 5 Est. Rows: 1 of 1  Cost: 1                             Tablespace: TBL_USERS                           (93)  INDEX (UNIQUE) INDEX UNIQUE SCAN DAZ.CST_USR_SIGLA  [Analyzed]                                 Est. Rows: 1                       (95)  INDEX (UNIQUE) INDEX RANGE SCAN DAZ.IDX_MEM_FECHA_HOY  [Analyzed]                             Est. Rows: 1  Cost: 1                   (97)  INDEX (UNIQUE) INDEX UNIQUE SCAN DAZ.CST_IDI_CODIGO_45  [Analyzed]                         Est. Rows: 1               (104)  NESTED LOOPS                     Est. Rows: 1  Cost: 2                   (102)  NESTED LOOPS                         Est. Rows: 1  Cost: 2                       (100)  TABLE TABLE ACCESS BY INDEX ROWID DAZ.UTL_MULTIEMPRESA_EMPRESA  [Analyzed]                        (100)   Blocks: 5 Est. Rows: 1 of 1  Cost: 1                             Tablespace: TBL_USERS                           (99)  INDEX (UNIQUE) INDEX UNIQUE SCAN DAZ.CST_USR_SIGLA  [Analyzed]                                 Est. Rows: 1                       (101)  INDEX (UNIQUE) INDEX RANGE SCAN DAZ.IDX_MEM_FECHA_HOY  [Analyzed]                             Est. Rows: 1  Cost: 1                   (103)  INDEX (UNIQUE) INDEX UNIQUE SCAN DAZ.CST_IDI_CODIGO_45  [Analyzed]                         Est. Rows: 1

You should be able to avoid the problem by turning of join factorization using a hint. Google tells me that there is a parameter called _optimizer_join_factorization and a hint called NO_FACTORIZE_JOIN but I haven't tested them. I'm not sure why flushing the shared pool and cache every hour. To find out why, we would have to take the investigation to the next level and use SQLT. One of my presentations at RMOUG 2016 is called "Give me a SQLT report (or admit that you're not serious about solving your SQL performance problem)" :-) Kindest regards,
Iggy
P.S. Here is how to collect reliable and useful query plans alter session set "_rowsource_execution_statistics"=true;set linesize 200set pagesize 1000set tab off
-- first execute the query using the view-- then display the query planselect * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED IOSTATS LAST +PEEKED_BINDS'));

• next execute each individual branch of the UNION ALL-- then display the query planselect * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED IOSTATS LAST +PEEKED_BINDS')); -- http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 22 2015 - 22:57:24 CEST