SelectEtoile : Base de connaissance SGBD

Posts Tagged 'performance'

Trace Serveur Session ORACLE

  • Différents paramètres :

TIMED_STATISTICS : mise en place de la collecte des informations sur le temps d’attente d’un événement.

STATISTICS_LEVEL : Niveau de collecte des statistics (Basic, Typical ou ALL)

DB_CACHE_ADVICE : off, ready ou on

TIMED_OS_STATISTICS

SQL_TRACE

LOG_CHECKPOINT_TO_ALERT

  • Serveur :
    • V$systat : activité compteur de l’instance (voir aussi v$statname)

    • V$system_event : compteur d’attente (associé à v$event_name(name))


  • Session :

    • V$session v$sesstat v$statname

    • V$session_wait (temps reel) v$event_name

    • V$session_event (cumul)

    • V$mystat : stat de ma session

    • Exemples de requête :

select vsn.name,vst.value from v$sesstat vst,v$statname vsn where vst.sid=7 and vst.STATISTIC#=vsn.STATISTIC# order by 2

 

select * from v$session_wait where sid=7


SQL> select s.sid,s.username, n.name, t.value

from v$statname n, v$session s, v$sesstat t

where s.sid=t.sid

and n.statistic#=t.statistic#

and s.type='USER'

and n.name='session pga memory'

order by 4


USERNAME NAME VALUE

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

SYS session pga memory 357848

 

  • Exemple de trace d'une requete

 

Alter system set sql_trace : Setting sql_trace=true is a prerequisite when using tk prof.

Alter system set timed_statistics : Setting timed_statistics=true might be usefule when using tk prof

Mise en place de la trace sur une session :

alter system set sql_trace=true;

alter session set sql_trace=true;

