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

Hi Andy :-),

The SQL is not written by me, just has been found from our monitor platform, probably ex-colleague wrote it. After I observed it also seems to be difficult to understand its complex nest structure clearly.

Yes, your re-writing method makes me happy and opens my eyes to brightly. Very cool. I've tested it again, only taking *30 - 40 seconds* to return the result we expected. Oops, the original SQL query are spending *8 minutes or even longer* to retrieve the result.

Thank you so much! Re-writing SQL is a fabulous and one of the most efficient tuning methods.

Best Regards
Quanwen Zhao

Andy Sayer <andysayer_at_gmail.com> 于2023年5月31日周三 13:30写道：

> 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