Re: Rare huge surge in TEMP space usage

Oh yes, I've seen plenty of those. That is a phenomenon known as "lousy query".  If it cannot be tuned the other way, I would break it into materialized views and use bitmap indexes on the MV's.

Regards

On 12/6/18 5:05 AM, Frank Gordon wrote:
> Hello,
>
> Oracle 12.1.0.2.0, Windows.
>
> A query that ran properly for months, for a single day surged in it's
> usage of TEMP space, we increased the TEMP space 5x and had to wait
> for more disk to be allocated. During the waiting period the query
> went back to normal.
> The query is of the form
>
> WITH t AS (select ... from tables WHERE ... group by everything),
> u AS (select ... from T, table WHERE ..)
> SELECT ... FROM tables, T, U
>
> The "group by everything" is suspicious; as is the usage of the same
> tables over and over again within the subquery factoring clauses.
> Materialized views and subquery factoring also suspicious. HASH JOINS
> also suspicious
> In the plan a CARTESIAN JOIN is suspicious also.
> Started looking at historical stats and 10046/10053 traces.
>
> Anyone ever seen anything like this before?
>
> Thanks,
> Frank
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l