afte changing INSTANCE_GROUPS, queries no longer executed in parallel_750645.1
After changing the init parameter INSTANCE_GROUPS, queries are no longer being executed in parallel. (Doc ID 750645.1)
In this Document
Applies to:Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 [Release: 9.2 to 11.1] Information in this document applies to any platform. SymptomsChanged the initialization parameter settings for the parameters INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP. Now the parameters are as follows:*.instance_groups='MYRAC,MYRAC1,MYRAC2,MYRAC3' MYRAC1.parallel_instance_group='MYRAC1' MYRAC2.parallel_instance_group='MYRAC2' MYRAC3.parallel_instance_group='MYRAC3' After restarting the instances, parallel execution is disabled on all instances. Parallel query processes do not get spawned even when the execution plan shows parallel. ChangesChanged the initialization parameter settings for INSTANCE_GROUPSCause
Background
The parameters INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP are used to control parallel execution in RAC instances. INSTANCE_GROUPS can have multiple values but it is static; PARALLEL_INSTANCE_GROUP can be modified dynamically at the system level and can also be set at the session level. In order for parallel execution to occur in a session where you have PARALLEL_INSTANCE_GROUP set, the value for PARALLEL_INSTANCE_GROUP has to be found in the list of groups specified by INSTANCE_GROUPS for that instance. If PARALLEL_INSTANCE_GROUP is set to a value which is NOT found in INSTANCE_GROUPS, then parallel execution will be disabled. In this case, it looks like the value of PARALLEL_INSTANCE_GROUP is part of the list of INSTANCE_GROUPS, so it's not clear why parallel execution is disabled. However, in point of fact that is NOT the case - the value of PARALLEL_INSTANCE_GROUP is NOT part of the list of INSTANCE_GROUPS. This is due to the placement of single quotes in the value. The problem is with this setting: *.instance_groups='MYRAC,MYRAC1,MYRAC2,MYRAC3' Due to the single quotes, what this setting does is create a SINGLE instance group named 'MYRAC,MYRAC1,MYRAC2,MYRAC3' This setting created one instance group with a very long name. Then, when setting the parallel_instance groups as follows: MYRAC1.parallel_instance_group='MYRAC1' MYRAC2.parallel_instance_group='MYRAC2' MYRAC3.parallel_instance_group='MYRAC3' these groups 'MYRAC1', 'MYRAC2', 'MYRAC3' don't match any of the existing instance groups -- since the only existing instance group is the awkwardly named group 'MYRAC,MYRAC1,MYRAC2,MYRAC3'. Since the value of parallel_instance_group doesn't match any known instance group, parallel execution is disabled. This issue is hard to spot because the setting for INSTANCE_GROUPS looks correct, but actually says something different than what was intended. SolutionUnfortunately the parameter instance_groups is not dynamically modifiable, therefore this requires restart of each instance. However, multiple instances can have different values for INSTANCE_GROUPS, so the restarts can be staggered, thereby avoiding downtime.1. change the value of instance_groups in the pfile or spfile eg for spfile: alter system set instance_groups='MYRAC','MYRAC1','MYRAC2','MYRAC3' SCOPE=SPFILE SID='*' ; 2. restart each instance one at a time (to avoid downtime) You should now be able to execute queries in parallel again.
NB for 11g: With 11g, parallel query slaves are integrated with Services so that setting up INSTANCE_GROUPS and setting PARALLEL_INSTANCE_GROUP is no longer necessary.
You no longer have to set up the INSTANCE_GROUPS parameter in 11g. Instead of instance groups, you can use the service names directly, eg: ALTER SESSION SET PARALLEL_INSTANCE_GROUP = service_name . The INSTANCE_GROUPS parameter is deprecated in 11g; you can still use it as described in this note, but it is retained for backward compatibility only. You also do not need to set PARALLEL_INSTANCE_GROUPS explicitly in order to restrict query servers to a subset of instances. If you execute a SQL statement in parallel, the default behaviour is that parallel processes only run on the instances that offer the service with which you originally connected to the database. This does not affect other parallel operations such as parallel recovery or the processing of GV$ queries. To override this behavior, set a value for the PARALLEL_INSTANCE_GROUP initialization parameter. For more information, see "Administering Services" in Chapter 4 of the Oracle? Real Application Clusters Administration and Deployment Guide for 11.1 . ReferencesNOTE:201799.1 - Relevant Parallel Excution INIT.ora ParametersNOTE:47331.1 - Init.ora Parameter "INSTANCE_GROUPS" Reference Note NOTE:47351.1 - Init.ora Parameter "PARALLEL_INSTANCE_GROUP" Reference Note |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1144705/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- GCD QueriesGC
- DataPump Export/Import Of LOBs Are Not Executed in ParallelExportImportParallel
- PG: Utility queries
- CSS media queriesCSS
- Oracle Lock Information QueriesOracleORM
- F. Scalar Queries
- F - Two Sequence Queries
- X86 CPU First instruction ExecutedStruct
- Database performance gets slower the longer the database is runningDatabaseORM
- Trees and XOR Queries AgainAI
- ElasticSearch:Request cannot be executed; I/O reactor status: STOPPEDElasticsearchReact
- ORA-8103 "object no longer exists"-8103.1Object
- 第2節:mysql.gtid_executed表/gtid_executed變數/gtid_purged變數的更改時機MySql變數
- 淺談Blocking Changing Tracking檔案BloC
- partition table update partition-key result in changing tablespace
- [轉]Hierarchical Queries之LEVEL應用
- Changing between 32-bit and 64-bit Word Sizes
- PostgreSQL DBA(170) - pgAdmin(Queries For Index Maintenance)SQLIndexAINaN
- Testing JPA Queries with Spring Boot and @DataJpaTestSpring Boot
- influxdb 筆記: Continuous Queries - CQsUX筆記
- Queries to view Alert Log content And Alert LocationView
- Monitor All SQL Queries in MySQL (alias mysql profiler)MySql
- ORA-8103 "object no longer exists" 診斷與解決Object
- DataStage系列教程 (Slowly Changing Dimension)緩慢變化維AST
- PostgreSQL DBA(36) - PG 12 Inlined WITH queriesSQLinline
- Educational Codeforces Round 19 E. Array Queries
- root使用者操作檔案提示 changing permissions of '***': Operation not permittedMIT
- SRVCTL CHANGING GROUP OWNERSHIP FROM OINSTALL TO ASMADMIN FOR ORACLE BINARYASMOracle
- rst2pdf介紹 – Changing Restructured Text into PDFs with PythonRESTStructPython
- Java訪問Elasticsearch報錯Request cannot be executed; I/O reactor status: STOPPEDJavaElasticsearchReact
- AIX: Database performance gets slower the longer the db is running_316533.1AIDatabaseORM
- AIX: Database performance gets slower longer the database is running_316533.1AIDatabaseORM
- No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executedUI
- MySQL 如何獲取執行中的Queries資訊?MySql
- Media Queries媒體查詢常用關鍵詞
- Codeforce GYM 100741 A. Queries
- Procedure PqStat to monitor Current PX Queries (Doc ID 240762.1)
- 【層次查詢】Hierarchical Queries之LEVEL應用