数据库出现hang住,我们看到process已经超了

进行hanganalyze文件问题收集，怀疑是并行出现了问题

这里的通过sql_id看不到全部的sql_text，也是个问题，这个先解决首要问题，所以先忽略

The SQL Text Stored for a Create Table As Select (CTAS) Statement is Truncated to 20 Characters in View DBA_HIST_SQLTEXT (Doc ID 2039794.1)

我们发现cpu使用率很高，再通过hanganalyze文件分析并行，我们进一步查看数据库并行的情况，parallel_max_servers

关于parallel_max_servers这个参数值的设置

th 11.2.0.2 there is a new method to compute the default for PARALLEL_MAX_SERVERS.

 

In the Oracle Rdbms Reference Guide we find:

parallel_max_servers = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5

In the formula, the value assigned to concurrent_parallel_users running at the default degree of parallelism on an

instance is dependent on the memory management setting.

 - If automatic memory management is disabled (manual mode), then the value of concurrent_parallel_users is 1.

 - If PGA automatic memory management is enabled, then the value of concurrent_parallel_users is 2.

 - If global memory management or SGA memory target is used in addition to PGA automatic memory management,

   then the value of concurrent_parallel_users is 4.

The value is capped by processes -15 (this is true for versions prior 11.2.0.2 as well).

As example we have the following values

 

parallel_threads_per_cpu  = 2

cpu_count                 = 4

pga_aggregate_target      = 500M

sga_target                = 900M

processes                 = 150

 

parallel_max_servers = 2 * 4 * 4 * 5 = 160

parallel_max_servers = min( 150-15 , 160 ) = 135

So with these values we get a default of 135 for parallel_max_servers.

Note if the parallel_max_servers is reduced due to value of processes, then you see similar to the following in alert log (e.g. at instance start up):

Mon May 06 18:43:06 2013

Adjusting the default value of parameter parallel_max_servers

 

 

SQL>  show parameter cpu

 

NAME                                 TYPE        VALUE

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

cpu_count                            integer     60

parallel_threads_per_cpu                integer     2

resource_manager_cpu_allocation        integer     60

 

 

而操作系统上的逻辑cpu为72，这里是oracle使用cpu的个数，没有全部分配给oracle使用,这里有个疑问

[root@wtyzcdb23 ~]# grep processor /proc/cpuinfo | wc -l

72

 

数据库使用的不是自动内存管理（AMM）

 

 

 

 

 

PGA内存自动管理

 

而根据之前的parallel_max_servers的设置方法计算：

Parallel_max_servers=2*60*5*4=2400

 

 

Bug 13743987 - A high CPU_COUNT can cause ORA-68 for parallel_max_servers (Doc ID 13743987.8)

 

 

 

目前parallel_max_servers参考统一支撑1库，设置为400

将_cursor_obsolete_threshold从1024调整为200设置需要重启生效