Tuning des applications

Rédacteur: V. Fenoll & Oracle Doc.

Collecte auto des stats GATHER_STATS_JOB

Job créé automatiquement lors de la création de la base
Il appelle la procédure GATHER_DATABASE_STATS_JOB_PROC qui affecte des priorités aux objets nécessitant des stats.
La collecte auto n’est pas prise en charge pour les tables externes, les statistiques systèmes et les objets fixes.

Optimisation des interrogations OPTIMIZER_DYNAMIC_SAMPLING = 2

Il est préférable de ne pas calculer de stats sur les objets très MAJ et d’augmenter ce paramètre à 4 ou 5. Quelquefois utilisé par l’optimiseur conjointement aux stats s’il pense que c’est utile..
PGA_AGREGATE_TARGET = AUTO par défaut maintenant
Nouvelle colonne TIME dans PLAN_TABLE

Statistiques sur les objets du dictionnaire avec DBMS_STATS

GATHER_FIXED_OBJETS_STATS  et     GATHER_DICTIONARY_STATS
Ou utiliser GATHER_DATABASE_STATS ( …    GATHER_FIXED=’TRUE’  et
GATHER_SYS = ‘true’)

Méthode recommandée pour le calcul des stats

DBMS_STATS (options => ‘GATHER AUTO’)

Diverses MAJ statistiques

DBMS_STATS comporte de nouvelles valeurs :
GRANULARITY     => AUTO (default)
=> GLOBAL AND PARTITION
DEGREE        => AUTO_DEGREE

Verrouiller les stats avec DBMS_STATS

.LOCK_[TABLES | SCHEMA]
.UNLOCK_[TABLES | SCHEMA]
select stattype_locked from dba_tab_statistics

Divers DBMS_STATS

FORCE        => ‘true’
Pour forcer la suppression/remplacement des statistiques même si elles sont verrouillées.

Historique des stats

Les anciennes version des stats sont enregistrées.
DBA_OPTSTAT_OPERATIONS pour connaître l’heure de début et de fin de toutes les opérations
DBA_TAB_STATS_HISTORY pour connaître l’historique des modifications apportées aux stats au cours des 31 derniers jours.

Gérer l’historique des statistiques

Pour restaurer DBMS_STATS.RESTORE_[TABLE | SCHEMA | DATABASE]_STATS en utilisant un timestamp.

.RESTORE_FIXED_OBJECTS_STATS requiert le privilège ANALYZE ANY DICTIONARY
.RESTORE_DICTIONARY_STATS requiert le privilège ANALYZE ANY ou ANALYZE ANY DICTIONARY
.RESTORE_SYSTEM_STATS

Configuration / récupération de la période de conservation:
.ALTER_STATS_HISTORY_RETENTION
.GET_STATS_HISTORY_RETENTION

Purge manuelle des stats :
.PURGE_STATS

Gérer les stats dans EM

Page Manage optimizer Statistics

Modification de la surveillance LMD des tables

Le paramètre STATISTICS_LEVEL est le commutateur de surveillance (MONITORING) des tables. Avec TYPICAL et ALL toutes les tables sont monitorées.

ALTER TABLE MONITORING devient obsolète.

Obsolescence de l’optimiseur RBO

Les valeurs CHOOSE et RULE ne sont plus prises en charge pour OPTIMIZER_MODE.
ALL_ROWS est la valeur par défaut pour  OPTIMIZER_MODE.

Automatic SQL Tuning : Présentation

Automatic SQL Tuning est proposé à l’utilisateur par l’intermédiaire de la fonction de conseil STA (SQL Tuning Advisor).

STA remplace le réglage tuning manuel des instructions SQL.

Lorsqu’il est appelé en mode réglage, l’optimiseur est nommé optimiseur ATO (Automatic Tuning Optimizer). Le réglage effectué par ATO est appelé Automatic SQL Tuning .

La tâche qui consiste à identifier les instructions SQL à forte consomamtion de ressources a été automatisée au moyen du moniteur ADDM (Automatic Database Diagnostic Monitor).

STA : Présentation

STA (SQL Tuning Advisor) appelle l’optimiseur ATO pour effectuer 4 types d’analyses :
–    Analyse des stats
–    Profiling des instructions SQL
–    Analyse de chemin d’accès  (proposition de création / maj index)
–    Analyse de structure SQL (modification syntaxique ou sémantique d’une requête)

STA accepte une ou plusieurs instructions SQL en entrée :
–    Requête spécifique
–    Cache
–    AWR
–    Charge globale personnalisée

