Re: How to avoid using the execution plan with parallel on oracle rac 19.13?

Hello Mark,

I wanna use separately v$view and then union all of each v$ but the approach need extra app to summary it.

Not trying it so far -
*https://oraganism.wordpress.com/2012/05/20/gv_function/* <https://oraganism.wordpress.com/2012/05/20/gv_function/>.

Mark W. Farnham <mwf_at_rsiz.com> 于2023年5月31日周三 19:22写道：

> completely untested, but since instance id is in the query it seems to me
> a union all of each v$ in place of using gv$ would keep “n” lowest for all
> orders of operation.
>
>
>
> For a small number of instances the savings may be worth the extra code,
> but unless you’ve built a generator copy the single query to multiple
> chained together with union all substituting the instance identifier, it
> would tend to be tedious and error prone for a large number of instances.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Timur Akhmadeev
> *Sent:* Wednesday, May 31, 2023 2:26 AM
> *To:* quanwenzhao_at_gmail.com
> *Cc:* l.flatz_at_bluewin.ch; oracle-l_at_freelists.org
> *Subject:* Re: How to avoid using the execution plan with parallel on
> oracle rac 19.13?
>
>
>
> Apart from re-writing the query as Andy suggested you should try using
> GV$SQLSTATS_PLAN_HASH instead.
>
> It misses a few columns compared to V$GV$SQLAREA_PLAN_HASH but it is less
> expensive, and has longer data retention.
>
>
>
> On Wed, May 31, 2023 at 8:14 AM Quanwen Zhao <quanwenzhao_at_gmail.com>
> wrote:
>
> Hello Lothar,
>
>
>
> You can see the attachment I've uploaded in - it's a SQL monitor report
> generated by tool sqlhc.
>
>
>
> Lothar Flatz <l.flatz_at_bluewin.ch> 于2023年5月30日周二 17:53写道：
>
> Hi,
>
> unfortunately there is no attachment in this mail.
> A query of the v$ views is often one of the most difficult to tune.
> Statistics are often incorrect, estimates are wrong and there is a
> tendency by the optimizers to generate inefficient plans.
> The general strategy is break the query into smaller parts useing WITH
> .. /*+ materialize */ to prohibit view merging.
> Even if you read the best books, you would be still a novice with tuning.
> Yo would need somebody experienced to help you.
>
> Thanks
>
> Lothar
> Am 28.05.2023 um 07:23 schrieb Mladen Gogala:
>
>
>
> On 5/27/23 23:10, Quanwen Zhao wrote:
>
>
>
> If so, how to optimize the sql to reduce I/O and query time? By the way
> I've generated the *SQL monitor* and *ADDM reports* for this sql and
> uploaded them in attachment.
>
>
>
> Use the Force Quanwen, use the Force. Just kidding. The Force emits CO2
> and that's bad for the environment. Greta wouldn't want you to use to
> Force.
>
> What you are asking is how to become a DBA. The best way is to join a
> large organization which has several levels of DBA personnel and have a
> senior DBA mentor you. I would seriously advise Tom Kyte's "Expert Oracle
> Arachitecture", the "Core DBA" book by Jonathan Lewis and, mandatory, Cary
> Millisap's: "Optimizing Oracle for Performance". The last book is written
> in time of Oracle 8i, but is one of the philosophically most important
> bookse ever written about Oracle. This is, of course, just for the
> starters. After that, there is Christian Antognini's Oracle tuning book and
> many others. Being a DBA is a life long process. A good way to start is to
> figure out the air speed velocity of an unladen swallow. One has to know
> these things when you're a DBA
>
>
>
> --
> Mladen Gogala
> Database Consultantu
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
>
>
>
>
>
>
> --
>
> Regards
> Timur Akhmadeev
>

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