The index parallel degree was changed after rebuilding it parallel.  Here is my test with 8.1.7.0.0

SQL> select table_name,index_name,degree from dba_indexes
  2  where index_name='I_PLC_CUSTID_UK';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
DEGREE
----------------------------------------
PLC                            I_PLC_CUSTID_UK
1


SQL> alter index loa_data.I_PLC_CUSTID_UK rebuild online;

Index altered.

Elapsed: 00:01:25.03

SQL> select table_name,index_name,degree from dba_indexes
  2  where index_name='I_PLC_CUSTID_UK';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
DEGREE
----------------------------------------
PLC                            I_PLC_CUSTID_UK
1

SQL> alter index loa_data.I_PLC_CUSTID_UK rebuild online parallel 4;

Index altered.

Elapsed: 00:00:56.04
SQL> select table_name,index_name,degree from dba_indexes
  2  where index_name='I_PLC_CUSTID_UK';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
DEGREE
----------------------------------------
PLC                            I_PLC_CUSTID_UK
4

 

Hi, tom:
I am testing how to build up the index faster during database reorg.But however, I failed to figure it out:
Test system has 8CPU/8GRam/8Disk Raid5.
I tried to build index in parallel with nologging, but in parallel is even slower than single thread:
1 thread nologging: 1Minute and 22 second.
6 thread nologging: 1 Minute adn 30 second.
12 Thread nologging: 1 Minute and 40 Second.
No one else using this machine.
And my statspack report during parallel index build:

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 64,594 179,134 41.04
PX Deq: Table Q Normal 88,100 174,969 40.09
PX Deq: Execute Reply 666 61,336 14.05
PX Deq: Execution Msg 731 16,122 3.69
control file parallel write 249 2,139 .49

Can you please give me some idear? IO is 100% busy though, during index build.But from the wait event report,only direct path read is io related, and raid5 is good for read.

parameter:
min_server:0
max_server:12
it is using 12 thread when rebuilding with parallel 12.
And large pool size is 30MB, but after index build, from v$sgastat , the free large pool is still 30MB?

Now I am testing table with 8M records(the table with largest records in my system).
And I am surprised to find:
SQL> CREATE INDEX idx_bidid ON productbidinfo(bid_id)
  2  PARALLEL (DEGREE 2)
  3  NOLOGGING
  4  /

Index created.

Elapsed: 00:04:52.37
SQL> 
SQL> DROP INDEX idx_bidid
  2  /

Index dropped.

Elapsed: 00:00:00.21
SQL> 
SQL> CREATE INDEX idx_bidid ON productbidinfo(bid_id)
  2  PARALLEL (DEGREE 2)
  3  NOLOGGING
  4  /

Index created.

Elapsed: 00:02:47.91
SQL> 
SQL> DROP INDEX idx_bidid
  2  /

Index dropped.

Elapsed: 00:00:00.17
As far as I know, parallel thread use direct path read, which means that it does not go into sga, so why the second time, it runs faster?
And it seems that when i use parallel degree 2, oracle is actually using 4 pxxx running, maybe 2 for read , and 2 for sorting? 
 

Someone here thinks that unless the index is on a partitioned table, the only thing subdivided in parallel is the FTS to make the index. That's not true, is it? Since I imagine it's not.. can you elaborate on the mechanism by which an index is built in pieces and then merged together seemlessly?

Thanks.

Tom -

I found a thread on google groups about this topic, and one of the "seemingly smart" posters said not to build or rebuild in parallel, as it will lead to "fragmentation of the index." Could you refute his argument, or at least shed some light on what he could possibly mean? Based on your comments above, (re)building in parallel is all kinds of good. Is there a downside? Surely there is, otherwise Oracle would automatically do it, right?
</code> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=324BDF39.24C2%40mink.att.com&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DISO-8859-1%26q%3Dparallel%2Bindex%2Bbuild%2Bfragment%26meta%3Dgroup%253Dcomp.databases.oracle.* <code>

Thanks bunches, Tom. So I'm curious...why doesn't Oracle build indexes in parallel by default, then? I mean, you're always saying that you can come up with a case for just as easily as a case against. So what is a valid case against?

