Home -> Mailing Lists -> Oracle-L -> Rare huge surge in TEMP space usage

Rare huge surge in TEMP space usage

From: Frank Gordon <frankagordon_at_gmail.com>
Date: Thu, 6 Dec 2018 10:05:16 +0000
Message-ID: <CAA0QMtk=fk4FHnzXM9fx+m1TCveX3=BNhTyZ8xDbki+NKoUeDg_at_mail.gmail.com> 





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


Received on Thu Dec 06 2018 - 11:05:16 CET












Original text of this message