Identifier les partitions ayant vicié la statistique dans une liste de schéma

J'ai 5 développement de schémas. Et chacun d'entre eux ont des tables partitionnées. Nous avons également des scripts pour créer dynamiquement les tables de partition (Mensuel/Annuel). Nous devons aller à DBA à chaque fois pour obtenir les détails sur la partition des tableaux. Notre vrai problème, c'est que nous avons une table de partition avec 9 partitions. Chaque jour, après un delta de l'opération de chargement (Mises à jour/Supprime l'aide d'un PL/SQL) aussi certains APPEND charge à l'aide de SQL*Loader. Cette opération se produit lorsque la base de données a la charge de pointe. Nous avons quelques problèmes de performance sur cette table.(SELECT requêtes)

Quand ils sont signalés à DBA, ils disent les statistiques de la table sont obsolètes et après qu'ils font "la collecte de statistiques", comme par magie, la requête fonctionne plus rapidement. J'ai cherché sur ce et a identifié quelques informations sur dynamic performance vues.

Donc, maintenant , j'ai les Questions suivantes.

1) les développeurs de générer la liste de tous les partitionon tables, le nom de partition, pas de registres disponibles sans passer par DBA?

2) Doit-on identifier la dernière analysé la date de chaque partition

3) de plus, le statut de la partition(index) si elle utilisable ou inutilisable.

InformationsquelleAutor Devaraj Mahesan | 2014-01-21

J'ai utilisé pour interroger all_ les tableaux mentionnés ci-dessous.

La statistique et de l'histogramme des détails que vous mentionnez sera mise à jour à une fréquence automatiquement par Oracle. Mais lorsque la base de données est occupé avec beaucoup de charges, j'ai vu ces opérations doit être déclenché manuellement. Nous avons été confrontés à une situation similaire, nous avons donc utilisé la force de l'Analyser son fonctionnement après notre charge pour les tables critiques. Vous avez besoin d'avoir le privilège de l'identifiant que vous utilisez pour charger la table.

ANALYZE TABLE table_name PARTITION (partition_name) COMPUTE STATISTICS;

MODIFIER: ANALYZE de ne plus recueillir CBO stats comme mentionné ici

Donc, DBMS_STATS paquet doit être utilisé.

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

Et jusqu'à ce que l'analyse est terminée, l'affichage des tableaux ci-dessous peuvent ne pas produire les résultats précis (en Particulier la last_analyzed et num_rows colonnes)

Remarque: Essayez de remplacer all_ comme dba_ dans les noms de table, si vous y avez accès, vous pouvez les essayer.

Vous pouvez également essayer d'obtenir SELECT_CATALOG_ROLE pour le développement de votre id que vous utilisez, de sorte que vous pouvez SÉLECTIONNER le data dictionary views, ce qui réduit la dépendance sur DBA sur ces requêtes.(Encore DBA sont les bonnes personnes pour quelques questions!!)

Requête pour identifier la table de partition, partition de nom, le nombre de lignes et la dernière Analysé date!

select 
      all_part.owner as schema_name,
      all_part.table_name,
      NVL(all_tab.partition_name,'N/A'),
      all_tab.num_rows,
      all_tab.last_analyzed
from
      all_part_tables all_part,
      all_tab_partitions all_tab
where all_part.table_name = all_tab.table_name and
      all_tab.partition_name = all_tab.partition_name and
      all_part.owner=all_tab.table_owner and
      all_part.owner in ('SCHEMA1','SCHEMA2','SCHEMA3')
order by all_part.table_name,all_tab.partition_name;

Ci-Dessous la Requête renvoie l'index/nom de la table qui sont UNUSABLE

SELECT INDEX_NAME,
  TABLE_NAME,
  STATUS
FROM ALL_INDEXES
WHERE status NOT IN ('VALID','N/A');

Ci-Dessous la Requête renvoie l'index/la table (PARTITION) nom UNUSABLE

SELECT INDEX_NAME,
  PARTITION_NAME,
  STATUS ,
  GLOBAL_STATS
FROM ALL_IND_PARTITIONS
WHERE status != 'USABLE';

DBMS_STATS serait sans doute mieux que ANALYSE.
Je suis d'accord avec @jonearles et c'est une recommandation générale trop. Vous ne devez pas utiliser ANALYZE pour recueillir des statistiques post Oracle 8i, utilisez plutôt DBMS_STATS. Vous pouvez consulter asktom.oracle.com/pls/asktom/... trop
Merci @jonearles et Incognito, j'ai édité ma réponse.

InformationsquelleAutor Maheswaran Ravisankar

Normalement il n'est pas nécessaire pour identifier les objets qui ont besoin de statistiques recueillies. Oracle recueille automatiquement des statistiques pour rassis objets, à moins que la tâche a été
désactivé manuellement. C'est généralement suffisant pour les systèmes OLTP. Utiliser cette requête pour connaître l'état de la tâche:

