I’m having trouble with the parallel execution of an Oracle SQL query for creating indexes on separate table (assume each table have single index).
The performance boost isn’t even close to what I was hoping for. I therefore need your help in determining what to check for in the Oracle DB host in order to troubleshoot the issue.
Although, I found all SQL queries having WAIT_CLASS as “USER I/O”. I also did a check on IOPS through NetData UI on parallel index creation env, its max observed near ~24%.
Just to notice, we already aware that PARALLEL hint may help here, but we are looking further improvement on top of that.
Please let me know, whether any tunning on Oracle DB Side required or We can assume there won't be any benefit by executing index creation in parallel (even on different table) and we can only specify PARALLEL degree to enhance the performance.
A nice easy place to start is
- Start a brand new session ( to reset all stats)
- create your index
- query the session statistics to see where you spent your time, eg
SQL> conn scott/tiger
Connected.
SQL> create index test_ix on t_lookup(par_date);
Index created.
SQL> select
2 EVENT
3 ,TOTAL_WAITS
4 ,TOTAL_TIMEOUTS
5 ,SECS
6 ,rpad(to_char(100 * ratio_to_report(secs) over (), 'FM00.00') || '%',8) pct
7 from (
8 select EVENT
9 ,TOTAL_WAITS
10 ,TOTAL_TIMEOUTS
11 ,TIME_WAITED/100 SECS
12 ,max_wait
13 from v$session_event
14 where sid = to_number(sys_context('USERENV','SID'))
15 and event not like 'SQL*Net message%'
16 union all
17 select 'CPU', null, null, value/100 , 0 from v$sesstat
18 where statistic# = ( select statistic# from v$statname where name = 'CPU used by this session')
19 and sid = to_number(sys_context('USERENV','SID'))
20 )
21 order by 4;
EVENT TOTAL_WAITS TOTAL_TIMEOUTS SECS PCT
---------------------------------------------------------------- ----------- -------------- ---------- -------------
Disk file operations I/O 4 0 0 00.00%
log file switch completion 2 0 0 00.00%
log file sync 1 0 0 00.00%
db file sequential read 3 0 0 00.00%
index (re)build lock or pin object 4 0 0 00.00%
events in waitclass Other 310 1 .02 00.25%
direct path write temp 2447 0 .02 00.25%
direct path write 13143 0 .07 00.88%
direct path read temp 2925 0 .1 01.25%
db file scattered read 295 0 .36 04.52%
CPU 7.4 92.85%
11 rows selected.So you can see in my case, 92% of the time was spent in CPU, 4% reading the data, 2% writing temporary data.
You can use that to get some insight as to where your time is being spent. That then becomes an input into how to proceed from there - post it back as a (nicely formatted) review