Should I kill the process?

[lgaorcl]

I am creating a index on a 40 gig table(composite partitioned). It's been running for 24 hours. Is it normal? Should I kill the process and rerun the script or just wait? What could I do to speed it up? I created another one on the same table different column, it took me about 12 hours.

Any suggestions?

Thanks!

[davey23uk]

what is the create statement? how much memory do you have, sort area size?

[svk]

Is using PARALLEL an option for you ? If yes, give it a try.

Before killing the process, I would try to estimate how much work it has completed. If it is nearing completion and you can estimate completion time that is acceptable, leave it running.

[lgaorcl]

Yes, it is paralleled. How could I estimate the time to complete, the work has done? My concern is that the job is dead somewhere. How could I be sure it is running fine?

[gandolf989]

If you look at the session through DBA Studio/OEM it should show you what it is currently doing as well as an estimate on how much longer it should take.

[svk]

01. See if the session's status is ACTIVE.
02. If the session is ACTIVE, look at the wait reason for this session. It should not be anything else than "db file scattered read" for most of the time.
03. See how much temp space it is using. Compare that with estimated size of index. (Size of columns+rowid)*# of rows should give you a very rough but usable estimate of how large the index will get.
04. Look at the v$session_longops record for this session. This is similar to looking at OEM's logops tab.
05. Look in the tablespace where the index is supposed to go. See if there are "temp segments" getting created there owned by SYS. If there are, index build operation is done. Oracle is just moving the segment to its final destination.

[lgaorcl]

Where on the OEM can you see how much time remaining for the job?

Thanks!

[lgaorcl]

1. The session is active.
2. Where should I look to see the wait reason for this session.
3. V$session_longops says there are 15378 (seconds, minutes?) time_remaIning.
4. I checked some of the tablespaces indexes suppose to go, there are some temp segments created with 164....as index name there.

Look at the temp segments created, I wanted to wait. It seems Oracle is moving the objects to the destination.
But look at the time_remaining, is that seconds or minutes?

I created the similar one yesterday, it only took me 12 hours. This one has been running 30 hours.

Thanks!

[svk]

Databases->Your DB->Instance->Session->Your SID->"Long Operations" tab in right pane.

This indicates only currently running long op and previous one during the same session.

[lgaorcl]

Svk:

I can only see 'top session' from my OEM. Another process has been deleting duplicate records for a big table. It shows: current wait event: PX Deq: Execute Reply. Seconds spent waiting: 97983 and time_remaining for this session is: 10326.
Could you tell me what is this session waiting for? Is the time seconds or minutes? Is the time accurate?

Thank you very much for the help!