Sybase Adaptive Server Enterprise 15.0.2 : Diagnostic and Tuning Tools, Quick ref

Logo

Execution plans

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

To limit the optimization time of a query in relation to the execution time :

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

To display plan costs :

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

Display options :

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

To display abstract plans :

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 '( ... )'.

To display logical operators :

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

To display selectivity of 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 command, governing plans

Computing the execution plan following the tables order in the from clause (force plan) :

set forceplan on|off

Enable/disable reformatting :

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

Procedure cache :

  • Limit the amount of procedure cache used by the query optimizer when evaluating plans :
sp_configure 'max resource granularity',1-100
set resource_granularity 1-100
  • Valid only with allrows_dss for a slow request or a 701 error that occurs during optimization (procedure cache full) :
set bushy_space_search on|off

Enable/disable merge joins :

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

Enable/disable hash joins :

set hash_join on|off

Enable/disable nested loop joins (Nl joins) :

set nl_join on|off

Options export (implicit in 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]

Statement Cache

Server configuration :

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

MDA table : 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 */

Statistics

datachange function, amount of change (%) in the data distribution since update statistics last ran :

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

Missing statistics :

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

Indexes creations

To display used resources when creating an 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

To view server settings not set to default values :

sp_configure 'nondefault'