Re: Table access slow

Felipe Mart?nez wrote:

> 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

? No cost? Are you sure you have gathered statistics, as mentioned earlier?

-- 

Regards,
Frank van Bortel