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

sconover_at_gmail.com wrote:
> Yep, I gathered stats on the whole schema using
> dbms_stats.gather_table_stats.
>
> Does it make any sense that even just using the RBO a join of a table
> with 1m rows with a table with 2m rows would take this long? This
> doesn't seem like a big deal in terms of what Oracle can handle. Those
> 1.4m logical reads are what bother me...
>
> Are there any obvious db environment / setup / config values that could
> be off that would cause my problem?
>
> -Steve
>

Just to look at your problem from a different angle:

Why do you insist on using that darn indexes? Those 1.4M consistent gets scare me like hell, especially when the CBO came up with a slower but far more efficient plan in the first place:

 >Elapsed: 00:00:13.08
 >
 >Execution Plan
 >----------------------------------------------------------
 >   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1566 Card=1 Bytes=22
 >          )
 >
 >   1    0   SORT (AGGREGATE)
 >   2    1     HASH JOIN (Cost=1566 Card=696739 Bytes=15328258)
 >   3    2       INDEX (FAST FULL SCAN) OF 'E_DEP_TYPE_IDX'
 >           (UNIQUE) (Cost=219 Card=696739 Bytes=9754346)
 >
 >   4    2       TABLE ACCESS (FULL) OF 'EMPLOYEE_CATEGORY_LINK'
 >(Cost=243 Ca
 >          rd=1345799 Bytes=10766392)
 >
 >
 >Statistics
 >----------------------------------------------------------
 >          0  recursive calls
 >          0  db block gets
 >       4794  consistent gets
 >       7322  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

Now I'd go back to that plan and do a 10046 trace at level 8 and look at the waits.

Another thing:

Is the employee id in both tables as not null defined? And how many rows do you get for the category_id ?

Holger Received on Fri Mar 04 2005 - 02:17:46 CST