19c: Performance Tuning Simplified

 Today, Got an interesting scenario on performance Tuning. One of the Production was spiking up, 

Logged in to see the timeframe of the incident. ,. figured out the window of the issue. 

Started generated the ADDM report for the window.  Caution: You Need Licensing for Diagnostics+Tuning Packs. 

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

  434058914 FINPRDC             1 finprdc

Listing the last 3 days of Completed Snapshots

Instance     DB Name      Snap Id       Snap Started    Snap Level

------------ ------------ ---------- ------------------ ----------

..

..

finprdc      FINPRDC          93966  26 Sep 2023 00:00    1

                              93967  26 Sep 2023 01:00    1

                              93968  26 Sep 2023 02:00    1

                              93969  26 Sep 2023 03:00    1

                              93970  26 Sep 2023 04:00    1

                              93971  26 Sep 2023 05:00    1

...

...

                              94024  28 Sep 2023 10:00    1

                              94025  28 Sep 2023 11:00    1

                              94026  28 Sep 2023 12:01    1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 94022

Begin Snapshot Id specified: 94022

Enter value for end_snap: 94023

End   Snapshot Id specified: 94023

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_94022_94023.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name addmrpt_1_94022_94023.txt

Running the ADDM analysis on the specified pair of snapshots ...

Once you have ADDM analysis, work on the tuning those. In my case, I found bunch of SQLs causing the spike on the system. 

2jby734cpv2n9

099b782mjhyu2

350m76kgfr3wy 

aku1s143kcka5

2bqx223ywv6ys

f8ngukz9ytn70

45f6rjbat9c25

8n5k2yanx8axv

63jkajtd53g3z

I started working on the Tuning those SQL using SQL Tuning Advisor. You can use SQL Tuning Advisor steps on the previous writings https://applicationsdba.blogspot.com/2021/12/sql-tuning-through-sql-profiling.html

In Today's case, I found statistic went off from Tuning Advisor Report, I started gathering stats to resolve the issue. 

You can use below views.

DBA_ADVISOR_TASKS - Basic information about existing tasks.

DBA_ADVISOR_LOG - Status information about existing tasks.

DBA_ADVISOR_FINDINGS - Findings identified for an existing task.

DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task.

This is what I have executed. 

select * from DBA_ADVISOR_TASKS ORDER BY TASK_ID DESC

Found the Tuning Task ID to get the recommendation information. 

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_task_099b782mjhyu2') from DUAL;

...

...

  Recommendation

  --------------

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'SABRIX', tabname =>

            'SABRIX_XMLTYPE_TEMP_GT', estimate_percent =>

            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE

            AUTO');

I have to take care of the scheduling the stats on app schema to fix this permanently.