Select sum (bytes) from dba_segment: waiting for & #39; gc cr request & #39;, dba_segmentgc

Select sum (bytes) from dba_segment: waiting for 'gc cr request', dba_segmentgc
SO: 0x5533efea0, type: 4, owner: 0x554631060, flag: INIT/-/0x00 if: 0x3 c: 0x3
Proc = 0x554631060, name = session, file = ksu. h LINE: 12624 ID:, pg = 0
(Session) sid: 388 ser: 1 trans: 0x0, creator: 0x554631060
Flags: (0x51) USR/-flags_idl: (0x1) BSY /-/-/-/-/-
Flags2: (0x40409)-/INC
DID:, short-term DID:
Txn branch: 0x0
Oct: 3, prv: 0, SQL: 0x5425ece68, psql: 0x450616048, user: 0/SYS
Ksuxds FALSE at location: 0
Service name: SYS $ BACKGROUND
Current Wait Stack:
0: waiting for 'enq: PV-syncstart'
Name | mode = 0x50560006, 0 = 0x0, 0 = 0x0
Wait_id = 8642197 seq_num = 64811 snap_id = 1
Wait times: snap = 3934 min 28 sec, exc = 3934 min 28 sec, total = 3934 min 28 sec
Wait times: max = infinite, heur = 3934 min 28 sec
Wait counts: cballs = 1088 OS = 1088
In_wait = 1 iflags = 0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker (s ):
Inst: 1, sid: 773, ser: 1
Dumping final blocker:
Inst: 1, sid: 397, ser: 7

----------------------------------------
SO: 0x55da-f80, type: 4, owner: 0x55463e8e8, flag: INIT/-/0x00 if: 0x3 c: 0x3
Proc = 0x55463e8e8, name = session, file = ksu. h LINE: 12624 ID:, pg = 0
(Session) sid: 773 ser: 1 trans: 0x0, creator: 0x55463e8e8
Flags: (0x51) USR/-flags_idl: (0x1) BSY /-/-/-/-/-
Flags2: (0x409)-/INC
DID:, short-term DID:
Txn branch: 0x0
Oct: 0, prv: 0, SQL: 0x0, psql: 0x0, user: 0/SYS
Ksuxds FALSE at location: 0
Service name: SYS $ BACKGROUND
Current Wait Stack:
0: waiting for 'enq: PR-contention'
Name | mode = 0x50520006, 0 = 0x0, 0 = 0x0
Wait_id = 410416 seq_num = 934 snap_id = 1
Wait times: snap = 3934 min 29 sec, exc = 3934 min 29 sec, total = 3934 min 29 sec
Wait times: max = infinite, heur = 3934 min 29 sec
Wait counts: cballs = 76 OS = 76
In_wait = 1 iflags = 0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker (s ):
Inst: 1, sid: 397, ser: 7
Dumping final blocker:
Inst: 1, sid: 397, ser: 7
There are 1 sessions blocked by this session.
Dumping one waiter:
Inst: 1, sid: 388, ser: 1
Wait event: 'enq: PV-syncstart'
P1: 'name | mode' = 0x50560006
P2: '0' = 0x0
P3: '0' = 0x0




