Oracle … as usual

Oracle by Laurent Leturgez

Monitor Oracle B*Tree index creation

Today, one of my client asks me how to monitor index creation and was a little bit curious about what happened during this process.

To answer him, I remember operation that are executed to build an index:

1- Data needed to build the index is read

2- A sort segment is created

3- The index is progressively build as a temporary segment in the destination tablespace

The first phase is monitored like a classic read operation : V$SESSION_WAIT, 10046 event trace etc.

Next phase can be monitored by querying the V$SORT_USAGE view:

select USERNAME,SESSION_NUM,TABLESPACE,CONTENTS,SEGTYPE,BLOCKS*dbbs/1024/1024 as sizeMb
  2* from v$sort_usage, (select to_number(value) dbbs from v$parameter where name='db_block_size')

USERNAME   SESSION_NUM TABLESPACE CONTENTS  SEGTYPE       SIZEMB
---------- ----------- ---------- --------- --------- ----------
LAURENT             46 TEMP       TEMPORARY SORT             101

Finally, when the sort segment has been created, you will see a new segment in the index tablespace. This segment is a TEMPORARY segment with a numeric format name (Usually the final size is close to the sort segment size) :

select segment_name,segment_type,sum(bytes)/1024/1024/1024 as SIZE_GB
from dba_segments
where tablespace_name='DWH_P_IDX'
group by segment_name,segment_type;
SEGMENT_NAME                   SEGMENT_TYPE       SIZE_GB
------------------------------ ------------------ -------
.../...
14.4483                        TEMPORARY           1.1875
.../...

If you repeat this query, you will see the segment size grow. When the operation ends, this temporary segment becomes an INDEX segment which is … your index.

Advertisements

One response to “Monitor Oracle B*Tree index creation

  1. E.Hatt-Swank September 16, 2012 at 3:14 PM

    I know this is an older post but I wanted to thank you for sharing this information. It came in very handy on a problem I was having which kept me busy all weekend! I had a 200gb index to recreate and I could see that it was going very slowly, but wasn’t sure how much more time it would take. Your queries helped me make the decision to kill it and start over, which helped immensely, and it finished in much better time on the 2nd try. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: