SelectEtoile : Base de connaissance SGBD

Posts Tagged 'table'

Catalog MySql

 

Accès au catalogue mysql

 

  • SCHEMATA : informations sur les bases (ou schémas).

  • TABLES : les tables.

  • COLUMNS : leurs colonnes.

  • STATISTICS : les indexes.

  • USER_PRIVILEGES : privilèges globaux.

  • SCHEMA_PRIVILEGES : privilèges sur les bases.

  • TABLE_PRIVILEGES : privilèges sur les tables.

  • COLUMN_PRIVILEGES : privilèges sur les colonnes.

  • CHARACTER_SETS : jeux de caractères.

  • COLLATIONS : interclassements.

  • COLLATION_CHARACTER_SET_APPLICABILITY : correspondances interclassements / charsets.

  • TABLE_CONSTRAINTS : contraintes des tables.

  • KEY_COLUMN_USAGE : clés primaires et étrangères.

  • ROUTINES : les procédures et fonctions stockées.

  • VIEWS : les vues.

  • TRIGGERS : les déclencheurs.

 

Exemples de requete sur le CATALOG :

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'nom_base';

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'nom_base';

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'nom_base' AND TABLE_NAME = 'nom_table' ORDER BY ORDINAL_POSITION;

 

Visualisation de la clé primaire :

  • SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'nom_base' AND TABLE_NAME = 'nom_table' AND COLUMN_KEY = 'PRI' ORDER BY ORDINAL_POSITION;

 

Visualisation des colonnes indexes :

SELECT COLUMN_NAME, COLUMN_KEY

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'nom_base'

AND TABLE_NAME = 'nom_table'

AND COLUMN_KEY IS NOT NULL

ORDER BY ORDINAL_POSITION;

 

 

Visualisation des infos sur les indexes :

SELECT *

FROM INFORMATION_SCHEMA.STATISTICS

WHERE TABLE_SCHEMA = 'nom_base'

AND TABLE_NAME = 'nom_table';

 

 

Infos sur les contraintes d’intégrité :

SELECT *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_SCHEMA = 'nom_base'

AND TABLE_NAME = 'nom_table'

AND CONSTRAINT_TYPE = 'FOREIGN KEY';

 

SELECT k.CONSTRAINT_SCHEMA, k.CONSTRAINT_NAME, k.TABLE_NAME, k.COLUMN_NAME

, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_TABLE_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c

ON k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME

WHERE c.CONSTRAINT_TYPE = 'FOREIGN KEY';

 

Liste des clés étrangères non indexes :

SELECT k.CONSTRAINT_SCHEMA, k.CONSTRAINT_NAME, k.TABLE_NAME, k.COLUMN_NAME

, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c

ON k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA

AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME

LEFT JOIN INFORMATION_SCHEMA.STATISTICS AS s

ON k.CONSTRAINT_SCHEMA = s.TABLE_SCHEMA

AND k.TABLE_NAME = s.TABLE_NAME

AND k.COLUMN_NAME = s.COLUMN_NAME

WHERE c.CONSTRAINT_TYPE = 'FOREIGN KEY'

AND s.INDEX_NAME is null;

 

Table contenant les infos sur les ps et fonctions :

  • SELECT * FROM INFORMATION_SCHEMA.ROUTINES

 

Idem pour les triggers :

  • SELECT * FROM INFORMATION_SCHEMA.TRIGGERS

 

 

Tables contenant les infos sur les privileges :

  • USER_PRIVILEGES
  • SCHEMA_PRIVILEGES
  • TABLE_PRIVILEGES
  • COLUMN_PRIVILEGES

 

 

 

 

 

 

 

 

Administration et optimisation générales Mysql

Administration  / Optimisation MySQL

 

Liens des docs de référence (en français)

 

Orientation de l'optimiseur

● Possibilité de le contraindre

– USE INDEX
– FORCE INDEX
– IGNORE INDEX

● STRAIGHT_JOIN

 

Le cache de requête

