Re: view tuning with a UNION ALL

It does look excessive for the 2 rows processed. There look to be lots of full table scans going on, which may or may not be an issue. If the tables concerned are small then this might not be an issue - the names ..._LOOKUP and ...._STAT suggest they might be but you need to check this out. The CAFE_CUST name suggests a larger table. Could you post the SQL for the view and for the query and row counts for the tables ?

Andy

"MT" <mr_amorica_at_hotmail.com> wrote in message news:fb4bc755.0211251742.77be1394_at_posting.google.com...
> I'm fairly new to query tuning but have been lurking on this group to
> find out as much as I possibly can to help me with this problem (and
> others). if anyone has ideas as to where I could concentrate my
> tuning on I would greatly appreciate it. here is the explain plan and
> auto trace results:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (GROUP BY)
> 2 1 FILTER
> 3 2 NESTED LOOPS
> 4 3 NESTED LOOPS
> 5 4 VIEW OF 'CAFE_SUMMARIZED_ALL'
> 6 5 UNION-ALL
> 7 6 FILTER
> 8 7 NESTED LOOPS
> 9 8 NESTED LOOPS
> 10 9 NESTED LOOPS
> 11 10 TABLE ACCESS (FULL) OF 'ORG_DIV_LOOKUP'
> 12 10 TABLE ACCESS (BY INDEX ROWID) OF
'CAFE_ACCT_ALL'
> 13 12 INDEX (RANGE SCAN) OF 'CAFE_ACCT_ALL_I2'
(NON-UNIQUE)
> 14 9 INDEX (UNIQUE SCAN) OF
'RSRC_MGR_ID_PK'(UNIQUE)
> 15 8 TABLE ACCESS (BY INDEX ROWID) OF 'CFS_COPY'
> 16 15 INDEX (RANGE SCAN) OF 'ACCT_ID_IDX'
(NON-UNIQUE)
> 17 7 TABLE ACCESS (FULL) OF 'CAFE_CUST'
> 18 7 TABLE ACCESS (FULL) OF 'LDG_PRD_STAT'
> 19 6 FILTER
> 20 19 NESTED LOOPS
> 21 20 NESTED LOOPS
> 22 21 TABLE ACCESS (FULL) OF 'LDG_PRD_STAT'
> 23 21 TABLE ACCESS (FULL) OF 'ORG_DIV_LOOKUP'
> 24 20 TABLE ACCESS (BY INDEX ROWID) OF
'CAFE_ACCT_ALL'
> 25 24 INDEX (RANGE SCAN) OF
'CAFE_ACCT_ALL_I2'(NON-UNIQUE)
> 26 19 TABLE ACCESS (FULL) OF 'CAFE_CUST'
> 27 4 TABLE ACCESS (BY INDEX ROWID) OF 'WBS_DTL'
> 28 27 AND-EQUAL
> 29 28 INDEX (RANGE SCAN) OF 'WBS_DTL_I2' (NON-UNIQUE)
> 30 28 INDEX (RANGE SCAN) OF 'WBS_DTL_I1' (NON-UNIQUE)
> 31 3 TABLE ACCESS (BY INDEX ROWID) OF 'WBS_ENTITY'
> 32 31 INDEX (RANGE SCAN) OF 'WBS_ENTITY_I1' (NON-UNIQUE)
> 33 2 TABLE ACCESS (FULL) OF 'LDG_PRD_STAT'
> 34 2 TABLE ACCESS (FULL) OF 'LDG_PRD_STAT'
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 44 db block gets
> 104621 consistent gets
> 0 physical reads
> 0 redo size
> 707 bytes sent via SQL*Net to client
> 1343 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 2 rows processed
>
>
> So, there is something that is causing my consistent gets to be high.
> when I run a query to return the same results--using a slightly
> different data model that eliminates the need for the UNION (but can't
> use this model for other reasons)-- it runs in about 170ms and the
> consistent gets is at about 4000.
>
> thanks in advance
Received on Wed Nov 27 2002 - 08:40:45 CST