SelectEtoile : Base de connaissance SGBD

Posts Tagged 'plan'

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 !!!

Quel plan associé à un SQL_ID sous ORACLE

Question

On souhaite savoir quels plans sont ou ont associés à un SQL_ID.

Pour cela on interroge les vues des historiques de perf Oracle qui stockent leurs informations par clichés (snap).

 

 

Exemple

Si on regarde les ordres SQL qui référencent la table CLOTURES …

 

OX_TP_SO> select distinct SQL_ID,SQL_TEXT from v$sqltext where upper(SQL_TEXT) like '%CLOTURES%';

 

SQL_ID        SQL_TEXT

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

1yn864s73q81t update CLOTURES set code = 76303671 where code = 7630175

2fcwzbj4gac9y insert into CLOTURES (numero, CODE ,CODE_SJ,

guup682k5m1bq BLE FROM USERCOLUMN WHERE TABLENAME='CLOTURES' AND DISAB

[…]

76wsr0n32vma3 _cloture <> c2.cloture or c1.min_hyb <> c

76wsr0n32vma3 | ' - Cloture has changed: ' || c1.cloture ||' instead o

52pm3sm8wsg3x update CLOTURES set code = 76303622 where code = 7618738

6cyg7vuaj8jpw update CLOTURES set code = 76303655 where code = 7630175

d8g7sky5ht92w ,VALEUR,CLOTURE from CLAUSE where CODE=:

76wsr0n32vma3 f ' || c2.valeur  when c1.cloture <> c2.

76wsr0n32vma3 f ' || c2.cloture  when c1.min_hyb <> c2.

5fb35dd99uuhn delete from CLOTURES where CODE=:c_1

76wsr0n32vma3 d as MESSAGE  from shtemp1 i, clotures c1, clotu

 

… voici les plans associé à

 

> luncher SGBD plan_change 76wsr0n32vma3

 

SQL_ID        | PLAN_HASH_VALUE | SNAP_COUNT | SNAP_INTERVAL   | TIME_INTERVAL

------------- | --------------- | ---------- | --------------- | -----------------------------------------

76wsr0n32vma3 |      3234493881 |          2 | 25385-->25386   | [2009-11-04 17:30] --> [2009-11-04 18:00]

76wsr0n32vma3 |      1579711755 |          5 | 25468-->25475   | [2009-11-06 11:00] --> [2009-11-06 14:30]

              | --------------- |            |                 |

count         |               2 |            |                 |

 

Dans ce cas précis, on observe un changement de plan entre le 4 et le 6 novembre.

On peut également noter que la requête apparait sur 2 clichés (=1h) le 4 nov et 5 clichés (=2h30) le 6 nov.

 

On peut ensuite demander un plan en particulier ou tous les plans associés au SQL_ID.

SQL> select * from table(dbms_xplan.display_awr('76wsr0n32vma3'));

                                                 SQL_ID       

à affiche les 3 plans

 

 

SQL> select * from table(dbms_xplan.display_awr('76wsr0n32vma3', 3234493881));

                                                 SQL_ID        PLAN_HASH_VALUE

à affiche seulement un plan particulier

 

 

Script  plan_change.sql

set verify off

set linesize 200

set feedback off

set timing off

set colsep ' | '

 

col SNAP_INTERVAL for A15

 

break on report

comput count of plan_hash_value on report

 

select distinct

     st.SQL_ID

    ,st.plan_hash_value

    ,count(1) as Snap_Count

    ,to_char(min(sn.snap_id)) || '-->' ||  to_char(max(sn.snap_id))  Snap_Interval

    ,'[' || to_char(min(sn.begin_interval_time),'yyyy-mm-dd hh24:mi') ||

                '] --> [' || to_char(max(sn.begin_interval_time),'yyyy-mm-dd hh24:mi') || ']' Time_interval

from

        dba_hist_snapshot   sn,

        dba_hist_sqlstat    st

where   st.snap_id = sn.snap_id

and     st.dbid = sn.dbid

and     st.instance_number = sn.instance_number

and     st.SQL_ID='&1'

group by st.SQL_ID,st.plan_hash_value

order by Snap_Interval,st.plan_hash_value

/

Visualisation du plan d'execution des requetes sous SQLServer

Visualisation du plan d'exécution des requêtes en cours

 

SELECT Db_name(runningqueries.database_id)                                AS dbname,
       runningqueries.start_time,
       runningqueries.status,
       Substring(runningtextqueries.TEXT,(runningqueries.statement_start_offset / 2) + 1,
                 ((CASE runningqueries.statement_end_offset
                     WHEN -1
                     THEN Datalength(runningtextqueries.TEXT)
                     ELSE runningqueries.statement_end_offset
                   END - runningqueries.statement_start_offset) / 2) + 1) AS currentstatement,
       runningtextqueries.TEXT                                            AS completequery,
       runningplanqueries.query_plan,
       runningqueries.cpu_time,
       runningqueries.total_elapsed_time,
       runningqueries.granted_query_memory                                AS numofpagesallocated,
       runningmemoryconsoqueries.requested_memory_kb                      AS requestedmemory_kb,
       runningmemoryconsoqueries.granted_memory_kb                        AS memoryallocated_kb,
       runningmemoryconsoqueries.required_memory_kb                       AS minimalmemoryneeded_kb,
       runningmemoryconsoqueries.used_memory_kb                           AS currentlymemoryused_kb,
       runningmemoryconsoqueries.max_used_memory_kb                       AS maxmemoryused_kb
FROM   sys.dm_exec_requests AS runningqueries
       CROSS APPLY sys.Dm_exec_sql_text(runningqueries.sql_handle) AS runningtextqueries
       CROSS APPLY sys.Dm_exec_query_plan(runningqueries.plan_handle) AS runningplanqueries
                   LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS runningmemoryconsoqueries
                     ON runningqueries.sql_handle = runningmemoryconsoqueries.sql_handle

GO