ps. Hope you enjoyed your stay overseas. You were missed.

no

Hi

We have moved all the tables and indexes to LMT. The following syntax was used to benefit from the Enterprise Edition version Parallel clause.

ALTER TABLE XXXXX MOVE TABLESPACE LMT PARALLEL NOLOGGING;
ALTER INDEX YYYYY REBUILD TABLESPACE LMTIND PARALLEL NOLOGGING;

The rebuilds and move has been successfully completed for all the objects.

I have been running statspack on our databases. And after the rebuilds and move, I have started observing the following wait events on top 5 wait events in statspack reports.

PX Deq: Execution Msg
PX Deq: Table Q Normal

I understand that PX Deq: Execution Msg is an idle event and can be ignored. But what about PX Deq: Table Q Normal? I have not found any information on this event.

Also, this makes me believe that though I intended to use the PARALLEL clause for the move and rebuilds, the objects have inherited the clause. And performing operations in PARALLEL causing the waits related to Parallel queries.

I checked the DBA_TABLES and DBA_INDEXES but all tables have DEGREE=1 and indexes have DEGREE=DEFAULT.

1. What is wait event PX Deq: Table Q Normal?
2. Does it have a performance impact?
3. How can I determine what objects are causing the parallel wait events?

regards

I got the following information from Metalink

At the moment we consider the following PX wait events as idle:
- "PX Idle Wait"
- "PX Deq: Execution Msg"
- "PX Deq: Table Q Normal"
- "PX Deq Credit: send blkd"
- "PX Deq: Execute Reply"
- "PX Deq Credit: need buffer"
- "PX Deq: Signal ACK"
- "PX Deque wait"

In 9i statspack does not filter out all idle PX wait event in the Top 5 wait event section. So it can happen that you see a PX idle wait event in this section.

This answers my first 2 questions.

regards

I just wonder why I cannot use nologging with subpartition. Would you explain why?

18:04:01 ops$rms@rmsdev> alter index PP_IDX1
18:04:01 2 rebuild subpartition SYS_SUBP4469 nologging
18:04:01 3 /
rebuild subpartition SYS_SUBP4469 nologging
*
ERROR at line 2:
ORA-14189: this physical attribute may not be specified for an index subpartition

Tom,

We recently configured our data warehouse for parallel query using parallel automatic tuning parameters, as suggested.

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
log_parallelism integer 1
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean TRUE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_max_servers integer 40
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

Today, working on tuning a query I noticed high waits. No matter how I affected the explain plan (through hints etc), for all versions of the SQL the elasped time was significantly higher than cpu, and the total query performance remained relatively constant.

The following tkprof output illustrates my point (output from original SQL, no hints).

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.02 0.27 0 32 0 0
Fetch 8 0.26 457.31 0 0 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.28 457.58 0 32 0 28

<...>

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 18 0.00 0.00
PX Deq: Parse Reply 30 0.05 0.24
SQL*Net message to client 8 0.00 0.00
PX Deq: Execute Reply 931 2.00 432.04
PX qref latch 124 1.00 24.54
SQL*Net message from client 8 0.00 0.01
PX Deq: Signal ACK 33 0.10 0.42
enqueue 7 0.00 0.00
latch free 2 0.00 0.00

So a significant amount of time was spent on the PX Deq: Execute Reply and PX qref latch events.

Where do I go from here? I read the Data Warehousing guide, and although it references many dynamic performance views, I found very little instruction regarding how to interprete what these views illustrate. For example, below is output from querying the V$PQ_TQSTAT view post-sql execution.

SELECT dfo_number, tq_id, server_type, process, num_rows, bytes, waits
FROM V$PQ_TQSTAT
ORDER BY dfo_number DESC, tq_id, server_type, process; 2 3

