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.