● Mise en cache des requêtes SELECT et de leur résultat
● Les requêtes doivent être strictement identiques (case,espace,...)
● Le cache est toujours à jour, car lorsqu'une table est modifiée, les requêtes qui lui font références sont invalidées
● Certaines fonctions empêchent la mise en cache(NOW(), RAND()...)

 

Initialisation du cache de requêtes : SHOW VARIABLES LIKE 'query_cache%';

Visualisation des compteurs de ce cache : SHOW STATUS LIKE 'qcache%';

 

● FLUSH QUERY CACHE : Défragmente le cache de requête
● RESET QUERY CACHE : Réinitialise le cache de requêtes
● FLUSH TABLES : Réinitialise le cache de requêtes

 

Les commandes flush :

● flush privileges : Recharge les privilèges des tables de droits dans la base mysql
● flush tables : Ferme toutes les tables ouvertes, et force les tables utilisées à se refermer. Cela vide aussi le cache de requêtes.
● flush table tablename : Vide du cache uniquement les tables nommées.
● flush logs : Ferme et réouvre tous les fichiers de log

● flush query cache : Dé-fragmente le cache des requêtes pour mieux en utiliser la mémoire. Cette commande n'effacera aucune requête du cache, à la différence de RESET QUERY CACHE.

 

Description de quelques paramétrages MySQL :

● Key_buffer_size

- Tampon qui stock les index des tables MyISAM
- 25 à 30% de la RAM, pour un serveur dédié MySQL, en full MyISAM
- Pas trop grand car risque de swap !
- Ratio: key_reads / key_read_requests < 0.03 (0.01 encore mieux), sinon l'augmenter
- La fraction utilisée du buffer est : (Key_blocks_used * key_cache_block_size) / key_buffer_size

 

● myisam_sort_buffer_size

– Tampon pour la création d'index pour les requêtes de maintenance: ALTER TABLE, REPAIR TABLE, LOAD DATA INFILE
– SET SESSION myisam_sort_buffer_size = 800*1024*1024;
ALTER TABLE ma_table ADD INDEX ...;
 

● bulk_insert_buffer_size

– Tampon pour les insertions massives
• INSERT ...SELECT
• INSERT VALUES(),(),(),....
• LOAD DATA INFILE

 

● innodb_buffer_pool_size

– Tampon pour stocker les index et des données des tables InnoDB
– Jusqu'à 80% de la RAM, pour un serveur dédié MySQL, en full InnoDB
 

● innodb_flush_logs_at_trx_commit (1 par défaut)

– 0: Risque de pertes de transactions validées en cas de crash d 'InnoDB
– 1: transactions flushées après chaque commit. Pas de pertes de transactions validées (ACID)
– 2: Risque de pertes de transactions validées uniquement en cas de crash de l'O.S.

 

● innodb_log_buffer_size

– Taille du tampon des logs d'InnoDB
– Vidé environ toutes les secondes (checkpoint)
– En général entre 8Mo & 16Mo
 

● innodb_log_file_size

– Taille des fichiers de log d'InnoDB (2 par défaut)
– Une grande valeur améliore les performances. Mais augmente le temps de restauration
– Valeurs courantes : 64Mo à 512Mo

 

● table_cache

– Cache des descripteurs de fichier
– Chaque table ouverte nécessite un descripteur de fichiers (par connection), plus un pour le .MYI (MyISAM)
– Augmenter votre table_cache si opened_tables croit rapidement
 

● thread_cache

– Cache des threads
– Chaque session prend un thread à la connexion et le rend à la déconnexion
– Augmenter si threads_created croit rapidement
– Taux de succès du cache de thread: threads_created/connections

 

● read_buffer_size

- Chaque thread qui fait une recherche séquentielle alloue un buffer de cette taille pour son scan. Si vous faites de nombreux scan séquentiels, vous pourriez avoir besoin d'augmenter cette valeur
- Tampon d'enregistrements pour les full table scans
 

 ● open_files_limit

- Il est courant dce l'augmenter assez largement (ex : 65000)

 