DFO_NUMBER TQ_ID SERVER_TYP PROCESS NUM_ROWS BYTES WAITS
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 0 Consumer P000 19 416 7
1 0 Consumer P001 19 416 55
1 0 Consumer P002 19 416 55
1 0 Producer P003 57 1104 1
1 0 Producer P004 0 72 0
1 0 Producer P005 0 72 0
1 1 Consumer P003 1194417 22332761 13334
1 1 Consumer P004 1192831 22301452 11981
1 1 Consumer P005 1191088 22268771 12656
1 1 Producer P000 98553 1959552 2
1 1 Producer P001 1647644 30760694 50
1 1 Producer P002 1832139 34182738 50
1 2 Consumer P003 17033547 114671679 5375
1 2 Consumer P004 17025347 114616817 5145
1 2 Consumer P005 17007914 114499021 4837
1 2 Producer P000 18029320 119846595 65
1 2 Producer P001 15287535 103182498 54
1 2 Producer P002 17749953 120758424 58
1 3 Consumer P000 9 279 77
1 3 Consumer P001 9 309 117
1 3 Consumer P002 3 138 122
1 3 Producer P003 7 242 0
1 3 Producer P004 7 242 0
1 3 Producer P005 7 242 0
1 4 Consumer QC 7 213 1
1 4 Producer P000 3 81 2
1 4 Producer P001 3 90 2
1 4 Producer P002 1 42 2

28 rows selected.


Please advise.

Regards,

Lisa

Hi

When set index to a parallel degree rebuild does not use parallel processes but when use in the rebuild clause it does use why so?


>alter index x_idx rebuild partition x_part1;

Index altered.

>!ps -ef |grep p0
oracle 1061 1040 0 10:36 pts/0 00:00:00 /bin/bash -c ps -ef |grep p0
oracle 1063 1061 0 10:36 pts/0 00:00:00 grep p0

>alter index x_idx rebuild partition x_part1 parallel 4;

Index altered.

>!ps -ef |grep p0
oracle 1065 1 6 10:36 ? 00:00:00 ora_p000_test8
oracle 1067 1 1 10:36 ? 00:00:00 ora_p001_test8
oracle 1069 1 1 10:36 ? 00:00:00 ora_p002_test8
oracle 1071 1 1 10:36 ? 00:00:00 ora_p003_test8
oracle 1073 1 1 10:36 ? 00:00:00 ora_p004_test8
oracle 1074 1040 0 10:36 pts/0 00:00:00 /bin/bash -c ps -ef |grep p0
oracle 1076 1074 0 10:36 pts/0 00:00:00 grep p0

Looking for a rule of thumb, yeah I know ROT. But we are planning for a generic procedure that will load to the tables of a data warehouse. We will alter the procedure for specific tables if tuning discovers the need. Our plan is to follow advice on the forum to:

1. disable constraints
2. set indexes unusable
3. load data
4. enable constraints
5. rebuild indexes parallel nologging
6. backup
7. set indexes to logging

Our question is ,should we set the indexes to noparallel after the load? Maybe you can't answer this without more info, but maybe you can give examples why you would or would not leave an index in parallel?

Thanks Tom, for this extremely valuable resource.

I'm struggling with the concept of parallelism on an index versus parallelism on a table for queries. Can you give a high level overview of when you would want to parallelize the query against an index versus parallelizing against the table?

If it helps for context, I am working in a data warehousing environment.

Thanks Tom

We have a pretty simple query that runs without issue if we execute directly on the server where the oracle database resides.

If I execute the same query from a different box (after loggin in using tnsnames), I get the following error:

Oracle Error: ORA-12805: parallel query server died unexpectedly

I did traced using tkprof but I did not get any information with regard to this error.
I have the wait events which I got from the tkprof report:

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 26 0.00 0.00
library cache pin 1 0.00 0.00
row cache lock 2 0.00 0.00
DFS lock handle 28 0.19 0.42
enq: TC - contention 2 0.03 0.03
enq: PS - contention 5 0.00 0.00
PX Deq: reap credit 156 1.31 61.46
PX Deq: Join ACK 154 3.94 397.82
rdbms ipc reply 79686 0.00 0.12
reliable message 1 0.00 0.00
SQL*Net break/reset to client 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 206.71 206.71
********************************************************************************


