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@xxxxxxxxx> 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@xxxxxxxxx> 于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@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