J'ai des problèmes avec mon plan d'exécution de la requête pour une requête de taille moyenne sur une grande quantité de données dans Oracle 11.2.0.2.0. Afin de faire accélérer les choses, j'ai introduit un filtre de plage qui fait environ quelque chose comme ceci: Comme vous pouvez le constater, je veux limiter le Le problème est, PL / SQL créera des variables de liaison pour ce qui précède Ce n'est que dans ce cas, j'ai mesuré le plan d'exécution de la requête pour être beaucoup mieux lorsque je viens d'aligner les valeurs, c'est-à-dire lorsque la requête exécutée par Oracle est en réalité quelque chose comme < Pré> xxx par "Beaucoup", je veux dire 5-10x plus vite. Notez que la requête est exécutée très rarement, c'est-à-dire une fois par mois. Donc, je n'ai pas besoin de cacher le plan d'exécution. Comment puis-je valeurs en ligne dans PL / SQL? Je sais sur Exécution immédiate , mais je préférerais avoir PL / SQL Compilez ma requête et ne faites pas la concaténation de la chaîne.
Ai-je simplement mesurer quelque chose qui s'est passé par coïncidence ou puis-je supposer que les variables d'inlinage sont en effet meilleurs (dans ce cas)? La raison pour laquelle je demande est parce que je pense que les variables de liaison force oracle pour concevoir un plan d'exécution General , alors que les valeurs inlinées permettraient d'analyser des statistiques de colonne et d'index très spécifiques . Je peux donc imaginer que ce n'est pas juste une coïncidence.
suis-ce que je manque quelque chose? Peut-être qu'il y a une autre façon d'obtenir une amélioration du plan d'exécution de la requête, autrement que l'inlinisation variable (note que j'ai essayé de nombreuses notes aussi, mais je ne suis pas un expert sur ce domaine)?
rejoindre de organisations à l'aide d'une gamme optionnelle de numéros d'organisation. Le code client peut appeler do_stuff avec (supposé être rapide) ou sans (très lent) la restriction. Le problème
org_from et org_to , ce que j'attendrais dans la plupart des cas: Mes questions
5 Réponses :
Ça sent Bind anguille , mais je ne suis que sur Oracle 10, je ne peux donc pas prétendre que le même problème existe dans 11.
Merci pour l'entrée. Je vais vérifier cet article tout de suite!
Découvrez la réponse de Justin, il références également "variable furtivement"
Votre article semble vraiment décrire les symptômes que je ressens. Les cardinalités des différents régimes d'exécutions ne semblent pas changer même si je modifie les valeurs de liaison.
@Lukas - L'odeur passe des variables liées au codé dur semble faire disparaître le problème de performance. J'ai une application multisession lorsque certains ont couru rapidement, et certains ont ralentissé, en raison du plan étant généré pour la petite N (numérisation de la table complète), qui a ensuite été appliquée à une grande quantité de N. linde, s'est avérée être la raison.
Étant donné que les plans de requête sont en réalité différents différents, cela implique que les estimations de la cardinalité de l'optimiseur sont désactivées pour une raison quelconque. Pouvez-vous confirmer à partir des plans de requête que l'optimiseur s'attend à ce que les conditions soient insuffisamment sélectives lorsque des variables de liaison sont utilisées? Puisque vous utilisez 11.2, Oracle devrait utiliser Le partage de curseur adaptatif Donc, il ne devrait donc pas s'agir d'un problème de jeux de variables de liaison (en supposant que vous appelez la version avec des variables de liaison plusieurs fois avec des valeurs Vous pouvez toujours utiliser un indice dans la requête pour forcer un index particulier à utiliser (bien qu'à l'aide d'un Stabilité du plan stocké ou de l'optimiseur serait préférable d'une perspective de maintenance à long terme). Toute de ces options serait préférable de recourir à la SQL dynamique . Un test supplémentaire pour essayer, cependant, serait de remplacer la syntaxe de jointure SQL 99 avec l'ancienne syntaxe d'Oracle, c'est-à-dire qui ne devrait rien changer , mais il y a eu des problèmes d'analyses avec la syntaxe SQL 99 afin que ce soit quelque chose à vérifier. non différents dans vos tests. < p> Les estimations de la cardinalité sur le bon plan sont-elles effectivement correctes? Je sais que vous avez dit que les statistiques sur la colonne NO sont précises mais je serais méfiant d'un histogramme errant qui peut ne pas être mis à jour par votre régularisation Processus de collecte de statistiques, par exemple.
Merci pour vos commentaires étendus! Je vais essayer ce que vous suggérez et double vérifier les statistiques et les histogrammes. L'entretien à long terme n'est pas aussi important d'une question dans ce cas, car les choses changent rarement autour de notre base de données. Mais là encore, je ne suis pas un expert d'Oracle, alors peut-être que vous soulignez quelque chose que je suis tout à fait inconscient.
Une bonne entrée sur les estimations de cardinalité ne sont pas correctes. Je reçois toujours les mêmes cardinalités pour chaque valeur inline, alors j'arrive peut-être de courir dans le même problème que les variables de liaison, avec la petite différence que j'ai chanceuse d'avoir obtenu un "bon" plan à la première tentative. Je vais continuer à enquêter ...
J'ai juste essayé d'éviter la syntaxe de jointure SQL 99, le remplacer par une jointure croisée et des prédicats. Cela change complètement le plan d'exécution, mais les résultats sont les mêmes. Avec des variables liées, j'ai eu un mauvais plan, avec des valeurs inlinettes, j'ai eu un bon plan
Dans l'un de vos commentaires, vous avez dit:
"Aussi j'ai vérifié diverses valeurs de liaison. Avec des variables de liaison, je reçois un peuple complet Scans de table, alors que avec codé dur valeurs, le plan ressemble beaucoup mieux. " P> BlockQuote>
Il y a deux chemins. Si vous passez à NULL pour les paramètres, vous sélectionnez tous les enregistrements. Dans ces circonstances, une analyse de table complète est la manière la plus efficace de récupérer des données. Si vous passez des valeurs, les lectures indexées peuvent être plus efficaces, car vous ne sélectionnez qu'un petit sous-ensemble de l'information. P>
Lorsque vous formulez la requête en utilisant des variables de liaison, l'optimiseur doit prendre une décision: devrait-elle présumer que la plupart du temps, vous passerez des valeurs ou que vous passerez à NULLS? Difficile. Alors, regardez-le d'une autre manière: est-ce plus inefficace de faire une numérisation de table complète lorsque vous n'avez besoin que de sélectionner un sous-ensemble d'enregistrements ou de lire des lectures indexées lorsque vous devez sélectionner tous les enregistrements? P>
Il semble que l'optimiseur ait débuté pour des analyses de table complètes comme étant l'opération la moins inefficace pour couvrir toutes les éventualités. P>
alors que lorsque vous avez un code difficile, les valeurs l'optimiseur sait immédiatement que 10 est NULL code> évalue à FALSE, et il peut donc peser le mérite d'utiliser des lectures indexées pour rechercher les enregistrements de sous-ensemble souhaités. P>
Alors, que faire? Comme vous le dites que cette requête ne fonctionne qu'une fois par mois, je pense que cela ne nécessiterait qu'une petite modification des processus métiers d'avoir des requêtes distinctes: une pour toutes les organisations et une pour un sous-ensemble d'organisations. P>
"BTW, supprimer le: R1 est Null Clause ne change pas le plan d'exécution beaucoup, ce qui me laisse avec l'autre côté de la condition,: r1 BlockQuote>
D'accord, donc la chose est que vous avez une paire de variables liées qui spécifient une plage em>. En fonction de la répartition des valeurs, différentes gammes pourraient convenir à différents plans d'exécution. C'est-à-dire que cette gamme pourrait (probablement) une balayage de plage indexée ... p>
xxx pré> ... alors que cela est susceptible d'être plus adapté à une numérisation de table complète. . P>
WHERE org.id BETWEEN 10 AND 1199999
Merci pour les commentaires. C'est ma pensée intuitive aussi. Je voulais juste être sur. BTW, supprimer le : R1 est NULL clause ne modifie pas beaucoup le plan d'exécution, ce qui me laisse de l'autre côté de la condition ou , : r1 < = org.no où null ne serait pas de sens de toute façon, comme org.no est non null
Merci pour la mise à jour. Plus je lisai à travers vos suggestions de vos gars, plus je suis à la conclusion que peu importe les valeurs que je lie à la requête, le même plan est réutilisé, comme dans l'article affiché par EvilTeach. Cet article mentionne également les prédicats de la portée. En fait, supprimer le prédicat de la plage et le remplacer par un simple org.no =: B1 semble également fournir un plan acceptable. Cependant, depuis que j'utilise 11g et non 10g, je vérifierai et vérifie également les suggestions IK_ZELF. Il y a eu de nombreux ajouts de fonctionnalités à Oracle depuis la modernisation de 11 g.
Cela ressemble beaucoup à un besoin de partage de curseur adaptatif, associé à la stabilité SQLPLAN.
Je pense que ce qui se passe est que le paramètre Si le partage de curseur adaptatif est déjà actif, l'optimiseur générera un plan neuf / meilleur, stockera-le dans les SQL_PLAN_BASINES, mais ne peut pas l'utiliser, jusqu'à ce que quelqu'un accepte ce plan récent comme un plan alternatif acceptable. Vérifiez J'espère que cela vous aidera. capture_sql_plan_baselines est vrai . Et la même chose pour user_sql_plan_baselines . Si cela est vrai, ce qui suit se produit:
dba_sql_plan_baselines et voyez si votre requête a des entrées avec acceptées = 'NO' et vérifié = null
Vous pouvez utiliser dbms_spm.évolve pour évoluer le nouveau plan et l'avoir automatiquement accepté si la performance du plan est d'au moins 1,5 fois mieux que sans le nouveau plan.
J'aime votre expertise de plus en plus chaque jour! Peut-être devrions-nous vous engager dans notre entreprise! :-) Je vais vérifier à travers vos suggestions dès que possible. Merci pour les commentaires!
@Lukas Eder, je suis toujours ouvert aux suggestions. Merci: D
J'ai ajouté cela comme un commentaire, mais vous offrira ici aussi. J'espère que ce n'est pas trop simpliste et envisagez les réponses détaillées, je peux vous empêcher de mal comprendre le problème exact, mais de toute façon ...
semble que votre table ait une colonne NO (Org.no) qui est définie comme un numéro. Dans votre exemple de code papier, vous utilisez numéros pour faire la comparaison. Dans votre procédure, vous passez varchar2 : afin de comparer Varchar2 à Number, Oracle devra faire les conversions , afin que cela puisse causer les analyses complètes. solution: modifier Proc pour passer en numéros < / p>
Belle observation, je n'avais pas pensé à cela. C'est l'inverse, en fait. L'exemple inline doit utiliser des chaînes, pas des chiffres, car org.no est de type varchar2
@Lucas, merci. Semblait simpliste compte tenu des autres commentaires / solutions. Peut ne pas modifier un plan d'explication qui utilise des vers graves, mais le temps d'exécution réel doit être affecté si la comparaison de nombres à Varcharars (ou inversement). Quoi qu'il en soit, j'espère que cela aide en quelque sorte.
Je suis à peu près sûr que c'est une coïncidence. Toutes les questions sont "compilées" et je suis tout à fait sûr que je suis sûr de savoir ce que vous demandez "Variables d'inlinge". Les variables de liaison sont la façon de dire à Oracle "Demandez-moi la valeur qui se trouve dans la piscine partagée".
@Sathya, je vais mettre à jour ma question. Par "inlinge", je veux dire "l'allusion", c'est-à-dire que les variables ne sont plus variables, mais des constantes à l'instruction SQL
Avez-vous des plans de requête différentes de manière cohérente avec des variables liées à des valeurs codées durement? Ou votre différence de performance est-elle potentiellement en raison de la requête «lente» en lisant un tas de données dans le cache (base de données, OS ou SAN) afin que la requête «rapide» bénéficie alors de lectures plus rapides? Quelle version de Oracle? Est-il prudent de supposer que la colonne
no dansorganisations est une clé unique? Les statistiques sur la colonneno sont-elles exactes?@Justin Oui, les plans sont cohérents. J'ai mesuré 1, 2, 1, 2, 1, 2, etc., j'ai également vérifié diverses valeurs de liaison. Avec des variables de liaison, je reçois des analyses de table complètes, alors que des valeurs codées dur, le plan ressemble beaucoup mieux. La version est le 11.2.0.2.0.
Non est unique, les statistiques sont exactes.@Justin, juste au cas où, le problème n'est pas dans la table Organisations . Celui-là n'a qu'environ 550 enregistrements. Chaque organisation dispose de 100k + enregistrements dans diverses autres tables impliquées dans la requête complète bien que
Peut-être poster les plans d'exécution?
@Ffustred ... N'utilisez alors pas de formulairesDesigner :-) Il faudra du temps pour afficher la requête complète (29 lignes) et les plans d'exécution (45-47 étapes d'exécution), car je aurais besoin de les anonymiser avant de poster eux ici. Je le ferai depuis un moment, si cela est vraiment nécessaire. La question importante ici (pour moi), cependant, c'est que s'il existe un moyen de contourner
exécuter immédiatement . J'ai réorganisé l'ordre des questions ...@Lukas Eder: Ok, je ne me suis pas réalisé que c'était que compliqué! Je suppose que vous avez déjà créé des index de manière appropriée et les a mis à jour?
@Frustration: Oui, les index sont corrects. C'est pourquoi l'inlinage / codage dur que les valeurs sont si rapides, car alors ils sont réellement utilisés. J'ai également essayé d'utiliser toutes sortes de notes pour forcer Oracle à utiliser ces index (je devrais mettre cela dans ma question).
@Lukas Eder: Hmmm C'est une chose délicate ... Parfois, j'ai découvert que l'index "le plus correct" n'est pas un élément évident. J'ai parfois fixé des problèmes similaires en créant de nouveaux indices combinant des colonnes qui ne sont pas manifestement liées, mais comme elles participent tous à la requête, il accélère considérablement la requête en question. Mais cela varie beaucoup sur les requêtes et la structure de table sous-jacente. Peut-être que partie des conditions peut être réécrite pour référencer d'autres colonnes indexées? Qu'en est-il de la réécriture d'utiliser
entre pour le filtre de la plage? Pourrait-il aider?@Frustrated, le
entre le mot clé donne presque le même plan d'exécution. Probablement, un index très peu orthodoxe pourrait résoudre le problème au-delà de ma compréhension. Mais cela n'expliquerait pas pourquoi les valeurs inlinées fonctionnent tant mieux que les variables liées. Je pense que je vais devoir poster la requête ... :-)@Lukas: Juste un coup ici, mais dans votre procédure, vous passez à Varchar2, mais votre exemple d'inlinication utilise des chiffres. Possible que Oracle effectue des conversions sur les scènes lorsque vous utilisez les varcharars passés dans la procuration? (c.-à-d. Définir des paramètres sous forme de numéro).
@Tbone, hmm, belle observation.
org.no est une colonnevarchar2 , mais il contient uniquement des numéros à 5 chiffres. Donc, je suppose que, dans mon exemple en ligne, Oracle fait une coulée automatique outo_char ou quelque chose comme ça. Je vérifierai si cela a une signification pour les plans d'exécution (je serais surpris cependant)