Oracle mise à jour simple déclaration, des performances médiocres
J'ai une table avec un big data (autour de 10 millions d'enregistrements), de sorte que le plus simple mise à jour de l'énoncé de prendre pour toujours.
Par exemple:
update mesg
set archived = 1
, last_update = SYSDATE
where id = 0
and crea_date_time < '07/27/2011 13:53:36'
and archived = 0;
Cette déclaration prend environ 3 Heures. bien que nous ayons l'indice de l'id, et l'indice composite sur crea_date_time, et il n'y a pas de déclencheurs.
Est-il un accessoire que je peux faire pour augmenter les performances.
J'ai essayé d'ajouter un index sur les archives, mais sans effet.
voici quelques informations supplémentaires.
CREATE TABLE "MESG"
(
"ID" NUMBER(3,0) NOT NULL ENABLE,
"UMIDL" NUMBER(10,0) NOT NULL ENABLE,
"UMIDH" NUMBER(10,0) NOT NULL ENABLE,
.
.
.
"ARCHIVED" NUMBER(1,0) NOT NULL ENABLE,
"LAST_UPDATE" DATE,
"CREA_DATE_TIME" DATE NOT NULL ENABLE,
.
.
.
CONSTRAINT "PK_RMESG" PRIMARY KEY ("AID", "UMIDH", "UMIDL") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "XXXX_IDX" ENABLE
)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "XXXX_MESG" ;
L'index:
CREATE INDEX "E_RCREATIONDATE" ON "RMESG"
(
"CREA_DATE_TIME"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "XXXX_IDX" ;
CREATE UNIQUE INDEX "PK_RMESG" ON "RMESG"
(
"ID", "UMIDH", "UMIDL"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "XXXX_IDX" ;
Et Le plan de requête est pour ma machine locale avec 180K enregistrements
ID PID Operation Name Rows Bytes Cost CPU Cost IO Cost Temp space IN-OUT PQ Dist PStart PStop
0 UPDATE STATEMENT 1 44 877 6245703 877
1 0 UPDATE MESG
2 1 TABLE ACCESS BY INDEX ROWID MESG 1 44 877 6245703 877
3 2 INDEX RANGE SCAN IX_MESG_CREATIONDATE 158K 877 6245703 877
- le nombre d'enregistrements affectés par la mise à jour?
- 10 millions de dollars n'est pas beaucoup de disques. Cela semble fou, il prendrait 3 heures, même si vous n'avez pas l'indice de quoi que ce soit. Avez-vous une clé primaire définie? C'est de la folie.
- Sont-il des mises à jour simultanées à partir d'autres opérations? Pourriez-vous être en attente sur une serrure?
- Oui, j'ai une clé primaire, et les enregistrements effectués autour de 158K
- Et cette requête de parler si longtemps, même sur ma machine avec 180K dossiers, il prend environ 45 secondes
- Avez-vous besoin de l'index sur CREA_DATE_TIME? Pouvez-vous l'essayer sans elle???
- il y a beaucoup de CREA_DATE_TIME les instructions select, en fait ce tableau ont 5 indice
- Sonne comme la mise à jour est pris dans la ligne de verrouillage, vérifier
v$locked_object
pour voir si il y a d'objets empêchant la mise à jour de passer par - Pouvez-vous inclure le plan de requête dans la question?
- oui, bien sûr, vous pouvez le trouver maintenant 🙂
- Il n'y a aucune garantie que la requête sur la 10 millions dossier dataset serait la même que celle de la requête sur votre machine locale du 180k enregistrements. Oracle utilise une fonction du coût de l'optimiseur qui détermine le chemin d'accès basé sur les statistiques enregistrées sur chaque table.
- si des index sur des colonnes à jour(crea_date_time est l'un d'entre eux), les 3 heures sont normaux. Ces indices doivent être mis à jour pour chaque ligne modifiée.
- est-ce à systématiquement prendre 3 heures, ou juste arrivé une fois?
- yah c'est toujours 🙁
- oui, vous avez raison, mais toujours avec 180K il faut environ 62 secondes, ce qui est beaucoup trop 🙂
- Pouvez-vous essayer de créer une répétition de l'indice id, crea_date_time et archivées, et de voir comment s'effectue la mise à jour? (Comment, par ailleurs, de nombreux dossiers sont mis à jour sur votre table?)
Vous devez vous connecter pour publier un commentaire.
Je suppose que l'archivage est un pseudo-valeur booléenne qui peut être 0 ou 1. Dans un tel cas, l'optimiseur peut choisir d'ignorer l'index.
Afin de vérifier si l'index est utilisé, vous pourriez essayer
et puis
Si vous mettez à jour le nombre de lignes, les index ne sera pas vous aider. Ils seulement d'améliorer l'accès aux données.
Ce qui est lent pour vous, c'est la mise à JOUR elle-même.
A. Est-ce de la table de lecture/mise à jour lourde par les utilisateurs?
Si non, vous pouvez essayer de recréer la table avec de nouvelles données.
B. un Autre ideea/aider, si vous avez le permis, pour partitionner votre mesg table sur crea_date_time (je suppose que c'est
date
type).Dans ce cas, votre mise à jour, n'analyse pas toute la table, mais le plus important, probablement que vous n'aurez jamais besoin de marquer comme archive choses. Anciennes partitions - anciennes données.
C. L'indice sur CREA_DATE_TIME est le ralentissement de la mise à jour. Si elle n'est pas absolument nécessaire, laissez tomber.
D'abord pensez-vous vraiment dire à exécuter:
(<> le bon code pour ORacle? Ou n'est utiliser !=? ou peut-être lorsque l'archivage est null ou où archivé = 0 depanding sur la façon dont vous stockez les données)
Droit maintenant vous changez la last_update date et le archivé pour tous les documents déjà archivés. Si vous pourriez être la mise à jour des millions d'enregistrements qui sont déjà archivées. Donc, plutôt que de mettre à jour les 120 000 enregistrements qui doivent être archivées depuis la dernière fois que vous avez fait, vous êtes à la mise à jour des 35.000.000 enregistrements dont la plupart sont déjà archivées. Peuvent faire une grande différence dans la performance à mettre à jour uniquement les enregistrements qui doivent être mis à jour.
Ensuite, je ne sais pas à propos de l'Oracle, mais il est parfois plus rapide d'exécuter une mise à jour importante/insérer/supprimer des lots dans SQl Server. Donc, avez-vous essayé une boucle par 1000 (ou 50000-vous peut-être tester pour voir ce qui fonctionne et ce à un moment? Cela peut réduire un grand nombre de contention sur la table et de faire les choses plus rapidement.
Indices ne sont pas le problème. C'est la mise à jour.
Je pense que vous aurez à plonger dans l'attente de l'analyse, dictionnaire le tableau v$session_waits. Si vous avez de l'entreprise gestionnaire de la base de données de contrôle vous pouvez utiliser les outils de performance pour voir ce qui est à l'origine du retard. Ma conjecture est qu'il a quelque chose à voir avec les performances de la redo logs OU comme d'autres l'ont mentionné un problème de verrouillage.
Pour commencer:
Google sur oracle attendre de l'analyse, vous trouverez beaucoup de matériel.
Trois heures, c'est trop long, même pour la quantité de données.
Vous pouvez essayer de modifier la requête pour éviter le type de casting, appliquer to_data fonction de votre date de chaîne (de convertir ce "07/27/2011 13:53:36' à ce jour)
Mais le problème devrait être verrous, vous pouvez vérifier les verrous avec Le meurtre d'un oracle session pour supprimer un verrou script.
Aussi, vous pouvez diviser votre grand requête dans de petites mises à jour, certains thinkl comme:
Vous devriez aller pour le partitionnement de table et index de partitionnement.
Une augmentation des performances des