Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Udayan.

Asked: December 07, 2016 - 10:22 am UTC

Last updated: December 10, 2016 - 1:42 am UTC

Version: Oracle database 11g R2

Viewed 10K+ times! This question is

You Asked

Hello Tom,

The index creation is taking more time. Previously the index creation used to take around 1 min and now it has increased to 10 - 20 min.
The indexes are dropped and recreated on a daily basis during our night load.
Due to this, our night loads timing has increased.

I would like to understand how can we estimate how much time an index creation should take? Or what are the factors which affect the index creation timings.

Regards,
Udayan

and Connor said...

You can trace the process to see where the time is being taken. Do that first so we know *where* the time is being lost.

Probably the most common cause for an index creation jumping up suddenly is that the sorting of rows need to be spilled to temporary storage, whereas previously it might not have been needed to do so.

You can workaround this by setting workspace management to manual for that session and explicitly nominating sort size parameters.

Hope this helps.

Rating

  (6 ratings)

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

Comments

Udayan Jain, December 08, 2016 - 12:40 pm UTC

Hello Connor,

Thanks for your response.
If possible could you please explain me in detail how to run a trace for index creation and how can we change the sort size parameters as a work around.

Note: You can even share any blog link for that.

Regards,
Udayan Jain
Connor McDonald
December 10, 2016 - 1:30 am UTC

exec dbms_monitor.session_trace_enable(waits=>true)
create index ...
exec dbms_monitor.session_trace_disable
select value from V$DIAG_INFO;

and then run tkprof on the tracefile.

More details

A reader, December 09, 2016 - 7:55 am UTC

"
You can workaround this by setting workspace management to manual for that session and explicitly nominating sort size parameters. "


Please elaborate with example - coding
Chris Saxon
December 09, 2016 - 4:02 pm UTC

See the example below.

Setting Workspace management

Rajeshwaran, Jeyabal, December 09, 2016 - 1:55 pm UTC

....
You can workaround this by setting workspace management to manual for that session and explicitly nominating sort size parameters. "
....

runquery.sql
set serveroutput off;
column sid new_val SID
select userenv('sid') sid from dual;
alter session set workarea_size_policy=MANUAL;
alter session set sort_area_size=&1;
prompt run @reset_stats.sql &SID and @watch_stats in another session here!
pause
prompt running the sql to sort
set termout off;
select * from t order by 1,2,3,4;
set termout on;
prompt run @watch_stats in another session here!
pause


watch_stats.sql
column name format a40 trunc;
column KbytesWrites format 99999999999;
column Diff_KbytesWrites format 99999999999;

merge into session_stats t1 using 
   ( select s1.name, s2.value
 from v$sysstat s1,
   v$sesstat s2
 where ( s1.name like '%ga%'
 or s1.name like '%direct%temp%' )
 and s1.statistic# = s2.statistic#
 and s2.sid = :sid ) t2
on (t1.name = t2.name)
when matched then
 update set
    t1.diff = t2.value - t1.value,
    t1.value = t2.value
when not matched then
 insert (t1.name,t1.value,t1.diff)
 values (t2.name,t2.value,NULL)
/


select name,
 CASE when name like '%ga%'
 then round(value/1024)
 else value
 end as KbytesWrites,
 CASE when name like '%ga%'
 then round(diff/1024)
 else value
 end as Diff_KbytesWrites
from session_stats
order by name
/


run_query.sql
set serveroutput off;
column sid new_val SID
select userenv('sid') sid from dual;
alter session set workarea_size_policy=MANUAL;
alter session set sort_area_size=&1;
prompt run @reset_stats.sql &SID and @watch_stats in another session here!
pause
prompt running the sql to sort
set termout off;
select * from t order by 1,2,3,4;
set termout on;
prompt run @watch_stats in another session here!
pause



Run#1

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

demo@ORA12C> @run_query.sql 65536

       SID
----------
       365


Session altered.

old   1: alter session set sort_area_size=&1
new   1: alter session set sort_area_size=65536

Session altered.

run @reset_stats.sql        365 and @watch_stats in another session here!

running the sql to sort
run @watch_stats in another session here!

demo@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


