Re: Table access slow

From: Felipe Mart?nez <homer_at_vodafone.es>
Date: 10 Feb 2004 10:18:32 -0800
Message-ID: <c0a27e19.0402101018.62e04973@posting.google.com>

As I said in my former post, I analyzed indexes and explained execution plan:

     Indexes analysis does not seem to be strange compared with similar indexes (actas is the problem table and afiliados is another table with similar in size):

INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS

----------------- -------------- ------ ----------- -------------
XPKCCS_AFILIADOS  CCS_AFILIADOS       2         880         53995
XINCCS_AFILIADOS1 CCS_AFILIADOS       2         957          5120
XINCCS_AFILIADOS2 CCS_AFILIADOS       2         932          5290
XINCCS_AFILIADOS3 CCS_AFILIADOS       2         988             5
XINCCS_AFILIADOS4 CCS_AFILIADOS       2        1122            10
XINCCS_AFILIADOS5 CCS_AFILIADOS       2        1009            17
XINCCS_AFILIADOS6 CCS_AFILIADOS       2         841          6342
XINCCS_AFILIADOS7 CCS_AFILIADOS       2        1036          7735
XPKCCS_ACTAS      CCS_ACTAS           2         315         25327
XINCCS_ACTAS1     CCS_ACTAS           2         389         13764
XINCCS_ACTAS2     CCS_ACTAS           2         472         14433
XINCCS_ACTAS3     CCS_ACTAS           2         242             8


      Regarding explain plan, it seems that problematic querys are

those with ccs_actas as main table and with a big set of rows as result, because I have been executing successfully some querys with joins on ccs_actas and small row sets. However, I have serious doubts about this, because it is hard to guess which querys work and wich don't. I am quite confused now.

Here is explain plan:

Operation                      Object
------------------------------ ------------------------------

SELECT STATEMENT ()
 SORT (GROUP BY)
  HASH JOIN ()

   TABLE ACCESS (BY ROWID)     CCS_COMARCA
    INDEX (RANGE SCAN)         XPKCCS_COMARCA
   NESTED LOOPS (OUTER)
    VIEW ()                    RESUMEN_ACTAS
     SORT (GROUP BY)
      NESTED LOOPS (OUTER)
       NESTED LOOPS (OUTER)
        NESTED LOOPS (OUTER)
         HASH JOIN (OUTER)
          TABLE ACCESS (FULL)  CCS_ACTAS
          VIEW ()              CCS_ELECT
           SORT (UNIQUE)
            UNION-ALL ()
             TABLE ACCESS (FUL CCS_ELECTCOMITES
             TABLE ACCESS (FUL CCS_ELECTDELEGADOS
             TABLE ACCESS (FUL CCS_ELECTFUNCIONPUBLICA
         VIEW ()               CCS_CAND_FP
          SORT (GROUP BY)
           TABLE ACCESS (FULL) CCS_CANDFUNCIONPUBLICA
        VIEW ()                CCS_CAND_C
         SORT (GROUP BY)
          TABLE ACCESS (FULL)  CCS_CANDCOMITES
       VIEW ()                 CCS_CAND_D
        SORT (GROUP BY)
         TABLE ACCESS (FULL)   CCS_CANDDELEGADOS
    TABLE ACCESS (BY ROWID)    CCS_FEDERACION
     INDEX (UNIQUE SCAN)       XPKCCS_FEDERACION

Hope this help somebody to solve this problem. Felipe Received on Tue Feb 10 2004 - 12:18:32 CST