7

I have a really huge table, with ~200 million rows. It had no index/pk at all. Selects in this table were (obviously) running slow. I decided to create a PK using 3 columns. I did it in a test environment that has a smaller version of this table and it worked like a charm.

So, before going home i did a ALTER TABLE HUGETABLE ADD CONSTRAINT PK_HUGETABLE PRIMARY KEY (ID1, ID2, ID3);

I expected it to run over the night, but it has been already over 24 hours and it still running.

I know if i had kept the Session Id before starting my query i would be able to track it at V$SESSION_LONGOPS. But i didn't.

Is there any way to check how is my query going or how long will it still take?

Michael
  • 477
  • 1
  • 5
  • 12

2 Answers2

7

You should still be able to query V$SESSION_LONGOPS. If you run something like

SELECT sid, serial#, start_time, sofar, totalwork, time_remaining, message
  FROM v$session_longops
 WHERE time_remaining > 0

you'll probably see only one session that started yesterday and the other columns should corroborate that with indications that the session has done a lot of work. The MESSAGE should also indicate something like a full scan on HUGETABLE.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I couldnt find it. I found a lot of process going on, but couldnt identify mine. So I gave up and cancelled it :) I will stop everything during the weekend and try it again. This time I will get the session id before starting the job. Thanks anyway. – Michael Oct 08 '10 at 17:15
  • 2
    Next time, define the index with PARALLEL n, where n is some acceptable degree of parallelism on your system. When done, alter the index to NOPARALLEL. Also, if you aren't using Data Guard and can take a backup of the database when done, use the NOLOGGING clause. – Adam Musch Oct 11 '10 at 15:38
0

You don’t need to remember your session id to monitor a statement's status. As Justin suggested, you shouldn’t have too many long running queries since previous day. You can specify start time as well to narrow down the results.

select * from V$SESSION_LONGOPS where time_remaining > 0 and start_time > <'your run date/time here'>;

Besides 'PARALLEL n' option, you should consider using 'ONLINE' option as well if you are creating (or rebuilding) an index while concurrent read/write are taking place on HUGE TABLE.

pahariayogi
  • 1,073
  • 1
  • 7
  • 18