Whilst the standard answer for this would be to get your DBA to extend the TEMP tablespace, I think the problem lies in your query.
Specifically, the way you’ve written your WHERE clause predicates. I suspect that the first three predicates are meant to be your join predicates, and the last four are supposed to restrict the rows from the course table that are being joined to.
However, what is happening is that the first four predicates are being calculated first (because AND takes precedence over OR) and I suspect that is causing some problems with your joins — possibly some unintended cross joining, and that may be what is unexpectedly blowing up your TEMP tablespace.
To prevent this from happening, you have two possible solutions:
1. Clarify your AND/OR logic with brackets in the correct places:
SELECT DISTINCT
t4.s_studentreference "Student ID",
t3.p_surname "Surname",
t3.p_forenames "Forenames",
t1.m_reference "Course",
t2.e_name "Enrolment Name"
FROM student t4,
person t3,
enrolment t2,
course t1
WHERE t4.s_id(+) = t3.p_id
AND t2.e_student = t3.p_id
AND t2.e_course = t1.m_id
AND (t1.m_reference LIKE 'LL563%15'
OR t1.m_reference LIKE 'LL562%15'
OR t1.m_reference LIKE 'LL563%16'
OR t1.m_reference LIKE 'LL562%16');
The above groups all the OR statements together and then ANDs them into the rest of the predicates.
2. Use ANSI join syntax and separate out the search predicates from the join predicates:
SELECT DISTINCT
t4.s_studentreference "Student ID",
t3.p_surname "Surname",
t3.p_forenames "Forenames",
t1.m_reference "Course",
t2.e_name "Enrolment Name"
FROM student t4,
RIGHT OUTER JOIN person t3 ON t4.s_id = t3.p_id
INNER JOIN enrolment t2 ON t3.p_id = t2.e_student
INNER JOIN course t1 ON t2.e_course = t1.m_id
WHERE t1.m_reference LIKE 'LL563%15'
OR t1.m_reference LIKE 'LL562%15'
OR t1.m_reference LIKE 'LL563%16'
OR t1.m_reference LIKE 'LL562%16';
Of course, the latter doesn’t preclude the use of brackets in the right place when you’re working with a mix of ANDs and ORs in the where clause…
Option 2 would be my preferred solution — the ANSI join syntax really is the way forward these days when writing SQL.
Each tablespace has one or more datafiles that it uses to store data.
The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.
To find out if the actual limit is 32gb, run the following:
select value from v$parameter where name = 'db_block_size';
Compare the result you get with the first column below, and that will indicate what your max datafile size is.
I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).
Block Sz Max Datafile Sz (Gb) Max DB Sz (Tb)
-------- -------------------- --------------
2,048 8,192 524,264
4,096 16,384 1,048,528
8,192 32,768 2,097,056
16,384 65,536 4,194,112
32,768 131,072 8,388,224
You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you’ve currrently set the max file size to (which cannot exceed the aforementioned 32gb):
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
MAXSIZE_SET is the maximum size you’ve set the datafile to. Also relevant is whether you’ve set the AUTOEXTEND option to ON (its name does what it implies).
If your datafile has a low max size or autoextend is not on you could simply run:
alter database datafile 'path_to_your_filethat_file.DBF' autoextend on maxsize unlimited;
However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:
alter tablespace system add datafile 'path_to_your_datafiles_foldername_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
Each tablespace has one or more datafiles that it uses to store data.
The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.
To find out if the actual limit is 32gb, run the following:
select value from v$parameter where name = 'db_block_size';
Compare the result you get with the first column below, and that will indicate what your max datafile size is.
I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).
Block Sz Max Datafile Sz (Gb) Max DB Sz (Tb)
-------- -------------------- --------------
2,048 8,192 524,264
4,096 16,384 1,048,528
8,192 32,768 2,097,056
16,384 65,536 4,194,112
32,768 131,072 8,388,224
You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you’ve currrently set the max file size to (which cannot exceed the aforementioned 32gb):
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
MAXSIZE_SET is the maximum size you’ve set the datafile to. Also relevant is whether you’ve set the AUTOEXTEND option to ON (its name does what it implies).
If your datafile has a low max size or autoextend is not on you could simply run:
alter database datafile 'path_to_your_filethat_file.DBF' autoextend on maxsize unlimited;
However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:
alter tablespace system add datafile 'path_to_your_datafiles_foldername_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
How to resolve/troubleshoot the error ORA-01652: unable to extend temp segment by 128 in tablespace TEMP? It seems to me that tablespace TEMP (31 GB) is already quite large.
select * from v$sort_segment;
TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS RELATIVE_FNO
TEMP 0 0 128 6 32766 4194048 6 768 32760 4193280 0 1006121 0 0 32766 4194048 32766 4194048 32764 4193792 0
select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
1 3687 22-SEP-11 3 1 ONLINE READ WRITE 34359721984 4194302 1048576000 8192 /san/oradata/prod/temp01.dbf
select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
/san/oradata/prod/temp01.dbf 1 TEMP 34359721984 4194302 ONLINE 1 YES 34359721984 4194302 32000 34357641216 4194048
Edited by: 881656 on Apr 26, 2012 2:52 PM
Статьи про Oracle
->
Администрирование
ORA-01652: unable to extend temp segment
v:1.0 24.07.2011
Петрелевич Сергей
Ошибка вида ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 говорит о том, что Oracle
больше не может расширить табличное пространство. Часто это происходит по причине кривых рук программиста, написавшего
не оптимальный запрос. Выявить такой запрос можно, например так:
select s.SQL_ID, s.sql_fulltext, u.TABLESPACE, u.EXTENTS, u.BLOCKS
from v$tempseg_usage u, v$
sql s
where s.sql_id = u.sql_idand u.extents>1;
Выполнять запрос надо во время возникновения ошибки.
Таким запросом можно периодически мониторить базу, отслеживая кривые запросы.
Если какой-то запрос начинает интенсивно использовать табличное пространство TEMP, то это это веский повод разобраться, почему он так работает
и провести оптимизацию не дожидаясь появления ошибки.
Метки:
v$представления
администрирование
Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.
Перевод отсюда http://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext
Каждое табличное пространство имеет один или больше файл данных, в которые записываются данные.
Максимальный размер файла данных зависит от размера блока данных в базе данных. Я думаю, что у вас по умолчанию установлено 32Гб на один дата файл.
Что бы определить, что текущий предел 32Гб, запустите скрипт:
select value from v$parameter where name = 'db_block_size'Сравните результат полученный в запросе и максимальный размер файла данных в таблице представленной ниже.
У меня установлена Oracle Personal Edition 11g r2 в ней по умолчанию установлен размер блока 8,192 (32Гб на один файл).
Размер Максимальный Максимальный
блока (байт) размер файла (Мб) размер БД (Мб)
-------- -------------------- --------------
2 048 8 192 524 264
4 096 16 384 1 048 528
8 192 32 768 2 097 056
16 384 65 536 4 194 112
32 768 131 072 8 388 224Вы можете запустить это запрос чтобы определить какие файлы данных у вас есть, с какими табличными пространствами они связаны и какой максимальный размер для них установлен:
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files xMAXSIZE_SET — максимальный размер файла данных. Также имеет значение включена ли опция AUTOEXTEND в значение ON (его название указывает на что влияет это свойство(Авторасширение)).Если размер файла данных слишком маленький или опция авто расширения выключена, то выполните скрипт:
alter database datafile 'path_to_your_filethat_file.DBF' autoextend on maxsize unlimited;Если размер файла данных равен 32Гб или около того, то вам нужен новый файл данных для этого табличного пространства. Вот команда по созданию его:
alter tablespace system add datafile 'path_to_your_datafiles_foldername_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;Кроме того хорошей практикой для вас является иметь отдельное табличное пространство для индексов (похоже у вас не так, так как вы не указали табличное пространство в скрипте create index и там используется значение по умолчанию, т.е. системное табличное пространство). Поэтому после создания такого табличного пространства вы должны запустить что-то вроде этого:
CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)
tablespace name_of_ts_for_indexesПосле создания табличного пространства и файла данных:
create tablespace name_of_ts_for_indexes datafile
'c:appxyzoradataorclname_of_ts_for_indexes01.dbf' autoextend on maxsize unlimited nologging;Таким образом ваши таблицы будут находиться в одном табличном пространстве, а индексы — в другом.
Добавлю еще команды
ALTER DATABASE DATAFILE <file_name> RESIZE <size>;
ALTER TABLESPACE PROBATBS ADD DATAFILE <file_name> SIZE <size> REUSE AUTOEXTEND ON NEXT <size> MAXSIZE <size>
Еще ссылки:
http://www.orahome.ru/ora-admin/102
https://iusoltsev.wordpress.com/2010/08/02/ora-01652-unable-to-extend-temp-segment/
http://www.sql.ru/forum/131782/ora-01652-unable-to-extend-temp-segment
http://delphimaster.net/view/3-58578/all
http://www.sql.ru/forum/1162644/ora-01654-unable-to-extend-index
Problem
User performs a task in Controller.
— For example, user attempts to run a report to compare subconsolidation and legal consolidation, with different closing versions, group perspective and currency codes (same currency).
User receives error message.
Symptom
The error will vary depending on what task the user is performing.
Example
— Running a report:
Standard Error
Number: 5
Source: FrangoDirect.ExcelLinkD.fetchValues#system.WebServices
Description: System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.Runtime.InteropServices.COMException(0x80040E14): ORA-01652: Unable to extend temp segment by 128 in Tablespace TEMP at Cognos.Controller.Proxy.CCRWS.ExcelLinkB_FetchValues(String sGuid, DataSet rsKeys, String sMcurr, Boolean bLocLang, String sUser, String sRep, Boolean bIsLnk, String sSortOrd, Int32 |OptBitSet)
Cause
Oracle temporary tablespace (in the above example it is called «TEMP») is full.
Environment
Controller application repository database hosted on an Oracle database server.
Diagnosing The Problem
Ask your I.T. department’s Oracle administrator (‘DBA’) to check the following:
- These scripts are based on Oracle 11G. They may need to be modified slightly for different Oracle versions.
=========================================
(1) Locate your temporary datafile and tablespace, by running the following Oracle script:
SELECT file_name, TABLESPACE_NAME FROM DBA_TEMP_FILES;
Example output:
FILE_NAME TABLESPACE_NAME
D:APPADMINISTRATORORADATACCR11GR2TEMP01.DBF TEMP
1 rows selected
In the above example:
- The temporary tablespace name is called ‘TEMP’
- there is only one ‘temp’ tablespace file (TEMP01.DBF)
- It is located on the D: drive of the Oracle database server, in the above folder location.
=========================================
(2) Check if the operating system has any free space left on the hard drive where the temporary tablespace is located.
- In the above example, check to make sure that there is some free hard drive space left on the Oracle server’s D: drive.
=========================================
(3) Check if there is any space available in temporary tablespace (segment):
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total — SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Example output:
TABLESPACE MB_TOTAL MB_USED MB_FREE
—————————————————————-
TEMP 2548 2536 12
In the above example:
- there is over 2 GB allocated for temporary operations
- However, only 12 MB is free.
Resolving The Problem
Ask your I.T. department’s Oracle DBA (administrator) to increase the size of the (full) Oracle temporary tablespace and (if necessary) free up hard drive space on the database server.
Also, check to see if the Oracle database server’s hard drives are full.
Steps:
Ask your I.T. department’s Oracle DBA to:
1. Run the above scripts (see section ‘Diagnosing the problem’) to find out the name and current status of the temporary tablespace
- In the above example, the error message clearly states that the full tablespace is called ‘TEMP’ in this case, but it may be different in your environment.
- TIP: To see how full *all* your Oracle database tables are currently, see separate IBM Technote #1346962.
2. Free up hard drive space on the Oracle database server (if necessary)
3. Increase the size of your temporary tablespace.
- For example, to increase the size to 5Gb run a script similar to this:
ALTER DATABASE TEMPFILE ‘D:APPADMINISTRATORORADATACCR11GR2TEMP01.DBF’ RESIZE 5120M;
Related Information
[{«Product»:{«code»:»SS9S6B»,»label»:»IBM Cognos Controller»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Controller»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»10.2.1;10.2.0;10.1.1;10.1;8.5.1″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]
Historical Number
1033778
Oracle documentation
We receive this error ORA-01652 while inserting data,creating index, table when we don’t have enough space in tablespace.
Solution is to add sufficient space in the tablespace identified in the error ORA-01652 There are three ways to do it
- Add datafile to the tablespace
- Resize the existing datafile in the tablespace
- auto-extent the datafile in the tablespace
This can happen with permanent tablespace and Temporary tablespace Each one is explained below with example below
How to Fix ORA-01652 in Permanent Tablespace
- we can use ALTER TABLESPACE to add datafile in tablespace like
Syntax ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile> Example ALTER TABLESPACE TEST ADD DATAFILE '/u01/oracle/TEST/oradata/test_4.dbf' SIZE 1000M With Oracle ASM ALTER TABLESPACE TEST ADD DATAFILE '+DATA ' SIZE 1000M; Alter tablespace to add multiple datafilesSQL>alter tablespace tablespace_name add datafile
<’destination 1’> size 10M autoextend on next 124M,
<’destination 2’> size 10M autoextend on next 124M,
<’destination 3’> size 10M autoextend on next 124M;
2. we can use ALTER TABLESPACE resize datafile in tablespace like
First find the location of present datafiles in the tablespace select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%&1%'; Then look at the space in the mount where datafile is present using df -k command And then resize the datafile Syntax Alter database datafile <datafile> resize <size> Example select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'EXAMPLE_DB'; /u01/oracle/testdb/example_db01.dbf 1000 Alter database datafile '/u01/oracle/testdb/example_db01.dbf' resize 2000M; With Oracle ASM diskgroups ALTER DATABASE DATAFILE '+DATA/testdb/example_db01.dbf' resize 2000M;
3. How to modify the existing Datafile auto extend characteristics in a Tablespace
you can modify datafile auto extend using alter database datafile option
Syntax
ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;
Example
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE 2400M;
How to in TEMP tablespace
If this is happening for the temporary tablespace, we can add temp-file to get it working and at the same time we can look out for the session using high temporary tablespace to clear the temp space. We can then tune that sql so that it does not consume high amount of temporary tablespace and does not cause issues to the Other oracle session in getting the temp tablespace
We can get the total space in the TEMP tablespace using the command
select sum(bytes)/1024/1024/1024 Total_space from dba_temp_files;
Free space using the command
For Non RAC SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;
For RAC select inst_id, tablespace_name, segment_file, total_blocks, used_blocks, free_blocks, max_used_blocks, max_sort_blocks from gv$sort_segment;
And then add temp file using the below command
Syntax ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size> Example ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/TEST/oradata/temp_4.dbf' SIZE 1000M; With ASM ALTER TABLESPACE TEST ADD TEMPFILE '+DATA ' SIZE 1000M;
We can check for the session using temp segment using the query
col inst_id format 999
col sid format 99999
col status format a11
col program format a20 trunc
col machine format a17 trunc
col action format a39
col module format a39
col blocks heading "TEMP BLOCKS"
SELECT b.blocks, s.inst_id, s.sid, s.status,
s.action,
s.program,
s.machine,
s.module
FROM gv$session s,
gv$sort_usage b
WHERE s.saddr = b.session_addr
and s.inst_id = b.inst_id
and b.blocks > 100000
order by b.blocks desc;
Hope you like the details on ORA-01652 error and solutions . Please do let me know the feedback on it and how do you solve this problem in your oracle database environment
Related Article
How to check tablespace in Oracle Database
What you need to know about create tablespace statement in Oracle
How to shrink the datafile in Oracle
How to move LOB segment to another tablespace
С определённого момента с грустной ошибкой ORA-01652: unable to extend temp segment перестал выполняться тяжёлый запрос, ежесуточно выполняемый в PL/SQL job’е и формирующий отчётную таблицу с начала времён за весь отчётный период
Можно в соответствиями с простейшей рекомендацией Oracle увеличить размер временного табличного пространства, но при достаточно большом размере существующего — 10 ГБ это не всегда допустимо и, кроме того, это пагубная практика просто некрасивое решение
Можно пересмотреть логику приложения и вместо еженощного полного перезаполнения вспомогательной таблицы раз в сутки дополнять собранные данные — будет использоваться временный сегмент меньшего размера, однако, потребуется модификация структуры таблиц и отчётных запросов
Ну и наконец, можно попробовать модифицировать запрос с целью уменьшения требуемого размера временного сегмента (temporary segment), точнее, с этого нужно начинать
Первоначальный запрос, потребовавший для выполнения более 10 GB временного табличного пространства
SQL> insert/*+ APPEND*/ into ALSO_PROGRAM 2 select /*+ ALL_ROWS*/ PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID from ( 3 SELECT I.PROGRAM_ID PROGRAM_ID 4 , B1.OPTION_ID OPTION_ID 5 , COUNT(B1.OPTION_ID) COUNT_OPTION_ID 6 , row_number() over (partition by PROGRAM_ID order by COUNT(B1.OPTION_ID) desc) rn 7 FROM ITEM I, OPTION O, BASKET B1, ORDER ORD1, 8 (select ORD.COMPANY_ID, B.OPTION_ID 9 from BASKET B, ORDER ORD 10 where ORD.ID = B.ORDER_ID 11 AND ORD.PAID_UP = 'Y') BORD 12 WHERE ORD1.ID = B1.ORDER_ID 13 AND ORD1.COMPANY_ID = BORD.COMPANY_ID 14 AND O.ID = BORD.OPTION_ID 15 AND ORD1.PAID_UP = 'Y' 16 AND O.ITEM_ID = I.ID 17 AND B1.BUNDLE_OPTION_ID IS NULL 18 GROUP BY I.PROGRAM_ID, B1.OPTION_ID 19 ) where rn <= 5; insert/*+ APPEND*/ into ALSO_PROGRAM * ERROR at line 1: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP Elapsed: 00:04:12.16
Ещё раз (также неудачно) выполнив запрос с подсказкой gather_plan_statistics посмотрим на статистику выполнения запроса, точнее на ту часть статистики, которая была собрана до возникновения ошибки
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------------
| 1 | LOAD AS SELECT | | 1 | | 0 |
|* 2 | VIEW | | 1 | 1250K| 0 |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 1250K| 0 |
| 4 | HASH GROUP BY | | 1 | 1250K| 0 |
|* 5 | HASH JOIN | | 1 | 1250K| 171M|
|* 6 | INDEX FAST FULL SCAN | IX_BASKET | 1 | 2909K| 2910K|
|* 7 | HASH JOIN | | 1 | 1095K| 233M|
|* 8 | VIEW | index$_join$_006 | 1 | 865K| 1569K|
|* 9 | HASH JOIN | | 1 | | 1569K|
|* 10 | HASH JOIN | | 1 | | 1569K|
|* 11 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 865K| 1569K|
| 12 | INDEX FAST FULL SCAN | IX_ACCOUNT_ID | 1 | 865K| 2597K|
| 13 | INDEX FAST FULL SCAN | IX_ORDER_DELIVERY | 1 | 865K| 2597K|
|* 14 | HASH JOIN | | 1 | 1000K| 1591K|
| 15 | INDEX FAST FULL SCAN | IX_ITEM_PROGRAM_ID | 1 | 99508 | 99553 |
|* 16 | HASH JOIN | | 1 | 1000K| 1591K|
| 17 | INDEX FAST FULL SCAN | IX_OPTION_ITEM_ID | 1 | 286K| 286K|
|* 18 | HASH JOIN | | 1 | 1000K| 1591K|
|* 19 | VIEW | index$_join$_009 | 1 | 865K| 1569K|
|* 20 | HASH JOIN | | 1 | | 1569K|
|* 21 | HASH JOIN | | 1 | | 1569K|
|* 22 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 865K| 1569K|
| 23 | INDEX FAST FULL SCAN| IX_ACCOUNT_ID | 1 | 865K| 2597K|
| 24 | INDEX FAST FULL SCAN | IX_ORDER_DELIVERY | 1 | 865K| 2597K|
| 25 | INDEX FAST FULL SCAN | IX_BASKET_ORDER_OPTION | 1 | 3002K| 2678K|
-------------------------------------------------------------------------------------------------
Судя по собранной статистике выполнения запроса — ожидаемое E-rows=1250K и полученное количество строк A-rows=171M на шаге 5 (где и произошла ошибка) отличаются более, чем в 100 раз (!) — т.е. план выполнения построен на неточных или неполных данных статистики и, видимо, мог бы быть улучшен оптимизатором Oracle автоматически, но сейчас не об этом, попробуем уменьшить потребность во временном дисковом пространстве для hash операций модифицируя текст запроса:
Вариант №1 Выполнить запрос в два этапа с использованием промежуточной временной таблицы (global temporary table GTT)
SQL> create global temporary table ALSO_PROGRAM2 ON COMMIT PRESERVE ROWS as select /*+ ALL_ROWS gather_plan_statistics*/ ORD.COMPANY_ID, B.OPTION_ID from BASKET B, ORDER ORD where ORD.ID = B.ORDER_ID AND ORD.PAID_UP = 'Y'; 2 3 4 5 6 Table created. Elapsed: 00:00:44.12 ---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | ---------------------------------------------------------------------------------------------------------------------------------------------- | 1 | LOAD AS SELECT | | 1 | | | | | | 1 |00:00:43.06 | |* 2 | HASH JOIN | | 1 | 1026K| 27M| 24M| 27940 (1)| 00:05:36 | 1856K|00:00:38.04 | |* 3 | VIEW | index$_join$_002 | 1 | 887K| 14M| | 20514 (1)| 00:04:07 | 1616K|00:00:20.56 | |* 4 | HASH JOIN | | 1 | | | | | | 1616K|00:00:17.32 | |* 5 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 887K| 14M| | 26768 (1)| 00:05:22 | 1616K|00:00:01.62 | | 6 | INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX | 1 | 887K| 14M| | 11865 (1)| 00:02:23 | 2664K|00:00:02.66 | | 7 | INDEX FAST FULL SCAN | IX_ BASKET_ORDER_OPTION | 1 | 3078K| 32M| | 2835 (1)| 00:00:35 | 3079K|00:00:03.08 | ---------------------------------------------------------------------------------------------------------------------------------------------- SQL> select segtype, blocks from gv$tempseg_usage; SEGTYPE BLOCKS --------- ---------- DATA 3968
последний запрос даёт размер сегмента временной таблицы = 3968*8k ~ 32MB
Пробуем выполнить запрос с использованием созданной временной таблицы
SQL> insert/*+ APPEND */ into ALSO_PROGRAM 2 select /*+ ALL_ROWS*/ PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID from ( 3 SELECT I.PROGRAM_ID PROGRAM_ID 4 , B1.OPTION_ID OPTION_ID , COUNT(B1.OPTION_ID) COUNT_OPTION_ID 5 6 , row_number() over (partition by PROGRAM_ID order by COUNT(B1.OPTION_ID) desc) rn 7 FROM ITEM I, OPTION O, BASKET B1, ORDER ORD1, ALSO_PROGRAM2 BORD 8 WHERE ORD1.ID = B1.ORDER_ID 9 AND ORD1.COMPANY_ID = BORD.COMPANY_ID 10 AND O.ID = BORD.OPTION_ID 11 AND ORD1.PAID_UP = 'Y' 12 AND O.ITEM_ID = I.ID 13 AND B1.BUNDLE_OPTION_ID IS NULL 14 GROUP BY I.PROGRAM_ID, B1.OPTION_ID 15 ) where rn <= 5; Elapsed: 00:36:08.40 -------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | -------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | LOAD AS SELECT | | 1 | | | | | | 1 |00:01:58.68 | |* 2 | VIEW | | 1 | 2605K| 129M| | 142K (1)| 00:28:29 | 84802 |00:01:58.25 | |* 3 | WINDOW SORT PUSHED RANK | | 1 | 2605K| 188M| 442M| 142K (1)| 00:28:29 | 109K|00:01:58.04 | | 4 | HASH GROUP BY | | 1 | 2605K| 188M| 442M| 142K (1)| 00:28:29 | 4798K|00:01:46.74 | |* 5 | HASH JOIN | | 1 | 2605K| 188M| 71M| 49681 (1)| 00:09:57 | 303M|00:26:01.79 | |* 6 | INDEX FAST FULL SCAN | IX_BASKET | 1 | 2982K| 36M| | 4738 (1)| 00:00:57 | 2983K|00:00:05.98 | |* 7 | HASH JOIN | | 1 | 2285K| 137M| 24M| 33282 (1)| 00:06:40 | 258M|00:04:47.41 | |* 8 | VIEW | index$_join$_006 | 1 | 887K| 14M| | 20514 (1)| 00:04:07 | 1616K|00:00:20.87 | |* 9 | HASH JOIN | | 1 | | | | | | 1616K|00:00:17.64 | |* 10 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 887K| 14M| | 26768 (1)| 00:05:22 | 1616K|00:00:01.62 | | 11 | INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX | 1 | 887K| 14M| | 11865 (1)| 00:02:23 | 2664K|00:00:02.66 | |* 12 | HASH JOIN | | 1 | 2087K| 91M| 9264K| 5808 (1)| 00:01:10 | 1856K|00:00:12.37 | |* 13 | HASH JOIN | | 1 | 296K| 5785K| 2200K| 720 (1)| 00:00:09 | 296K|00:00:01.89 | | 14 | INDEX FAST FULL SCAN| IX_ITEM_PROGRAM_ID | 1 | 102K| 998K| | 66 (0)| 00:00:01 | 102K|00:00:00.10 | | 15 | INDEX FAST FULL SCAN| IX_OPTION_ITEM_ID | 1 | 296K| 2892K| | 235 (1)| 00:00:03 | 296K|00:00:00.30 | | 16 | TABLE ACCESS FULL | ALSO_PROGRAM2 | 1 | 2087K| 51M| | 873 (2)| 00:00:11 | 1856K|00:00:01.86 | -------------------------------------------------------------------------------------------------------------------------------------------------
Время выполнения немаленькое, результат — успешный. Однако размер использованного временного сегмента более 5 GB (при лимите в 10), не радует (:
SQL> select OPERATION_ID, OPERATION_TYPE, ACTIVE_TIME, MAX_TEMPSEG_SIZE, LAST_TEMPSEG_SIZE from gv$sql_workarea 2 where SQL_ID = 'ddxgfk02uja5y' and LAST_TEMPSEG_SIZE > 0 3 / OPERATION_ID OPERATION_TYPE ACTIVE_TIME MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE ------------ --------------------- ----------- ---------------- ----------------- 5 HASH-JOIN 2117382492 5465178112 5465178112
Вариант №2 Можно попробовать использовать SQL конструкцию WITH (subquery factoring) c подсказкой MATERIALIZE для того, чтобы Oracle во время выполнения запроса создал и использовал внутреннюю временную таблицу (т.н. материализация подзапроса) вместо предварительного создания global temporary table
SQL> insert/*+ APPEND */ into ALSO_PROGRAM 2 with bord as 3 (select /*+ MATERIALIZE */ ORD.COMPANY_ID, B.OPTION_ID 4 from BASKET B, ORDER ORD 5 where ORD.ID = B.ORDER_ID 6 AND ORD.PAID_UP = 'Y') 7 select /*+ ALL_ROWS gather_plan_statistics*/ PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID from ( 8 SELECT I.PROGRAM_ID PROGRAM_ID 9 , B1.OPTION_ID OPTION_ID 10 , COUNT(B1.OPTION_ID) COUNT_OPTION_ID 11 , row_number() over (partition by PROGRAM_ID order by COUNT(B1.OPTION_ID) desc) rn 12 FROM ITEM I, OPTION O, BASKET B1, ORDER ORD1, 13 BORD 14 WHERE ORD1.ID = B1.ORDER_ID 15 AND ORD1.COMPANY_ID = BORD.COMPANY_ID 16 AND O.ID = BORD.OPTION_ID 17 AND ORD1.PAID_UP = 'Y' 18 AND O.ITEM_ID = I.ID 19 AND B1.BUNDLE_OPTION_ID IS NULL 20 GROUP BY I.PROGRAM_ID, B1.OPTION_ID 21 ) where rn <= 1000; insert/*+ APPEND gather_plan_statistics*/ into ALSO_PROGRAM * ERROR at line 1: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP Elapsed: 00:09:47.80
Несмотря на то, что подсказка сработала (судя по появившейся в плане операции TEMP TABLE TRANSFORMATION) и план создания этой внутренней таблицы совпадает с планом создания global temporary table, общий план запроса изменился и опять потребовалось > 10 GB дискового пространства для очередного hash join (операция c id=14 плана выполнения успела выбрать «только» 125 млн строк):
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Temp | Cost (%CPU)| E-Time | A-Rows | -------------------------------------------------------------------------------------------------------------------------------- | 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | 0 | | 2 | LOAD AS SELECT | | 1 | | | | | 1 | |* 3 | HASH JOIN | | 1 | 1110K| 26M| 31255 (1)| 00:06:16 | 2021K| |* 4 | VIEW | index$_join$_003 | 1 | 963K| | 23128 (1)| 00:04:38 | 1764K| |* 5 | HASH JOIN | | 1 | | | | | 1764K| |* 6 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 963K| | 32511 (1)| 00:06:31 | 1764K| | 7 | INDEX FAST FULL SCAN | ORDER_ID_LID_COMID_IDX | 1 | 963K| | 13636 (1)| 00:02:44 | 2891K| | 8 | INDEX FAST FULL SCAN | IX_BASKET_ORDER_OPTION | 1 | 3330K| | 3155 (1)| 00:00:38 | 3332K| | 9 | LOAD AS SELECT | | 1 | | | | | 0 | |* 10 | VIEW | | 1 | 1095K| | 74435 (1)| 00:14:54 | 0 | |* 11 | WINDOW SORT PUSHED RANK | | 1 | 1095K| 151M| 74435 (1)| 00:14:54 | 0 | | 12 | HASH GROUP BY | | 1 | 1095K| 151M| 74435 (1)| 00:14:54 | 0 | |* 13 | HASH JOIN | | 1 | 1095K| 55M| 42074 (1)| 00:08:25 | 0 | |* 14 | HASH JOIN | | 1 | 963K| 26M| 30155 (1)| 00:06:02 | 125M| |* 15 | VIEW | index$_join$_008 | 1 | 963K| | 23128 (1)| 00:04:38 | 1764K| |* 16 | HASH JOIN | | 1 | | | | | 1764K| |* 17 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 963K| | 32511 (1)| 00:06:31 | 1764K| | 18 | INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX | 1 | 963K| | 13636 (1)| 00:02:44 | 2891K| |* 19 | HASH JOIN | | 1 | 1110K| 9M| 3436 (1)| 00:00:42 | 389K| |* 20 | HASH JOIN | | 1 | 322K| 2376K| 798 (1)| 00:00:10 | 323K| | 21 | INDEX FAST FULL SCAN| IX_ITEM_PROGRAM_ID | 1 | 110K| | 72 (2)| 00:00:01 | 110K| | 22 | INDEX FAST FULL SCAN| IX_OPTION_ITEM_ID | 1 | 322K| | 271 (1)| 00:00:04 | 323K| | 23 | VIEW | | 1 | 1110K| | 932 (1)| 00:00:12 | 389K| | 24 | TABLE ACCESS FULL | SYS_TEMP_AFD9FFA07_D2678009 | 1 | 1110K| | 932 (1)| 00:00:12 | 389K| |* 25 | INDEX FAST FULL SCAN | IX_BASKET | 0 | 3227K| | 5343 (1)| 00:01:05 | 0 | -------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- ... 13 - access("B1"."ORDER_ID"="ORD1"."ID") 14 - access("ORD1"."COMPANY_ID"="BORD"."COMPANY_ID") 15 - filter("ORD1"."PAID_UP"=:SYS_B_1) 16 - access(ROWID=ROWID) 17 - access("ORD1"."PAID_UP"=:SYS_B_1) 19 - access("O"."ID"="BORD"."OPTION_ID") 20 - access("O"."ITEM_ID"="I"."ID") 25 - filter("B1"."BUNDLE_OPTION_ID" IS NULL)
Судя по секции Predicate Information операция 14 неудачно пыталась произвести hash join больших таблиц наборов данных запроса: таблицы ORDER ORD1 с фильтром ORD1.PAID_UP = ‘Y’ (операции 15-18) и результата объединения таблиц ITEM, OPTION и «материализованного» подзапроса BORD (операции 19-24) по условию («ORD1″.»COMPANY_ID»=»BORD».»COMPANY_ID»)
Для «облегчения» проблемного hash join‘а нужно либо уменьшить количество строк в наборе данных (что будем считать невозможным), либо попробовать избавиться от тяжелой операции, изменив порядок соединения таблиц
О сравнении использования global temporary table (GTT) и внутренней материализации подзапроса, с использованием subquery factoring (the WITH-subquery):
если CBO решает материализовать WITH-подзапрос, вы получаете временный набор данных (temporary resultset), который нельзя проиндексировать. По мере роста количества данных можно столкнуться с проблемами производительности. GTTs могут быть легко проиндексированы, таким образом поддерживая работу с большими промежуточными наборами данных
Вариант №3 Пробуем выделить ещё один запрос в раздел FROM SQL запроса и использовать беcсмысленную (fake) операцию group by для предотвращения объединения (merge) основного запроса со встроенными во FROM обзорами — inline views, строки 11-16 и 17-23 модифицированного запроса:
SQL> insert /*+ APPEND gather_plan_statistics*/
2 into ALSO_PROGRAM
3 select
4 PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID
5 from (SELECT /*+ ALL_ROWS */ I.PROGRAM_ID PROGRAM_ID,
6 BORD1.OPTION_ID OPTION_ID,
7 COUNT(BORD1.OPTION_ID) COUNT_OPTION_ID,
8 row_number() over(partition by PROGRAM_ID order by COUNT(BORD1.OPTION_ID) desc) rn
9 FROM ITEM I,
10 OPTION O,
11 (select ORD.COMPANY_ID, B.OPTION_ID, B.ROWID
12 from BASKET B, ORDER ORD
13 where ORD.ID = B.ORDER_ID
14 AND ORD.PAID_UP = 'Y'
15 group by ORD.COMPANY_ID, B.OPTION_ID, B.ROWID
16 ) BORD,
17 (select ORD1.COMPANY_ID, B1.OPTION_ID, B1.ROWID
18 from BASKET B1, ORDER ORD1
19 where ORD1.ID = B1.ORDER_ID
20 AND ORD1.PAID_UP = 'Y'
21 AND B1.BUNDLE_OPTION_ID IS NULL
22 group by ORD1.COMPANY_ID, B1.OPTION_ID, B1.ROWID
23 ) BORD1
24 WHERE BORD.COMPANY_ID = BORD1.COMPANY_ID
25 AND BORD.OPTION_ID = O.ID
26 AND O.ITEM_ID = I.ID
27 GROUP BY I.PROGRAM_ID, BORD1.OPTION_ID)
28 where rn <= 1000;
3388544 rows created.
Elapsed: 00:03:11.56
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('2xa7s8tb8457b','0','ALL ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | LOAD AS SELECT | | 1 | | | | | | 1 |00:03:07.02 |
|* 2 | VIEW | | 1 | 1376K| 68M| | 127K (1)| 00:25:25 | 3388K|00:02:51.39 |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 1376K| 55M| 137M| 127K (1)| 00:25:25 | 4928K|00:02:51.08 |
| 4 | HASH GROUP BY | | 1 | 1376K| 55M| 137M| 127K (1)| 00:25:25 | 5234K|00:02:31.88 |
|* 5 | HASH JOIN | | 1 | 1376K| 55M| 23M| 97315 (1)| 00:19:28 | 331M|00:06:02.81 |
| 6 | VIEW | | 1 | 1091K| 11M| | 47074 (1)| 00:09:25 | 1952K|00:00:18.40 |
| 7 | SORT GROUP BY | | 1 | 1091K| 43M| 117M| 47074 (1)| 00:09:25 | 1952K|00:00:16.44 |
|* 8 | HASH JOIN | | 1 | 1091K| 43M| 26M| 35282 (1)| 00:07:04 | 1952K|00:00:12.16 |
|* 9 | VIEW | index$_join$_010 | 1 | 959K| 15M| | 22999 (1)| 00:04:36 | 1757K|00:00:07.30 |
|* 10 | HASH JOIN | | 1 | | | | | | 1757K|00:00:05.54 |
|* 11 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 959K| 15M| | 32225 (1)| 00:06:27 | 1757K|00:00:01.76 |
| 12 | INDEX FAST FULL SCAN | ORDER_ID_LID_COMID_IDX | 1 | 959K| 15M| | 13548 (1)| 00:02:43 | 2880K|00:00:05.76 |
|* 13 | INDEX FAST FULL SCAN | IX_BASKET | 1 | 3215K| 76M| | 5314 (1)| 00:01:04 | 3217K|00:00:06.43 |
|* 14 | HASH JOIN | | 1 | 1106K| 32M| 9M| 46790 (1)| 00:09:22 | 2013K|00:00:27.08 |
|* 15 | HASH JOIN | | 1 | 322K| 6303K| 2376K| 797 (1)| 00:00:10 | 322K|00:00:00.69 |
| 16 | INDEX FAST FULL SCAN | IX_ITEM_PROGRAM_ID | 1 | 110K| 1076K| | 72 (2)| 00:00:01 | 110K|00:00:00.11 |
| 17 | INDEX FAST FULL SCAN | IX_OPTION_ITEM_ID | 1 | 322K| 3151K| | 271 (1)| 00:00:04 | 322K|00:00:00.32 |
| 18 | VIEW | | 1 | 1106K| 11M| | 44292 (1)| 00:08:52 | 2013K|00:00:18.67 |
| 19 | SORT GROUP BY | | 1 | 1106K| 42M| 110M| 44292 (1)| 00:08:52 | 2013K|00:00:16.65 |
|* 20 | HASH JOIN | | 1 | 1106K| 42M| 26M| 32972 (1)| 00:06:36 | 2013K|00:00:12.21 |
|* 21 | VIEW | index$_join$_007 | 1 | 959K| 15M| | 22999 (1)| 00:04:36 | 1757K|00:00:06.26 |
|* 22 | HASH JOIN | | 1 | | | | | | 1757K|00:00:04.50 |
|* 23 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 959K| 15M| | 32225 (1)| 00:06:27 | 1757K|00:00:01.76 |
| 24 | INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX | 1 | 959K| 15M| | 13548 (1)| 00:02:43 | 2880K|00:00:02.88 |
| 25 | INDEX FAST FULL SCAN | IX_BASKET_ORDER_OPTION | 1 | 3318K| 72M| | 3137 (1)| 00:00:38 | 3320K|00:00:03.32 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
...
5 - access("BORD"."COMPANY_ID"="BORD1"."COMPANY_ID")
...
14 - access("BORD"."OPTION_ID"="O"."ID")
15 - access("O"."ITEM_ID"="I"."ID")
...
Запрос, как и было запланировано, выполнился без объединений (complex view merging, см.операции создания inline view — VIEW | SORT GROUP BY в строках 6-7, 18-19 плана), в разы быстрее, с минимальным использованием временного табличного пространства (80 МБ) при выполнении hash group by:
SQL> select OPERATION_ID, OPERATION_TYPE, ACTIVE_TIME, MAX_TEMPSEG_SIZE, LAST_TEMPSEG_SIZE
2 from gv$sql_workarea where SQL_ID = '2xa7s8tb8457b' where LAST_TEMPSEG_SIZE > 0
3 /
OPERATION_ID OPERATION_TYPE ACTIVE_TIME MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
------------ ---------------------------------------- ----------- ---------------- -----------------
4 GROUP BY (HASH) 187013608 83886080 83886080
Можно попробовать ещё немного оптимизировать/уменьшить стоимость запроса, заменив избыточные е операции группировки подсказками NO_MERGE
SQL> select /*+ ALL_ROWS gather_plan_statistics */ 2 PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID 3 from (SELECT I.PROGRAM_ID PROGRAM_ID, 4 BORD1.OPTION_ID OPTION_ID, 5 COUNT(BORD1.OPTION_ID) COUNT_OPTION_ID, 6 row_number() over(partition by PROGRAM_ID order by COUNT(BORD1.OPTION_ID) desc) rn 7 FROM ITEM I, 8 OPTION O, 9 (select /*+ NO_MERGE*/ 10 ORD.COMPANY_ID, B.OPTION_ID, B.ROWID 11 from BASKET B, ORDER ORD 12 where ORD.ID = B.ORDER_ID 13 AND ORD.PAID_UP = 'Y' 14 ) BORD, 15 (select /*+ NO_MERGE*/ 16 ORD1.COMPANY_ID, B1.OPTION_ID, B1.ROWID 17 from BASKET B1, ORDER ORD1 18 where ORD1.ID = B1.ORDER_ID 19 AND ORD1.PAID_UP = 'Y' 20 AND B1.BUNDLE_OPTION_ID IS NULL 21 ) BORD1 22 WHERE BORD.COMPANY_ID = BORD1.COMPANY_ID 23 AND BORD.OPTION_ID = O.ID 24 AND O.ITEM_ID = I.ID 25 GROUP BY I.PROGRAM_ID, BORD1.OPTION_ID) 26 where rn <= 1000; 3388557 rows selected. Elapsed: 00:02:38.50 PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Used-Tmp| ----------------------------------------------------------------------------------------------------------------------------------------------------- |* 1 | VIEW | | 1 | 1091K| | 94080 (1)| 00:18:49 | 3388K|00:02:27.30 | |* 2 | WINDOW SORT PUSHED RANK | | 1 | 1091K| 108M| 94080 (1)| 00:18:49 | 4979K|00:02:22.11 | 99328 | | 3 | HASH GROUP BY | | 1 | 1091K| 108M| 94080 (1)| 00:18:49 | 5234K|00:02:10.62 | 111K| |* 4 | HASH JOIN | | 1 | 1091K| 23M| 70496 (1)| 00:14:06 | 331M|00:05:45.70 | | | 5 | VIEW | | 1 | 1091K| | 33458 (1)| 00:06:42 | 1952K|00:00:13.34 | |* 6 | HASH JOIN | | 1 | 1091K| 26M| 33458 (1)| 00:06:42 | 1952K|00:00:11.39 | | |* 7 | VIEW | index$_join$_009 | 1 | 959K| | 22999 (1)| 00:04:36 | 1757K|00:00:06.22 | |* 8 | HASH JOIN | | 1 | | | | | 1757K|00:00:02.70 | | |* 9 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 959K| | 32225 (1)| 00:06:27 | 1757K|00:00:01.76 | | 10 | INDEX FAST FULL SCAN | ORDER_ID_LID_COMID_IDX | 1 | 959K| | 13548 (1)| 00:02:43 | 2880K|00:00:02.88 | |* 11 | INDEX FAST FULL SCAN | IX_BASKET | 1 | 3215K| | 5314 (1)| 00:01:04 | 3217K|00:00:06.43 | |* 12 | HASH JOIN | | 1 | 1106K| 9M| 33588 (1)| 00:06:44 | 2013K|00:00:21.84 | | |* 13 | HASH JOIN | | 1 | 322K| 2376K| 797 (1)| 00:00:10 | 322K|00:00:00.68 | | | 14 | INDEX FAST FULL SCAN | IX_ITEM_PROGRAM_ID | 1 | 110K| | 72 (2)| 00:00:01 | 110K|00:00:00.11 | | 15 | INDEX FAST FULL SCAN | IX_OPTION_ITEM_ID | 1 | 322K| | 271 (1)| 00:00:04 | 322K|00:00:00.32 | | 16 | VIEW | | 1 | 1106K| | 31090 (1)| 00:06:14 | 2013K|00:00:15.54 | |* 17 | HASH JOIN | | 1 | 1106K| 26M| 31090 (1)| 00:06:14 | 2013K|00:00:11.51 | | |* 18 | VIEW | index$_join$_006 | 1 | 959K| | 22999 (1)| 00:04:36 | 1757K|00:00:06.13 | |* 19 | HASH JOIN | | 1 | | | | | 1757K|00:00:04.37 | | |* 20 | INDEX RANGE SCAN | IX_ORDER_PAID_UP | 1 | 959K| | 32225 (1)| 00:06:27 | 1757K|00:00:01.76 | | 21 | INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX | 1 | 959K| | 13548 (1)| 00:02:43 | 2880K|00:00:02.88 | | 22 | INDEX FAST FULL SCAN | IX_BASKET_ORDER_OPTION | 1 | 3318K| | 3137 (1)| 00:00:38 | 3320K|00:00:03.32 | ----------------------------------------------------------------------------------------------------------------------------------------------------
Как и ожидалось, стоимость запроса уменьшилась, план изменился, незначительно выросло потребление временных дисковых сегментов, но это всего лишь сотни мегабайт
ORA-1652: unable to extend temp segment by %s in tablespace TEMP
This error occur when user running the queries doesn’t get the enough free space in the temporary tablespace to complete the task.
Usually SMON automatically clean up the unused temporary segments so increasing the temporary tablespace is not a good idea in production environment. If this issue is happening on the regular basis then you need to analyse the temporary tablespace and the sessions along with the queries holding the temp space.
It’s better if you can analyse and tune the queries. Below are some of the troubleshooting steps using which you can find the root cause.
Query To Check TEMP Tablespace Usage:
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ------------ ------------ ----------------------
TEMP 13107122 1293301 17411
The following query will display the information about each statement using space in a sort segment.
This will also including information about the database sessions that issued the statement and the temporary tablespace and amount of sort space being used.
SELECT S.sid || ',' || S.serial# sid_serial, S.username,T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
Run the above query at regular intervals to know which statement is chewing up temp space.
To add datafiles to the temp tablespace:
select file_name,tablespace_name,bytes/1024/1024/1024, maxbytes/1024/1024/1204,autoextensible from dba_temp_files;ALTER TABLESPACE TEMP ADD TEMPFILE 'LOCATION' size 100m reuse autoextend on next 50m maxsize 20G
To avoid the ORA-1652 error, you can configure the temp tablespace usaage alert and monitor the sessions/queries causing the temp issues.
Each tablespace has one or more datafiles that it uses to store data.
The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.
To find out if the actual limit is 32gb, run the following:
select value from v$parameter where name = 'db_block_size';
Compare the result you get with the first column below, and that will indicate what your max datafile size is.
I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).
Block Sz Max Datafile Sz (Gb) Max DB Sz (Tb)
-------- -------------------- --------------
2,048 8,192 524,264
4,096 16,384 1,048,528
8,192 32,768 2,097,056
16,384 65,536 4,194,112
32,768 131,072 8,388,224
You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you’ve currrently set the max file size to (which cannot exceed the aforementioned 32gb):
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
MAXSIZE_SET is the maximum size you’ve set the datafile to. Also relevant is whether you’ve set the AUTOEXTEND option to ON (its name does what it implies).
If your datafile has a low max size or autoextend is not on you could simply run:
alter database datafile 'path_to_your_filethat_file.DBF' autoextend on maxsize unlimited;
However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:
alter tablespace system add datafile 'path_to_your_datafiles_foldername_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
Each tablespace has one or more datafiles that it uses to store data.
The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.
To find out if the actual limit is 32gb, run the following:
select value from v$parameter where name = 'db_block_size';
Compare the result you get with the first column below, and that will indicate what your max datafile size is.
I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).
Block Sz Max Datafile Sz (Gb) Max DB Sz (Tb)
-------- -------------------- --------------
2,048 8,192 524,264
4,096 16,384 1,048,528
8,192 32,768 2,097,056
16,384 65,536 4,194,112
32,768 131,072 8,388,224
You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you’ve currrently set the max file size to (which cannot exceed the aforementioned 32gb):
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
MAXSIZE_SET is the maximum size you’ve set the datafile to. Also relevant is whether you’ve set the AUTOEXTEND option to ON (its name does what it implies).
If your datafile has a low max size or autoextend is not on you could simply run:
alter database datafile 'path_to_your_filethat_file.DBF' autoextend on maxsize unlimited;
However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:
alter tablespace system add datafile 'path_to_your_datafiles_foldername_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
Перевод отсюда http://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext
Каждое табличное пространство имеет один или больше файл данных, в которые записываются данные.
Максимальный размер файла данных зависит от размера блока данных в базе данных. Я думаю, что у вас по умолчанию установлено 32Гб на один дата файл.
Что бы определить, что текущий предел 32Гб, запустите скрипт:
select value from v$parameter where name = 'db_block_size'Сравните результат полученный в запросе и максимальный размер файла данных в таблице представленной ниже.
У меня установлена Oracle Personal Edition 11g r2 в ней по умолчанию установлен размер блока 8,192 (32Гб на один файл).
Размер Максимальный Максимальный
блока (байт) размер файла (Мб) размер БД (Мб)
-------- -------------------- --------------
2 048 8 192 524 264
4 096 16 384 1 048 528
8 192 32 768 2 097 056
16 384 65 536 4 194 112
32 768 131 072 8 388 224Вы можете запустить это запрос чтобы определить какие файлы данных у вас есть, с какими табличными пространствами они связаны и какой максимальный размер для них установлен:
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files xMAXSIZE_SET — максимальный размер файла данных. Также имеет значение включена ли опция AUTOEXTEND в значение ON (его название указывает на что влияет это свойство(Авторасширение)).Если размер файла данных слишком маленький или опция авто расширения выключена, то выполните скрипт:
alter database datafile 'path_to_your_filethat_file.DBF' autoextend on maxsize unlimited;Если размер файла данных равен 32Гб или около того, то вам нужен новый файл данных для этого табличного пространства. Вот команда по созданию его:
alter tablespace system add datafile 'path_to_your_datafiles_foldername_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;Кроме того хорошей практикой для вас является иметь отдельное табличное пространство для индексов (похоже у вас не так, так как вы не указали табличное пространство в скрипте create index и там используется значение по умолчанию, т.е. системное табличное пространство). Поэтому после создания такого табличного пространства вы должны запустить что-то вроде этого:
CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)
tablespace name_of_ts_for_indexesПосле создания табличного пространства и файла данных:
create tablespace name_of_ts_for_indexes datafile
'c:appxyzoradataorclname_of_ts_for_indexes01.dbf' autoextend on maxsize unlimited nologging;Таким образом ваши таблицы будут находиться в одном табличном пространстве, а индексы — в другом.
Добавлю еще команды
ALTER DATABASE DATAFILE <file_name> RESIZE <size>;
ALTER TABLESPACE PROBATBS ADD DATAFILE <file_name> SIZE <size> REUSE AUTOEXTEND ON NEXT <size> MAXSIZE <size>
Еще ссылки:
http://www.orahome.ru/ora-admin/102
https://iusoltsev.wordpress.com/2010/08/02/ora-01652-unable-to-extend-temp-segment/
http://www.sql.ru/forum/131782/ora-01652-unable-to-extend-temp-segment
http://delphimaster.net/view/3-58578/all
http://www.sql.ru/forum/1162644/ora-01654-unable-to-extend-index
Статьи про Oracle
->
Администрирование
ORA-01652: unable to extend temp segment
v:1.0 24.07.2011
Петрелевич Сергей
Ошибка вида ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 говорит о том, что Oracle
больше не может расширить табличное пространство. Часто это происходит по причине кривых рук программиста, написавшего
не оптимальный запрос. Выявить такой запрос можно, например так:
select s.SQL_ID, s.sql_fulltext, u.TABLESPACE, u.EXTENTS, u.BLOCKS
from v$tempseg_usage u, v$
sql s
where s.sql_id = u.sql_id
and u.extents>1;
Выполнять запрос надо во время возникновения ошибки.
Таким запросом можно периодически мониторить базу, отслеживая кривые запросы.
Если какой-то запрос начинает интенсивно использовать табличное пространство TEMP, то это это веский повод разобраться, почему он так работает
и провести оптимизацию не дожидаясь появления ошибки.
Метки:
v$представления
администрирование
Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.