Πώς βελτιώνουμε ένα query που είχε τρέξει στο παρελθόν σε βάση δεδομένων της Oracle (SQL Tuning Advisor with AWR Snapshots)

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
• Πώς κάνουμε μαζικά detach / attach βάσεις δεδομένων στον SQL Server - 2 Φεβρουάριος 2024

Σε προηγούμενο άρθρο είχαμε αναλύσει πώς βελτιώνουμε ένα query στην Oracle με την χρήση του SQL Tuning Advisor. Όμως για να εκτελέσουμε τον SQL Tuning Advisor θα πρέπει το query να βρίσκεται ακόμα στο Shared Pool δηλαδή στη μνήμη (RAM). Αν όμως το query είχε εκτελεστεί παλιότερα δεν θα υπάρχει πλέον στη μνήμη. Σε αυτό το άρθρο θα δούμε πως μπορούμε πάλι να τρέξουμε τον SQL Tuning Advisor αντλώντας πληροφορία μέσα από τα AWR Snapshots.

Θα πρέπει να θυμίσουμε ότι ο SQL Tuning Advisor κατά την εκτέλεση του συλλέγει πληροφορίες από διάφορες πηγές (όπως θα δούμε στην παρακάτω εικόνα) και στο τέλος κάνει προτάσεις ώστε να βελτιωθεί η απόδοση στο query.

Οι συστάσεις που μας προτείνει να κάνουμε μπορεί να είναι:

• Να πάρουμε στατιστικά σε συγκεκριμένους πίνακες καθώς είναι stale
• Να φτιάξουμε καινούργια indexes
• Να αλλάξουμε τον τρόπο που είναι γραμμένο το query
• Να χρησιμοποιήσουμε ένα SQL profile ώστε να κάνουμε force ένα διαφορετικό πλάνο για το query

Για να κάνουμε χρήση τον SQL Tuning Advisor θα πρέπει να έχουμε Enterprise έκδοση της Oracle ή να έχουμε αγοράσει το Tuning Pack

Το παράδειγμα

Για αρχή θα πρέπει να βρούμε το SQL_ID από το query που θέλουμε να δούμε. Αν δεν έχουμε το SQL_ID σε αυτό το άρθρο μπορείς να δεις πως θα το βρεις εύκολα μέσα από το dynamic view GV$ACTIVE_SESSION_HISTORY.

Αφού βρήκαμε το SQL_ID με το παρακάτω query θα δούμε τα διάφορα πλάνα που μπορεί να είχε αλλάξει μέσα στον χρόνο.

SELECT SQL_ID, PLAN_HASH_VALUE, TIMESTAMP FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='93uy7ndb6uf2z';
 

Αυτό που μας ενδιαφέρει τώρα είναι να κρατήσουμε τα snap_id από τα snapshots που θέλουμε να μελετήσουμε το query.

select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat 
where sql_id='93uy7ndb6uf2z' 
--and plan_hash_value='3448248699' 
order by snap_id desc;

Σαν επόμενο βήμα θα πρέπει να δημιουργήσουμε το tuning task συμπληρόντας το begin_snap/end_snap (χρονικά όρια που είχε τρέξει), το sql_id και να δώσουμε στο task κάποιο όνομα στο task_name.

DECLARE
   l_sql_tune_task_id  VARCHAR2(100);
BEGIN
   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                           begin_snap  => 71200,
                           end_snap    => 71201,
                           sql_id      => '93uy7ndb6uf2z',
                           scope       => DBMS_SQLTUNE.scope_comprehensive,
                           time_limit  => 60,
                           task_name   => '93uy7ndb6uf2z_AWR_tuning_task',
                           description => 'Tuning task for statement 93uy7ndb6uf2z  in AWR');
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Στη συνέχεια εκτελούμε το tuning task που δημιουργήσαμε πριν:

 
exec dbms_sqltune.execute_tuning_task (TASK_NAME => '93uy7ndb6uf2z_AWR_tuning_task');

