Sybase Adaptive Server Enterprise 15.0.2 : Outils de diagnostic et de tuning, aide-mémoire

Logo

Plans d’exécutions

set plan optgoal
     allrows_oltp | allrows_mix | allrows_dss
          
select * from … where …
         plan '(use optgoal allrows_dss)'

Limiter le temps d’optimisation d’une requête par rapport au temps d’exécution :

sp_configure 'optimization timeout limit',0-100
set plan opttimeoutlimit 0-100

Afficher les coûts des plans :

set plancost on
L : logical io ( e : estimate )
P : physical_io ( e : estimate )
R : row count ( e : estimate )
Cpu: Cpu tics

Options d’affichage :

set option
   {   {show | show_lop | show_managers | show_log_props |
       show_parallel | show_histograms | show_abstract_plan |
       show_search_engine | show_counters | show_best_plan |
       show_code_gen | show_pio_costing | show_lio_costing |
       show_pll_costing | show_elimination | show_missing_stats}
      
      {normal | brief | long | on | off}
    }

Afficher les plans abstraits :

set option show_abstract_plan on|off
The Abstract Plan (AP) of the final query
execution plan:
( nl_join ( i_scan PKCN_PORTFOLIO ( table ( b
PORTFOLIO ) ) ) ( i_scan X5_ID_XACT_CASH ( table (
a ID_XACT_CASH ) ) ) ) ( prop ( table ( b
PORTFOLIO ) ) ( parallel 1 ) ( prefetch 2 )
( lru ) ) ( prop ( table ( a ID_XACT_CASH ) )
( parallel 1 ) ( prefetch 2 ) ( lru ) )
To experiment with the optimizer behavior, this AP
can be modified and then passed to the optimizer
using the PLAN clause: SELECT/INSERT/DELETE/UPDATE
... PLAN '( ... )'.

Afficher les opérateurs logiques :

set option show_lop on
( project
 ( join
    ( scan ID_XACT_CASH
    )
    ( scan PORTFOLIO
    )
 )
)

Afficher la sélectivité des indexes :

set option show_lio_costing normal
Beginning selection of qualifying indexes for
table 'ID_XACT_CASH',
Estimating selectivity of index
'ID_XACT_CASH.AIM1', indid 3
 PF_COD = 'AWFEUROLIQ'
 Estimated selectivity for PF_COD,
 selectivity = 0.001560471,
 Intelligent Scan selectivity reduction from 1
to 0.06445105
 scan selectivity 0.06445105, filter
selectivity 0.001560471
 restricted selectivity 1
 1750753 rows, 7208.077 pages
 Data Row Cluster Ratio 0.2326496
 Index Page Cluster Ratio 0.8658771
 Data Page Cluster Ratio 0.2821443
 using index prefetch (size 16K I/O)
 Large IO selected: The number of leaf pages
qualified is > MIN_PREFETCH pages
 in index cache 'default data cache' (cacheid
0) with LRU replacement

set, gouverner les plans

Évaluer le plan en respectant l’ordre des tables dans la clause from :

set forceplan on|off

Activer/Désactiver le reformatting :

set store_index on|off
select * from … where …
         plan '(use store_index on|off)'

Cache de procédures :

  • Limiter la quantité de cache de procédure utilisable par l’optimiseur de requêtes lors de l’évaluation des plans :
sp_configure 'max resource granularity',1-100
set resource_granularity 1-100
  • Valable uniquement avec allrows_dss pour une requête lente ou une erreur 701 qui survient lors de l’optimisation (cache de procédures saturé) :
set bushy_space_search on|off

Activer/Désactiver les jointures par fusion (merge joins) :

set merge_join on|off
select * from … where …
         plan '(use merge_join on|off)'
sp_configure 'enable merge join', 0|1|2

Activer/Désactiver les jointures par hachage (hash joins) :

set hash_join on|off

Activer/Désactiver les jointures imbriquées (nested loop joins - Nl joins) :

set nl_join on|off

Export des options (implicite dans les triggers login) :

set export_options on|off

sp_monitor

sp_monitor [[connection | statement], [cpu | diskio | elapsed time]]  
	[event, [spid ]]
	[procedure, [dbname, [procname[, summary | detail]]]]
	[enable] [disable] 
	[help], 
	[deadlock][procstack]

Cache de requêtes - Statement Cache

Configuration serveur :

sp_configure 'statement cache size', 0, '100M'
sp_configure 'enable literal autoparam',1

Table MDA : master..monCachedStatement

Session :

set statement_cache on|off
set literal_autoparam on|off

select show_cached_text(SSQLID)

select SSQLID, show_cached_text(SSQLID)
from master..monCachedStatement

dbcc purgesqlcache[(SSQLID)]

dbcc prsqlcache(SSQLID|0,0|1)
/** 0 : trace, 1 : trace + showplan */

Statistiques

Fonction datachange, % de modifications de la table depuis la dernière exécution de la commande update statistics :

select datachange(table,partition,col)
select datachange('INSTRUMENT', null,null);

Statistiques manquantes - Missing stats :

dbcc traceon(3604)
set option show_missing_stats on | off | long

select convert(varchar(30),object_name(id)),
       indid, moddate
from sysstatistics
where moddate < dateadd(dd,-10,getdate())

Traces

sp_helpapptrace
go
dbcc traceon(3604)
go
set tracefile '/tmp/tf.txt' for pid
go
set show_sqltext on
set statistics io on
set statistics time on
set statistics plancost on
set showplan on
…
set showplan off
set statistics io off
set statistics time off
set show_sqltext off
set tracefile off for pid
go

Création des indexes

Afficher les ressources utilisées lors de la création d’un index

set sort_resources on | off
The sort is performed using Serial sort.
Sort buffer size: 500
Parallel degree: 1
Number of output devices: 19
The data to be sorted has approximately 3877
rows, 999 used pages (1998 KB) and 62 unused pages
(124 KB).

sp_options

sp_options help

sp_options [ [show | help
   [, <option_name>|<category_name>|null
   [, dflt | non_dflt | null
   [, <spid>] ] ] ] ]
          
sp_options show       
Category: Query Tuning
name               currentsetting     defaultsetting    scope
--------------------------------------------------------------
optgoal            allrows_mix        allrows_mix       0
opttimeoutlimit    40                 10                0
merge_join         1                  1                 4 
hash_join          0                  0                 4 
nl_join            1                  1                 4
…

Configuration

Afficher les paramètres serveur qui ne sont pas à leurs valeurs par défaut.

sp_configure 'nondefault'