Thank you Mark
I gathered statistics on table again, after deleting, and problem persists
EXEC dbms_stats.delete_table_stats( 'SOA',
'SOATRANSACCIONES',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA', 'SOATRANSACCIONES',
cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'SOATRANSACCIONES',
METHOD_OPT=>'FOR ALL COLUMNS SIZE
SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'SOA',
'TRCLISOA',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA',
'TRCLISOA',cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'TRCLISOA', METHOD_OPT=>'FOR ALL
COLUMNS SIZE SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'DAZ',
'UTL_TABLAS_ME',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('DAZ',
'UTL_TABLAS_ME',cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('DAZ', 'UTL_TABLAS_ME', METHOD_OPT=>'FOR
ALL COLUMNS SIZE
SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'SOA',
'CHEQUES_ME',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA', 'CHEQUES_ME',
cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'CHEQUES_ME', METHOD_OPT=>'FOR
ALL COLUMNS SIZE
SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'SOA',
'CUP_VENC_ME',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA', 'CUP_VENC_ME',
cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'CUP_VENC_ME', METHOD_OPT=>'FOR
ALL COLUMNS SIZE
SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'SOA',
'EMISION',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA', 'EMISION',
cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'EMISION', METHOD_OPT=>'FOR ALL
COLUMNS SIZE SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'SOA',
'COMIS_DEF_ME',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA',
'COMIS_DEF_ME',cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'COMIS_DEF_ME', METHOD_OPT=>'FOR
ALL COLUMNS SIZE
SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'SOA',
'INSTRUMENTO_ME',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA', 'INSTRUMENTO_ME',
cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'INSTRUMENTO_ME',
METHOD_OPT=>'FOR ALL COLUMNS SIZE
SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
EXEC dbms_stats.delete_table_stats( 'SOA',
'OPERACIONES_ME',cascade_indexes=>TRUE,cascade_columns=>TRUE);
exec dbms_stats.gather_table_stats('SOA', 'OPERACIONES_ME',
cascade=>true,estimate_percent=>100,force=>true);
exec dbms_stats.gather_table_stats('SOA', 'OPERACIONES_ME',
METHOD_OPT=>'FOR ALL COLUMNS SIZE
SKEWONLY',cascade=>true,estimate_percent=>100,force=>true);
2015-10-22 9:26 GMT-04:00 Powell, Mark <mark.powell2@xxxxxxx>:
Have you checked the last_analyzed date for the tables and indexes
involved in this query? Perhaps running dbms_stats with 100% sample size
might be an option.
Posting the full SQL and plan is necessary for anyone to really provided
target suggestions.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Iggy Fernandez
*Sent:* Wednesday, October 21, 2015 7:23 PM
*To:* jcdrpllist@xxxxxxxxx; mvshelton
*Cc:* ORACLE-L
*Subject:* RE: performance. 7 queries independent queries takes 3s with
union all in view 98s
re: any idea is welcome
The 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'));
------------------------------
Date: Wed, 21 Oct 2015 18:20:58 -0400
Subject: Re: performance. 7 queries independent queries takes 3s with
union all in view 98s
From: jcdrpllist@xxxxxxxxx
To: mvshelton@xxxxxxxxxxxxx
CC: iggy_fernandez@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Thank you
To try I doubled the pga_aggregate_target, and it didn't fixed, in theory
I should reduce the memory because hash happens when there is a lot of
memory, as I remember.
So I tried to reduce the pga_aggregate_target and memory_target and the
problem is the same.
Based on the article of Iggy I added the hint
/*+ NO_QUERY_TRANSFORMATION */ and nothing.
disables alter system set "_optimizer_cost_based_transformation" = off ;
I remember it was of no use but I tried too
alter system set "_optimizer_max_permutations"=3000;
and either, any idea is welcome, thank you :)
2015-10-21 15:08 GMT-04:00 mvshelton <mvshelton@xxxxxxxxxxxxx>:
If your hash joins are slower are you sorting to disk, if so you may need
to increase your pga.
Matt
Sent via the Samsung Galaxy S™ III, an AT&T 4G LTE smartphone
-------- Original message --------
From: Iggy Fernandez
Date:10/21/2015 2:51 PM (GMT-05:00)
To: jcdrpllist@xxxxxxxxx, ORACLE-L
Subject: RE: performance. 7 queries independent queries takes 3s with
union all in view 98s
The answer should leap out right out of the query plan
Perhaps there's join factorization going on. See
https://blogs.oracle.com/optimizer/entry/optimizer_transformations_join_factorization
But the best bet is to examine the query plan.
Iggy
------------------------------
Date: Wed, 21 Oct 2015 11:47:18 -0400
Subject: performance. 7 queries independent queries takes 3s with union
all in view 98s
From: jcdrpllist@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Hello if you can help please
I found something very interesting, I tested two times to be sure. 11.2.0.3
There is a view having 7 queries joined with union all
query1
union all
query2
union all
...
When executed in the view all 7 takes 98s
If in the same view I put only one query at a time I get 3s total, the
query is unchanged
The problem is using a hash join instead of nested loops
1 1.38s
2 0.04s
3 1.11s
4 0.05s
5 0.35s
6 0.02s
7 0.09s
all 7 98x
Thank you :)