Αφού ολοκληρωθεί βλέπουμε τα αποτελέσματα από την ανάλυση με το παρακάτω select query:

 
SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 200;
SELECT DBMS_SQLTUNE.report_tuning_task('93uy7ndb6uf2z_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24;

Στα αποτελέσματα θα δούμε σε τρεις διαφορετικές ενότητες.

Το General Section που περιέχει το SQL text.

Το Finding Sections που περιέχει τις προτάσεις που βρήκε όπως:

• Statistics Finding: Την δημιουργία στατιστικών σε έναν stale πίνακα που διάβάζει το query
• Index Finding: Που προτείνει την δημιουργία index στο πεδίο που βρισκότανε στο where clause
• SQL Profile Finding: Προτείνει να ενεργοποιήσουμε το SQL Profile που δημιούργησε που κάνει χρήση ενός καλύτερου πλάνου

Τέλος στο Explain Plan Section μας αναλύει τα 2 διαφορετικά πλάνα το ένα που έχει τρέξει το query και το άλλο που μας προτείνει.

        | OTHER_AFM                   |   12
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : 93uy7ndb6uf2z_AWR_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 01/01/2021 17:22:25
Completed at       : 01/01/2021 17:22:54

-------------------------------------------------------------------------------
Schema Name: USERS
SQL ID     : 5t5mxfm0ph3n0
SQL Text   : SELECT   TR.NAME,
                                                                  TR.AGE,
                                                                  TR.CITY,
                                                                  TR.SIGN_DATE
                                                            FROM    USERS.CUSTOMERS TR
                                                            WHERE   
                                                                  TR.SIGN_DATE  = TO_DATE('30/12/2020','DD/MM/YYYY')

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Optimizer statistics for table "USERS"."CUSTOMERS" are stale.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'USERS', tabname =>
            'CUSTOMERS', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO', cascade => TRUE);

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.


2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
 
  Recommendation (estimated benefit: 99.99%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index USERS.IDX$$_17C0B0001 on
    USERS.CUSTOMERS("SIGN_DATE");
	
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 68.27%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'SQL_TUNNING_TASK_5t5mxfm0ph3n0', task_owner => 'SYS', replace =>
            TRUE);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 321555184

--------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |     6 |   330 |    19   (0)| 0
|   1 |  PX COORDINATOR      |                  |       |       |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000         |     6 |   330 |    19   (0)| 0
|   3 |    PX BLOCK ITERATOR |                  |     6 |   330 |    19   (0)| 0
|*  4 |     TABLE ACCESS FULL| CUSTOMERS |     6 |   330 |    19   (0)| 0
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("TR"."SIGN_DATE"=TO_DATE(' 2020-12-30 00:00:00', 'syyyy-mm-dd')

2- Using SQL Profile
--------------------
Plan hash value: 3378393080

--------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     6 |   330
|   1 |  PARTITION RANGE SINGLE             |                    |     6 |   330
|   2 |   INLIST ITERATOR                   |                    |       |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| CUSTOMERS   |     6 |   330
|*  4 |     INDEX RANGE SCAN                | INX_CUSTOMERS_1 |     1 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TR"."SIGN_DATE"=TO_DATE(' 2020-12-30 00:00:00', 'syyyy-mm-dd')
        

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

PL/SQL procedure successfully completed

Τέλος για να σβήσουμε το task ή να δούμε σε τι κατάσταση βρίσκεται εκτελούμε τα παρακάτω:

--SELECT task_name, status FROM dba_advisor_log WHERE task_name = '93uy7ndb6uf2z_AWR_tuning_task';
execute dbms_sqltune.drop_tuning_task('93uy7ndb6uf2z_AWR_tuning_task');

Πηγές:

• Analyzing SQL with SQL Tuning Advisor
• Automatic Workload Repository