● table_open_cache

- Si l'indicateur Opened_tables augmente, il est nécessaire d'accroitre la variable 'table_open_cache'
- L'augmentation de cette variable augmente le nombre de descripteurs

 

● sort_buffer_size

  • Tampon pour GROUP BY / ORDER BY
  • Indicateurs :
    • Sort_scan  : indique en général les tablescan
    • Sort_merge_passes : pas assez d'espace dans le sort buffer pour faire un tri. Il faut peut-être augmenter le sort_buffer_size.
 

● join_buffer_size

- Zone de jointure des requêtes n'utilisant pas d'index. Elle peut être augmenté mais le mieux est de trouver les indexes pertinents pour cette requête.
- Ce buffer est alloué une fois pour chaque jointure entre deux tables.
 

● tmp_table_size

- Tampon pour les tables temporaires stockées en mémoire (memory). Au delà, elles sont copiées sur disque (MyISAM)

 

● query_cache_size

- La mémoire allouée pour stocker les résultats des vieilles requêtes
- Pour désactiver, il suffit de positionner à 0

 

● max_allowed_packet

- Le buffer de message est initialisé avec net_buffer_length octets, mais peut grandir jusqu'à max_allowed_packet octets lorsque nécessaire.
- Cette valeur est par défaut petit, pour intercepter les gros paquets, probablement erronés. Vous devez augmenter cette valeur si vous utilisez de grandes colonnes BLOB. Cette valeur doit être aussi grande que le plus grand BLOB que vous utiliserez.

 

● thread_cache_size

- nombre threads
- Dans beaucoup de cas, on met cette valeur à 0. 

 

 

 

 

Indicateurs de performance :

Ces indicateurs sont interrogeable via 'show status like "%expression%";'

● Indicateur de hautes charges

– max_used_connections
– threads_created
– opened_tables
 

● Indicateur de réponses lentes

– slow_queries
– Slow_launch_threads (Nombre de threads qui ont pris plus de slow_launch_time secondes pour être créés)

 

● Indicateurs de comportements

– Table_locks_immediate
– Table_locks_waited
 

● Les handler

– Handler_read_first=>lecture d'index
– Handler_read_key=>lecture d'index
– Handler_read_next=>lecture d'index
– Handler_read_prev=>lecture d'index
– Handler_read_rnd=>lecture des données
– Handler_read_rnd_next=>lecture des données

 

● Indicateurs de requêtes:

- Tables temporaires
• Created_tmp_disk_tables => augmenter tmp_table_size
• Created_tmp_tables => order by, group by
- Requêtes pas optimisées
• Select_full_join, Select_full_range_join: problèmes d'index dans la jointure
• Select_scan: full scan sur la première table de la jointure
- Requêtes coûteuse
• sort_merge_passes => augmenter le sort_buffer_size

 

Supervision 

● Supervision des requêtes lentes

- log-slow-queries[=file_name] : génère un fichier contenant les requêtes SQL qui vont mettre plus de long_query_time secondes à s'exécuter.

- Le temps d'acquisition d'un verrou n'est pas compté. 

 

 

Paramètres généraux :

lower_case_table_names = 1 : Rend insensitive le nom des tables

ex : même résultat pour ces 2 commande: 'select * from matable' et 'select * from MaTable'

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
pour vérification : SELECT @@global.tx_isolation; ou SELECT @@tx_isolation;

 

Paramètres InnoDB :

innodb_data_home_dir = /MYSQL/dbdata/InnoDB_data
innodb_data_file_path = ibdata1:500M;ibdata2:5000M

Cette conf permet de fixer la taille de l'enveloppe InnoDB (ici , 5.5 Go)

Pour superviser, le taux de remplissage de cette enveloppe, il suffit de lancer la commande suivante :

select date_format(now(),'%m/%d/%Y %H:%i:%s') 'mysdate',

engine,

round((sum(data_length)+sum(index_length))/1024/1024) as used,

