Verificar plano de execução no Sybase ASE

Várias ferramentas do Sybase ASE podem ser utiliadas para se avaliar o custo e o plano de execução de um comando SQL.

SET STATISTICS TIME ON : mostra o tempo necessário para executar o comando.

SET STATISTICS SUBQUERYCACHE ON: Exibe informações sobre Cache Hits para subqueries.

SET STATISTICS IO ON: exibe informações sobre leituras físicas e lógicas, e gravações.

SET SHOWPLAN ON: exibe o plano de execução em si.

No exemplo abaixo, eu utilizo todas estas opções para analiar estatísticas do mesmo SQL. 

[sybase@nerv00 ~]$ isql -SNERV00 -Usa -w999 -PNerv2011
1> USE pubs2
2> GO
1> sp_tables
2> GO
table_qualifier table_owner table_name table_type remarks
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------ -----------
pubs2 dbo sysalternates SYSTEM TABLE NULL
pubs2 dbo sysattributes SYSTEM TABLE NULL
pubs2 dbo syscolumns SYSTEM TABLE NULL
pubs2 dbo syscomments SYSTEM TABLE NULL
pubs2 dbo sysconstraints SYSTEM TABLE NULL
pubs2 dbo sysdepends SYSTEM TABLE NULL
pubs2 dbo sysencryptkeys SYSTEM TABLE NULL
pubs2 dbo sysgams SYSTEM TABLE NULL
pubs2 dbo sysindexes SYSTEM TABLE NULL
pubs2 dbo sysjars SYSTEM TABLE NULL
pubs2 dbo syskeys SYSTEM TABLE NULL
pubs2 dbo syslogs SYSTEM TABLE NULL
pubs2 dbo sysobjects SYSTEM TABLE NULL
pubs2 dbo syspartitionkeys SYSTEM TABLE NULL
pubs2 dbo syspartitions SYSTEM TABLE NULL
pubs2 dbo sysprocedures SYSTEM TABLE NULL
pubs2 dbo sysprotects SYSTEM TABLE NULL
pubs2 dbo sysqueryplans SYSTEM TABLE NULL
pubs2 dbo sysreferences SYSTEM TABLE NULL
pubs2 dbo sysroles SYSTEM TABLE NULL
pubs2 dbo syssegments SYSTEM TABLE NULL
pubs2 dbo sysslices SYSTEM TABLE NULL
pubs2 dbo sysstatistics SYSTEM TABLE NULL
pubs2 dbo systabstats SYSTEM TABLE NULL
pubs2 dbo systhresholds SYSTEM TABLE NULL
pubs2 dbo systypes SYSTEM TABLE NULL
pubs2 dbo sysusermessages SYSTEM TABLE NULL
pubs2 dbo sysusers SYSTEM TABLE NULL
pubs2 dbo sysxtypes SYSTEM TABLE NULL
pubs2 dbo au_pix TABLE NULL
pubs2 dbo authors TABLE NULL
pubs2 dbo blurbs TABLE NULL
pubs2 dbo discounts TABLE NULL
pubs2 dbo publishers TABLE NULL
pubs2 dbo roysched TABLE NULL
pubs2 dbo sales TABLE NULL
pubs2 dbo salesdetail TABLE NULL
pubs2 dbo stores TABLE NULL
pubs2 dbo titleauthor TABLE NULL
pubs2 dbo titles TABLE NULL
pubs2 dbo sysquerymetrics VIEW NULL
pubs2 dbo titleview VIEW NULL
(42 rows affected)
(return status = 0)
1> SET STATISTICS TIME ON
2> GO
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
1> SET STATISTICS SUBQUERYCACHE ON
2> GO
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
1> SET STATISTICS IO ON
2> GO
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
1> SET SHOWPLAN ON
2> GO
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
1> SELECT COUNT(*) FROM titles
2> GO
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
2 operator(s) under root
|ROOT:EMIT Operator (VA = 2)
|
| |SCALAR AGGREGATE Operator (VA = 1)
| | Evaluate Ungrouped COUNT AGGREGATE.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | titles
| | | Index : titleind
| | | Forward Scan.
| | | Positioning at index start.
| | | Index contains all needed columns. Base table will not be read.
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
18
Table: titles scan count 1, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 10 ms.
(1 row affected)
1>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.