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

Fortunately Andy’s re-write moots the need to add any complexity for possible execution cost reduction.  

His code is typically (for him) easy to read, documenting the goal just by reading query, and tends to be friendly to the CBO generating a useful plan.  

mwf  

From: Quanwen Zhao [mailto:quanwenzhao_at_gmail.com] Sent: Wednesday, May 31, 2023 9:55 AM
To: Mark W. Farnham
Cc: timur.akhmadeev_at_gmail.com; 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?  

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