ceiling(substring( table_comment, instr( table_comment, ':' ) +2, instr( table_comment, ' kB' ) - instr( table_comment, ':' ) -2 )/1024)+round(data_free/(1024*1024)) as free

from information_schema.tables

where engine='InnoDB'

group by engine;

 

Gestion de la log tournante sur 3 fichiers de 512 Mo. En cas de changement de taille de la log, il suffit stopper l'instance MySQL, modifier dans le fichier cnf la taille des log, renommer les anciens fichiers de log au cas ou et de redémarrer. MySQL recrée automatiquement les nouveaux fichiers de log avec leur nouvelle taille. Il peut y avoir des problèmes de performance si ces fichiers de log sont trop petit. 

 innodb_log_file_size=512M
innodb_log_files_in_group=3
innodb_log_group_home_dir=/MYSQL/dbdata/InnoDB_log

 

InnoDB essaie d'écrire sur le disque au moins une fois par seconde, mais cette écriture n'est plus garantie. Pour garantir cette écriture, il faut passer ce paramètre à 1.

innodb_flush_log_at_trx_commit=0

 

 

 

 

 

 

 

Sauvegarde / Restauration

Sauvegarde / restauration d'une table : 

Bcp out :

  • mysql> SELECT a,b,a+b INTO OUTFILE '/tmp/jp1.txt'
  • -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  • -> LINES TERMINATED BY '\n'
  • -> FROM jp1;

 

Bcp in :

  • mysql> LOAD DATA INFILE "/tmp/jp1.txt" INTO TABLE jp1
  • -> FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
  • -> LINES TERMINATED BY '\n';
  • LOAD DATA INFILE 'ta_syb_aseconfig_total.out' INTO TABLE ta_syb_aseconfig
  • FIELDS TERMINATED BY ';' ENCLOSED BY '' ESCAPED BY '\\'
  • LINES TERMINATED BY '\n';

 

Sauvegarde d'une Base de données

mysqldump [options] db_name [tables]

Sauvegarde : mysqldump -u root -ppwd --opt mabase -h monServeur > backup-production.sql

Exemple : mysqldump --socket=mysql.sock --user=root --password=pwd testjp > /tmp/testjp.sql

La commande dump permet de générer le script de création des objets et de la base elle même. Ci-dessous, un exemple de résultat de la commande :

 

-- MySQL dump 10.9

--

-- Host: localhost Database: testjp

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

-- Server version 4.1.11-max-log

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `jp1`

--

 

DROP TABLE IF EXISTS `jp1`;

