Pourquoi est-Oracle si lent lorsque je passe la java.sql.L'horodatage pour une colonne de DATE?

J'ai une table avec un DATE colonne avec le temps (comme d'habitude dans Oracle puisqu'il n'y a pas un TIME type). Quand j'ai une requête que la colonne de JDBC, j'ai deux options:

  • Convertir manuellement les valeurs d'Oracle to_date()
  • Utiliser un java.sql.Timestamp

Les deux approches de travail et ont des zones exclusives de hideux. Mon problème est quand je suis SELECTing données. Voici deux exemples de requêtes:

select *
from TABLE
where TS between {ts '2009-12-08 00:00:00.000'} and {ts '2009-12-09 00:00:00.000'}

select *
from TABLE
where TS between trunc({ts '2009-12-08 00:00:00.000'}) and trunc({ts '2009-12-09 00:00:00.000'})

Les deux requêtes de travail, les mêmes résultats et produit exactement le même résultat dans EXPLAIN PLAN. Ce droit index sont utilisés.

Requête ne fonctionne 15 minutes, tandis que la deuxième requête prend 0.031 s. Pourquoi est-ce? Est-il un lieu central pour résoudre ce problème ou dois-je vérifier toutes mes requêtes pour cet article et de faire absolument sûr que la trunc() est là? Comment puis-je résoudre ce problème lorsque j'ai besoin de sélectionner jusqu'à une certaine deuxième?

[MODIFIER] La table est partitionnée et je suis sur Oracle 10.2.0.

  • Est votre table partitionnée? Oracle JDBC ne semble pas utiliser la partition de l'élagage lorsque vous définissez les paramètres comme Timestamp, pour des raisons que je n'ai jamais compris.
  • Oui, c'est partitionnée.
  • +1 j'aimerais comprendre aussi. Cela se produit uniquement sur une immense table?
  • Eh bien, je suppose que vous ne remarquerez même pas avec une petite table 🙂
  • Comme vous l'avez noté, à l'aide de l' {ts "} syntaxe de votre code de base de données agnostiques, mais est-il une façon de savoir ce que SQL est vraiment passé à la base de données? Et comment est-il important d'être de la base de données agnostique? Si vous pouviez poster le PLAN d'EXPLIQUER les résultats que nous pourrions être en mesure de mieux comprendre ce qui se passe.
  • Bob: Expliquer le plan montre les mêmes résultats, même lorsque j'utilise TO_DATE().

InformationsquelleAutor Aaron Digulla | 2009-12-22
  1. 3

    Je ne comprends pas ce que {ts '2009-12-08 00:00:00.000'} signifie réellement, car ce n'est pas Oracle SQL pour autant que je sais. Pouvez-vous montrer exactement ce que la requête est, vous êtes en cours d'exécution?

    Un problème possible est que vous êtes en précisant votre gamme de millisecondes. Oracle de type DATE ne redescend de quelques secondes. (Utiliser le type TIMESTAMP si vous avez besoin de stocker des fractions de secondes). Mais ce qui se passe, c'est que dans la première requête, Oracle est la conversion de chaque JOUR de la valeur à un HORODATAGE afin de faire la comparaison de votre date précisée.
    Dans le second cas, il sait TRUNC() va efficacement votre tour de valeur à quelque chose qui peut être exprimé comme une DATE, si aucune conversion n'est nécessaire.

    Si vous voulez éviter de telles conversions implicites, assurez-vous que vous êtes toujours en train de comparer des choses comparables.
    par exemple

    select * 
