create table/insert into table и использование tempseg

При инсерте в таблицу (insert as select) из запроса вида:

select  t.*,sum(t.zzzz) over(partition by t.last_ddl_time ) x
from (
select r.*,sum(r.zzz) over(partition by r.created )zzzz
from (
select e.*,sum(e.zz) over(partition by e.object_id )zzz
from (
select w.*,sum(w.z) over(partition by w.object_name )zz
from (
select q.*,sum(q.namespace) over(partition by q.owner ) z
from table1 q) w) e) r) t

каждый подзапрос использует временное ТС, и занятое им место не освобождается при переходе к след. подзапросу. Использование tempseg мониторится по v$sql_workarea_active.
В случае же операции create as select после перехода к след. подзапросу временные данные от предыдущего подзапроса очищаются. Чрезмерного заполнения временного ТС нет.
Собственно, вопрос: чем обосновано такое поведение?
10.2.0.4, 11.1.0.7; table1 пусть будет

create table1 as select * from dba_objects connect by level<=10

Well, I am a bit sceptical about после перехода к след. подзапросу временные данные от предыдущего подзапроса очищаются. I can see временные данные от пред-предыдущего подзапроса очищаются. And in any case временные данные очищаются asynchronously by SMON, so there are many variables starting from explain plan to how busy SMON is.

SY.

Позволю себе для начала небольшое уточнение:

SY

I can see временные данные от пред-предыдущего подзапроса очищаются.

здесь речь именно о случае INSERT AS SELECT? Т.е. Вы проверили аналогичным образом "у себя", и данные из темпа вычищаются? Да, спасибо за небольшое уточнение, речь о пред-предыдущем запросе.

D/S

Позволю себе для начала небольшое уточнение: SY I can see временные данные от пред-предыдущего подзапроса очищаются. здесь речь именно о случае INSERT AS SELECT? Т.е. Вы проверили аналогичным образом "у себя", и данные из темпа вычищаются? Да, спасибо за небольшое уточнение, речь о пред-предыдущем запросе.

No I did not test it. Statement "I can see временные данные от пред-предыдущего подзапроса очищаются" is generic. All I was saying временные данные от предыдущего подзапроса очищаются is non possible in general - insert or ctas.

SY.

OK, тогда перефразирую вопрос: почему SMON в случае ctas стабильно "вычищает" temp ТС от видимо не нужной информации, а в случае того же запроса, но в роли insert as select - нет? SMON настолько "асинхронный", что при фиксированном размере TEMP TC база предпочитает выдать ошибку о невозможности выделения места в ТЕМР ТС, чем вычистить временное пространство?
Выполните testcase и помониторьте v$sql_workarea_active - увидите, о чем я говорю.

Механизмы наполнения таблицы данными CTAS мне представляются ближе к

insert /*+ append*/ ...

чем к простому insert as select.
Ну и планы, статистика выполнения, ожидания - их тоже неплохо было бы показать.

Конечно пробовал и с /*+ APPEND*/ и без. Ситуация аналогичная.
Не затруднит ли кого-нибудь проверить "у себя"? Понимаю, воскресенье и всё такое... Хочется убедиться, что проблема где-то не имеет место быть. Два экземпляра, на которых сам тестил, никоим образом не связаны друг с другом. 11.1.0.7 вообще просто инсталляция по умолчанию под виртуальной машиной.

D/S,

Well, I doubted any trace will give a complete picture. Just as a wild guess. Plain INSERT:

1. select is executed which allocates space for temp segments
2. data is inserted
3. up to 5 min later (I believe it is SMON wakeup schedule) temp data is cleaned up.

CTAS:

1. select is executed which allocates space for temp segments
2. SMON allocates initial extent
3. data is inserted
3. same SMON thread takes care of cleanup.

Another scenario you can check by dumping temp segment blocks could be banal ITL if oracle does not care if it is "real" or temp block and maintains same mechanism for both.

SY.

SY,

Plain INSERT, up to 5 min later: реальный запрос, выполняясь порядка 15 мин. времени, "съедает" все 8Gb темпа и вываливается с соответствующей ошибкой. При этом всё, чтобы было размещено в темп при последовательном выполнении подзапросов, согласно v$sql_workarea_active в темпе и остается. Никакой очистки не происходит.
CTAS, data is inserted: что понимается под этим? Вставка в какую-то промежуточную таблицу, создаваемую в процессе выполнения запроса? Ведь никакой вставки в создаваемую мной таблицу быть не может до окончания выполнения всего запроса вследствие его специфики.

Пытался в процессе выполнения запроса выполнять

oradebug wakeup PID

Результат нулевой. Не говорит ли это о том, что дело не в "запаздывании" очистки SMON'ом?

D/S,

OK. Can you clarify: you are running CTAS which uses temp segments for intermediate tables. CTAS is running for a relatively long time. In the interum (before CTAS commits!!!) you see temp segments for пред-предыдущего подзапроса очищаются.

SY.