This discussion is archived
6 Replies Latest reply: Oct 16, 2013 4:08 AM by user12031742 RSS

Query is not working properly when statistics are computed (XE 11g Windows 7 32 bits)

user12031742 Newbie
Currently Being Moderated

Hi,

We have an application with a Oracle XE 11.2 database on a Windows 7 32 bits plateform.

 

We have a query wich access 4 tables end use a user defined package function.

 

When the statistics are computed the query is returning no rows, (with the data in the tables it should return 349 rows).

When the statistics are deleted (with ANALYZE TABLE xxx DELETE STATISTICS) the query is working fine.

I tried different statistics calculation methods, and sometimes the query is working, sometimes it is not!

 

I don't understand how the statistics caluclation method can change the query results.

 

Has anybody already encountered such an issue?

 

Regards,

Yves

  • 1. Re: Query is not working properly when statistics are computed (XE 11g Windows 7 32 bits)
    clcarter Expert
    Currently Being Moderated

    It Depends.

     

    The actual table data, cardinality, data distribution, update statistics and the type of update stats run, and many factors affect the optimizer and path choices made by the engine for a query.

     

    As a general rule the optimizer makes better selections with more recent table statistics. The engine also keeps statistics on table and index scans and other operations. However, comma, a full (e.g. 100%) update stats for a large table is a fairly resource intensive operation. The automatic stats updates that occur during the maintenance window may, or may not, include a table in an update stats run based on how long its been since a stats update occurred, and how much change activity (insert/delete/update) as been "seen" on the table.

     

    With no detailed schema information or entity relationships, e.g. primary and foreign keys, etc. it will be difficult to offer much help for a given scenario ... "user defined package function" is a pretty broad definition, need some specifics. Like perhaps there is a function based index, or something else that might be somewhat unusual?

  • 2. Re: Query is not working properly when statistics are computed (XE 11g Windows 7 32 bits)
    user12031742 Newbie
    Currently Being Moderated

    I understand how the statistics are working.

    What i don't undestand is why the number of rows returned is different with or without statistics.

    I tried yesterday to had a hint /*+ RULE */ and with the hint, rows are correctly returned!

    Here is the query:

     

    SELECT l_activite.copaip, l_activite.nunati, l_activite.numeul, obsoff.dapaul,

                                        obsoff.lai24h / 10 * DECODE (coefat, NULL, 1, 0, 1, coefat / 200) lai24h, obsoff.etfeob, obsoff.tplaco / 10 tplaco,

                                        obsoff.tblaco / 10 tblaco, l_lactoff.nulact, obsoff.dapaul - l_lactoff.dadela + 1 dulact, l_lactoff.dadela,

                                        l_activite.dcenul,

                                        (  obsoff.tblaco

                                         / 10

                                         * (DECODE (obsoff.tblaco, 0, 0, NULL, 0, obsoff.lai24h * DECODE (coefat, NULL, 1, 0, 1, coefat / 200) / 10))

                                        ) mg,

                                        (  obsoff.tplaco

                                         / 10

                                         * (DECODE (obsoff.tplaco, 0, 0, NULL, 0, obsoff.lai24h * DECODE (coefat, NULL, 1, 0, 1, coefat / 200) / 10))

                                        ) mp,

                                        DECODE (obsoff.tblaco, 0, 0, NULL, 0, obsoff.lai24h * DECODE (coefat, NULL, 1, 0, 1, coefat / 200) / 10) lait_mg,

                                        DECODE (obsoff.tplaco, 0, 0, NULL, 0, obsoff.lai24h * DECODE (coefat, NULL, 1, 0, 1, coefat / 200) / 10) lait_mp,

                                        pck_f_valorises.f_present_mul (obsoff.etfeob,

                                                                       obsoff.nulact,

                                                                       (SELECT MAX (l_obsoff.dapaul)

                                                                          FROM l_obsoff

                                                                         WHERE TRIM (l_obsoff.copaip) = TRIM (l_activite.copaip)

                                                                           AND TRIM (l_obsoff.nunati) = TRIM (l_activite.nunati)

                                                                           AND l_obsoff.dapaul < obsoff.dapaul

                                                                           AND l_obsoff.etfeob <> 'T'),

                                                                       obsoff.dapaul,

                                                                       l_lactoff.dadela

                                                                      ) as OK

                                   FROM l_activite, l_obsoff obsoff, l_lactoff, l_passage

                                  WHERE TRIM (l_activite.copaul) = TRIM ('FR')

                                    AND TRIM (l_activite.numeul) = TRIM ('61323017') || '    0'

                                    AND TRIM (l_activite.copaip) = TRIM (obsoff.copaip)

                                    AND TRIM (l_activite.nunati) = TRIM (obsoff.nunati)

                                    AND TRIM (l_activite.copaip) = TRIM (l_lactoff.copaip)

                                    AND TRIM (l_activite.nunati) = TRIM (l_lactoff.nunati)

                                    AND TRIM (l_passage.copaul) = TRIM (l_activite.copaul)

                                    AND TRIM (l_passage.numeul) = TRIM (l_activite.numeul)

                                    AND (   (DECODE (l_activite.dacosu, TO_DATE ('01/01/0001', 'dd/MM/YY'), 'VIDE', NULL, 'VIDE', l_activite.dacosu) = 'VIDE')

                                         OR (l_activite.dacosu >= l_passage.dapaul)

                                        )

                                    AND l_passage.dapaul = obsoff.dapaul

                                    AND obsoff.nulact = l_lactoff.nulact

                                    AND obsoff.dapaul >= l_activite.dcenul

                                    AND l_passage.dapaul <= to_date('24/04/2013')

                                    AND l_passage.dapaul >= to_date('24/04/2012') - 50

                                    AND pck_f_valorises.f_present_mul (obsoff.etfeob,

                                                                       obsoff.nulact,

                                                                       (SELECT MAX (l_obsoff.dapaul)

                                                                          FROM l_obsoff

                                                                         WHERE TRIM (l_obsoff.copaip) = TRIM (l_activite.copaip)

                                                                           AND TRIM (l_obsoff.nunati) = TRIM (l_activite.nunati)

                                                                           AND l_obsoff.dapaul < obsoff.dapaul

                                                                           AND l_obsoff.etfeob <> 'T'),

                                                                       obsoff.dapaul,

                                                                       l_lactoff.dadela

                                                                      ) = 1

     

    and the function :

    FUNCTION F_PRESENT_MUL(p_etat IN CHAR,p_nulact IN NUMBER,
    p_derniere_obs_lait IN DATE, p_date_passage IN DATE,p_dadela IN DATE)

    RETURN NUMBER IS

    /******************************************************************************
       NAME:       F_PRESENT_MUL
       PURPOSE:    Retourne 1 si présent MUL 0 sinon

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        01/12/2006  Mathieu GUIDEL        1. Created this function.

       NOTES:
    ******************************************************************************/
    BEGIN

       IF p_derniere_obs_lait IS NULL THEN /* vache présente MUL */
        RETURN 1;
       END IF;

       IF p_etat='P' THEN /* vache présente MUL */
       RETURN 1;

       ELSE

        IF p_etat = 'N' THEN /* NC ou FV */
         IF (p_date_passage - p_dadela + 1) <= 7 THEN /* FV */
          IF p_nulact=1 THEN
          RETURN 0;
          ELSE /* considérée comme tarie */
           IF (p_date_passage - p_derniere_obs_lait) < 100 THEN /* derniere obs lait inférieur à 100 jour, présente MUL */
           RETURN 1;
           ELSE /* FV tarie depuis trop longtemps : non présente MUL */
           RETURN 0;
           END IF;
          END IF;
         ELSE /* NC */
         RETURN 1;
         END IF;
        ELSE
         IF p_etat = 'T' THEN /* Tarie */
          IF (p_date_passage - p_derniere_obs_lait) < 100 THEN /* derniere obs lait inférieur à 100 jour, présente MUL */
          RETURN 1;
          ELSE /* tarie depuis trop longtemps : non présente MUL */
          RETURN 0;
          END IF;
          ELSE /* etat différent de P, N, T donc etat = S */
          /*vérification du contrôle précédent non FV*/
          IF (p_derniere_obs_lait - p_dadela + 1) <= 7 THEN /* FV au contrôle précédent*/
          RETURN 0;
          ELSE
            IF (p_date_passage - p_derniere_obs_lait) < 30 THEN /* derniere obs lait ou NC inférieur à 30 jour, présente MUL */
            RETURN 1;
            ELSE /* sortie sans lait depuis trop longtemps : non présente MUL */
            RETURN 0;
            END IF;
          END IF;
         END IF;

        END IF;
       END IF;

    END F_PRESENT_MUL;

     

    Regards

  • 3. Re: Query is not working properly when statistics are computed (XE 11g Windows 7 32 bits)
    Bas de Klerk Pro
    Currently Being Moderated

    Hi Yves,

     

    have you tried re-creating the indexes on all tables involved in the query ?

    Normally statistics should not have any influence on the query results, only situations I've encountered a similar situation you are having is when something was corrupt in the database, usually an index.

    Maybe also worth checking both the execution plans of the statement and see where the differences are ( might point you in the right direction ).

     

    Regards

    Bas

  • 4. Re: Query is not working properly when statistics are computed (XE 11g Windows 7 32 bits)
    user12031742 Newbie
    Currently Being Moderated

    Hi,

    Yes I tried to recreate the indexes. The issue is still here.

     

    I have compared the execution plans of the query, and its seems that when statistics are computed, an additionnal filter subpart is executed.

     

    Regards,

    Yves

     

     


  • 5. Re: Query is not working properly when statistics are computed (XE 11g Windows 7 32 bits)
    Bas de Klerk Pro
    Currently Being Moderated

    To me this sounds like a bug. Petty you don't get support from Oracle on XE

    If you can reproduce this on a supported database I would advise to create a ticket @ Oracle

    If you can wrap everything in a export or SQL file I can try to reproduce this on different database editions.

  • 6. Re: Query is not working properly when statistics are computed (XE 11g Windows 7 32 bits)
    user12031742 Newbie
    Currently Being Moderated


    Hi,

    I have just reproduce the issue on a 11.2.0.2 EE Windows 64 bits (seems to work fine in 11.2.0.3 Linux 64 bits).

    I will create a tar.

    Thank you for the help everyone,

    Regards

    Yves

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points