from my_table t
where t.ts between to_date('2009-12-08','YYYY-MM-DD') and to_date('2009-12-09','YYYY-MM-DD')
    • +1 La syntaxe de cette requête semble plus simple, plus compréhensible. Nous utilisons des trucs comme ça, et n'ont aucun problème.
    • {ts ...}, {d ...} est une fonctionnalité cachée de JDBC qui permet de spécifier un java.sql.Timestamp, java.sql.Date dans une DB agnositic façon.
    • Quant à votre question, ma question est plus: Comment puis-je éviter cela sans passer par toutes les sources?
    • Fonction cachée ... je commence à comprendre 😉 Si vous ne savez pas vraiment quelle est l'exacte SQL reçu par Oracle! 😉 Pour trouver, peut-être que vous pouvez reproduire le problème directement dans Oracle en comparant les performances de plusieurs requêtes, ne variant que le format de date utilisé (dans cette réponse)? Mais n'oubliez pas de nous tenir à jour! 🙂
    • Je suis sûr que le problème en est un de type implicites de conversion. Si vous donnez de l'Oracle une valeur d'horodatage à comparer à une colonne de type DATE, alors il va falloir faire une conversion, et il permettra d'éviter l'arrondissement. Donc, la seule option est de convertir l'ensemble de ses valeurs de DATE en TIMESTAMP. Par conséquent, il sera extrêmement lent et ne sera pas en mesure d'utiliser un indice sur la DATE de col. Vous pourriez être en mesure de contourner le problème en créant une FONCTION de BASE de l'INDICE sur la date de col, où la date est exprimée comme un timestamp.
    • Qu'entendez-vous par "sans passer par toutes les sources"? Vous avez des centaines de ces requêtes dans votre code source, que vous aimeriez corriger la modification d'un simple paramètre de configuration? 🙂
    • Je peux corriger le 10+ des endroits où ses brisées. Je suis circonspect sur le fait que quelqu'un pourrait briser la performance par la création d'une nouvelle requête, quelque part.
    • Le pilote JDBC envoie un java.sql.Horodatage de la DB. Mais je suis assez sûr que la GCM a raison: Il va provoquer un élargissement de conversion sur la colonne existante.
    • Je veux vraiment éviter de TO_DATE(). Quelle est la bonne JDBC de type à utiliser pour Oracle colonnes de DATE? java.sql.Date de tronquer les secondes, java.sql.Le temps va tronquer la partie de la date. Il n'y a pas de sql.DateTime. Dois-je utiliser oracle.sql.DATE?
    • Dans l'exemple que vous avez donné, vous ne semblez pas être intéressé dans le temps de l'élément de toute façon (juste la partie de la date), donc, avez-vous essayé d'utiliser le {d ...} raccourci pour passer à la date, au lieu d'un timestamp {ts ...} ?
    • Ou si vous êtes intéressé dans le temps de l'élément, peut-être essayer d'utiliser une expression qui ajoute {d ...} et {t ...}, ensemble, par exemple select * from ma_table t où t.ts entre ({d '2009-12-08'} + {t '12:01:02'}) et ({ d '2009-12-09'} + {t '12:01:02'} ) Aucune idée si cela va fonctionner ou comment il va effectuer, mais vaut la peine d'essayer
    • Ne fonctionne pas. Je me demande comment d'autres personnes utiliser JDBC Oracle. Tout le monde est à l'aide de Cordes et TO_DATE()???
    • Où je travaille, nous n'utilisons pas ces magiques jdbc {} expressions, mais les chaînes et to_date(). Nous aimons savoir ce que SQL est passée à Oracle plutôt que de laisser quelque chose entre Java et Oracle inventer le SQL sur le chemin à travers.

    InformationsquelleAutor CMG
  2. 4

    C'est parce que d'HORODATAGE type de données est plus précise que la DATE donc, lorsque vous fournissez des HORODATAGE valeur de paramètre dans la colonne de la DATE de la condition, l'Oracle a convertir toutes les valeurs de DATE en TIMESTAMP, pour faire une comparaison (c'est le INTERNAL_FUNCTION d'utilisation ci-dessus) et, par conséquent, l'indice doit être complète numérisée.

    • De sens; l'optimiseur est probablement trop "bête" pour comprendre que de passer le type serait plus efficace dans ce cas.
    InformationsquelleAutor FAB
  3. 3

    J'ai un problème similaire ici:

    Non-négligeable de l'exécution du plan de différence avec Oracle lors de l'utilisation de jdbc de type Timestamp ou Date

    Dans mon exemple, il provient essentiellement par le fait que lors de l'utilisation de JDBC de l'heure, un INTERNAL_FUNCTION est appliqué à la colonne de filtre, pas la lier des variables. Ainsi, l'indice ne peut pas être utilisé pour RANGE SCANS ou UNIQUE SCANS plus:

    //execute_at is of type DATE.