STA créé une tâche de réglage sous réserve du privilège ADVISOR.

Option LIMITED : vérification des stats, chemins d’accès et structure SQL. Pas de profil généré
Option COMPREHENSIVE : appelle également l’optimiseur en mode Profiling.

Analyse des stats

Recherche de stats obsolètes
Génération de stats auxiliaires

Profiling des instructions SQL

Au cours du profiling, ATO effectue des vérifications afin de valider ses propres estimations. La validation consiste à appliquer les prédicats à un échantillon de données ou à exécuter un fragment de l’instruction SQL.

ATO utilise l’historique d’exécution de l’instruction SQL.

ATO construit un profil SQL s’il a généré des infos auxiliaires puis il recommande l’acceptation du profil généré afin de l’activer.

Le profil est stocké dans le schéma SYS. Le plan correspondant n’est pas gelé.

STA   ATO   Profil   Dict   User

Analyse de chemin d’accès

Toute recommandation est spécifique à l’instruction SQL réglée.
L’analyse de chemin d’accès génère les recommandations suivantes :
–    Créer de nouveaux index
–    Exécuter SAA (SQL Access Advisor)

Analyse de structure SQL

Permet de détecter :
–    Utilisation de NOT IN à la place de NOT EXIST
–    Utilisation de UNION à la place de UNION ALL
–    L’utilisation de prédicats qui ne permettent pas d’utiliser les index
–    Des erreurs de conception (e.g produits cartésiens)

Package DBMS_TUNE

.create_tuning_task
.execute_tuning_task
.report_tuning_task
.accept_sql_profile

Argument FORCE_MATCH =’true’ permet de « binder » les instructions SQL.

Pour créer un profil : CREATE ANY SQL PROFILE

Import/export de profils SQL ou de STS (SQL Tuning Sets)

Utile d’une base de test vers une base de prod.

–    Création d’une table intermédiaire : CREATE_STGTAB_SQLPROF
–    Exécuter la proc : PACK_STGTAB_SQLPROF
–    Déplacer la table intermédiaire
–    Sur la cible, exécuter la proc : UNPACK_STGTAB_SQLPROF

Catégorie de réglage

Paramètre d’initialisation SQLTUNE_CATEGORY = nom de la catégorie du profil SQL utilisé.
Par défaut, la fonction de conseil STA applique automatiquement les profils SQL de cette catégorie.

Chaque session peut changer de catégorie en modifiant de manière dynamique ce paramètre.

Présentation SAA

SAA = SQL Access Advisor

Recommande la création, mise à jour ou suppression :
–    d’index (1)
–    de vue matérialisées (2)
–    de journaux de vue matérialisées

EM propose une analyse de (1) ou (2) ou (1 et 2)

Ne recommande pas de suppression pour les charges globales partielles
Optimise les vues pour une utilisation mex de la réécriture et de rafraichissement Fast.
Combine des index similaires en index uniques

SAA accepte une ou plusieurs instructions SQL en entrée :
–    Une source hypothétique
–    Cache V$SQL
–    STS
–    Charge globale personnalisée

SAA fournit également des filtres de charge ; e.g les 30 instructions les plus consommatrices, certaines tables ou utilisateurs

Définition de la charge globale pour SAA

Permet par la suite de générer des recommandations (contrairement à une charge partielle).

Les sources sont les suivantes :
–    current and recent sql activity
–    import workload from SQL repository (pour désigner un STS)
–    user defined workload : import SQL from a table or view
–    hypothetical workload: fourniture d’un schema

Options relatives aux recommendations

Dans les “Advanced options”:
–    Workload Volatility / Scope : pour favoriser les operations en lecture seule ou tenir compte de la volatilité des objets lors des recommandations
–    Il est possible de personnaliser les recommandations générées en classant par « Optimizer cost, buffer gets, … »

Flux de procédure pour SAA

Etape 1 : Créer et gérer les tâches et les données (utilise objet workload + 1 tâche)
CREATE[DELETE]_TASK
CREATE[DELETE]_SQLWKLD
Etape 2 : Préparer les tâches (utilise charge globale et paramètres d’accès)
Etape 3 : Collecter et gérer la charge globale  (utilise objet workload)
Etape 4 : Préparer et analyser les données (utilise objets workload)

La procédure DBMS_ADVISOR.QUICK_TUNE est un raccourci qui effectue toutes les opérations nécessaires pour analyser une instruction SQL unique.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.