Notes on parallelism between Oracle rebuild index and parallel

 

 

1. Rebuild index and parallelism description

In the previous blog, I sorted out some index-related blogs:

 

Oracle index details

Http://blog.csdn.net/tianlesoftware/article/details/5347098

 

How to speed up index creation

Http://blog.csdn.net/tianlesoftware/article/details/5664019

 

Five types of Oracle index Scanning

Http://blog.csdn.net/tianlesoftware/article/details/5852106

 

Oracle index Maintenance

Http://blog.csdn.net/tianlesoftware/article/details/5680706

 

Oracle alterindex rebuild and ORA-08104 description

Http://blog.csdn.net/tianlesoftware/article/details/6538928

 

When indexing create and rebuild, we can use parallel to speed up the operation as the CPU permits. However, there is a concern here that the degree of parallelism of indexes should also be noted for this table.

 

For OLTP databases, we recommend that you do not adjust the degree of parallelism for related tables or indexes unless it is only used for statistical or report tables or indexes. When parallel queries are enabled for a database, there is a default degree of parallelism in the table or index, which will lead to the database preferentially using the full table or full index scan execution plan, in addition, multiple parallel sub-processes will be generated. For OLTP applications, the execution efficiency of related SQL statements will be reduced.

 

For details about parallel, the official website is described as follows:

Http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#i2159323

 

The parallel_clause letsyou parallelize creation of the table and set the default degree of parallelismfor queries and the DML insert, update, delete, andmerge after table creation.

 

(1) noparallel: Specify noparallel forserial execution. This is the default, that is, the degree of parallelism is 1.

 

(2) parallel: Specify parallel ifyou want Oracle to select a degree of parallelism equal to the number of cpusavailable on all participating instances times the value ofthe parallel_threads_per_cpu initialization parameter.

 

(3) Parallel INTEGER: specificationof integer indicates the degree of parallelism, Which is thenumber of parallel threads used in the parallel operation. each parallel threadmay use one or two parallel execution servers. normally Oracle calculates the optimumdegree of parallelism, so it is not necessary for you to specify integer.

 

During parallel processing, Oracle starts several parallel processes to execute tasks at the same time. The higher the degree of parallelism, the more parallel processes, and the faster the execution speed. The default value is noparallel, if we set the degree of parallelism to the default value, the degree of parallelism is:

Number of server CPUs * Number of threads enabled by each CPU (parallel_threads_per_cpu)

 

Therefore, we recommend that you use noparallel or set the degree of parallelism to 1 instead of default.

 

You can use the degree fields in the dba_tables and dba_indexes views to view the degree of parallelism of related objects.

 

Note that when we use parallelism to rebuild indexes, after the rebuild is complete, the degree of parallelism of our indexes will be changed to the degree of parallelism of our rebuild, therefore, after rebuild, we need to modify the degree of parallelism of the index.

 

 

Ii. Example

Test environment: win7 + Oracle 11.2.0.1

 

SQL> select * from V $ version where rownum = 1;

 

Banner

-----------------------------------------------------------------------------

Oracle Database 11g enterprise editionrelease 11.2.0.1.0-64bit Production

 

 

-- View parallel parameters:

SQL> show parameter parallel_max_servers

 

Name type value

-----------------------------------------------------

Parallel_max_servers integer 20

 

SQL> show parameter parallel_threads_per_cpu

 

Name type value

-----------------------------------------------------

Parallel_threads_per_cpu integer 2

 

 

-- Test Table Dave information:

SQL> select count (*) from Dave;

 

Count (*)

----------

333798

 

SQL> Col segment_name for A15

SQL> L

1 * select segment_name, Bytes/1024/1024 | 'M' as "size" from dba_segmentswhere segment_name = 'Dave 'and owner = 'sys'

SQL>/

 

Segment_name size

--------------------------------------------------------

Dave 38 m

 

 

-- Use the default value to create an index:

SQL> Create index idx_dave_id on Dave (object_id );

Index created.

SQL>

 

-- View default values:

SQL> select degree from dba_indexes where index_name = 'idx _ dave_id ';

 

Degree

----------------------------------------

1

-- The default value is noparallel, which is 1.

 

 

-- Rebuild the index with a degree of parallelism of 4:

SQL> ater index idx_dave_id rebuildparallel 4;

Index altered.

 

-- View the degree of parallelism of the index at a time:

SQL> select degree from dba_indexes where index_name = 'idx _ dave_id ';

 

Degree

----------------------------------------

4

 

Note:

This is changed to 4, that is to say, the parallel indexing is enabled. This will affect the execution plan and consume a lot of resources when we use indexes. Therefore, we need to change the degree of parallelism to noparallel.

 

-- Change the degree of parallelism to noparallel:

SQL> alter index idx_dave_id noparallel;

Index altered.

 

-- View the degree of Parallelism:

SQL> select degree from dba_indexes where index_name = 'idx _ dave_id ';

 

Degree

----------------------------------------

1

 

Here it is changed to 1.

 

 

When using parallel processing, we can view related wait events through V $ px_session:

SQL> select. SQL _id,. event, count (*) from V $ session A, V $ px_session B where. SID = B. sid group by. SQL _id,. event;

 

 

 

For more tests on parallelism, refer:

Oracleparallel execution (parallel execution)

Http://blog.csdn.net/tianlesoftware/article/details/5854583

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940