I have a table on which I'm trying to index a column. This happens as part of an ETL process, however I have run this process directly to reproduce.
The table in question has about 15M rows and I am trying to index a single column.
create index xxjktst on my_table(my_column);
When I do this, I see the operation drop into v$longops and the percentage so far to the total work rises quickly (first 20 seconds or so) to about 20/30%. then it slows down rapidly, crawling along for a disproportionate amount of time (10+ minutes).
sofar totalwork perc elapsed remain op_name sql_op sqlplan_opts
--------- ------------- ------- ------- ------- --------------- ------- --------------
159622 159622 100 952 0 Sort Output SORT CREATE INDEX
During this, I checked the wait events and I see a lot of direct path write and direct path write temp wait events:
EVENT TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- ------------ -----------------
direct path write 82595 2.86 825945977
direct path write temp 25646 4.71 256457839
SQL*Net message from client 20123 1059.12 201232282
direct path read temp 8077 1.29 80768470
direct path read 7599 .2 75991307
which I presumed is because the sort operation has spilled to disk... however checking various other things on my system such as memory usage in "top" on the server, and the various v$ views, it doesn't look like it should be doing?
select * from v$pgastat;
NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
aggregate PGA target parameter 0 bytes 0
aggregate PGA auto target 0 bytes 0
global memory bound 1073741824 bytes 0
total PGA inuse 1553226752 bytes 0
total PGA allocated 1797935104 bytes 0
maximum PGA allocated 5396699136 bytes 0
total freeable PGA memory 190382080 bytes 0
process count 38 0
max processes count 67 0
PGA memory freed back to OS 7.0470E+10 bytes 0
total PGA used for auto workareas 844587008 bytes 0
NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
maximum PGA used for auto workareas 2134906880 bytes 0
total PGA used for manual workareas 1064960 bytes 0
maximum PGA used for manual workareas 4044142592 bytes 0
over allocation count 0 0
bytes processed 1.9445E+11 bytes 0
extra bytes read/written 6445032448 bytes 0
cache hit percentage 96.79 percent 0
recompute count (total) 6453 0
19 rows selected.
in v$sql_workarea the estimated optimal size is about 200Mb - however I have far more than that on the instance.
pga_aggregate_limit 29686490880 TRUE
pga_aggregate_target 14843245440 FALSE
my instance is a DBaaS instance with 60Gb RAM, so I think I have a few GB's free to play with, however I'm not sure if that is indeed the cause of the problem.
A really good view to look at is:
v$sql_workarea_histogram
You can take before and after snapshots to look at the cost for the index, eg
create global temporary table QUERY_BEFORE
on commit preserve rows as
select * from V$SQL_WORKAREA_HISTOGRAM;
-- create index
select
s.low_optimal_size,
s.high_optimal_size,
s.optimal_executions - b.optimal_executions delta_opt,
s.onepass_executions - b.onepass_executions delta_one,
s.multipasses_executions - b.multipasses_executions delta_multi,
s.total_executions - b.total_executions delta
from v$sql_workarea_histogram s,
query_before b
where s.low_optimal_size = b.low_optimal_size
and s.total_executions - b.total_executions > 0 ;
This gives a nice breakdown of the sorting and how much was in memory (delta_opt) versus spilling to disk (delta_one/multi).
"direct path write" will be the actual index creation (ie, we've finished sorting and now we are writing the index blocks).
In terms of the pga sizing, a lot will depend on what else is going on in the machine but it looks from above that you have ample free. There a couple of hidden parameters:
_smm_max_size: Maximum workarea size for one process
_pga_max_size: Maximum PGA size for a single process
that can also come into play. They vary by version - but since they're underscore I'd recommend contacting Support and getting a nod of approval before setting them.