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

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Thu, 22 Oct 2015 11:11:47 -0400
Message-ID: <CAGYrQys=eiSaoxxD6EPe+crj5u25mKFrR5hz0MgXmy9Hj5gBBg_at_mail.gmail.com>

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_at_hpe.com>:

> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Iggy Fernandez
> *Sent:* Wednesday, October 21, 2015 7:23 PM
> *To:* jcdrpllist_at_gmail.com; 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_at_gmail.com
> To: mvshelton_at_chartermi.net
> CC: iggy_fernandez_at_hotmail.com; oracle-l_at_freelists.org
>
> 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_at_chartermi.net>:
>
> 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_at_gmail.com, 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_at_gmail.com
> To: oracle-l_at_freelists.org
>
> 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 :)
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 22 2015 - 17:11:47 CEST

This message: [ Message body ]
Next message: Sandra Becker: "Re: Unable to execute runInstaller"
Previous message: Zelli, Brian: "sql dev"
In reply to: Powell, Mark: "RE: performance. 7 queries independent queries takes 3s with union all in view 98s"
Next in thread: Jonathan Lewis: "RE: performance. 7 queries independent queries takes 3s with union all in view 98s"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message