I'm running the following command to convert a relatively large table (34M+ rows) from non-partitioned to INTERVAL partitioned by day. It's been chugging for nearly 18 hours and its full table scan still only reports 80% complete in the session long ops.
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ALTER TABLE big_table_with_34M_rows_over_last_90_days MODIFY
PARTITION BY RANGE(partition_date) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(PARTITION p_default VALUES LESS THAN (TO_DATE('1999-01-01','yyyy-mm-dd')))
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | |
| 1 | LOAD AS SELECT | PROPUQ_OUTBOUND_STAGE_HIST | |
|* 2 | TABLE ACCESS FULL | PROPUQ_OUTBOUND_STAGE_HIST | 33M|
----------------------------------------------------------------------
This is taking far longer than I'd expect and I'd like to know what's taking all the time. Here are the session's waits. Am I just I/O bound? Other “normal” things are going on in the DB, but (hopefully) not against this specific table since it's exclusively locked. All physical I/O is against a network storage array, but I'm not too fluent with those details and don't have DB server access.
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT
direct path write 354815 0 16493 0.05 162
db file scattered read 77421 0 10204 0.13 93
direct path read 43363 0 6449 0.15 30
db file sequential read 62863 0 1796 0.03 3
local write wait 3364 0 1214 0.36 35
log file switch completion 97 0 291 3 9
direct path sync 565 0 260 0.46 3
log buffer space 4 0 93 23.37 47
jobq slave wait 1 0 23 23.23 23
events in waitclass Other 526 0 21 0.04 2
resmgr:internal state change 1 1 10 10.01 10
log file sync: SCN ordering 2 0 1 0.59 1
direct path write temp 10 0 0 0.04 0
latch: cache buffers chains 1 0 0 0.01 0
direct path read temp 9 0 0 0 0
library cache: mutex X 3 0 0 0 0