SelectEtoile : Base de connaissance SGBD

Posts Tagged 'system'

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

 

 

 

 

 

 

 

 

Profilling IQ (12.7). A la recherche de la requête psychopathe, tueuse de ressource

A quoi sert le profiling ?

Le profiling Iq permet d'obtenir des informations sur l'utilisation des objets : procédures, fonctions, events (Gestionnaire des tâches IQ), triggers système et triggers. 
Il est possible d'obtenir le temps d'exécution de chaque requête ainsi que le temps d'exécution pour chaque ligne d'une procédure stockée. Ces informations permettent d'identifier d'éventuelles procédures à optimiser.
Une autre utilisation, que je qualifierais de "détournée", permet de connaître les procédures qui ne seraient pas utilisées dans la base. 
Les informations sont stockées en mémoire et peuvent être consultées via Sybase central ou via des procédures système (voir plus bas). Les données sont visibles tant que le l'option est active ou que le serveur IQ est démarré.
 

Comment l'activer et l'administrer ?

 Activation/désactivation:

L'activation se fait via une procédure système:

Activation: sa_server_option 'procedure_profiling', 'On'
Désactivation: sa_server_option 'procedure_profiling', 'Off'

Le profiling est une option du moteur ASA appliquée à l'activité IQ. Les ressources utilisées pour les données prélevées sont celles attribuées au catalogue ASA. Il faut faire très attention à la mémoire allouée lors du démarrage du serveur IQ pour le catalogue (-c). Si la mémoire n'est pas suffisante tout va être envoyé dans l'espace temporaire utilisé par la partie ASA (pour les infos sur ce sujet c'est ici). 

RESET ou CLEAR 

Pour éviter tous les problèmes liés aux ressources, il faut penser à remettre à zéro les compteurs du profiling de temps en temps.

sa_server_option 'procedure_profiling', 'RESET' : Remet les compteurs du profiling à zéro sans activer ou désactiver le profiling
sa_server_option 'procedure_profiling', 'CLEAR' : Même chose mais désactive le profiling

profile_filter_conn ou profile_filter_user

Il est également possible de d'activer le profiling pour un utilisateur ou une session
Capturer les profiling information pour une connexion spécifique: sa_server_option 'procedure_profiling', 'profile_filter_conn'
Et Pour un utilisateur: sa_server_option 'procedure_profiling', 'profile_filter_user'

Consulter le résultat

 sa_procedure_profile_summary

Fonction: Permet d'obtenir un résumé sur les temps d'exécution de toutes les objets (Procédure, event...) qui ont été exécutées sur le serveur.

 Syntaxe:

sa_procedure_profile_summary (
[p_table_name
[, p_owner_name
[, p_object_name
[, p_object_type
[, p_ordering ] ] ] ] ]
)
Les arguments (Je ne traduis pas):
  •  p_object_name This optional char(128) parameter specifies the name of the stored procedure, function, event, or trigger.
  •  p_owner_name This optional char(128) parameter specifies the object’s owner.
  •  p_table_name This optional char(128) parameter specifies the table associated with a trigger (the value is NULL for other object types).
  •  p_object_type This optional char(1) parameter specifies the type of object to profile. It can be one of the following:
    • · P stored procedure
    • · F function
    • · E event
    • · T trigger
    • · S system trigger
    • · C ON UPDATE system trigger
    • · D ON DELETE system trigger
  •  p_ordering This optional char(1) parameter determines the order of columns in the result set. If no value is given, the results are listed from the longest execution time to the shortest execution time. Values and theresulting order are:
    • · P object_type, owner_name, object_name, table_name desc
    • · N object_name, owner_name, table_name, object_type desc
    • · O owner_name, object_type, object_name, table_name desc
    • · T table_name, owner_name, object_name, object_type desc
    • · E executions desc, object_name, owner_name, table_name,object_type desc
 Exemple de sortie :
