Examne sybase corrige

LPRO-ABD & ABD AVANCEE 2009/2010

Royaume du Maroc

Ministère de l'Education Nationale,

de l'Enseignement Supérieur, de la Formation

des Cadres et de la Recherche Scientifique

Université IBN TOFAIL

Licence professionnelle

Administration des bases de données

Promotion 2009-2010

PARTIE I : QCM MIN 2 / MAX 14.5 Moyenne de la

classe 10/ 25

1- Lors de l’analyse du rapport de SP_SYSMON on remarque que : SPINLOCK CONTENTION = 80%. Quelle(s) opération(s) doit-on entreprendre ? (2p) chaque partition ou cache ajouté aura sa propre HASH TABLE

Ajouter un buffer pool de 16k

Augmenter la taille des caches de données

Augmenter la taille des devices

Affecter plus de processeurs à ASE

Augmenter la mémoire allouée à l’ASE

X Partitionner les caches existants - Ajouter des caches de données et les alloués aux objets les plus utilisés

2- Quel est le propos de l’option de création de device : dsync? (1p) permet l’écriture directe sur disque sous unix

Synchroniser l’écriture dans les devices et leur miroir

Synchroniser l’écriture dans les bases de données utilisateurs et la base Master

X Contrôler l’écriture vers le système d’exploitation

Contrôler l’écriture lors d’une sauvegarde

3- Quelles actions peuvent causer un verrouillage sur les tables systèmes de la base Master ? (2p)

Exécution d’une transaction avec beaucoup d’insert

Exécution d’une transaction avec beaucoup d’update

X Exécution de plusieurs scripts de création de base de données avec l’allocation des ressources.

Suppression massive de colonnes

4- Sur quel niveau les statistiques table/indexe sont stockées? (1p) sysstatistics contient les statistiques sur les colonnes !!

Sysstatistics

X Systabstats

Indexe leaf level

Page système

5- Comment diagnostiquer un SKEW ? (1p) un SKEW et un balancement non équitable des partitions d’une table

Optdiag

X Sp_help

X Sp_helpartition

Sp_helpsegment

6- Quand on supprime une ligne d’une table DOL, qu’est ce qui est vrai ? (1p) suppression logique d’une ligne dans une

table DOL réclamé automatiquement ou par REORG ou autre technique de défragmention

Les lignes suivantes sont déplacées vers le haut

L’espace reste libre pour une éventuelle insertion

X L’espace ne sera pas réclamé immédiatement mais peut l’être par la suite

L’espace est mis à 0 et sera perdu à jamais

7- La table Tab1 a un indexe créé selon la commande : CREATE INDEX IDX1 ON TAB1 (COL1, COL2 DESC). Quelle(s) requête(s) n’auront pas besoin d’un tri? (2p) col1 est la clé principale de l’indexe. Et puisque le dernier niveau de l’indexe

est toujours chaîné on peut renverser l’ordre de tri mais jamais l’ordre des colonnes dans la clause ORDER BY

Select * from tab1 order by col1, col2

X Select * from tab1 order by col1, col2 desc

Select * from tab1 order by col2 desc, col1 asc

X Select * from tab1 order by col1 desc, col2 asc

EXAMEN CORRIGE

Examen – Administration base de données.


8- La table Tab1 a 50000 lignes, et un indexe non clustérisé sur col1. Quelle requête utilisera moins d’I/O? (1p)

Min et Max dans la même requête est toujours plus couteuse est déconseillée

Select min(col1) as MINIM, max(col1) as MAXIM from Tab1

X Select MINIM=(select min(col1) from Tab1), select MAXIM=(select max(col1) from tab1)

Aucune différence entre les deux.

9- Une procédure stockée d’UPDATE utilise une jointure entre Tab1 et Tab2. Lors de son exécution on remarque que l’OPTIMIZER, dans des cas choisit la table Tab1 comme externe, dans d’autre cas il choisit Tab2. Comment réduire ou éliminer les éventuels DEADLOCK ? (1p) faut garder le même ordre de choix de la table externe et celle interne pour que les requêtes suivent le même chemin de verrouillage de pages

