Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

apparent inactivity during index creation

Ian McClement_1
Occasional Advisor

apparent inactivity during index creation

We created an index on a 1.3GB table (using parallel(degree 3) and unrecoverable), during the first 15 mins approx 400mb was used in the TEMP tablespace and the same activity rate continued for approx 1 hour, then slowed until a maximum of approx 1.3GB was used in the TEMP tablespace after 2hours. this then remained static for 3.5 hours. During this period, top showed the oracle processes as :

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
1 rroot 18570 oracle 195 20 10776K 592K run 25:39 5.48 5.47 ora_p003_
4 rroot 18568 oracle 138 20 10776K 616K sleep 35:31 1.01 1.01 ora_p002_
4 rroot 8989 oracle 156 20 10116K 396K sleep 18:07 0.93 0.93 ora_dbwr

which nearly caused us to cancel the job, before oracle then started to write the index in approx 30 mins:

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
2 rroot 8989 oracle 156 20 10116K 396K sleep 30:17 81.21 81.07 ora_dbwr_
1 rroot 18568 oracle 194 20 10796K 1092K run 40:30 10.44 10.43 ora_p002_
3 rroot 18570 oracle 156 20 10780K 1080K sleep 30:06 8.45 8.44 ora_p003_

Would this period of apparent inactivity seem normal? Or Can you suggest where the problem may lie - we are looking at increasing the sort_area_size parameter, how much difference would this make?
There were no entries in the v$sort_segment during this process.

Can anyone offer any advice?

Cheers
Ian