-
Temp Segment Created Not In Temp Tbs During Creating Index Online
v8.1.6/solaris 8
HELLO,
I HAVE THIS INCIDENT ABOUT 2 WEEKS AGO, WHERE IN I HAVE TO RE-CREATE
INDEX DUE TO INTERNAL ERROR.
(from trace file):
______________________________________________________________
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [6122], [0], [4], [0], [], [], [], []
Current SQL statement for this session:
INSERT INTO POLHIS( BATNUM,POLNUM,SRCDOC,PAYRID,DATREC,DATRAN.........)
_____________________________________________________________
THE OBJECT AFFECTED WAS AN INDEX.
(from trace file):
_____________________________________________________________
Block header dump: 0x26815b80
Object id on Block? Y
seg/obj: 0x679cb csc: 0x00.3e9c0438 itc: 4 flg: - typ: 2 - INDEX
_____________________________________________________________
AFTER IDENTIFYING THE INDEX, WE EXTRACT THE INDEX CREATION SCRIPT, DROP
THE INDEX AND CREATE THE SAME INDEX WITH THE SAME SCRIPT. BUT SINCE IT WAS RUSH HOUR
WE CREATE THE INDEX WITH "ONLINE" OPTION. The index size was more than 1Gbytes, so we figure out
that it will take more time considering that there were also USERS utilizing the table were the
index is created. After 6 hours an error occured that saying:
______________________________________________________________________
ORA-01652 unable to extend temp segment by nnnn in tablespace GREPACIS
______________________________________________________________________
Now this is my question, how thus oracle come up with such error knowing that the temporary
tablespace of user who owned the index is TEMP. To my understanding temp segments are created
in the tablespace where the user assigned with temporary tablespace. I understand that I use
the option "ONLINE" when creating the index, but thus ORACLE have to use the tablespace used
for creating the index(GREPACIS) to also contain temp segments during sorting/merging just to
consider users processes that utilizes also the base table where in index was defined?
It really bugging me off for awhile, I did try to make some research but not that there
was an explanation of how does ORACLE manage the temp segment with given such scenario.
This problem was already solved, but there is this question that i can't figure out.
Could you guys shed some lights on this?
Here are more facts:
NOTE: TABLESPACE USED UPON CREATION OF INDEX WHEN ORA-01652 OCCUR
CREATE TABLESPACE "GREPACIS"
LOGGING
DATAFILE '/c1t17/oradata/CIS1/grepacis102.dbf' SIZE 1024M REUSE,
'/c0t13/oradata/CIS1/grepacis103.dbf' SIZE 2000M REUSE,
'/c1t06/oradata/CIS1/grepacis82.dbf' SIZE 2000M REUSE,
'/c0t02/oradata/CIS1/grepacis01.dbf' SIZE 700M REUSE,
'/c0t02/oradata/CIS1/grepacis02.dbf' SIZE 700M REUSE,
....
....
....
'/c0t13/oradata/CIS1/grepacis107.dbf' SIZE 500M REUSE
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 48K NEXT 48K MINEXTENTS 1
MAXEXTENTS 505 PCTINCREASE 50 ) MINIMUM EXTENT 0K
/
NOTE: INDEX CREATION COMMAND WHEN ORA-01652 OCCUR
prompt creating INDEX "GREPACIS"."HSTDET_HSTGLA"
CREATE INDEX "GREPACIS"."HSTDET_HSTGLA"
ON "GREPACIS"."HSTDET" ("HSTGLA")
TABLESPACE "GREPACIS" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 5120K NEXT 5120K MINEXTENTS 4 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
ONLINE
/
NOTE: TABLESPACE USED-SUCCESSFUL INDEX CREATION
CREATE TABLESPACE "GREP_IDX"
LOGGING
DATAFILE '/c1t17/oradata/CIS1/GREP_IDX.dbf' SIZE 1024M REUSE,
'/c1t17/oradata/CIS1/GREP_IDX1.dbf' SIZE 1024M REUSE,
'/c1t17/oradata/CIS1/GREP_IDX2.dbf' SIZE 1024M REUSE,
'/c1t17/oradata/CIS1/GREP_IDX3.dbf' SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2048K
NOTE: INDEX CREATION COMMAND-SUCCESSFUL INDEX CREATION
prompt creating INDEX "GREPACIS"."HSTDET_HSTGLA"
CREATE INDEX "GREPACIS"."HSTDET_HSTGLA"
ON "GREPACIS"."HSTDET" ("HSTGLA")
TABLESPACE "GREP_IDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
LOGGING
ONLINE
/
SQL> select temporary_tablespace
2 from dba_users
3 where username = 'GREPACIS';
TEMPORARY_TABLESPACE
------------------------------
TEMP
-
What my understanding from documents is that
The CREATE INDEX statement causes the server process (or processes if the index is being created in parallel) to sort the index values before building the tree. After the sort a final index is built in the INDEX tablespaces by using a temporary segment; once the index has been built completely, the segment type is changed to index.
so to avoid the error u can use the NOSORT while creating an index.
-
Thanks Akhadar,
So this is not the usual behaviour of creating temp segments for example when a user make a query?
And this means that the temp segment that being referred here, is actually the sorted/merged index values and eventually rename to an index when the index tree is finalized?
I hope you are right, and this really make sense. , I tell you this is just my first time to encounter such problem.
Anyway, many many thanks to you Akhadar.....
-
Originally posted by akhadar
What my understanding from documents is that
The CREATE INDEX statement causes the server process (or processes if the index is being created in parallel) to sort the index values before building the tree. After the sort a final index is built in the INDEX tablespaces by using a temporary segment; once the index has been built completely, the segment type is changed to index.
so to avoid the error u can use the NOSORT while creating an index.
The reason for it being built in the target TS as a temp segment is so that SMON(?) will clean it up if the create fails -- however a swift syntax check reveals that ...
You can specify NOSORT to indicate to Oracle that the rows are already stored in the database in ascending order, so that Oracle does not have to sort the rows when creating the index. If the rows of the indexed column or columns are not stored in ascending order, Oracle returns an error.
So it doesn't apply here.
-
Hi SlimDave,
Originally posted by slimdave
The reason for it being built in the target TS as a temp segment is so that SMON(?) will clean it up if the create fails -- however a swift syntax check reveals that ...
Is this really it Dave?, is it smart in the part of SMON to use a PERMANENT TABLESPACE rather than utilizing TEMPORARY TABLESPACES to create temp segments? and is it really for the purpose of cleaning up in case of a failure? I have doubts in this, but I'm not sure and I know you're a pretty smart kind of a person you have proven that a bunch of times, so....
-
What Dave is true regarding SMON
Check document 102339.1 for more clarification on this.
-
Got your point guys. I just believe that it is not the smart thing to do. So if the table and the index resides together(don't blame me, just inherited) in the same tbs, I just can't imagine the physical reads and the logical reads that will happened (I/O bottleneck).
-
Originally posted by reydp
Got your point guys. I just believe that it is not the smart thing to do. So if the table and the index resides together(don't blame me, just inherited) in the same tbs, I just can't imagine the physical reads and the logical reads that will happened (I/O bottleneck).
The above remark is totaly irrelevant for the subject of this thread.
The physical reads and the logical reads will be exactly the same, no matter if your indexes and tables are separated in different tablespaces.
And btw, that temporary segment that you observed in the INDEX tablespace was not used for sorting while building the index. The sorting is done in memory and if that isn't sufficient the intermediate sort batches are written down to another temp segment in TEMPORARY tablespace. The temp sefgment in your INDEX tablespace is used only after all sorting is done and when oracle builds the actual index.
You could observe the same principle (temporary segment in the destination tablespace) when creating a new table as select from another table (CTAS) - so this temporary segments have nothing to do with sorting.
Last edited by jmodic; 10-13-2004 at 08:58 AM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
The above remark is totaly irrelevant for the subject of this thread.
The physical reads and the logical reads will be exactly the same, no matter if your indexes and tables are separated in different tablespaces.
Most probably yes.
But come to think of this: If the datafile that is being written for the index uses the same disk where the data of the table is also written, there must be an I/O bottleneck during reading of the table and writing of the index? plus, what is claimed to be the temp segments written in INDEX tbs too.
That is why the best practice is to separate the TBS(datafile/disk) for TABLE and INDEX segments, which will also help performance for DMLs.
But I am sure you are very much aware of this.
Originally posted by jmodic
And btw, that temporary segment that you observed in the INDEX tablespace was not used for sorting while building the index. The sorting is done in memory and if that isn't sufficient the intermediate sort batches are written down to another temp segment in TEMPORARY tablespace. The temp sefgment in your INDEX tablespace is used only after all sorting is done and when oracle builds the actual index.
Thanks jmodic for clearing this. I believe this is what is called the SORTED OUTPUT which Akhadar remarked as the temp segment renamed to become the index itself.
Originally posted by jmodic
You could observe the same principle (temporary segment in the destination tablespace) when creating a new table as select from another table (CTAS) - so this temporary segments have nothing to do with sorting.
That is why I like you guys here. Not only that my concerns/questions that are being addressed, there also extra knowledge induced.
Cheer up... and many thanks.
-
Originally posted by reydp
Most probably yes.
But come to think of this: If the datafile that is being written for the index uses the same disk where the data of the table is also written, there must be an I/O bottleneck during reading of the table and writing of the index? plus, what is claimed to be the temp segments written in INDEX tbs too.
That is why the best practice is to separate the TBS(datafile/disk) for TABLE and INDEX segments, which will also help performance for DMLs.
But I am sure you are very much aware of this.
just a forgot something:
I was not referring here the number of physical reads and the logical reads. I was referring more on the I/O bottleneck.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|