object_type object_name                           owner_name table_name executions millisecs foreign_owner foreign_table
------------------------------------------------------------------------------------------------------------------------
P           sp_iq_process_login                   DBA        (NULL)     3780       13155     (NULL)        (NULL)
P           sp_login_environment                  dbo        (NULL)     3780       11703     (NULL)        (NULL)
P           sp_tsql_environment                   dbo        (NULL)     894        11567     (NULL)        (NULL)
P           sp_dba_version                        dbo        (NULL)     1154       9036      (NULL)        (NULL)
P           sp_iqtransaction                      dbo        (NULL)     1845       2401      (NULL)        (NULL)
P           sp_mda                                dbo        (NULL)     894        424       (NULL)        (NULL)
P           sa_procedure_profile                  dbo        (NULL)     2          6         (NULL)        (NULL)
P           sa_procedure_profile_summary          dbo        (NULL)     2          3         (NULL)        (NULL)
P           sp_iqwho                              dbo        (NULL)     1          2         (NULL)        (NULL)
P           sa_internal_procedure_profile         dbo        (NULL)     2          2         (NULL)        (NULL)
P           sa_server_option                      dbo        (NULL)     1          0         (NULL)        (NULL)
P           sa_internal_procedure_profile_summary dbo        (NULL)     2          0         (NULL)        (NULL)

 

sa_procedure_profile

Fonction: Permet d'obtenir les temps d'exécution pour chaque ligne de toutes les procédures qui ont été exécutées sur le serveur

Syntaxe:

sa_procedure_profile (
[ p_object_name
[, p_owner_name
[, p_table_name ] ] ]
)

Arguments:

  •   p_object_name This optional char(128) parameter specifies the name
    of the stored procedure, function, event, or trigger.
  •  p_owner_name This optional char(128) parameter specifies the object’s owner.
  •  p_table_name This optional char(128) parameter specifies the table
    associated with a trigger (the value is NULL for other object types).
Exemple de sortie:
 object_type object_name     owner_name table_name line_num executions millisecs percentage    foreign_owner foreign_table
