FAQ
Hi,

Platform: Oracle 10g, Linux on a 4 cpu box

I am creating a text index on a 65 million row 8-hash partitioned table.
Total 8k blocks for the table is 999424. Average clob column size is 300
bytes.

I am creating a text index as:

create index tidx_item_markers on items(marker_details)
indextype is ctxsys.context
parameters ('memory 128m') parallel 8
/
Index creation is painfully slow. Query from v$session_longops shows:

SQL>select opname, totalwork, units, time_remaining from v$session_longops
where time_remaining <>0;

(output formatted to show properly)

Rowid Range Scan,1990624,Blocks,798966
Rowid Range Scan,1863664,Blocks,741880
Rowid Range Scan,1867744,Blocks,830822
Rowid Range Scan,1236960,Blocks,498835
Rowid Range Scan,1343454,Blocks,417863
Rowid Range Scan,2142192,Blocks,858864

When total blocks in the table are 999424, why Oracle is showing more than
double the blocks to scan through for six parallel process? It is scanning
approx 13 times the table blocks. The table data was never deleted. With
this speed it will take more than 9 days to create index.

How can I speed up the index creation? Has anyone similar experience of
creating context index on a 65 million row table. How much time it takes to
do so?

Regards

Sandeep

Search Discussions

  • Crisler, Jon at Mar 18, 2008 at 4:57 pm
    Is there a capability to limit bandwidth used by DataGuard ? We have a
    DG config that seems to dominate a WAN link, and other traffic has
    higher priority over DataGuard traffic. I looked into NistNet which I
    have used before, but that is really not the purpose of that tool



    This is for RH Linux 64 bit.
  • Roman Podshivalov at Mar 18, 2008 at 5:10 pm
    Hi,

    Common practice would be to configure QoS on WAN routers.

    --romas
    On 3/18/08, Crisler, Jon wrote:

    Is there a capability to limit bandwidth used by DataGuard ? We have a
    DG config that seems to dominate a WAN link, and other traffic has higher
    priority over DataGuard traffic. I looked into NistNet which I have used
    before, but that is really not the purpose of that tool



    This is for RH Linux 64 bit.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 18, '08 at 4:20p
activeMar 18, '08 at 5:10p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2021 Grokbase