PreparedStatement stmt = connection.prepareStatement(
    "SELECT /*+ index(my_table my_index) */* " + 
    "FROM my_table " +
    "WHERE execute_at > ? AND execute_at < ?");

    Ces deux liaisons résultat totalement différent du comportement (pour exclure lier des variables de jeter un oeil, je me suis mise à exécution de deux dur-analyse):

    //1. with timestamps
stmt.setTimestamp(1, start);
stmt.setTimestamp(2, end);

//2. with dates
stmt.setDate(1, start);
stmt.setDate(2, end);

    1) Avec des horodatages, je reçois un INDEX FULL SCAN et donc un prédicat de filtre

    --------------------------------------------------------------
| Id  | Operation                    | Name                  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |
|*  1 |  FILTER                      |                       |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table              |
|*  3 |    INDEX FULL SCAN           | my_index              |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<:2)"
   3 - filter((INTERNAL_FUNCTION(""EXECUTE_AT"")>:1 AND 
               INTERNAL_FUNCTION(""EXECUTE_AT"")<:2))

    2) Avec les dates, j'ai beaucoup mieux INDEX RANGE SCAN et un accès prédicat

    --------------------------------------------------------------
| Id  | Operation                    | Name                  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |
|*  1 |  FILTER                      |                       |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table              |
|*  3 |    INDEX RANGE SCAN          | my_index              |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<:2)"
   3 - access(""EXECUTE_AT"">:1 AND ""EXECUTE_AT""<:2)

    La résolution de ce problème à l'intérieur de tiers Api

    Pour l'enregistrement, ce problème peut également être résolu au sein de tiers Api par exemple, en mode veille prolongée:

    Ou dans jOOQ:

    InformationsquelleAutor Lukas Eder
  4. 2

    J'ai eu ce problème sur un projet il y a un moment et en définissant la propriété de connexion oracle.jdbc.V8Compatible=true résolu le problème.

    Dougman lien vous indique comment le configurer:

    Vous définissez la propriété de connexion par
    ajouter à la java.util.Propriétés
    objet passé en
    DriverManager.getConnection ou à
    OracleDataSource.setConnectionProperties.
    Vous définissez le système de la propriété par
    y compris une option-D en java
    ligne de commande.

    java -Doracle.jdbc.V8Compatible="true"
    MyApp

    Note pour 11g et cette propriété est apparemment pas utilisé.

    De http://forums.oracle.com/forums/thread.jspa?messageID=1659839 :

    Une remarque pour ceux qui sont
    à l'aide de la 11gR1 (et sur) JDBC mince
    pilote: le V8Compatible connexion
    la propriété n'existent plus, donc vous ne pouvez pas
    appuyer sur pour envoyer votre
    java.sql.Horodatage comme un SQLDATE. Ce
    vous pouvez cependant appeler:

    setObject(i, aTimestamp, java.sql.Types.DATE) sends data as SQLDATE
setObject(i, aDate) sends data as SQLDATE
setDate(i, aDate) sends data as SQLDATE
setDATE(i, aDATE) (non standard) sends data as SQLDATE

setObject(i, aTimestamp) sends data as SQLTIMESTAMP
setTimestamp(i, aTimestamp) sends data as SQLTIMESTAMP
setObject(i, aTimestamp) sends data as SQLTIMESTAMP
setTIMESTAMP(i, aTIMESTAMP) (non standard) sends data as SQLTIMESTAMP
    InformationsquelleAutor Angus