Re: Performance problem: anything else I can do to make this faster?

From: Steve C <sgcjr_at_yahoo.com>
Date: 3 Mar 2005 09:45:53 -0800
Message-ID: <1109871953.020529.138170@o13g2000cwo.googlegroups.com>

I'm finally getting the CBO to use indexes (on the count query, at least) using:

alter session set optimizer_index_cost_adj = 50; alter session set optimizer_index_caching = 90;

My query times aren't changed much though, for the count query it's 2 seconds, for the record page query it's 5.

One thing: I haven't put primary or foreign keys on my tables. As a rule we don't rely on the database to do constraint checking. Would this figure into the optimizer's decisions around join strategies, etc?

Plans/statistics follow:

Count query:

Elapsed: 00:00:02.04

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=221 Card=1 Bytes=22) 1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=221 Card=696739 Bytes=15328258)
   3    2       INDEX (FAST FULL SCAN) OF 'E_ID_DEP_TYPE_IDX'
           (UNIQUE) (Cost=219 Card=696739 Bytes=9754346)

   4    2       INDEX (UNIQUE SCAN) OF 'EC_EMPID_CATID_IDX' (UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
    1395756  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

And for the record/page query:

Elapsed: 00:00:05.05

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64815 Card=10 Bytes=
          1411593214)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=64815 Card=696739 Bytes=1411593214)
   3    2       SORT (ORDER BY STOPKEY) (Cost=64815 Card=696739 Bytes=
          75944551)

   4    3         NESTED LOOPS (Cost=1048 Card=696739 Bytes=75944551)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEE' (Cost=104
          6 Card=696739 Bytes=70370639)

   6    4           INDEX (UNIQUE SCAN) OF 'EC_EMPID_CATID_IDX'

(UNIQUE) Statistics

          0  recursive calls
          0  db block gets
    1404259  consistent gets
        150  physical reads
          0  redo size
       2109  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

-Steve Received on Thu Mar 03 2005 - 11:45:53 CST