Skip to Main Content
  • Questions
  • Index creation slow - lots of direct path write/temp waits

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: February 06, 2019 - 2:12 pm UTC

Last updated: February 11, 2019 - 4:37 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

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.

and Connor said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up

John Keymer, February 08, 2019 - 10:28 am UTC

Thanks, I re-ran doing the below (I used a table which is slightly larger though).

LOW_OPTIMAL_SIZE HIGH_OPTIMAL_SIZE  DELTA_OPT  DELTA_ONE DELTA_MULTI      DELTA
---------------- ----------------- ---------- ---------- ----------- ----------
            2048              4095       1927          0           0       1927
           65536            131071         19          0           0         19
          131072            262143          8          0           0          8
          262144            524287         14          0           0         14
          524288           1048575         79          0           0         79
         1048576           2097151         91          0           0         91
         2097152           4194303         10          0           0         10
        33554432          67108863          1          0           0          1
        67108864         134217727          1          0           0          1
       134217728         268435455          1          0           0          1
      1073741824        2147483647          0          2           0          2



which doesn't seem bad if I'm interpreting the values correctly. Similarly my wait events are showing:

EVENT                         TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO WAIT_CLASS   TOTAL_WAITS
----------------------------- ----------- ------------ ----------------- ------------ -----------
direct path write                   65861         2.28         658607789 User I/O           28929
direct path read                    50907         1.33         509074801 User I/O           38394
direct path write temp              32969         5.54         329691450 User I/O            5946
SQL*Net message from client          2032        33.31          20320392 Idle                  61
direct path read temp                 614           .1           6138916 User I/O            6237
SQL*Net break/reset to client          23          .69            234032 Application           34
events in waitclass Other              16            0            157908 Other              16672
log file sync                           0          .04               862 Commit                 2
Disk file operations I/O                0            0               631 User I/O              22
SQL*Net message to client               0            0               153 Network               62


So perhaps it' that the disks are "slow" to read from and write to (i.e. reading the table data and writing the index back to disk).

Connor McDonald
February 11, 2019 - 4:37 am UTC

Well you definitely dumped to disk (DELTA_ONE = one pass through disk)

I would try bumping up some parameters as I indicated before, or for this particular operation you could revert to manual control of your pga, eg

alter session set workarea_size_policy = manual;
alter session set sort_area_size = nnn;
alter session set sort_area_retained_size = nnn;

If memory serves, 'nnn' is capped at 2G

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database