Re: group by clause filling up temp space

ewong wrote:
> Here is the explain plan. I've tried 0=1 it doesn't help. And yeah
> this is the only query that fills up temp. Thanks.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3162293791 Card=1847
> 2014 Bytes=664992504)
>
> 1 0 SORT (GROUP BY) (Cost=3162293791 Card=18472014 Bytes=66499
> 2504)
>
> 2 1 HASH JOIN (Cost=2480 Card=193301902913 Bytes=69588685048
> 68)
>
> 3 2 TABLE ACCESS (FULL) OF 'TABLEA' (Cost=727 Card=2
> 48875 Bytes=7964000)
>
> 4 2 TABLE ACCESS (FULL) OF 'TABLEB' (Cost=644
> Card=77
> 9648 Bytes=3118592)

You should seriously consider tracing the session executing this query, as that is likely the best avenue you have to discover what exactly is happening. Also, you state the tables are analyzed, yet you make no mention of how current the statistics are. I would suggest you use dbms_stats to compute the statistics rather than 'analyze table'; you may also need histograms for table b as it may have a skewed distribution with respect to the status value. You've already mentioned the SID column is indexed for both tables, however you're not using them according to the plan generated; this may be due to stale/incomplete statistics. Again I suggest using dbms_Stats.gather_Table_stats() to calculate your statistics, as 'analyze table', an inferior tool, is only provided for backward compatibility.

What is the value for db_file_multiblock_read_count? If this is too large it will favor full table scans over index scans. Another parameter you need to examine is optimizer_index_cost_adj; by default it's set to 100, equating an index scan to a full table scan. You might want to adjust this to a lower value (I set it to 15 in the instances I administer).

Any number of reasons could be causing this problem. I've provided several possible areas for adjustment; take each in turn and test how your query is affected. My guess is one of the above mentioned adjustments will improve your situation.

David Fitzjarrell Received on Tue Aug 30 2005 - 15:17:36 CDT