CREATE TABLE `jp1` (

`a` int(11) default NULL,

`b` int(11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `jp1`

--

 

 

/*!40000 ALTER TABLE `jp1` DISABLE KEYS */;

LOCK TABLES `jp1` WRITE;

INSERT INTO `jp1` VALUES (3,8),(1,1),(10,10),(6,7),(5,5),(8,9);

UNLOCK TABLES;

/*!40000 ALTER TABLE `jp1` ENABLE KEYS */;

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

Il faut prévoir un flush des logs binaire avant la sauvegarde full :

Soit lancer un 'flush logs' dans mysql

Soit ajouter l'option --flush-logs dans la commande mysqldump

 

Autre exemple de mysqldump :

mysqldump --opt ${DATABASE} --databases --routines --triggers --single-transaction --master-data=2 --hex-blob --flush-logs

--master-data=2 permet de mettre une ligne de commentaire dans le fichier généré donnant le binlog courant ainsi que la position
--single-transaction : permet de rendre transactionnel la sauvegarde et donc consistant
--hex-blob : Sauvegarde les colonnes binaires utilisant La notation hexadecimale
 
 

 

 

Restauration d'une base de données :

Restauration d'un dump full :

mysql -u root -ppwd -h monServeur -D mabase2 < backup-production.sql

Exemple : ‘mysql testjp < /tmp/testjp.dump’

 

Restauration des log binaire :

mysqlbinlog mysql-bin.000062 mysql-bin.000063 | mysql


Sauvegarde / restauration table (vont disparaître)

  • BACKUP TABLE table [, table] ... TO '/repertoire/de/sauvegarde/';
  • RESTORE TABLE table [, table] ... FROM '/repertoire/de/sauvegarde/';

 

 

Sauvegarde a l’aide de mysqlhotcopy (Prérequis : Perl et packages DBI)

  • mysqlhotcopy db_name [/répertoire/de/sauvegarde]
  • mysqlhotcopy -u root -ppwd mabase /mstoto/sgbd/sauvegarde/

 

 

 

 

 

Volumétrie Tables / Indexes / Bases SYBASE ASE

 

Liste de la volumétrie des tables en version 12.5 :

 

Use mabase
Go
select     DateJour               = convert(datetime,convert(char(10),getdate(),103),103),    
Base = db_name(),  
Tables = Object_name(id),       
NombreLigne = max(rowcnt(doampg)) ,       
Reserved = (sum(reserved_pgs(id,doampg))+ sum(reserved_pgs(id, ioampg)))*2,        
Data = (sum(data_pgs(id, doampg)))*2,       
Indexes = (sum(data_pgs(id, ioampg)))*2  
from sysindexes        
where id > 100       
group by id        
order by object_name(id)

 

 

 

 

Liste de la volumétrie des tables en version 15 :

use mabase
go
select top 10 t.DateJour,t.Base,t.Tables,Nb_row=convert(varchar(10),sum(t.rwct)),Reserved=sum(t.reserved), Data=sum(t.data), Indexes=sum(t.indexes)
from (select  DateJour        = convert(datetime,convert(char(10),getdate(),103),103),
        Base            = convert(varchar(15),db_name()),
        Tables          = convert(varchar(20),object_name(id)),
        case when indid < 2 then (row_count(db_id(),id)) when indid > 1 then 0 end as rwct,
        reserved        = (reserved_pages(db_id(),id,indid))*(@@maxpagesize/1024),
        case when indid < 2 then (data_pages(db_id(),id,indid))*(@@maxpagesize/1024) when indid > 1 then 0 end as data,
        case when indid > 1 then (data_pages(db_id(),id, indid))*(@@maxpagesize/1024) when indid < 2 then 0 end as indexes
from sysindexes
where id > 100) t
group by t.DateJour,t.Base,t.Tables
order by Reserved desc

 

 

 

 

 

 

 

Exemple de script pour estimer la compression

SET NOCOUNT ON;

DECLARE @tableName VARCHAR(256)

, @schemaName VARCHAR(100)

, @sqlStatementRow NVARCHAR(1000)

, @sqlStatementPage NVARCHAR(1000)

, @tableCount INT

, @statusMsg VARCHAR(1000);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '#tables%')

DROP TABLE #tables;

CREATE TABLE #tables

(

database_name sysname

, schemaName sysname NULL

, tableName sysname NULL

, processed bit

);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '#compressionPage%')

BEGIN

DROP TABLE #compressionPage;

DROP TABLE #compressionRow;

END

IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '#compressionPage%')

BEGIN

CREATE TABLE #compressionPage

(

objectName varchar(100)

, schemaName varchar(50)

, index_id int

, partition_number int

, size_current_compression bigint

, size_requested_compression bigint

, sample_current_compression bigint

, sample_requested_compression bigint

);

CREATE TABLE #compressionRow

(

objectName varchar(100)

, schemaName varchar(50)

, index_id int

, partition_number int

, size_current_compression bigint

, size_requested_compression bigint

, sample_current_compression bigint

, sample_requested_compression bigint

);

END;

INSERT INTO #tables

SELECT DB_NAME()

, SCHEMA_NAME([schema_id])

, name

, 0 -- unprocessed

FROM sys.tables;;

SELECT @tableCount = COUNT(*) FROM #tables;

WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0)

BEGIN

SELECT TOP 1 @tableName = tableName

, @schemaName = schemaName

FROM #tables WHERE processed = 0;

SELECT @statusMsg = 'Working on ' + CAST(((@tableCount - COUNT(*)) + 1) AS VARCHAR(10))