sys.dbms_system.set_sql_trace_in_session(session's id,serial number, true)

alter system set timed_statistics=true;

alter session set timed_statistics=true;

 

 

Les fichiers de trace sont dans user_dump_dest de v$parameter

Une fois les traces mises, nous lancons la requete problematique, puis nous mettons à false les traces ou on se deconnecte.

Pour analyser les traces, il suffit d’aller dans le repertoire de log et de lancer « tkprof ‘nom_fic_trc’ ‘fic_out’ »

 

 

 

  • Divers

    • Connaître le plan d’une requete : ‘explain plan for’

    • Connaître activité user : trace tkprof

    • Mise en place des traces : ‘alter session set SQL_TRACE true ;’

    • Voir plan d’un requete dans library cache : v$sql_plan

 

  • Analyser les trace oracle
Dans le pfile:
    sql_trace=true
    user_dump_dest=$USER_DUMP_DEST

ou dans une session:

exec sys.dbms_system.set_sql_trace_in_session(SID,SERIAL#,true);

Analyse des fichier traces:

for i in  $(ls *.trc)
do
 tkprof $i $i.out explain=system/cdcmgr sys=no sort=prscnt,prscpu,prsela,execpu,execnt,exerow,exemis,fchcpu,fchela,fchdsk,fchrow,userid waits=yes
done

 

 

  • Session consommatrice en espace TEMP :

 

SELECT   se.osuser, se.username, se.sid,su.extents, su.blocks * to_number(rtrim(p.value)) AS Space,tablespace
FROM v$sort_usage su, v$parameter p, v$session se
WHERE    p.name = 'db_block_size'
AND      su.session_addr = se.saddr

 

 

 

  • Session consommatrice en PGA :

 

select s.sid,s.username, n.name, t.value

from v$statname n, v$session s, v$sesstat t

where s.sid=t.sid

and n.statistic#=t.statistic#

and s.type='USER'

and n.name='session pga memory'

order by 4

 

 

  •  Trace d'une requête consommatrice (ex : vue v_today_diffusions)  :

SET LONG 2000

set linesize 2000

set pagesize 2000

set time on

set timing on

set autotrace traceonly explain

select * from v_today_diffusions;

-- explain plan for select * from v_today_diffusions;

-- select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

exit

 

 

Pb de perfs avec la procedure sp_executesql

Symptômes :


                Temps d'exécutions aléatoires des requêtes suite à une mise en PROD
                Non réponses aléatoire de certaines requêtes  

 

Causes  :


                Comportement du sp_executesql (ce n’est pas un bug, c’est une limite d’utilisation)

                Cette proc permet d'exécuter du code mais de garder le plan en cache pour une utilisation ultérieure, le problème c'est que les paramètres qui sont passés dans notre cas modifient beaucoup la sélectivité de la requête du coup le plan réutilisé n'est peut être pas approprié.

 

Mise en évidence :


                        'TRAPPER' la requête applicative via perfmon + le jeu de SET commande
               
                                Example:

                                        set quoted_identifier on
                                        set arithabort off
                                        set numeric_roundabort off
                                        set ansi_warnings on
                                        set ansi_padding on
                                        set ansi_nulls on
                                        set concat_null_yields_null on
                                        set cursor_close_on_commit off
                                        set implicit_transactions off
                                        set language Français
                                        set dateformat dmy
                                        set datefirst 1
                                        set transaction isolation level read committed
                               
                                exec sp_executesql N'SELECT top 110000 this_.IdOrdreEspece as IdOrdreE1_996_0_, this_.CodSens as CodSens996_0_, this_.IdCleRegroupement as IdCleReg3_996_0_,                                                 this_.CodEtablissementTiers as CodEtabl4_996_0_, this_.CodGuichetTiers as CodGuich5_996_0_, this_.CodCompteTiers as CodCompt6_996_0_, this_.CodCleRibTiers as                                                 CodCleRi7_996_0_, this_.DatEffet as DatEffet996_0_, this_.DatCreation as DatCreat9_996_0_, this_.DatModification as DatModi10_996_0_, this_.LoginCreation as LoginCr11_996_0_,                                 this_.LoginModification as LoginMo12_996_0_, this_.MntOrdre as MntOrdre996_0_, this_.TypEtatOrdre as TypEtat14_996_0_, this_.LibDebit as LibDebit996_0_, this_.LibCredit as                                 LibCredit996_0_, this_.LibCommentaire as LibComm17_996_0_, this_.LibBeneficiaire as LibBene18_996_0_, this_.TypOrdre as TypOrdre996_0_, this_.CodCompte as CodCompte996_0_,                                 this_.LibIntitule as LibInti21_996_0_, this_.CodDevise as CodDevise996_0_, this_.LibMessageErreur as LibMess23_996_0_, this_.MntEspeceJCP as MntEspe24_996_0_,                                                 this_.MntEspeceJ1CP as MntEspeceJ25_996_0_, this_.MntPatrimoineJCP as MntPatr26_996_0_, this_.MntPatrimoineJ1CP as MntPatr27_996_0_, this_.MntEspeceJCT as                                                 MntEspe28_996_0_, this_.MntEspeceJ1CT as MntEspeceJ29_996_0_, this_.MntPatrimoineJCT as MntPatr30_996_0_, this_.MntPatrimoineJ1CT as MntPatr31_996_0_ FROM                                         db_ldw.dbo.LUT_V_CarnetOrdreEspece this_ WHERE this_.DatCreation >= @p0 and this_.DatCreation < @p1 and this_.IdCleRegroupement = @p2',N'@p0 datetime,@p1 datetime,@p2                                 bigint',@p0='12/12/2010 00:00:00',@p1='20/12/2010 00:00:00',@p2=4678913124410789674
       
        Attention !!        Par defautl sql management studio met le set arithabort à 'on' alors que l'application le met à 'off', du coup les plans changent dans le procédure cache.
                       
                        1        DBCC FREEPROCCACHE;                (attention en prod ca peut couter cher)
                        2        jouer les SET commandes et la requête sur un petit périmètre (ici c'est le @p2 qui spécifie le carnet d'ordre des clients (petit pour 1 client mais gros pour un ensemble de clients)

  • normalement l'exécution est bonne et le plan est en cache
  • select * from master..syscacheobjects where sql like '%un bout de votre code%'                

                        3        jouer les SET commandes et la requête sur un Gros périmètre

  • La, c'est le drame, ça rame, le plan réutilisé n'est pas adapté pour un gros périmètre (confirmation avec le show plan )

 

Faites l'inverse :

                        1        DBCC FREEPROCCACHE;                (attention en prod ca peut couter cher)
                        2        Jouer les SET commandes et la requête sur un Gros périmètre

  • Normalement l'exécution est bonne et le plan est en cache
  • select * from master..syscacheobjects where sql like '%un bout de votre code%'

     
                        3        Jouer les SET commandes et la requête sur un petit périmètre

  • La, ca dépote, le plan plus 'complet' du gros périmètre est aussi valable pour les petites requêtes.


Erreur à ne pas faire :

Mettre à jour les stats à outrance, nous avons l'impression que ca va mieux, mais ce n'est pas à cause de la mise à jour des stats mais à cause de l'effet de bord :  la mise à jour des stats ayant invalidée les plans d'exécutions du cache pour les requêtes référençant les tables ou index mis à jour.

 

 

Solution :

Pour une fois, il est possible d'utiliser la commande (RECOMPILE) :


                        exec sp_executesql N'SELECT top 110000 this_.IdOrdreEspece as IdOrdreE1_996_0_, this_.CodSens as CodSens996_0_, this_.IdCleRegroupement as IdCleReg3_996_0_,                                                 this_.CodEtablissementTiers as CodEtabl4_996_0_, this_.CodGuichetTiers as CodGuich5_996_0_, this_.CodCompteTiers as CodCompt6_996_0_, this_.CodCleRibTiers as                                                 CodCleRi7_996_0_, this_.DatEffet as DatEffet996_0_, this_.DatCreation as DatCreat9_996_0_, this_.DatModification as DatModi10_996_0_, this_.LoginCreation as LoginCr11_996_0_,                                 this_.LoginModification as LoginMo12_996_0_, this_.MntOrdre as MntOrdre996_0_, this_.TypEtatOrdre as TypEtat14_996_0_, this_.LibDebit as LibDebit996_0_, this_.LibCredit as                                 LibCredit996_0_, this_.LibCommentaire as LibComm17_996_0_, this_.LibBeneficiaire as LibBene18_996_0_, this_.TypOrdre as TypOrdre996_0_, this_.CodCompte as CodCompte996_0_,                                 this_.LibIntitule as LibInti21_996_0_, this_.CodDevise as CodDevise996_0_, this_.LibMessageErreur as LibMess23_996_0_, this_.MntEspeceJCP as MntEspe24_996_0_,                                                 this_.MntEspeceJ1CP as MntEspeceJ25_996_0_, this_.MntPatrimoineJCP as MntPatr26_996_0_, this_.MntPatrimoineJ1CP as MntPatr27_996_0_, this_.MntEspeceJCT as                                                 MntEspe28_996_0_, this_.MntEspeceJ1CT as MntEspeceJ29_996_0_, this_.MntPatrimoineJCT as MntPatr30_996_0_, this_.MntPatrimoineJ1CT as MntPatr31_996_0_ FROM                                         db_ldw.dbo.LUT_V_CarnetOrdreEspece this_ WHERE this_.DatCreation >= @p0 and this_.DatCreation < @p1 and this_.IdCleRegroupement = @p2 OPTION (RECOMPILE) ',N'@p0                         datetime,@p1 datetime,@p2 bigint',@p0='12/12/2010 00:00:00',@p1='20/12/2010 00:00:00',@p2=4678913124410789674
       

Impacts :

 

  • Légère augmentation CPU dû à la recompilation des plans à chaque appels
  • Augmentation du temps de traitement de la requête pour les mêmes raisons

 

 

 Merci Matthieu !!!

Pb de performances suite à migration en SYBASE 15

Cet article résume quelques problèmes que l'on a eu lors de la migration de serveurs SYBASE 12.0 / 12.5 en 15. Tous les problèmes rencontrés ont été corrigés ou un contournement a été trouvé et mis en place.

 

Problème de lenteur sur un batch utilisant massivement des requêtes dynamiques impliquant une table temporaires.

Suite à une migration 12.5 vers 15, nous avons constaté qu'un batch utilisant massivement des requêtes dynamiques était fortement ralenti. En effet, l'application fait des milliers voire des millions de requête / curseur avec une jointure sur une table temporaire (table en #).
Les requêtes étant quasiment les mêmes, nous avons identifié qu'elles recompilaient systématiquement leur plan d'exécution (5 millions sur un run)


L'optimiseur SYBASE recompile les plans des statements car il s'appuit sur les id des tables temporaires. Or, il change à chaque fois. Nous avons alors positionné le traceflag 299 qui permet de ne pas recompiler les statements à chaque exécution. A ce que j'ai compris, ce traceflag permet à l'optimiseur SYBASE d'aller chercher le plan d'un statement en cache par rapport au checksum de la requête et non plus des id des tables.

Pour info, ce traceflag comportait des bugs jusqu'en version 12.5.4. Pourquoi SYBASE ne l'a pas mis par défaut, ca, c'est une autre question !!!!

Nous avons eu un gain énorme en terme de CPU ainsi que les temps d'exécution du batch complet (Avant durée 2h et CPU 80%, Apres 1h et CPU 40%)

Cette analyse a pu se faire grâce à Asemonlogger nouvelle version. Dans l'onglet 'summary' / Tableau 'Summary Statistics' / Paragraphe 'Statement Cache Activity', nous avions autant de NumRecompilesSchemaChanges que de HitCount. 

 

 

Problème de contention sur la table syslogins

Nous avons constaté sur un batch qu'une contention très importante était apparue en version 15. Le batch lance des milliers voire des millions de connexions / déconnexions au serveur SYBASE. La contention se situait sur la ligne concernant le login qui lançait le batch.

Depuis la version 12.5.4, le serveur ASE écrit systématiquement les informations de comptabilité de CPU et IO dans la table syslogins de la base master. (Correction d'un problème : CR485461)

Pour désactiver l'écriture de ces compteurs, nous avons activer le traceflag 8101 (Annule la Correction citée ci-dessus).

En parallèle, nous avons désactiver la mémorisation de la dernière date de login via la commande suivante :

sp_passwordpolicy 'set', 'enable last login updates', 0 

 

Nous avons aussi augmenter les intervalles de temps que SYBASE utilise pour 'flusher' les compteurs CPU et IO lors des connexions.

sp_configure accounting
2> go
Msg 17411, Level 16, State 1:
Server 'DATASERVER', Procedure 'sp_configure', Line 322:
Configuration option is not unique.

 Parameter Name                 Default              Memory Used Config Value         Run Value            Unit                 Type
 ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- ----------
 cpu accounting flush interval          200                    0          200                  200         clock ticks          dynamic
 i/o accounting flush interval         1000                    0         1000                 1000         clock ticks          dynamic
 

 Nous les avons passer à 2147483647.

 

Problème de contention du le procedure cache

Il arrive que l'on détecte une forte charge CPU sur un serveur ASE 15 à mettre en corrélation avec une contention sur le procedure cache (Resource->rproccache_spin très élevé sur l'onglet spinlocks de Asemonlogger). Cela est du à la nouvelle gestion de l'allocation mémoire dans le procedure cache.

Un contournement proposé par SYBASE est de mettre le traceflag 753 qui permet de revenir au mode de gestion du procedure cache 12.5.

 

 

A suivre....

 

 

 

 

 

 

 

Procédure Collecte / Analyse Perfs SQL Server

Y

 
Procédure Analyse Perfs SQL server

 

 

 

 

 

Les outils LIVE :

Dashboard pour SQL Server 2005 :

Mise en œuvre du dashboard :

·         Lancer le programme SQLServer2005_PerformanceDashboard.msi

·         Une fois que c’est fait, lancer le setup.sql sur le SQLServer dans la base msdb. Le fichier est dans C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard

·         En suite il faut lancer un report à partir de SQL Server Management Studio (click droit sur l’instance/Reports/Custom Reports) et ouvrir le fichier

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard\performance_dashboard_main.rdl

 

 Dashboard pour SQL Server 2008

 

Perfmon

Cet outil va permettre la lecture du fichier BLG produit avec SQLDIAG. Il est déjà installé sur le serveur. Il se lance de la façon suivante :

Start => Execute => perfmon.msc  

Pour prendre en compte le fichier BLG :

·         Cliquer sur l’icône ‘System Monitor Properties’ (icône en forme de cylindre)

·         Cocher ‘Log Files’

·         Ajouter le fichier BLG

 Pour ajouter des compteurs sur le graphe, cliquer sur l’icone ‘+’

Vérification classique de ces compteurs en cas de problèmes de perfs :

  • Memory / Available MBytes doit être supérieur à 100
  • SQLServer : Memory Manager / Target Server Memory
  • SQLServer : Memory Manager / Total Server Memory : doit être inférieur au Target Server Memory
  • Buffer Manager : Page Life Expectancy doit être supérieur à 300 secondes
  • Logical Disk : Avg. Disk sec/Read
  • Logical Disk : Avg. Disk sec/Write
    • Idéal quand < 5ms pour du transaction log et < 10 ms pour du data
    • 10-20 ms est en général acceptable
    • > 20 ms ne l'est plus
  • Processor / %Privileges Time contre %User Time
  • Processor : Sqlserver %Processor Time

 

On peut automatiser la prise de mesure des compteurs perfmon en créantdes templates xml.

L'article suivant explique très bien cela : cliquer ici et se positionner sur le paragraphe 'mise en place des compteurs avec les fichiers .xml'

 

 

SQL Server 2008 and 2008 R2 instances, Data Collector (+ SQL Utility in SQL Server 2008 R2):

 

 

Dans SQL Server 2008, SQL Server Activity Monitor est inclus directement dans SSMS

 

Les outils Post-Mortem

SCOM 2007 + SQL Server Management Pack:

 

SQLDiag (Data Collect) :

C’est l’outil qui permet la collecte des informations et system et SGBD. Il est déjà installé avec l’instance SQL SERVER et se trouve dans le répertoire d’installation des sharedtools, par default

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

Au premier lancement, il génère des fichiers XML de configurations. Il faut ensuite l’arrêter pour le relancer en utilisant le fichier de config SD_Detailed.XML de la façon suivante :

SQLDIAG /I SD_Detailed.XML

Dans ce cas tous les résultats seront déposés dans le répertoire :

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLDIAG

 

Vous pouvez aussi lui spécifier un répertoire avec l’option /O

SQLDIAG /I SD_Detailed.XML /O C:\Program Files\Microsoft SQL Server\90\Tools\Binn\output

 

La prise de mesure peut être aussi planifiée.

SQLDIAG /I SD_Detailed.XML /B 20100201_05:30:00 /E 20100201_12:00:00 /O C:\Program Files\Microsoft SQL Server\100\Tools\Binn\output

 

L’utilisation du SQLDIAG coûte 1% CPU

Les fichiers générés qui nous sont utiles par la suite :

SQLDIAG.BLG
MSGEPX03_MX_TP_DBA_sp_trace.trc

Pour info, il existe 2 packages avec des sqldiag prédéfinis à télécharger ici (tout en bas à gauche de la page). Ces 2 packages sont intégrables / interprétables dans SQL Nexus (voir ci-dessous)

 

 

SQL Nexus (Analyse de trace)

 

L'installation se fait très simplement par un détare de la distrib. Cet outil permet d’intégrer le(s) fichier(s) .trc, le(s) fichier(s) .BLG (perfmon), et les fichiers générés par les requêtes DMV des packages cités à la fin du paragraphe sqldiag. (ex : sur msgepx03 dans D:\Perfs\Tools SQLServer\SQLNexus3.0.0.0)

Il faut au préalable installer ReportViewer :

Pour plus d'explications, aller sur le lien suivant : http://sqlnexus.codeplex.com/wikipage?title=GETTING_STARTED&referringTitle=Home

D'ailleurs pour la collecte des données, codeplex met à disposition 2 scripts (Version 2005 et 2008) sous PerfStatsScript

 

La collecte des données

Sur le serveur cible, déposer le PerfStatsScript.zip correspondant à votre version. 

StartSQLDiagTrace.cmd : Récupération des compteurs génériques permettant une première analyse de Perfs

StartSQLDiagDetail_Trace.cmd : Récupération de compteurs plus détaillés pour une analyse plus fine. Cela peut générer quelques lenteurs (Attention en Prod)

StartSQLDiagNoTrace.cmd : Ne lance pas de traces via le Profiler

StartSQLDiagForReplay.cmd  : Utile pour simuler une Prod. Je n'ai pas encore étudier ce cas

 Le lancement d'un de ces 4 fichiers génère les fichiers de traces dans un répertoire SQLDiagOutput

Ce répertoire contient des fichiers '.out' contenant les données de SQLServer (Tables, Procédures...), des fichiers .trc (Trace SQL Server Profiler) et un fichier SQLDIAG.BLG (Trace SQLDIAG).

 

 

Chargement des données dans SQLNexus

SQLNexus a besoin d'une base pour stocker les données de la collecte.

Lancer sqlnexus.exe, renseigner le SQLServer sur lequel vous avez créé votre base et choisissez celle-ci dans le menu déroulant en haut à droit

 

 

Cliquer sur Import dans le menu de gauche ou dans File > Import. Donner le répertoire ou se trouve tous les fichiers de la collecte.

En cliquant sur Options, on peut choisir le type d'import que l'on fait, si l'on recrée une base etc... (L'import du fichier SQLDIAG.blg est par défaut désactivé, il suffit de l'activer pour l'intégrer)

Cliquer sur '> Import', voici un exemple d'import :

 

Visualisation des données (ex pris sur un serveur SQLServer 2008)

Il est mis à disposition différents rapports sur menu de gauche.

En cliquant sur un menu, cela ouvre une nouvelle page avec les données lui correspondant.

Le message 'The database doesn't have necessary data to run this report' quand on clique sur un report signifie que les données nécessaire à ce rapport ne sont pas en base. Dans notre cas, le report 'SQL Server 2000 PerfStats' nous renvoie le message.

 

 

Outil PAL (analyse fichier perfmon .BLG)

Cet utilitaire permet d'extraire et de synthétiser les informations contenu dans le fichier blg

On peut le télécharger depuis http://pal.codeplex.com

Lorsque l'erreur suivante apparait :

You cannot call a method on a null-valued expression.
At C:\Program Files\PAL\PAL v2.0.3\PAL.ps1:2102 char:55
+         If ($iUBound -gt $aSortedNumbers.GetUpperBound <<<< (0))
    + CategoryInfo          : InvalidOperation: (GetUpperBound:String) [], Par
   entContainsErrorRecordException
    + FullyQualifiedErrorId : InvokeMethodOnNull

L'application de ce correctif http://pal.codeplex.com/Thread/View.aspx?ThreadId=228805 peut résoudre le problème.

Lancement de l’analyse

 

 

 

 

 

 

 

Prise en compte du fichier BLG :

 

 

 

 

 

 

 

 

Choix du type de trace :

 

 

 

 

Renseignements divers (CPU, Mémoire, …) du serveur monitoré

 

 

 

 

 

 

Choix de l’intervalle, à faire en fonction bien sûr du temps de la trace :

 

 

 

 

Renseignement de la trace de sortie (fichier html) :

 

 

 

 

 

Quelques exemples de graphes et de tableaux du fichier html de sortie :