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'));