Also, there was no information regarding this in alert log file.

I am wondering why this error what to debug here.

Thanks,

One of the DBA recommended to use INSTANCE_GROUP pamaeter in RAC instances to overcome this error. In fact, quite a lot of RAC instances are using this parameter and along with that another parameter PARALLEL_INSTANCE_**.

How does these parameter help us in RAC instances? How do you set it?

Thanks,

Oracle Error: ORA-12805

Hi tom,

Need your help.

We have a table with 1094000000 rows, and the size of the table 104G. We need to create an index on one of the columns and have failed twice doing it on account of insufficient TEMP tablespace and the other one the UNDO tablespace when the PQ slave got the error :

ERROR at line 1:
ORA-12801: error signaled in parallel query server P062
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$" too small

With undo configured as AUTO, why it could not expand the extent and failed.

How do we calculate that the approx. TEMP and UNDO required to complete this operation. Its taking about 13 hrs and throws an error which adds to more frustation.

Please help.

Hi tom,

Need your help.

We have a table with 1094000000 rows, and the size of the table 104G. We need to
create an index on one of the columns and have failed twice doing it on account
of insufficient TEMP tablespace and the other one the UNDO tablespace when the
PQ slave got the error :

ERROR at line 1:
ORA-12801: error signaled in parallel query server P062
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$" too
small

With undo configured as AUTO, why it could not expand the extent and failed.

How do we calculate that the approx. TEMP and UNDO required to complete this
operation. Its taking about 13 hrs and throws an error which adds to more
frustation.

Please help.

**** -- Just a note that we are maintaing a standby also for the primary database in case of DR, thus the NOLOGGING is out of option.

Is there any way of creating the index in NOLOGGING mode and again sync up with the standby!

Will the primary key creation as a local index would improve the performance of the build process.

Just putting in all my thoughts, and depending on your proposal to go forward as i would n't like to waste another 13 hrs of precious time.

Hi tom,

I autoextend the datafiles to "UNLIMITED" but still the process of creation the index is talikg too long too complete. I thought of something like this and need your guidance and support.

1.Use the export transport tablespace feature to take the datafiles (data and index) on to another database server which will be in NOARCHIVELOG MODE.

2.Generate the PK_INDEX on that database using Alter table Request_data add constraint PK_REQUEST_DATA using (Create index PK_REQUEST_DATA on Request_data(request_tag) nologging parallel 16);

I think it should be quick enough to build the index.

3.Take an export using transport tablespace again from this database server.

4. Drop the tablespace from the Primary Database including contents and datafiles.

5. Looks scary but no other option is left. If I manage to get some space on the box I will rename the tablespace till we import back the stuff. Will there be conflict for the objects when the tablespace is going to get imported for the objects belonging to the same user.

I mean if there is a user A having an object Obj1 in tablespace T1 and I rename the tablespace T1 to T2 and then import back the Tablespace T1 using import Transport tablespace which will also have an object Obj1. what is going to happen?

6.Automatically the tablespaces will be dropped/renamed from the Standby database.