Créer un indexe sur Tab1

X Set force plan

Set sort_merge

Créer une vue sur les deux tables

10- La promotion du verrouillage au niveau serveur est configurée comme suit : Page lock promotion (HWM 200, LWM 100, PCT 60) Row lock promotion (HWM 1800, LWM 1200, PCT 70). La table DOL Tab1 a 200 pages et 10 lignes par page. Une requête sur Tab1 a besoin de 1400 verrouillages de ligne. Quelle sera la promotion de verrouillage dans ce cas ? (3p) la promotion est toujours vers la table entière

Aucune promotion

Promotion vers le verrouillage de 140 pages

Promotion vers le verrouillage de 140 pages puis un verrouillage de table

X Promotion vers le verrouillage de table

11- Qu’est ce qui faux à propos du partitionnement d’un cache ? (2p)

X Le cache par défaut ne peut pas être partitionné (il peut l’être)

Un cache avec un buffer pool de 16 pages peut être partitionné

X Un cache de 2MO peut être partitionné en 8 partitions (minimum taille de 256 pages)

Un cache relaxé LRU peut être partitionné

X Un cache de 200MO peut être partitionné sur 128 partitions (maximum 64 partitions)

12- Qu’est ce qui est vrai à propos d’un verrouillage au niveau ligne (DOL ROW)? (2p)

Les pages d’indexe sont verrouillées

Aucun verrouillage

Verrouillage de pages ou de table

X Verrouillage de lignes ou de table (soit ligne soit la promotion vers la table entière)

13- Quels sont les solutions pour diminuer le verrouillage sur la dernière page d’une table DOL ? (1p)

Partitionner la table

Créer un indexe clustérisé

Changer le type de verrouillage en DOL ROW

Lier la table à un cache de données nommé

X Aucune de ces actions (la concurrence sur la dernière page est sentie lors de l’insertion alors que dans les tables DOL on

n’a pas ce problème)

14- L’utilisation d’un curseur de modification est avantageuse qu’une commande SQL, car ____? (2p)

Plus rapide (le curseur est plus lent)

X Concurrence optimale côté verrouillage (le fait de COMMITET à chaque ligne modifiée libère la page du verrouillage)

Moins de lignes dans le journal de transaction (produit + de ligne dans le journal car on doit COMMITER à chaque modification)

Meilleur gestion des privilèges utilisateur (les utilisateurs doivent avoir les privilèges)

15- Qu’est ce qui est vrai pour une table DOL avec un cluster ration = 0.9 ? (2p) supérieur à 0.8 pas de fragmentation,

les données sont très bien structurées

La table est mal structurée et doit être optimisée

Les données sont denses ce qui produira un Page-spliting

X Large IO est particulièrement efficace

L’indexe clustérisé est aussi grand que la table

16- Quelle est le nombre maximal de tables de travail pour une jointure? (1p)

10

12

X 14

16



PARTIE II : Question / Réponse MIN 0 / MAX 19 Moyenne de la

classe 9.88/ 25

1. Quelle est la hiérarchie de stockage dans Sybase. (2p)

Device, database fragment, allocation unit, extent, page

2. La requête suivante est exécutée très fréquemment

Select t1.col1, t1.col2, t2.col1, t2.col2, t2.col3 t2.col4

from tab1 t1, tab2 t2 where t1.col1=t2.col1

Dans un souci de performance, quelle technique de dénormalisation peut-on utiliser ? (2p)

Réponses possibles : Joindre la table Tab1 et Tab2 dans une seule table. Placer la colonne Tab1.Col2 dans Tab2

3. Quel est le propos de l’option de création de table exp_row_size. Quelles sont ses conditions?