The other session(session#2) reported the Temp Space usage like this.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

demo@ORA12C> @reset_stats.sql        365

Table dropped.


Table created.


PL/SQL procedure successfully completed.

demo@ORA12C> @watch_stats

17 rows merged.


NAME                                     KBYTESWRITES DIFF_KBYTESWRITES
---------------------------------------- ------------ -----------------
HSC OLTP negative compression                       0
OLAP Aggregate Function Calc                        0
OLAP Aggregate Function Logical NA                  0
OLAP Aggregate Function Precompute                  0
Workload Replay: time gain                          0
calls to kcmgas                                     0
foreground propagated tracked transactio            0
index cmph ld, CU negative comp                     0
ka wait calls for invalid kga                       0
physical reads direct temporary tablespa            0                 0
physical writes direct temporary tablesp            0                 0
redo write gather time                              0
session pga memory                               1729
session pga memory max                           2241
session uga memory                                852
session uga memory max                           1228
slave propagated tracked transactions               0

17 rows selected.

demo@ORA12C> @watch_stats

17 rows merged.


NAME                                     KBYTESWRITES DIFF_KBYTESWRITES
---------------------------------------- ------------ -----------------
HSC OLTP negative compression                       0                 0
OLAP Aggregate Function Calc                        0                 0
OLAP Aggregate Function Logical NA                  0                 0
OLAP Aggregate Function Precompute                  0                 0
Workload Replay: time gain                          0                 0
calls to kcmgas                                     0                 0
foreground propagated tracked transactio            0                 0
index cmph ld, CU negative comp                     0                 0
ka wait calls for invalid kga                       0                 0
physical reads direct temporary tablespa         7181              7181
physical writes direct temporary tablesp         7181              7181
redo write gather time                              0                 0
session pga memory                               1857               128
session pga memory max                           2689               448
session uga memory                               1235               384
session uga memory max                           1749               520
slave propagated tracked transactions               0                 0

17 rows selected.

demo@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production



Run#2
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

demo@ORA12C> @run_query.sql 1048576

       SID
----------
       365


Session altered.

old   1: alter session set sort_area_size=&1
new   1: alter session set sort_area_size=1048576

Session altered.

run @reset_stats.sql        365 and @watch_stats in another session here!

running the sql to sort
run @watch_stats in another session here!

demo@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


The other session(session#2) reported the Temp Space usage like this.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

demo@ORA12C> @reset_stats.sql        365

Table dropped.


Table created.


PL/SQL procedure successfully completed.

demo@ORA12C> @watch_stats

17 rows merged.


NAME                                     KBYTESWRITES DIFF_KBYTESWRITES
---------------------------------------- ------------ -----------------
HSC OLTP negative compression                       0
OLAP Aggregate Function Calc                        0
OLAP Aggregate Function Logical NA                  0
OLAP Aggregate Function Precompute                  0
Workload Replay: time gain                          0
calls to kcmgas                                     0
foreground propagated tracked transactio            0
index cmph ld, CU negative comp                     0
ka wait calls for invalid kga                       0
physical reads direct temporary tablespa            0                 0
physical writes direct temporary tablesp            0                 0
redo write gather time                              0
session pga memory                               1665
session pga memory max                           1985
session uga memory                                852
session uga memory max                           1036
slave propagated tracked transactions               0

17 rows selected.

demo@ORA12C> @watch_stats

17 rows merged.


NAME                                     KBYTESWRITES DIFF_KBYTESWRITES
---------------------------------------- ------------ -----------------
HSC OLTP negative compression                       0                 0
OLAP Aggregate Function Calc                        0                 0
OLAP Aggregate Function Logical NA                  0                 0
OLAP Aggregate Function Precompute                  0                 0
Workload Replay: time gain                          0                 0
calls to kcmgas                                     0                 0
foreground propagated tracked transactio            0                 0
index cmph ld, CU negative comp                     0                 0
ka wait calls for invalid kga                       0                 0
physical reads direct temporary tablespa         1425              1425
physical writes direct temporary tablesp         1425              1425
redo write gather time                              0                 0
session pga memory                               3905              2240
session pga memory max                           3905              1920
session uga memory                               3218              2366
session uga memory max                           3218              2182
slave propagated tracked transactions               0                 0

17 rows selected.

demo@ORA12C> exit



Run#3

demo@ORA12C> @run_query.sql 104857600

       SID
----------
       365


Session altered.

old   1: alter session set sort_area_size=&1
new   1: alter session set sort_area_size=104857600

Session altered.

run @reset_stats.sql        365 and @watch_stats in another session here!

running the sql to sort
run @watch_stats in another session here!

demo@ORA12C>


The other session(session#2) reported the Temp Space usage like this.


demo@ORA12C> @reset_stats.sql        365

Table dropped.


Table created.


PL/SQL procedure successfully completed.

demo@ORA12C> @watch_stats

17 rows merged.


NAME                                     KBYTESWRITES DIFF_KBYTESWRITES
---------------------------------------- ------------ -----------------
HSC OLTP negative compression                       0
OLAP Aggregate Function Calc                        0
OLAP Aggregate Function Logical NA                  0
OLAP Aggregate Function Precompute                  0
Workload Replay: time gain                          0
calls to kcmgas                                     0
foreground propagated tracked transactio            0
index cmph ld, CU negative comp                     0
ka wait calls for invalid kga                       0
physical reads direct temporary tablespa            0                 0
physical writes direct temporary tablesp            0                 0
redo write gather time                              0
session pga memory                               1025
session pga memory max                           1537
session uga memory                                404
session uga memory max                           1036
slave propagated tracked transactions               0

17 rows selected.

demo@ORA12C> @watch_stats

17 rows merged.


NAME                                     KBYTESWRITES DIFF_KBYTESWRITES
---------------------------------------- ------------ -----------------
HSC OLTP negative compression                       0                 0
OLAP Aggregate Function Calc                        0                 0
OLAP Aggregate Function Logical NA                  0                 0
OLAP Aggregate Function Precompute                  0                 0
Workload Replay: time gain                          0                 0
calls to kcmgas                                     0                 0
foreground propagated tracked transactio            0                 0
index cmph ld, CU negative comp                     0                 0
ka wait calls for invalid kga                       0                 0
physical reads direct temporary tablespa            0                 0
physical writes direct temporary tablesp            0                 0
redo write gather time                              0                 0
session pga memory                               1217               192
session pga memory max                          14529             12992
session uga memory                                660               256
session uga memory max                          13707             12670
slave propagated tracked transactions               0                 0

17 rows selected.

demo@ORA12C>


The observation here is when workarea_size_policy is set to MANUAL, we depend on sort_area_size values rather than PGA_AGGREGATE_TARGET - so during each run, i keep increasing the sort_area_size values that inturn reduces the physical IO ( "physical reads(writes) direct temporary tablespace" ) Temp space, and does the sorting entirely in-memory.

Chris Saxon
December 09, 2016 - 3:51 pm UTC

Thanks for the example

on unusable indexes

Rajeshwaran, Jeyabal, December 09, 2016 - 2:09 pm UTC

....
The indexes are dropped and recreated on a daily basis during our night load.
Due to this, our night loads timing has increased.
....

Team,

Any reason that you don't comment about UNUSABLE indexes here?
since dropping an index and any error during recreate would lead to missing index. it would be safe to set them unusable rather than dropping and recreating them.

since starting 11G, setting an index unusable would de-allocate the space too.

drop table t purge;
create table t as select * from all_objects;
create index t_idx on t(owner,object_type,object_id);
exec dbms_stats.gather_table_stats(user,'T');


demo@ORA10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

demo@ORA10G> select blocks
  2  from user_segments
  3  where segment_name ='T_IDX';

    BLOCKS
----------
       384

demo@ORA10G>
demo@ORA10G> alter index t_idx unusable;

Index altered.

demo@ORA10G>
demo@ORA10G> select blocks
  2  from user_segments
  3  where segment_name ='T_IDX';

    BLOCKS
----------
       384

demo@ORA10G>


Where as in 11g database, it goes like this.

demo@ORA11G> select * from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

demo@ORA11G> select blocks
  2  from user_segments
  3  where segment_name ='T_IDX';

    BLOCKS
----------
       384

demo@ORA11G>
demo@ORA11G> alter index t_idx unusable;

Index altered.

demo@ORA11G>
demo@ORA11G> select blocks
  2  from user_segments
  3  where segment_name ='T_IDX';

no rows selected

demo@ORA11G>

Chris Saxon
December 09, 2016 - 4:01 pm UTC

Yes, you could do that. The effect is the same really (provided you have skip_unusable_indexes = true). Unusable is slightly safer because there's less risk of forgetting to re-create one...

Marality

A reader, December 09, 2016 - 7:41 pm UTC

Tkx for the example. But the goal is to get rid of the reliable size of this parameter sort area? ?

If so it would be painful to make the check for each sql stmt. ..

Please give more details and elaborate the essence of your thoughts.

Marality

A reader, December 09, 2016 - 7:41 pm UTC

Tkx for the example. But the goal is to get rid of the reliable size of this parameter sort area? ?

If so it would be painful to make the check for each sql stmt. ..

Please give more details and elaborate the essence of your thoughts.
Connor McDonald
December 10, 2016 - 1:42 am UTC

You can create a login trigger to set the sort size for that entire session if that is easier:

set echo on
create or replace
trigger LARGE_SORT_SIZE
after logon on DEMO.schema
begin
  execute immediate 'alter session set workarea_size_policy = manual';
  execute immediate 'alter session set sort_area_size = ????';
  execute immediate 'alter session set sort_area_retained_size = ????';
exception 
  when others then 
    --- appropriate handler here
end;
/


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.