SelectEtoile : Base de connaissance SGBD

Posts Tagged 'statistics'

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

 

 

Fonctionnement des update statistics

Les update statistics permettent au moteur SYBASE de connaître la répartition des données dans les tables. Il va pouvoir avec ces statistiques vont lui permettre de faire un choix de plan d'execution pour les requêtes demandées.

Pour mettre à jour les statistics, ilsuffit de lancer la commande :

update statistics nom_table

 

Description des différents update statistics :

update statistics nom_table : calcule les statistiques sur la 1ère colonne de chaque index

update index statistics nom_table : calcule les statistiques sur toutes les colonnes de chaque index

update all statistics nom_table : calcule les statistiques sur toutes les colonnes de la table

update statistics nom_table (col_name) : calcule les statistiques sur la colonne spécifiée

update statistics nom_table (nom_index) : calcule les statistiques sur toutes les colonnes de l'index nom_index

 

 

2 options supplémentaires existent :

with sampling = N percent : permet de calculer les stats sur N % de la table

using steps values : permet de définir le nombre de step des histogrammes (defaut 20)

 

Si les statistics ne sont pas passées : l’optimiseur peut faire appel à la méthode MAGICSC

  • égalité : selectivité à 0.1

  • intervalle fermé : 0.25

  • intervalle ouvert : 0.33

Dans une SARG ou une JOIN, si on utilise des variables, des fonctions et des expressions arithmetiques, l’optimiseur ne peut pas estimer directement les cout. Il passe donc par la methode magicSC ou densitySC.

 

 

Il est souhaitable de lancer la commande sp_recompile nom_tableune fois l'update statistics fait.

Celui-ci force l'optimiseur SYBASE à recalculer le plan de requête pour les procédures stockés faisant appel à la table traitée.

 

 

 

 

Analyser une requete sql SYBASE ASE

Les outils présentés ci-dessous permettent de tracer et d'analyser une requête sql sur un serveur SYBASE ASE.

 

  • set showplan on : affiche le plan d'exécution de la requête
  • set statistics io,time on : affiche le nombre d'io/ table/index et le temps des différentes étapes
  • set noexec on : combiner avec showplan, permet d'afficher le plan sans executer la requête
  • set tracefile dirname/filename [for spid] : redirige les résultats des ordres SQL d'un session vers le fichier spécifié (New ASE15)
Toujours faire un 'set tracefile off' en fin de session
Sinon, il est possible que toutes les autres sessions soient polluées par cette trace
  • set show_sqltext {on | off} : affiche le SQL text d'un requête, procédure stockée, curseur ...
  • dbcc traceon(3604) : affiche la sortie à l'écran. Il faut positionner cette option pour les commandes suivantes
  • dbcc traceon(302) : affiche le cout des jointures et des SARG sur chaque table
  • dbcc traceon(310) : affiche les plans de moins en moins couteux (NEW PLAN). Par contre, il n’affiche pas les plans de plus fort cout.
  • dbcc traceon(317) :  affiche les plans plus couteux que les NEW PLANS. Ce sont les WORK PLANS.
  • dbcc sqltext(spid) : affiche la requête lancée par le spid

 


Différents paramètres de 310 et 317:

  • total cost : estimation du cout total en millisecondes d’une jointure (lp*2ms+pp*18ms)

  • varno : position de la table dans la clause from (NB : max 16 tables / jointure, si varno > 16 worktable)

  • indexid : indid de l’index utilisé (0 si la table)

  • pathtype : type d’opération (sclause, join ou orstruct)

  • method : NESTED ITERATION (en majorité), REFORMATTING ou OR OPTIMIZATION

  • outerrows : nombre de lignes de la table externe pour chaque ligne de la table courante

  • joinsel : estimation de la selectivité de la table ou de l’indexe de la table courante

  • cpages : nombres de pages (donnees ou indexes) que l’optimiseur estime dans la table courante

  • prefetch : prefetch strategy (N : pas de prefetch, S : prefetch strategy). Important quand le server est configuré en Large IO.

  • Iosize : taille des blocks io choisi par l’optimiseur

  • Replace : strategie de replacement dans le cache (LRU ou MRU)

  • Lp : Nombre de logical reads = outerrows * cpages

  • Pp : nombre de physical reads

  • Corder : principale (premiere) colonne de l’indexe (colid de syscolumns)

  • Jnvar, Refcost, Refpages, reftotpages, ordercol[0] sont peu utilisables et peu importantes

 

 

Si vous avez un doute sur une requête, il est possible de forcer le plan d'exécution ou de forcer un index précis.

Cela est très utile si on pense qu'un index ou un plan est meilleur que ce que l'optimiseur SYBASE a choisi

 

Pour forcer le plan, il suffit de lancer la commande suivante :

set forceplan on -- avant de lancer la requête

Pour forcer l'index, il suffit de préciser entre parenthèses "index nom_index" comme indiqué dans l'exemple suivant :

select * from ma_table (index mon_index) where col1 ='00'

 

Le forceplan force l'optimiseur a choisir son plan dans l'ordre des tables de la clause FROM. L'optimiseur choisit toutefois les index qu'il peut prendre sauf si vous les avez forcer aussi.

 

Informations pratiques :

Si une valeur d’une colonne est plus importante que les autres (ex : 90 %), les jointures sur cette colonne (ex : valeur1 représentant 0.001 % de la table) ne prendront pas forcément le bon index.

En utilisant la commande sp_modifytats comme suit, cela permet de modifier l’indice de densité de la colonne et à l’optimiseur de prendre un meilleur indexe.

 

Sp_modifystats tablename,colonnename,’REMOVE_SKREW_FROM_DENSITY’

 

Lien très intéressant sur le sujet : http://www.sybase.com/detail?id=2602#539757