Re: Steps for performance tuning/debugging

A fundamental step is to understand what the query is trying to do. It is often fairly easy to work out why performance could be improved by examining the SQL Monitor (or rowsource execution stats) version of the plan without knowing what the SQL is actually supposed to achieve, but as a developer it should be possible to understand the requirements and data and draw a "map" of what you're trying to do. See this classic: Designing Efficient SQL: A Visual Approach - Simple Talk (red-gate.com) <https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/designing-efficient-sql-a-visual-approach/>

Regards
Jonathan Lewis

On Thu, 20 Jul 2023 at 15:37, yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Hello Experts,
> I understand there exists numerous books and blogs for performance tuning
> in Oracle database and also documents for standard coding guidelines
> available for developers. However, many times developer/support group folks
> ask to suggest key steps which they can perform for tuning queries or
> debugging performance issues before reaching out to DBA Groups.
>
> There are many scenarios which could occur and the solution will be
> different for each of them. So rather than making things too complicated ,
> I am looking for key steps which we can suggest to a developer to make
> their life easier in finding and fixing real issues rather than guiding
> them to follow more bookies' stuff. What should those steps be?
>
> Something comes to mind like:-
> Understanding the execution plan.
> After that with gather_plan_statistics hint, finding the estimated
> cardinality vs actual cardinality and lookout for major deviation.
> Which would then point to the fixing the statistics of specific objects or
> higher dynamic sampling etc.
>
> Anything else we can add or If you can guide me to any such blogs or
> already available Document, would be helpful?
>
> Regards
> Yudhi
>

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