Overview

This document explains the estimation and monitoring methods for TEMP capacity when building large indexes.

Method

1. Estimation of TEMP Capacity for Building Large Indexes

1.1. Precise TEMP Size Recommendation for Creating Large Indexes

During the sorting process, sort keys are normalized for faster comparison, which causes them to become larger than the original data size. 
Because of this, even if the temp table size is set larger than the table size, an error indicating insufficiency may occur. 
Since the increase in size varies depending on the actual data format in the table, it is difficult to calculate an exact minimum value.

If the indexed columns are numerous and consist of many string columns, the TEMP size may need to be up to twice the table size.
Also, if other sorting queries are running simultaneously, the TEMP usage by those queries should be considered when estimating.

Furthermore, the parallel option used during index creation may increase memory usage but will not significantly affect TEMP tablespace usage. 
It is recommended to set an appropriate degree of parallelism (dop) according to the number of CPUs or IO channels.

2. Monitoring TEMP Usage During Index Build

2.1. Monitoring Using v$tempseg_usage

When monitoring v$tempseg_usage during index creation, if usage is minimal, the following points should be checked. The index creation statement explained in this document is create index online parallel.
The operation method of this feature first creates an unusable index and then performs an internal online rebuild.
The online rebuild is performed by inserting data into segments with names different from the user-specified index name (XPK_TEST_TAB), which can be observed by querying objects with high usage in dba_segments ordered by usage, and upon completion, the segments are exchanged.
In other words, until the operation completes, the XPK_TEST_TAB segments only contain an empty shell.

2.2. Case Study

Below is an actual case of creating a large index on a large general table (1.5T) at a specific site.

<Phenomenon>

When attempting to recreate a 2.3T index with blevel 6 on a 1.5T table by dropping and using the create index online option, the process did not complete even after 20 hours, and monitoring the v$tempseg_usage view showed usage of only 0.27M.

<Cause>

Call stack obtained from the session still running the index creation after 20 hours:

000000010bab4abc xi_sort_keystore_load_

000000010b8ffd54 xi_sort_run_load_from_ssgmt

000000010b8d4ff4 xi_sort_2p_merge

000000010afa9a54 op_sort_stage_merge_run

=> The sort node is finishing sorting and outputting results.

In other words, runs in temp are being merged and output, and the temp segments are being released, causing a decrease in temp usage, so v$tempseg_usage was monitored as using 0.27M.

Example CREATE TABLE and CREATE INDEX ONLINE Statements

CREATE TABLE TIBERO.TEST_TAB
(
    BSDT VARCHAR(8) NOT NULL,
    RPT_ID VARCHAR(20) NOT NULL,
    RFACT VARCHAR(5) NOT NULL,
    SRC_FG VARCHAR(5) NOT NULL,
    RPT_TYPE VARCHAR(50) NOT NULL,
    UD1 VARCHAR(16) NOT NULL ,
    UD2 VARCHAR(16) NOT NULL ,
    UD3 VARCHAR(16) NOT NULL ,
    UD4 VARCHAR(16) NOT NULL ,
    UD5 VARCHAR(16) NOT NULL ,
    UD6 VARCHAR(16) NOT NULL ,
    UD7 VARCHAR(16) NOT NULL ,
    UD8 VARCHAR(16) NOT NULL ,
    UD9 VARCHAR(16) NOT NULL ,
    UD10 VARCHAR(16) NOT NULL ,
    UD11 VARCHAR(16) NOT NULL ,
    UD12 VARCHAR(16) NOT NULL ,
    UD13 VARCHAR(16) NOT NULL ,
    UD14 VARCHAR(16) NOT NULL ,
    UD15 VARCHAR(20) NOT NULL ,
    SCEN_ID VARCHAR(30) NOT NULL ,
    PRICE NUMBER NULL,
    VAR NUMBER NULL,
    BASE_PRICE NUMBER NULL,
    VAR_TOT NUMBER NULL,
    VAR_IR NUMBER NULL,
    VAR_EQ NUMBER NULL,
    VAR_FX NUMBER NULL,
    VAR_VL NUMBER NULL
)
TABLESPACE DATA_TS

CREATE UNIQUE INDEX TIBERO.XPK_TEST_TAB ON TIBERO.TEST_TAB
(
    BSDT ,
    RPT_ID ,
    RFACT ,
    SRC_FG ,
    RPT_TYPE ,
    UD1 ,
    UD2 ,
    UD3 ,
    UD4 ,
    UD5 ,
    UD6 ,
    UD7 ,
    UD8 ,
    UD9 ,
    UD10 ,
    UD11 ,
    UD12 ,
    UD13 ,
    UD14 ,
    UD15 ,
    SCEN_ID 
)
ONLINE
PARALLEL 8
NOLOGGING
TABLESPACE INDEX_TS