Thank you :) Iggy for your time
I tried and didn't work.
alter session set "_optimizer_join_factorization" = FALSE;
SELECT /*+ NO_FACTORIZE_JOIN */
The true I'm not serious solving my performance problems, until this moment
I had a global idea of oracle database, learn set correctly parameter,
gather statistics, and creating indexes based on trace or statspack, and
lately we had to start applying patches. meanwhile the data and complexity
of system grows we have to start to learn new tricks. I currently don't
have metalink , so I either can be checking on bugs, now I have to start to
learn to solve like this problems I didn't face before.
I'll send a SQLT as soon as I can :).
2015-10-22 16:57 GMT-04:00 Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>:
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 200
set pagesize 1000
set tab off
-- first execute the query using the view
-- then display the query plan
select * 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 plan
select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED
IOSTATS LAST +PEEKED_BINDS'));
