Comment améliorer les performances de l'Oracle à l'aide de SELECT DISTINCT
Je suis actuellement en train de travailler dans le déploiement d'un ERP OFBiz
La base de données utilisée est Oracle 10g Enterprise
L'un des plus grands problèmes est quelque oracle des problèmes de performance, l'analyse de la ofbiz les journaux, la requête suivante:
SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID,
SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, CREATED_BY,
FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, BILLING_ACCOUNT_ID,
ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, TERMINAL_ID, TRANSACTION_ID,
AUTO_ORDER_SHOPPING_LIST_ID, NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE,
REMAINING_SUB_TOTAL, GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, FECHA_RECEPCION_BODEGAL FROM
ERP.ORDER_HEADER WHERE ((STATUS_ID = :v0 OR STATUS_ID = :v1 OR STATUS_ID = :v2) AND
(ORDER_TYPE_ID = :v3)) ORDER BY ORDER_DATE DESCest très lent. Nous avons testé l'exécution de la requête sans DISTINCTS et il faut environ 30 secondes. Il y a 4.000.000+ enregistre dans la table.
Il y a pour l'indice de PK champ n ° de commande et presque tous les autres domaines
L'EXPLIQUER PLAN DISTINCT est:
SELECT STATEMENT () (null)
SORT (ORDER BY) (null)
HASH (UNIQUE) (null)
TABLE ACCESS (FULL) ORDER_HEADERet sans le DISTINCT est:
SELECT STATEMENT () (null)
SORT (ORDER BY) (null)
TABLE ACCESS (FULL) ORDER_HEADERtoutes les idées concernant le tuning oracle pour améliorer les performances de ce type de requêtes?
Il est très difficile de réécrire la requête, car il est généré automatiquement par ofbiz
donc, je pense que la solution est de tuning oracle
merci d'avance
EDIT: j'ai analysé la requête à l'aide de tkprof ,comme suggéré par Rob van Wijk et haffax,et le résultat est le suivant
********************************************************************************
SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID,
SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, CREATED_BY,
FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, BILLING_ACCOUNT_ID,
ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, TERMINAL_ID, TRANSACTION_ID,
AUTO_ORDER_SHOPPING_LIST_ID, NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE,
REMAINING_SUB_TOTAL, GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, FECHA_RECEPCION_BODEGAL FROM
ERP.ORDER_HEADER WHERE STATUS_ID = 'ORDER_COMPLETED' ORDER BY ORDER_DATE DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 9.10 160.81 66729 65203 37 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 9.14 160.83 66729 65203 37 50
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file scattered read 8178 0.28 146.55
direct path write temp 2200 0.04 4.22
direct path read temp 36 0.14 2.01
SQL*Net more data to client 3 0.00 0.00
SQL*Net message from client 1 3.36 3.36
********************************************************************************Il semble donc que le problème est le "fichier de base de données dispersés à lire", toutes les idées de comment régler oracle afin de réduire l'attente dans ce cas?
Suivi avec le nouveau tkprof résultat, cette fois, la fermeture de la session:
********************************************************************************
SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID,
SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, CREATED_BY,
FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, BILLING_ACCOUNT_ID,
ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, TERMINAL_ID, TRANSACTION_ID,
AUTO_ORDER_SHOPPING_LIST_ID, NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE,
REMAINING_SUB_TOTAL, GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, FECHA_RECEPCION_BODEGAL FROM
ERP.ORDER_HEADER WHERE STATUS_ID = 'ORDER_COMPLETED' ORDER BY ORDER_DATE DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 8.23 47.66 66576 65203 31 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 8.26 47.68 66576 65203 31 50
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58
Rows Row Source Operation
------- ---------------------------------------------------
50 SORT ORDER BY (cr=65203 pr=66576 pw=75025 time=47666679 us)
3456659 TABLE ACCESS FULL ORDER_HEADER (cr=65203 pr=65188 pw=0 time=20757300 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file scattered read 8179 0.14 34.96
direct path write temp 2230 0.00 3.91
direct path read temp 52 0.14 0.84
SQL*Net more data to client 3 0.00 0.00
SQL*Net message from client 1 1510.62 1510.62
********************************************************************************- Les colonnes order_date, order_type_id et status_id où les valeurs null. Après réglage à être PAS NULL, la requête s'exécute en environ 50 secondes. Maintenant, le plan distinct, est égal au régime sans distinctes. La partie la plus lente est le genre
Vous devez vous connecter pour publier un commentaire.
Si la différence entre les deux requêtes est important, que les serait étonnant. Vous mentionnez que la requête sans DISTINCTES prend environ 30 secondes. Combien de temps la requête avec l'DISTINCTES prendre?
Pouvez-vous montrer le tkprof de sortie de la requête avec le DISTINCT, après avoir retracé la session avec un "alter session set événements" 10046 trace du nom de contexte pour toujours, 8'", et débranchez la fin de la requête? De cette façon, nous pouvons voir où le temps est passé et si c'était l'attente de quelque chose ("chemin direct lire temp" peut-être?)
Ce qui concerne,
Rob.
Suivi, après la tkprof fichier a été publié:
Je vois que vous avez réussi à obtenir le tkprof de sortie, mais malheureusement, vous n'avez pas déconnecter de votre session avant de créer le tkprof fichier. Maintenant, le curseur a été laissée ouverte et il n'a pas écrit STAT# lignes à votre fichier de trace. C'est pourquoi vous n'avez pas un plan /rangée de fonctionnement de la source dans votre tkprof fichier. Il serait bien si vous pouvez répéter le processus, si la suggestion ci-dessous se révèle être de la foutaise.
Un peu de la spéculation de ma part: je pense que le DISTINCT est presque un no-op parce que vous choisissez donc de nombreuses colonnes. Si cela est vrai, alors votre prédicat "OÙ STATUS_ID = 'ORDER_COMPLETED'" est très sélectif et vous aurez l'avantage d'avoir un index sur cette colonne. Après la création de l'index, assurez-vous de les analyser correctement, peut-être même avec un histogramme sur si les valeurs des données sont faussées. Le résultat final sera un autre plan de cette requête, en commençant avec un INDEX RANGE SCAN, suivie d'une TABLE ACCESS BY ROWID, conduisant à une très rapide de la requête.
Après avoir créé un index, vous pouvez avoir la table de ré-analysés, y compris les histogrammes de l'aide de cette instruction:
exec dbms_stats.gather_table_stats([propriétaire],[nom_table],cascade=>true,method_opt=>'POUR TOUTES les COLONNES INDEXÉES TAILLE ')
Ce qui concerne,
Rob.
Puisque vous êtes à la commande de résultats selon
order_dateil est important que vous avez un descendant de l'index sur ce champ.Dites aussi à oracle que vous souhaitez utiliser cet indice. Placez le
order_datepremier champ dans la requête et utiliser un indice commeCe n'est pas tant d'avoir des indices, mais plutôt de raconter oracle pour les utiliser. Oracle est très pointilleux sur les indices. Vous obtenez les meilleurs résultats lorsque vous choisissez des indices en fonction de votre plus important de requêtes. En cas de doute, trace une requête. De cette façon, vous pouvez voir dans quelle partie de la requête oracle passe le plus de temps et si vos indices sont en fait pris en charge ou pas. Le suivi est très précieux lors de la lutte contre les problèmes de performance.
Est ORDER_ID déclaré que le PK à l'aide d'une contrainte de CLÉ PRIMAIRE? Parce que si c'est moi attendons à ce que l'optimiseur de reconnaître que la diversité est superflu dans cette requête et l'optimiser l'extérieur. Sans la contrainte, il ne sait pas qu'il est superflu et ainsi de se consacrer inutiles et des efforts considérables dans le "de-de gruger" les résultats.
Essayez de désactiver l'agrégation de hachage:
http://oracle-randolf.blogspot.com/2011/01/hash-aggregation.html
Lors du dépannage des applications où je n'ai pas le contrôle de la SQL je trouve que le dbms_sqltune package permet d'économiser beaucoup de temps. Voir http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sqltun.htm , et oui, malheureusement, vous devriez être autorisé à l'utiliser.
Il y a des procédures dans l'exécution de ce package d'une analyse de paramétrage à l'égard d'une sql_id dans la piscine commune ou le référentiel AWR. L'analyse permettra de contenir l'indexation des recommandations s'il y a des améliorations à faire avec d'autres index. Plus important encore, l'analyseur peut découvrir une amélioration du chemin d'accès qu'il peut mettre en œuvre avec ce que l'Oracle des appels SQL Profil - c'est un ensemble de conseils qui seront stockées et utilisées chaque fois que ce sql_id est exécutée. Ce qui se passe sans exiger des conseils pour être codé dans l'instruction SQL, et il ya aussi une option pour faire ce que vous pouvez penser que la correspondance floue si votre application génère des valeurs littérales dans la déclaration au lieu de lier les variables.
Bien sûr, cet outil ne devrait pas être un substitut pour la compréhension de l'application et des structures de données, mais la lecture par le biais de la sortie de détails le chemin d'exécution d'un meilleur plan (si présente) peut être éducative.
Oracle pour accéder à l'ensemble de la table à chaque fois que vous exécutez la requête ( ACCÈS à la TABLE (COMPLET) ).
Création d'un INDEX sur le STATUS_ID et ORDER_TYPE_ID colonnes
sera d'une grande aide, surtout si il y a plusieurs valeurs différentes de STATUS_ID et ORDER_TYPE_ID dans le ORDER_HEADER table.