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