---------------------------------------------------------------------------------------------------------------------------------------------
P           sa_procedure_profile         dbo        (NULL)     6        3          1         16.666666666666668   (NULL)        (NULL)
P           sa_procedure_profile         dbo        (NULL)     20       2          4         66.66666666666667    (NULL)        (NULL)
P           sa_procedure_profile         dbo        (NULL)     21       2          0         0.0                  (NULL)        (NULL)
P           sa_procedure_profile         dbo        (NULL)     22       2          1         16.666666666666668   (NULL)        (NULL)
P           sa_procedure_profile_summary dbo        (NULL)     8        3          2         40.0                 (NULL)        (NULL)
P           sa_procedure_profile_summary dbo        (NULL)     20       3          2         40.0                 (NULL)        (NULL)
P           sa_procedure_profile_summary dbo        (NULL)     21       3          0         0.0                  (NULL)        (NULL)
P           sa_procedure_profile_summary dbo        (NULL)     35       3          0         0.0                  (NULL)        (NULL)
P           sa_procedure_profile_summary dbo        (NULL)     51       3          1         20.0                 (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     3        1158       483       5.327597617471873    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     77       1158       505       5.57026251930289     (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     79       1158       3024      33.35539377895434    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     81       1158       91        1.0037502757555703   (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     82       1158       49        0.5404809177145379   (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     83       1158       52        0.5735715861460402   (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     84       1158       55        0.6066622545775424   (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     85       1158       772       8.515332009706595    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     91       1158       735       8.107213765718068    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     93       1158       560       6.176924773880432    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     95       1158       499       5.504081182439886    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     97       1158       512       5.647474078976395    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     100      1158       359       3.9598499889697774   (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     102      1158       2         0.022060445621001543 (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     105      1158       3         0.03309066843150232  (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     117      1158       0         0.0                  (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     120      1158       631       6.960070593425987    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     122      1158       217       2.3935583498786674   (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     123      1158       74        0.8162364879770572   (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     124      1158       4         0.04412089124200309  (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     125      1158       397       4.378998455768807    (NULL)        (NULL)
P           sp_dba_version               dbo        (NULL)     130      1158       42        0.4632693580410324   (NULL)        (NULL)
P           sp_iq_process_login          DBA        (NULL)     2        3793       186       1.410373066424022    (NULL)        (NULL)
P           sp_iq_process_login          DBA        (NULL)     51       3793       1150      8.720048528965727    (NULL)        (NULL)
P           sp_iq_process_login          DBA        (NULL)     53       3793       10        0.07582650894752806  (NULL)        (NULL)
P           sp_iq_process_login          DBA        (NULL)     54       3793       11810     89.55110706703063    (NULL)        (NULL)
P           sp_iq_process_login          DBA        (NULL)     55       3793       32        0.24264482863208978  (NULL)        (NULL)
P           sp_iqtransaction             dbo        (NULL)     3        1851       251       10.4149377593361     (NULL)        (NULL)
P           sp_iqtransaction             dbo        (NULL)     25       1851       232       9.62655601659751     (NULL)        (NULL)
P           sp_iqtransaction             dbo        (NULL)     27       1851       1297      53.81742738589212    (NULL)        (NULL)
P           sp_iqtransaction             dbo        (NULL)     28       1851       443       18.38174273858921    (NULL)        (NULL)
P           sp_iqtransaction             dbo        (NULL)     39       1851       187       7.759336099585062    (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     3        1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     40       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     42       1          1         50.0                 (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     43       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     59       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     66       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     69       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     71       2          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     72       2          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     73       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     75       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     76       1          1         50.0                 (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     88       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     91       1          0         0.0                  (NULL)        (NULL)
P           sp_iqwho                     dbo        (NULL)     94       1          0         0.0                  (NULL)        (NULL)
P           sp_login_environment         dbo        (NULL)     2        3793       6         0.051133458326231465 (NULL)        (NULL)
P           sp_login_environment         dbo        (NULL)     3        3793       99        0.8437020623828192   (NULL)        (NULL)
P           sp_login_environment         dbo        (NULL)     4        897        11629     99.10516447929095    (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     2        897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     3        897        3         0.7058823529411765   (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     4        897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     5        897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     6        897        0         0.0                  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     7        897        0         0.0                  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     8        897        4         0.9411764705882353   (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     9        897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     10       897        3         0.7058823529411765   (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     11       897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     12       897        21        4.9411764705882355   (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     13       897        2         0.47058823529411764  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     14       897        0         0.0                  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     20       897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     21       897        2         0.47058823529411764  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     22       897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     26       897        0         0.0                  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     30       897        2         0.47058823529411764  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     37       897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     38       897        2         0.47058823529411764  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     39       897        7         1.6470588235294117   (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     41       897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     50       897        0         0.0                  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     51       897        4         0.9411764705882353   (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     52       897        360       84.70588235294117    (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     77       897        2         0.47058823529411764  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     81       897        1         0.23529411764705882  (NULL)        (NULL)
P           sp_mda                       dbo        (NULL)     85       897        3         0.7058823529411765   (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     2        897        1         0.00862217623728229  (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     3        897        19        0.16382134850836352  (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     8        897        842       7.2598723917916885   (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     9        897        775       6.682186583893775    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     10       897        789       6.8028970512157265   (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     11       897        792       6.828763579927574    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     12       897        797       6.871874461113985    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     13       897        753       6.492498706673564    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     14       897        749       6.4580100017244355   (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     15       897        750       6.466632177961718    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     16       897        795       6.85463010863942     (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     17       897        741       6.389032591826177    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     18       897        753       6.492498706673564    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     19       897        743       6.406276944300742    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     20       897        764       6.58734264528367     (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     21       897        730       6.294188653216072    (NULL)        (NULL)
P           sp_tsql_environment          dbo        (NULL)     22       897        805       6.940851871012243    (NULL)        (NULL)


Mise en application

 En cours de réalisation