Recentemente ho dovuto analizzare un DB Oracle che manifestava rallentamenti su alcuni statement SQL che “in condizioni normali” venivano eseguiti in parallel. Gli statement SQL di cui era stato constatato un rallentamento venivano eseguite tutte dallo stesso applicativo e avevano tutti la particolarità di accedere in full table scan su una tabella di grandi dimensioni definita con parallel_clause uguale a 16. Da un punto di vista dei processi server utilizzati la situazione del DB era la seguente:

SQL> SELECT * FROM v$px_process_sysstat;

STATISTIC VALUE
------------------------------ ---------
Servers In Use                       248
Servers Available                      8
Servers Started                   150838
Servers Shutdown                  150646
Servers Highwater                    256
Servers Cleaned Up                     0
Server Sessions                  1066951
Memory Chunks Allocated               16
Memory Chunks Freed                    0
Memory Chunks Current                 16
Memory Chunks HWM                     16
Buffers Allocated               15900444
Buffers Freed                   15893892
Buffers Current                     6552
Buffers HWM                         9827

In quel momento il DB aveva 248 parallel server in uso e 8 disponibili (il parallel_max_server sul DB era stato impostato quindi a 256).

Ho eseguito quindi un Full Table Scan sulla tabella incriminata (sempre quella definita con la parallel_clause a 16):

SELECT COUNT(*) FROM tabella;

quindi ho osservato la V$pq_sesstat per verificare che la query fosse stata eseguita in parallel e il DOP (Degree Of Parallelism) utilizzato:

SQL> select * from V$Pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          8             0
Allocation Height                       8             0
Allocation Width                        1             0
Local Msgs Sent                       406           406
Distr Msgs Sent                         0             0
Local Msgs Recv'd                     406           406
Distr Msgs Recv'd                       0             0

La query era stata quindi eseguita in parallel ma con un downgraded da parallel 16 a parallel 8, in quel momento, infatti, erano disponibili solo 8 px server come indicato dalla V$PX_PROCESS_SYSSTAT. In queste condizioni, tutte le query che venivano eseguite in parallel avrebbero quindi subito delle penalizzazioni e quindi dei rallentamenti.

Ok, capito, ma cosa c’entra tutto questo con l’evento SQL * Net break / reset to client? Durante l’analisi avevo notato una situazione molto strana monitorando le sessioni:

select *
from gv$session
where status = 'INACTIVE'
and event = 'SQL*Net break/reset to client';

In particolare, avevo notato che in quel momento sul DB erano presenti 15 sessioni query coordinator (QC) inattive sull’evento di attesa “SQL*Net break/reset to client”, sessioni lanciate tutte da uno stesso applicativo, alcune addirittura vive da diversi giorni prima, che accedevano tutte alla stessa tabella (sempre quella definita con parallel clause 16). Quindi si era arrivati al punto di avere 15*16 (240) processi server occupati senza far nulla. Gli statement SQL erano rimasti appesi perché il client aveva sganciato la sessione e le query erano rimaste orfane ma la sessione non era morta e quindi non erano morti i suoi processi parallel. I processi parallel esistevano ma non stavano facendo nulla perché la loro sessione (QC) query coordinator era in attesa che il client accettasse il reset/break.

Dopo il kill di queste sessioni la situazione era la seguente:

SQL> select * from V$PX_PROCESS_SYSSTAT;
STATISTIC VALUE
------------------------------------------ --------
Servers In Use                                    0
Servers Available                                64
Servers Started                              151230
Servers Shutdown                             151230
Servers Highwater                               256
Servers Cleaned Up                                0
Server Sessions                             1069615
Memory Chunks Allocated                          16
Memory Chunks Freed                               0
Memory Chunks Current                            16
Memory Chunks HWM                                16
Buffers Allocated                          15937578
Buffers Freed                              15937578
Buffers Current                                   0
Buffers HWM                                    9827

Dopo il kill delle sessioni, in condizioni di riposo, il DB teneva quindi vivi solo 64 processi server disponibili ( parallel_min_server) e 0 processi server in uso. Nel momento in cui il carico sarebbe salito ne avrebbe attivato altri fino ad un massimo di 256 (parallel_max_server). Se dovessi mai vedere un tuo DB che accusa sintomi come quelli appena descritti ricordati di quanto appena scritto in questo post.