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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Timur Akhmadeev
Sent: Wednesday, May 31, 2023 2:26 AM
To: quanwenzhao@xxxxxxxxx
Cc: l.flatz@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxxx> 于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
