Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> view tuning with a UNION ALL

view tuning with a UNION ALL

From: MT <mr_amorica_at_hotmail.com>
Date: 25 Nov 2002 17:42:25 -0800
Message-ID: <fb4bc755.0211251742.77be1394@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 Mon Nov 25 2002 - 19:42:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US