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