+ ' of ' + CAST(@tableCount AS VARCHAR(10))

FROM #tables

WHERE processed = 0;

RAISERROR(@statusMsg, 0, 42) WITH NOWAIT;

SET @sqlStatementRow = 'EXECUTE sp_estimate_data_compression_savings '''

+ @schemaName + ''', ''' + @tableName + ''', NULL, NULL, ''ROW'';'

SET @sqlStatementPage = 'EXECUTE sp_estimate_data_compression_savings '''

+ @schemaName + ''', ''' + @tableName + ''', NULL, NULL, ''PAGE'';'

INSERT INTO #compressionRow

EXECUTE sp_executesql @sqlStatementRow;

INSERT INTO #compressionPage

EXECUTE sp_executesql @sqlStatementPage;

UPDATE #tables

SET processed = 1

WHERE tableName = @tableName

AND schemaName = @schemaName;

END;

SELECT objectName

--, index_id

--, partition_number

, sum(size_current_compression/1024/1024) as CurentSize_GB

, sum( size_requested_compression/1024/1024) as EstimageSize_GB

, 100-100* sum( size_requested_compression/1024/1024)/sum(size_current_compression/1024/1024) as '%'

, 'ROW'

FROM #compressionRow

GROUP BY objectName

UNION

SELECT objectName

--, index_id

--, partition_number

, sum(size_current_compression/1024/1024) as CurentSize_GB

, sum( size_requested_compression/1024/1024) as EstimageCompression_GB

, 100-100* sum( size_requested_compression/1024/1024)/sum(size_current_compression/1024/1024) as '%'

, 'PAGE'

FROM #compressionPage

GROUP BY objectName

ORDER BY objectName

Petit guide des commandes SYBASE ASE

Un petit guide des différentes commandes pour SYBASE ASE


Les commandes système 'dbcc' :

 

dbcc traceon(3604) : affiche à l’écran

dbcc traceon(3605) : redirige dans la log

dbcc traceon(11209) : updatestat renvoie le row count

dbcc traceon(328) : disable the reformatting

dbcc sqltext(spid) : affiche la requete d’une session

dbcc pss(0,spid,0) :

dbcc page(dbname,page_id) : description d’une page problematique, acces a une page et a son contenu

dbcc checkdb(db_name) : contrôle le chaînage des pages, vérifie la cohérence des tables

dbcc checkcatalog : vérifie les références des tables systèmes

dbcc checkalloc(db_name) : identification des erreurs, vérifie l’allocation des pages dans toute la base de données (traitement long)

dbcc checktable(table_name) : decrit les lien entre chaque page d’un table, indique le nombre moyen de ligne par page (used_pgs nombre de page utilisé par une table, et rowent, nombre de ligne d’une table)

dbcc indexalloc(table_name,indid,full) : vérifie l’allocation des pages pour l’index spécifié

dbcc tablealloc(table_name,full,fix) : vérifie/corrige la bonne/mauvaise allocation des pages d’une table avec l’option (fix ou nofix)

dbcc listoam ou dbcc tablealloc (attention verrou): permet d’examiner des extents et des pages d’allocations

dbcc pglinkage : permet de suivre les chaînes de pages.

dbcc prtipage : permet d’afficher une page d’index.

dbcc gettrunc : liste les points de troncature

dbcc settrunc(ltm,’ignore’) : supprime le point de troncature

Dbcc engine (net, show moteur) affiche les tâches liées au moteur

Dbcc engine (net, showall moteur) affiche toutes les tâches

Dbcc engine (net, netengine moteur) affiche les moteurs auquel sont liées les tâches en cours

dbcc dbrepair(database_name,dropdb) : permet de dropper une base quand elle dans un état 'suspect'. Commande non supportée

 

dbcc dbreboot :

dbcc help(dbreboot)

dbreboot (report | reboot | reboot_norecovery | shutdown | shutdown_load | restart | restart_norecovery, <dbname1> [, <dbname2> ...])

Reboot the specified databases.Options

report – Shows a report on the specified database(s).

reboot – Database is shutdown and restarted with recovery.

reboot_norecovery – Database is shutdown and restarted and left in a “not recovered” state. Can be used to add more space.

shutdown – Database is shutdown and left in an unusable state.

shutdown_load – Database is shutdown and left in a state that allows LOAD DATABASE to be done.

restart – Restart and recover a database that has been shutdown.

restart_norecovery – Restart database and left in a “not recovered” state. Good when there are problems with recovery.

DBCC MARKPROCS(DBName) : commande suite à un rechargement d'un dump d'un autre environnement. Il s'agit en fait d'un bug se traduisant par une erreur lors de l'exécution de curseurs (Ex : Table TableName not found !!!)

 

Les commandes systèmes utiles :

 

select lct_admin(« abort »,0,2) ---- 2 pour tempdb, 0 pour tous les process

select (sysstat2 & 57344) from sysobjects where name=’table_name’ :

donne le type de locking :

0 ou 8192 allpages

16384 datapages

32768 datarows

select inttohex() et/ou biginttohex().

select pssinfo(spid, « tempdb_pages ») : donne les pages créées dans tempdb pour un spid

syslogshold : liste des process en tache de fond

 

 

 

 

Les commandes 'set' :

 

set showplan on : decrit le l’arbre d’instruction

set statistics io on : decrit les io memoire et disque

set statistics time on : decrit le temps de reponse (Parse et Execute time : query plan, Execute time : execute query plan)

set noexec on : n’execute pas la requete mais montre le plan avec showplan

set fmtonly :

set rowcount :

set statistics subquerycache on : affiche les acces ios physique et memoire

SET BACKGROUND ON : redirige l'output dans l'error log du data server

set proc_return_status off : supprime le return status

set plan optgoal allrows_oltp (SYBASE ASE 15)

set plan opttimeoutlimit 3

set statistics plancost on

set compatibility_mode on : activation du compatibility mode. C'est à dire activer l'optimiseur 12.5 sur une version 15

set statement_cache off : désactivation du statement cache

set opportunistic_distinct_view off : mis en place lors de la détection d'un bug SYBASE 15 sur la gestion d'un 'select into' vers une table temporaire #

set option show_missing_stats on : affiche les colonnes de la requete qui n'ont pas de statistiques (option de la 15)

set switch on PRINT_OUTPUT_TO_CLIENT (ASE 12.5.4) : permet de rediriger la sortie à l'écran

set switch on print_plan_index_selection (ASE 12.5.4) : équivalent au 302 (dbcc traceon(302), set switch on 302)

 

 

 

Les Procédures Stockées système :

 

sp_helpsort : configuration server (LANG)

sp_deviceattr : modifie l’option dsync

sp_dbcc_faultreport : liste des erreurs par table

sp_dbcc_faultreport(« long »,db_name,table_name) : specifie les erreurs détaillées sur une seule table

sp_showplan spid,null,null,null : montre le plan

sp_countmetadata ‘open objects’ ou ‘open indexes’ : compte les objets et la place memoire necessaire

sp_reportstats : permet d’obtenir les consommations CPU en fonction des logins

sp_clearstats : pour mettre à zéro les stats

sp_etspace : estime l’espace pris par une table

sp_helpsegment, sp_spaceused : donne l’espace d’une table

sp_object_stats "00:01:00", 5, faodb_agence : visualise les 5 tables les plus consommatrices en locks

sp_cachestrategy nom_base,nom_table,nom_index 

sp_cursorinfo :

sp_options 'show' : affiche les options de la session courante

sp_options 'show',null,null,4590 : affiche les options du spid 4590

 

 

Les variables globales :

@@timeticks (durée d’une impulsion en ms)

@@cpu_busy (nombre total d’impulsion CPU)

@@io_busy (impulsion consommées dans les E/S)

@@total_read (nombre total de lecture disque)

@@ total_write (nombre total d’écriture disque)

 

@@nestlevel compte le nombre d'imbrication.

@@identity