Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> view tuning with a UNION ALL
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
12 10 TABLE ACCESS (BY INDEX ROWID) OF
13 12 INDEX (RANGE SCAN) OF
14 9 INDEX (UNIQUE SCAN) OF
15 8 TABLE ACCESS (BY INDEX ROWID) OF
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
25 24 INDEX (RANGE SCAN) OF
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 Mon Nov 25 2002 - 19:42:25 CST