Temp Segment Created Not In Temp Tbs During Creating Index Online
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Temp Segment Created Not In Temp Tbs During Creating Index Online

  1. #1
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857

    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

  2. #2
    Join Date
    May 2001
    Posts
    736
    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.

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.....


  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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....

  6. #6
    Join Date
    May 2001
    Posts
    736
    What Dave is true regarding SMON
    Check document 102339.1 for more clarification on this.

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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).


  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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
  •  



Click Here to Expand Forum to Full Width