PROCESS 45: CJQ0
----------------------------------------
SO: 0x554645d80, type: 2, owner: 0x0, flag: INIT/-/0x00 if: 0x3 c: 0x3
Proc = 0x5 54645d80, name = process, file = ksu. h LINE: 12616 ID:, pg = 0
(Process) Oracle pid: 45, ser: 2, callcur/top: 0x5394a48b8/0x5394a48b8
Flags: (0x2) SYSTEM
Flags2: (0x0), flags3: (0x10)
Intr error: 0, call error: 0, sess error: 0, txn error 0
Intr queue: empty
Ksudlp FALSE at location: 0
(Post info) last post received ed: 0 0 9
Last post received-location: ksq. h LINE: 2014 ID: ksqrcl
Last process to post me: 554631060 1 2
Last post sent: 0 0 9
Last post sent-location: ksq. h LINE: 2014 ID: ksqrcl
Last process posted by me: 554631060 1 2
(Latch info) wait_event = 0 bits = 0
Process Group: DEFAULT, pseudo proc: 0x554a0000f8
O/S info: user: oracle, term: UNKNOWN, ospid: 8914
OSD pid info: Unix process pid: 8914, image: oracle @ callcenter-db1 (CJQ0)
SO: 0x5533d4fc0, type: 4, owner: 0x554645d80, flag: INIT/-/0x00 if: 0x3 c: 0x3
Proc = 0x554645d80, name = session, file = ksu. h LINE: 12624 ID:, pg = 0
(Session) sid: 397 ser: 7 trans: 0x0, creator: 0x554645d80
Flags: (0x51) USR/-flags_idl: (0x1) BSY /-/-/-/-/-
Flags2: (0x40409)-/INC
DID:, short-term DID:
Txn branch: 0x0
Oct: 0, prv: 0, SQL: 0x0, psql: 0x0000caef60, user: 0/SYS
Ksuxds FALSE at location: 0
Service name: SYS $ BACKGROUND
Current Wait Stack:
0: waiting for 'OS thread startup'
= 0x0, = 0x0, = 0x0
Wait_id = 2782824 seq_num = 30355 snap_id = 1
Wait times: snap = 3934 min 34 sec, exc = 3934 min 34 sec, total = 3934 min 34 sec
Wait times: max = 2 min 0 sec, heur = 3934 min 34 sec
Wait counts: cballs = 77 OS = 77
In_wait = 1 iflags = 0x15a0
There are 4 sessions blocked by this session.
Dumping one waiter:
Inst: 1, sid: 773, ser: 1
Wait event: 'enq: PR-contention'
P1: 'name | mode' = 0x50520006
P2: '0' = 0x0
P3: '0' = 0x0
Row_wait_obj #: 4294967295, block #: 0, row #: 0, file #0
Min_blocked_time: 0 secs, waiter_cache_ver: 63462
Wait State:
Fixed_waits = 0 flags = 0x22 boundary = 0x0/-1
Session Wait History:
Elapsed time of 0.000160 sec since current wait
0: waited for 'enq: PR-contention'
Name | mode = 0x50520006, 0 = 0x0, 0 = 0x0
Wait_id = 2782823 seq_num = 30354 snap_id = 1
Wait times: snap = 4 min 56 sec, exc = 4 min 56 sec, total = 4 min 56 sec
Wait times: max = infinite
Wait counts: cballs = 8 OS = 8
Occurred after 2.476248 sec of elapsed time
1: waited for 'OS thread startup'
= 0x0, = 0x0, = 0x0
Wait_id = 2782822 seq_num = 30353 snap_id = 1
Wait times: snap = 41 min 57 sec, exc = 41 min 57 sec, total = 41 min 57 sec
Wait times: max = 2 min 0 sec
Wait counts: cballs = 2 OS = 2
Occurred after 0.000168 sec of elapsed time
2: waited for 'enq: PR-contention'


Dumping one waiter:
Inst: 1, sid: 773, ser: 1
Wait event: 'enq: PR-contention'
P1: 'name | mode' = 0x50520006
P2: '0' = 0x0
P3: '0' = 0x0
Row_wait_obj #: 4294967295, block #: 0, row #: 0, file #0
Min_blocked_time: 0 secs, waiter_cache_ver: 63462


Internal wait relationship of the database:
Blocking blocker event
388 773,397 enq: PV-syncstart
773 397 enq: PR-contention
397 enq: PR-contention OS thread startup: ora-445


-- 1
Node 1 run the select sid statement, serial # from gv $ session where username = 'wanqiong ':
Cause of failure: PROCESS 53/57 runs the statement for two sessions, PROCESS 20: CKPT block.
PROCESS 20 because waiting for 'enq: PR-contention 'name | mode = 0x50520006
0x50520006 owner: PROCESS 45: CJQ0
Analysis by awk-f ass109.awk calldb1_ora_15639.trc:
Resource Holder State
Enqueue PR-00000000-00000000 45: 0: waiting for 'OS thread startup'
Enqueue PV-00000000-00000000 38: 38: is waiting for 45:
Latch 380030bc0 ??? Blocker
PROCESS 45: CJQ0 processes are the culprit! The process cannot be started.

-- 2 node 2
Node 2 running statement: select sum (bytes) from dba_segments, waiting time 0: waiting for 'gc cr request'
No obvious node communication error was found in cssd and crsd. It is suspected that node 1 will crash.