select status
from dba_autotask_client
where client_name = 'auto optimizer stats collection';

STATUS
------
ENABLED

Pour les données de l'entrepôt systèmes, il n'ya pas besoin d'interroger le dictionnaire de données pour rassis stats. Dans un entrepôt de données statistiques doivent être prises en compte après presque
chaque opération. Les développeurs doivent prendre l'habitude de toujours penser à les statistiques après un truncate, insert, swap, etc. Finalement, ils vont "juste savoir" quand à recueillir des données statistiques.

Mais si vous voulez continuer à voir la façon dont Oracle détermine si les statistiques sont rassis, regardez DBA_TAB_STATISTICS et DBA_TAB_MODIFICATIONS.

Voici un exemple de chargement initial avec la collecte des statistiques. La table et les partitions ne sont pas obsolètes.

create table test1(a number, b number) partition by list(a)
(
    partition p1 values (1),
    partition p2 values (2)
);
insert into test1 select 1, level from dual connect by level <= 50000;
begin
    dbms_stats.gather_table_stats(user, 'test1');
    dbms_stats.flush_database_monitoring_info;
end;
/

select table_name, partition_name, num_rows, last_analyzed, stale_stats
from user_tab_statistics
where table_name = 'TEST1'
order by 1, 2;

TABLE_NAME  PARTITION_NAME  NUM_ROWS  LAST_ANALYZED  STALE_STATS
----------  --------------  --------  -------------  -----------
TEST1       P1                 50000     2014-01-22           NO
TEST1       P2                     0     2014-01-22           NO
TEST1                          50000     2014-01-22           NO

Maintenant ajouter un grand nombre de lignes et les statistiques sont obsolètes.

begin
    insert into test1 select 2, level from dual connect by level <= 25000;
    commit;
    dbms_stats.flush_database_monitoring_info;
end;
/

select table_name, partition_name, num_rows, last_analyzed, stale_stats
from user_tab_statistics
where table_name = 'TEST1'
order by 1, 2;

TABLE_NAME  PARTITION_NAME  NUM_ROWS  LAST_ANALYZED  STALE_STATS
----------  --------------  --------  -------------  -----------
TEST1       P1                 50000     2014-01-22           NO
TEST1       P2                     0     2014-01-22          YES
TEST1                          50000     2014-01-22          YES

USER_TAB_MODIFICATIONS donne des renseignements plus précis sur la table de caducité.

--Stale statistics.
select user_tables.table_name, user_tab_modifications.partition_name
    ,inserts+updates+deletes modified_rows, num_rows, last_analyzed
    ,case when num_rows = 0 then null
        else (inserts+updates+deletes) / num_rows * 100 end percent_modified
from user_tab_modifications
join user_tables
    on user_tab_modifications.table_name = user_tables.table_name
where user_tables.table_name = 'TEST1';

TABLE_NAME  PARTITION_NAME  MODIFIED_ROWS  NUM_ROWS  LAST_ANALYZED  PERCENT_MODIFIED
----------  --------------  -------------  --------  -------------  ----------------
TEST1       P2                      25000     50000     2014-01-22                50
TEST1                               25000     50000     2014-01-22                50

InformationsquelleAutor Jon Heller

1
1. Oui, vous pouvez générer une liste de tables partitionnées, et beaucoup de données qui vous aimeriez voir, en utilisant ALL_PART_TABLES ou USER_PART_TABLES (à condition d'avoir accès).
  ALL_TAB_PARTITIONS peut être utilisée pour obtenir le nombre de lignes par partition, alongwith d'autres détails.
Vérifier d'autres points de vue Oracle a pour la collecte des détails sur les tables partitionnées.
1. Je voudrais suggérer que vous devriez analyser les tableaux, et, éventuellement, de reconstruire les index, chaque jour après votre chargement de données. Si votre charge de données affecte un grand nombre d'enregistrements dans la table, et va affecter les indices existants, c'est une bonne idée de façon proactive la mise à jour des statistiques pour la table et l'index.
2. Vous pouvez utiliser sur le système de points de vue pour obtenir cette information (Vérifier http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin005.htm)
InformationsquelleAutor Incognito
1

J'ai eu un peu ce genre de problème et je l'ai résolu par la collecte de statistiques sur rassis partitions en utilisant uniquement 11g nouveau INCREMENTAL option.

C'est l'approche inverse à votre problème, mais il pourrait valoir la peine d'examiner (précisément, de la façon dont oracle détermine ce qui est "périmé" partition est).
```
dbms_stats.set_table_prefs('DWH','FACT_TABLE','INCREMENTAL','TRUE')
```
Je préfère toujours l'approche proactive - sens, de recueillir des statistiques sur vicié de la partition lors de la dernière étape de mon etl, plutôt que de donner le développeur plus fort privs.

InformationsquelleAutor haki

Vous devez vous connecter pour publier un commentaire.