7.Move the datafiles (to both Primary & standby at the same location where they used to be (contents of which can be obtained from v$controlfile) and the metadata for export transport tablespace to the Primary.

8.Import the metadata export file in the Primary database which will plug in these datafiles and the redo logs so generated will be shipped to the standby database covering the Primary & standby to be in safe mode once again.

Please share your thoughts on this.

Hi Tom,

We do pump in log files to the tables via sqlldr, if we have to wait for 13 hrs then we will have a huge backlog to complete which further worries us.

That's the reason we wanted it to follow a course of action if you guide so.

Thanks
Vikas Khanna

I have used the Transport tablespace in 9r2 and you need to keep in mind that you can not transport materialized views and any systems objects like IOT - TOP or objects like SYS_IL0000145234C00011$$ LOBs. Ensure to Identify the transport set violation before dropping any tablespaces.

Hi Tom,

No we are holding back all our work, it's an offline index creation.

No modifications are happening on this table.

Thanks,
Vikas Khanna

Tom,

The system is just sitting idle, doing nothing. We have stopped all activities on the system.

The table is in a single defined tablespace.

Thanks

I am creating a unique index on a table with 8 billion records. Its been running for 17 hours. v$session_logops shows "Table scan". The database is 10.2.0.3 on Solaris 10 server. The database is in noarchivelog mode. The pga_aggregate_target is 1G. Temporary tablespace has extent size of 2G. Please could you how to estimate how long would this take and more importantly what is the better way of building this index quicker than it is now?

Thanks in advance.

big_table%ORA10GR2> delete from plan_table;

0 rows deleted.

big_table%ORA10GR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for create unique index x_idx on big_table(object_id)

Explained.

big_table%ORA10GR2> set linesize 121
big_table%ORA10GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3854999093

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |           |   100K|   488K|   367   (2)| 00:00:05 |
|   1 |  INDEX BUILD UNIQUE    | X_IDX     |       |       |            |          |
|   2 |   SORT CREATE INDEX    |           |   100K|   488K|            |          |
|   3 |    TABLE ACCESS FULL   | BIG_TABLE |   100K|   488K|   321   (2)| 00:00:04 |
------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 2097K bytes

14 rows selected.

Hi Tom

I have 3 questions:

1. While optimizing import (impdp) speed, we decided to build indexes separately, so we loaded the data (without constraints and indexes), created indexes and then imported constraints. While some of the reviewers on forum say that we should load data, enable constraint and then create indexes? The problem I see in latter approach is that some of the PK/UK constraints on partitioned tables lose their names, partitioning schemes etc as creating PK/UK constraints will implicitely create indexes with same name (as constraint) and default storage - I face this time & again, Am I right?

2. How is the behaviour of using parallel=8 in impdp command prompt different from sqlfile's parallel=8 option for index creation? is it that with former, 8 processes will pick up 1 index each and build them with parallel=1 and in latter 1 process will pick up 1 index and build it with parallel=8? Right?

3. I am creating some 100 GB of indexes using sqlfile option (nologging, parallel 8), but it's taking over 10 hours to finish. I don't see a potential issue in AWR which says:

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
d-26tltl1        Linux x86 64-bit                   16     8       2      94.29

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
direct path read                    613,115       4,629      8   30.2 User I/O
direct path read temp                86,566       3,493     40   22.7 User I/O
buffer busy waits                     2,422       1,899    784   12.4 Concurrenc
DB CPU                                            1,178           7.7
direct path write temp               10,064         738     73    4.8 User I/O

..
..

                                                             Avg
                                        %Time Total Wait    wait    Waits   % DB
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path read                613,115     0      4,629       8    766.4   30.2
direct path read temp            86,566     0      3,493      40    108.2   22.7
buffer busy waits                 2,422    47      1,899     784      3.0   12.4
direct path write temp           10,064     0        738      73     12.6    4.8
db file sequential read          16,336     0        491      30     20.4    3.2
control file sequential re       44,063     0        432      10     55.1    2.8
gc buffer busy acquire           14,665     0        381      26     18.3    2.5
log file sync                     2,635     0        356     135      3.3    2.3
control file parallel writ        4,750     0        290      61      5.9    1.9
DFS lock handle                   6,388     4        290      45      8.0    1.9
enq: CF - contention              1,378     0        212     154      1.7    1.4
PX Deq: Slave Session Stat        4,608     4        152      33      5.8    1.0
PX Deq: Table Q Get Keys          1,302     4        116      89      1.6     .8

..
..

top sql:

  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
     4,124        303            0        N/A    26.9 5s21m9r9275k3
Module: SQL*Plus
CREATE UNIQUE INDEX "GDWMGR"."SHIP_ADDR_EXTR_PK" ON "GDWMGR"."SHIP_ADDR_EXTR" ("
SHIP_ADDR_EXTR_I") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 81920
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREE
LIST GROUPS 1 BUFFER_POOL DEFAULT) NOLOGGING TABLESPACE "DDWDAT01" PARALLEL 8

..
..

                               Reads              CPU     Elapsed
Physical Reads  Executions    per Exec   %Total Time (s)  Time (s)    SQL Id
-------------- ----------- ------------- ------ -------- --------- -------------
     4,985,516           0           N/A   44.9   303.09   4123.93 5s21m9r9275k3
Module: SQL*Plus
CREATE UNIQUE INDEX "GDWMGR"."SHIP_ADDR_EXTR_PK" ON "GDWMGR"."SHIP_ADDR_EXTR" ("
SHIP_ADDR_EXTR_I") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 81920
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREE
LIST GROUPS 1 BUFFER_POOL DEFAULT) NOLOGGING TABLESPACE "DDWDAT01" PARALLEL 8
..
..

It is 11.1.0.7 RAC (4 node) Oracle EE, using ASM storage on Linux OS with following parameters:

memory_target                        big integer 8G

parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     320
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     4
parallel_threads_per_cpu             integer     2

pga_aggregate_target                 big integer 0

workarea_size_policy                 string      AUTO

disk_asynch_io                       boolean     TRUE
tape_asynch_io                       boolean     TRUE

Is there anything else I can do to make it run faster? Should I go for more paralle processes - maybe 16?

We are migrating some schemas from Oracle 10g (solaris) to Oracle 11g (rac on linux) and on a different characterst. The target DB is supposed to be consolidated, so I cannot think of another approach but data pump. So, this exericise is to benchmark and reduce the migration timings.

The commands aren't exact same - sqlfile has been modified to include 'nologging' and parallel n option in create index commands? Won't it be faster than using impdp with parallel n?

Thanks for guiding me

One thing I specifically want to (re) confirm is that - even thought the index creation command (DDL) has parallel 1 or parallel 2 and I give parallel 16 in impdp, will it use 16 parallel processes to build an index?

I read this "Note: One worker creates all the indexes but uses PX processes up to the PARALLEL value so indexes get created faster", but still wanted to (re) confirm!

Also, will it be 1 worker -> 1 index (n PX processes where n is from impdp PARALLEL=n) and n worker processes working all together on n indexes? Or will it be 1 index at a time and I'll see only 1 worker process?

I tested the case and found the answer.

Although there is a bug in oracle 11.1.0.7 that index creation always uses parallel 1 (maybe it will be useful for someone)

BUG 8604502 - INDEXES ARE ALWAYS CREATED WITH PARALLEL DEGREE 1 DURING IMPORT

Hi Sir,

How are you? . I am trying to refresh a schema. In which i am using a datapump import to import just the data and before this i am disabling the constraints and dropping the indexes and after the data is loaded i am creating the indexes and enabling the constraints. In this process the most time consuming part was the index creation there were around 1800 indexes and it took around 90min with parallel around 6 to create the index . I have come up with an alternative to run the index creation in parallel in multiple threads with parallels set 4 and 6 so my cpu will be handling 10 process at a time even with this approach i still get the same time results it takes around the same time when creating the indexes in serial or running in multiple threads. what could be the possible reason for this behavior and how to speed up the index creation process. Please advice.

Thanks for you time.

Tom, have you noticed creating transportable backup sets takes quite a bit of time as well? I was all fired up to start using those for one our apps that's about 70GB, it takes 3 hours to complete. I recall seeing in the documentation that it's a slow process creating the auxiliary instance. It's just a shame though, it's such a cool feature.

It does not take me 3 hours to restore 70GB either. Why the difference? Well for one thing I have a bunch of channels allocated, but when you run a TSPITR or a TT backup set, RMAN manages all the channels to the auxiliary instance. That could be it, I'm not really sure why it takes so much longer.

Thanks for the reply sir.
And for the creating indexes in lesser time you have suggested to transport the data. What exactly do you mean by transport of data is it using insert /*+append*/ over a database link please advice.

Thanks.

I have an application is slow performing. I checked the data dictionary and found that some indexes are having more than 600 extents and some more than 1000 extends. The application used to be faster earlier. Also the statistics are cureent. I kind of doubting the extends and rebuilding the indexes in parallel and take the degree to 1 after rebuild. Am I doing it right?