-- 3 Node 1 warning Log Analysis
Mon Aug 11 09:45:49 2014
Archived Log entry 58557 added for thread 1 sequence 27907 ID 0x4bcd9425 dest 1:
Mon Aug 11 16:08:44 2014
Errors in file/oracle/diag/rdbms/calldb/calldb1/trace/calldb1_cjq0_8914.trc (incident = 56362 ):
ORA-00445: background process "J000" did not start after 120 seconds
Incident details in:/oracle/diag/rdbms/calldb/calldb1/incident/incdir_56133/calldb1_cjq0_8914_i56362.trc
Kkjcre1p: unable to spawn jobq slave process
Errors in file/oracle/diag/rdbms/calldb/calldb1/trace/calldb1_cjq0_8914.trc:
Mon Aug 11 16:12:25 2014
Errors in file/oracle/diag/rdbms/calldb/calldb1/trace/calldb1_mmon_8497.trc (incident = 56202 ):
ORA-00445: background process "m000" did not start after 120 seconds
Incident details in:/oracle/diag/rdbms/calldb/calldb1/incident/incdir_56202/calldb1_mmon_8497_i56202.trc
Mon Aug 11 16:14:28 2014


Awr analysis memory 16 GB
Begin End
Host Mem (MB): 15,744.015, 744.0
SGA use (MB): 4,608.04, 608.0
PGA use (MB): 462.5460.1
% Host Mem used for SGA + PGA: 32.20.2.19


12 GB memory after restart
[Oracle @ calldb1/home/oracle] $ prtconf | grep 'memory'
Memory size: 12288 Megabytes


We suspect the problem lies in the memory:
1379200.1 according to the document, you can determine:




What does this message mean?


The message indicates that we failed to spawn a new process at the Operating System level to serve the request.
There are varous causes for this issue. This typically occurs when there is a temporary age or misconfiguration in
Operating System Resources, and thereby the problem shocould be investigated from an OS perspective. However
There are a few causes related to the Oracle Database as well.
The default 120 seconds (after which Oracle times out) can be extended dynamically (without a database restart) B
Setting the following event:


OS Configuration Checks [hardware fault confirmed yesterday]
Checks on Oracle Database Configuration
-- Dump systemstat 10 before database restart
/Oracle/diag/rdbms/calldb/calldb1/trace/calldb1_ora_15639.trc
How to obtain select sum (count), id, from aa, and sum in SQL

You can use the number resultSet. getInt (1 );
Or give an alias: select sum (count) as ss, id from aa and then resultSet. getInt (ss)

SELECT sum (1) FROM table; and SELECT count (1) FROM table;

The two have different processing methods for null records.

Count is used to count all rows.

Sum is the sum of rows not null,

Therefore, the results of the above two statements are different when there is a null row (the results are the same if there is no null record)

Msdn mentioned this problem in the 1st sentence of the aggregate function, as shown below:
Aggregate functions calculate a group of values and return a single value. Except COUNT, All Aggregate functions ignore null values.
Msdn.microsoft.com/zh-cn/library/ms173454 (v = SQL .90). aspx

-----------------------
Haven't lz understood it yet?

There is such a table tmp
A
1
2
3
4
5
6
Null
8

The value of count (1) is 8.
The sum (1) value is 7.

-----------------------
I thought lz had understood the usage of sum before asking about the unique usage of sum (1 ).
It seems that we have to explain all the Aggregate functions as follows:
Or the above example
Sum (a) = 29
Sum (1) = 7
Count (a) = count (1) = count (*) = 8
Max (a) = 8
Min (a) = 1
Avg (a) = sum (a)/sum (1) = 29/7 = 4.14 (if a is of the int type, the result is automatically rounded to 4 ), note that avg skips null rows, so the denominator can only be calculated as sum (1)

Check whether lz has any questions.

----------------------------
Pair

Lz scores are hard to earn.

Let's take a look at the above msdn connection, which contains detailed examples.
Msdn is well written. If you have any problems, it's good to go to it.

Msdn mentioned this problem in the 1st sentence of the aggregate function, as shown below:
Aggregate functions calculate a group of values and return a single value. Except COUNT, All Aggregate functions ignore null values.
Msdn.microsoft.com/zh-cn/library/ms173454 (v = SQL .90). aspx