Forum Stats

  • 3,851,413 Users
  • 2,263,973 Discussions
  • 7,904,701 Comments

Discussions

Is larger SGA will improve Index creation ?

Prakash Gore
Prakash Gore Member Posts: 4
edited Mar 5, 2014 7:33AM in General Database Discussions

Hello All,

Our server has 4GB of RAM, and currently SGA is configured only 400MB, we noticed index creation for a table having 6.5GB size is taking more than 45hrs.

We tried with parallel and nologging option. But still same issue.

Would you please let us know how we can improve the index creation performance.

Regards

Prakash

Tagged:

Answers

  • onkar.nath
    onkar.nath Member Posts: 733 Silver Badge

    are you creating index on PK col or a column with lots of NULLs?

    Try to trace the index creation command and you should be able to see why it is taking so long?

    Onkar

  • JohnWatson
    JohnWatson Member Posts: 2,461 Gold Trophy

    orclz>

    orclz> alter session set workarea_size_policy=manual;

    Session altered.

    orclz> alter session set sort_area_size=2147483647;

    Session altered.

    orclz> create index i1 on emp(ename);

    Index created.

    orclz>

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    technically it's the PGA for the process creating the index you have to increase - as shown in John Watson's answer. When you take a look at the execution plan for the index creation you will probably see much temp space usage: without sufficient memory the necessary sort operations have to be dumped to disk resulting in a lot of I/O.

This discussion has been closed.