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

Hi Quanwen,
Since your join back to GV$SQLAREA_PLAN_HASH was on what should be unique columns, I don't see why you wouldn't just pull everything from the first pass. I've attached what I'd do in a rush. A quick look at the monitor suggests that the IO is done by having to do the join so this should be a pretty good win.

Hope that helps,
Andy

On Tue, 30 May 2023 at 22:20, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:

> And also the ADDM report in attachment - not sure how much size Oracle-L
> allowed the max size is, because SQL monitor report has only 10 KB but has
> 65 KB for a screenshot Oracle-L is forbidden to send (probably the max
> allowed size is 50 KB).
>
> Quanwen Zhao <quanwenzhao_at_gmail.com> 于2023年5月31日周三 13:12写道：
>
>> 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
>>>
>>>
>>>

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




application/octet-stream attachment: 47h5f27tkdadh_unwithed.sql