Re: Steps for performance tuning/debugging

Thank you so much Jonathan. That helps.

Your footprint is everywhere :) . Actually I was looking into this blog ( https://jonathanlewis.wordpress.com/explain-plan/) of yours which mainly guides us to see and interpret the rowsource execution stats. But I understand your point that as a developer, one should understand the where/how much the data and relation between them, to come up with optimal query.

I was also trying to see if we can put some technical steps in which if the support group guy doesn't have an idea about the data he can also be able to take some action to debug and find the issue. So basically we encounter two types of issue 1)Real time performance issue , for which real time sql monitor , v$* views, sql trace report will be helpful. And 2)Historical performance issues , for which DBA_HIST* views, AWR reports will help to find the bottleneck points. And to fix the issues, we have approaches like fixing object statistics, modifying sql statements, optimizing data access using indexes, partitions etc.

Correct me if we are approaching in any wrong way.

Regards
Yudhi

On Thu, Jul 20, 2023 at 10:51 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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