(2p) Réserve un espace libre à la fin de ligne pour une éventuelle augmentation de la taille de la ligne ce qui nous permettra

d’éviter le ROW FORWORDING. A Utiliser avec des tables DOL sur des colonnes de longueur variable.

4. Citez 2 raisons pour qu’ASE n’utilise pas un large I/O. (1p)

Réponses possibles : Les pages souhaitées se trouvent déjà sur un buffer pool d’une page. Aucun large buffer pool n’existe. Les pages souhaitées se trouvent sur le premier extent de l’unité d’allocation. Le Prefetching est désactivé…

5. Donnez l’algorithme d’accès aux données dans Sybase. (2p)

Login

Permission

Proxy

DB User

Alias

Guest

Refus

6. Quand une page est flashée manuellement sur le disque ? (2p)

Après l’exécution manuelle de la commande CHECKPOINT (Autre réponse : après un COMMIT pour les pages du journal)

7. Quel est le propos des THRESHOLD ? (2p)

Procédures stockées utilisateurs exécutées quand l’espace libre (en terme de pages) sur le segment observé à atteint le seuil configuré.

8. Considérant la partie du rapport de SP_SYSMON suivante :

Cache Turnover

Buffers Grabbed 553.2 997.3 9956979 n/a Buffers Grabbed Dirty 0.0 0.0 10 0.01 %

……………………………………………………………… ……………………………………………………………… Asynchronous Prefetch Activity

APFs Issued 71.9 129.7 1294853 63.7 % APFs Denied Due To

APF I/O Overloads 0.0 0.0 0 0.0 % APF Limit Overloads 10 20 20780 10 % APF Reused Overloads 0.2 0.4 4053 0.2 %



Y a-t-il des problèmes sur ce serveur ? Si oui, lesquels ? Comment peut-on les résoudre ? (4p)

Oui.

1- Buffers Grabbed Dirty > 0 des DIRTY PAGES ont été trouvée dans la zone de vidage à l’extrémité LRU

Solution : revoir la configuration générale des caches et celle de la mémoire de l’ASE.

2- APF Limit Overloads = 10% un nombre important d’APF refusés pour manque de buffers Solution : augmenter la taille du cache, du pool et la portion des APFs dans le pool.

9. Citez 4 paramètres pris en compte par l’OPTIMIZER lors de l’optimisation des jointures par

fusion. (3p)

Réponses possibles : Set sort_merge on ; présence d’indexe ; Equi-jointure ; aucune colonne LOB ; nombre de table temp < 14…

10.

A- Ecrivez un script permettant de régénérer tous les triggers de la base de données. (2p)

ddlgen –Usa –P –Sserveur –TTR –Ddb_test –Oscript_trig.sql Créer un fichier script_trig.bat ayant comme texte l’instruction isql –Usa –P –Snom_serveur –iscript_trig.sql

Autre réponse : Utiliser le mode graphique !!!

B- Ecrivez un script permettant de vérifier et réindexer (en cas d’erreurs) toutes les tables

indexées. (3p)

select distinct('dbcc reindex (' + so.name + ')') from sysindexes si, sysobjects so

where si.id=so.id and si.indid between 1 and 250 and so.type='U'

Créer un fichier script_reindex.bat ayant comme texte l’instruction isql –Usa –P –Snom_serveur –iscript_reindex.sql

EXAMEN /20 (50%) TP et TD CLASSE

/20 (25%)

TP et Appréciation

/20 (25%)

Moyenne Générale

/20 (NOTE FINALE)*

MIN 0.80 8 0 4

MAX 13 20 20 18

Moyenne 6.36 13.78 10.14 9.72

Validation 14 Rattrapage 15

* : NOTE FINALE = ((NOTE TP TD CLASSE + APPRECIATION) x 0.25)

+

((NOTE TP + APPRECIATION) x 0.25)

+

(NOTE EXAMEN x 0.5)

+

MAJORATION

Sports

Examne sybase corrige