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

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

Stratos Matzouranis

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

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

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

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

Πολλές φορές θα τύχει να ακούσουμε ότι ξαφνικά ένα query που πήγαινε γρήγορα ξαφνικά άρχισε να αργεί ή ότι ένα query πηγαίνει αδικιολόγητα πολύ αργά.

Ο πιο εύκολος τρόπος να βρούμε τι φταίει και να το λύσουμε είναι με την εκτέλεση του SQL Tuning Advisor της Oracle.

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

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

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

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

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

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

Έπειτα θα πρέπει να φτιάξουμε το tuning task συμπληρώνοντας το SQL_ID και δίνοντας ένα TASK_NAME.

declare
task_nm varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '5t5mxfm0ph3n0',TASK_NAME => 'SQL_TUNNING_TASK_5t5mxfm0ph3n0');
end;
/

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

exec dbms_sqltune.execute_tuning_task (TASK_NAME => 'SQL_TUNNING_TASK_5t5mxfm0ph3n0');

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

SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 200;
SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNNING_TASK_5t5mxfm0ph3n0') 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 μας αναλύει τα δύο διαφορετικά πλάνα το ένα που έχει τρέξει το query και το άλλο που μας προτείνει.

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SQL_TUNNING_TASK_5t5mxfm0ph3n0
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
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 = 'SQL_TUNNING_TASK_5t5mxfm0ph3n0';
 execute dbms_sqltune.drop_tuning_task('SQL_TUNNING_TASK_5t5mxfm0ph3n0');

Πηγές:

• Analyzing SQL with SQL Tuning Advisor