Rare huge surge in TEMP space usage

[Frank Gordon]

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

[Dominic Brooks]

Queries getting a suboptimal plan and then consuming vast amounts of temp due to poorly sized workareas?

Not unusual.



Real time SQL monitoring or raw ASH data will confirm which particular lines of the bad plan were responsible.



________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Frank Gordon <***@gmail.com>
Sent: Thursday, December 6, 2018 10:05:16 AM
To: oracle-***@freelists.org
Subject: Rare huge surge in TEMP space usage

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
--
https://eur04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&amp;data=02%7C01%7C%7C7ed695b4e4ec40b5bdf508d65b628f91%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636796876245487313&amp;sdata=00MErfwE%2FjfWWzw37uKtOvxz9xa%2FaJY%2FitQDl8LcLN8%3D&amp;reserved=0

[Mladen Gogala]

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

Post by Frank Gordon
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