6f429244e498457b8400e72e4af683b0 Alberto Blog: TUNE SESSION_CACHED_CURSORS UPDATE

mercoledì 8 giugno 2011

TUNE SESSION_CACHED_CURSORS UPDATE

Visto l'interesse per l'impostazione e il tuning di questo parametro,
ho fatto delle ulteriori ricerche.
Mi sono messo in contatto con David Fitzjarrell per chiedere un approfondimento dell'argomento,
così mi ha fornito un statement SQL per aiutare a gestire questo parametro e il
parametro open_cursor.

--
-- sess_cached_cursors.sql
--
-- Retrieve current cursor settings, current cursor allocation and
-- compute percent usage
--
-- Calculate adjusted values where necessary
--
--
-- ddf
--

set linesize 132
column parameter format a30

select parameter, value, usage||'%',
case when to_number(usage) >= 100 then (to_number(usage)+10)*value/100 else to_number(value) end new_setting
from
(select
  parameter,
  lpad(value, 5)  value,
  case when value=0 then '0' else to_char(100 * used / value, '990') end  usage
from
  ( select
      max(s.value)  used
    from
      sys.v_$statname  n,
      sys.v_$sesstat  s
    where
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  ( select
      name parameter, value
    from
      sys.v_$parameter
    where
      name = 'session_cached_cursors'
  )
union all
select
  parameter,
  lpad(value, 5) value,
  to_char(100 * used / value,  '990') usage
from
  ( select
      max(sum(s.value))  used
    from
      sys.v_$statname  n,
      sys.v_$sesstat  s
    where
      n.name in ('opened cursors current', 'session cursor cache count') and
      s.statistic# = n.statistic#
    group by
      s.sid
  ),
  ( select
      name parameter, value
    from
      sys.v_$parameter
    where
      name = 'open_cursors'
  )
)
/
Parameter                  value    usage%      NEW_SETTING
session_cached_cursors   20      100%           22
open_cursors           900      16%                900

Questo è l'ouput dello script che ottengo in una istanza, si può facilmente
vedere che il parametro open_cursor è stato sopravvalutato, perchè solo il
16% ossia 144 cursori sono aperti, l'altro 84% rimane inutilizzato consumando però risorse.
Il session_cached_cursors è utilizzato al 100% quindi si dovrebbe aumentare
questo valore. Il campo new_setting viene valorizzato solo se il value dei
parametri è maggiore o uguale a 100.
In questo caso per esempio, si potrebbe pensare di diminuire open_cursors e di
aumentare session_cached_cursors per un migliore bilanciamento delle risorse.
Viene calcolato nel campo new_setting il nuovo valore che è praticamente il 10%.
Il 10% è stato messo come test, si potrebbe modificare lo script facendo aggiungere
anche il 50%, però per un tuning più equilibrato si deve procedere a piccoli passi,
finchè l'utilizzo non è uguale o inferiore al 100%.
Tutti i commenti sono benvenuti.

8 commenti:

Roberto ha detto...

Credo che se open_cursor è sovradimensionato ciò non comporti consumi aggiuntivi di risorse, è solo un limite, una costante scritta in SGA, per impedire che una sessione apra troppi cursori. Quindi un tuning del parametro open_cursort non penso che sia degno di nota, l'unica accortezza è che non sia troppo basso per l'applicazione, ma in tal caso arriva puntuale la telefonata con l'errore abbastanza esplicativo "maximum open cursors exceeded", che vuol dire che: o effettivamente c'è un sottodimensionamento di open_cursor, o c'è un errore che produce una proliferazione incontrollata di cursori... ecco un buon motivo perché ci sia questo limite.

Riguardo al tuning del parametro session_cached_cursor non sono un fanatico, credo che tranne applicativi particolari (tipo Oracle Forms) i defaults siano spesso abbastanza adeguati. Inoltre bisogna considerare non solo il numero di cursori in cache: "session cursor cache count", ma anche quante volte viene di fatto sfruttata la cache, cioè il rapporto "session cursor cache hits"/"parse count (total)".

P.S. Gli script scritti in caratteri non-courier sono poco leggibili, inoltre non stacca dal resto del testo.

Alberto ha detto...

I cursori sono assegnati 64 alla volta fino al raggiungimento del paramentro OPEN_CURSORS, normalmente avere un valore alto in effetti non comporta consumi aggiuntivi di risorse quindi prendendo i valori dal manuale si potrebbe dire che a parte per applicazioni dove vengono usate dei Form o report dove il valore di questo parametro è sicuramente intorno a 1000 per gli altri sistemi si aggirerà vicino al valore 200.

Tutta un'altra musica per il parametro session_cached_cursor dove se utilizzato bene può ridurre il carico sulla library cache.Tuttavia bisogna tenere conto che un valore alto produce più allocazione di memoria per ogni sessione e per questo aumenterà la memoria UGA che si trova nel PGA in modalità server dedicato e nella SGA in modalità server condiviso.

Quindi prima di eseguire qualsiasi modifica sostanziale per questo valore bisogna eseguire un minimo di tuning.

Tuttavia sempre nei miei appunti ho trovato questo statement:

select
to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
( select value calls from sys.v_$sysstat where name = 'parse count (total)' ),
( select value hard from sys.v_$sysstat where name = 'parse count (hard)' ),
( select value sess from sys.v_$sysstat where name = 'session cursor cache hits' )
/

CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
97.74% 1.96% 0.30%

Roberto ha detto...

L'ultima query non serve a molto. Io ti parlavo di "session cursor cache hits" e "parse count (total)" che trovi nella v$sesstat (non v$sysstat).

Alberto ha detto...

Se scorporiamo lo script sopra in questo modo troviamo i due valori necessari per eseguire il calcolo:
Questo per 'parse count (total)' che in questo caso mi restituisce il valore 2274960
select max(sum(s.value)) used from
sys.v_$statname n,
sys.v_$sesstat s where
n.name in ('parse count (total)') and
s.statistic# = n.statistic#
group by
s.sid;

e questo per il 'session cursor cache hits' che in questo caso mi restituisce il valore 1140319

select max(sum(s.value)) used from
sys.v_$statname n,
sys.v_$sesstat s where
n.name in ('session cursor cache hits') and
s.statistic# = n.statistic#
group by
s.sid

Eseguendo il rapporto "session cursor cache hits"/"parse count (total)" si ottiene 0,501247934.

Roberto ha detto...

Alberto, non capisco qual è il ragionamento che sta sotto le tue queries. Se posso permettermi qualche commento (leggi "critica" se sei pessimista).
Perché usi le scomode viste V_$% di SYS, quando Oracle ti fornisce i sinonimi pubblici V$% ?
La vista V$SESSTAT contiene statistiche relative alle sessioni correnti (cioè quando una sessione termina spariscono le righe relative ad essa), per ogni sessione contiene una riga per ogni statistica.
Quindi la query:
select s.sid, s.value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'parse count (total)';

c'ha una riga per sessione, non serve sum().
Le statistiche 'parse count (total)' e 'session cursor cache hits' possono essere messe in correlazione solo se si riferiscono alla medesima sessione. Se tu calcoli il massimo delle due tra tutte le sessioni, mi spieghi che significato può avere il loro rapporto?

La query corretta (l'ho scritta io quindi non fidarti troppo) che fornisce i rapporti 'session cursor cache hits'/'parse count (total)' è:

select hits.sid, hits, parses, round(hits/parses*100)
from (select sid, s.value hits
from v$statname n, v$sesstat s
where s.statistic# = n.statistic#
and n.name = 'session cursor cache hits'
) hits,
(select sid, s.value parses
from v$statname n, v$sesstat s
where s.statistic# = n.statistic#
and n.name = 'parse count (total)'
) parses
where hits.sid = parses.sid
and parses.parses > 0
order by 4 desc;

Se vuoi puoi poi mediare, calcolare massimi e minimi. Io sinceramente non ho mai approfondito la questione. Volevo solo aggiungere che, oltre alla dimensione della session cursor cache, esiste l'argomento dell'efficienza del suo utilizzo, espressa appunto dal rapporto tra le parse calls e quante di queste by-passano completamente (o quasi) le varie fasi del parsing. L'argomento è avanzato e lo lascio agli esperti.

Alberto ha detto...

Roberto, non sono pessimista quindi non leggo sicuramente "critica" ma quindi sicuramente commento.
Nelle mie queries ci sono degli errori perché in effetti il max su sum non ha molto senso.
Ma la somma dei valori di tutte le sessioni però sì e calcolata con questo SQL:

select sum(s.value) used from
sys.v_$statname n,
sys.v_$sesstat s where
n.name in ('parse count (total)') and
s.statistic# = n.statistic# ;

select sum(s.value) used from
sys.v_$statname n,
sys.v_$sesstat s where
n.name in ('session cursor cache hits') and
s.statistic# = n.statistic# ;

per poi fare il solito rapporto che in questo caso è 14106853/15526277 ossia 0,908579243

Adesso non ho capito se trovi inutile il calcolo di questo valore trovato così oppure se era solo un problema di SQL errati.
Lo statement SQL che hai postato è corretto e visualizza il dettaglio di ogni sessione con la relativa percentuale per ulteriori analisi.

Roberto ha detto...

Era il max() che secondo me proprio non andava. Messo come rapporto di somme potrebbe (per me) avere più senso, per esempio potrebbe essere visto come un indice globale.

Alberto ha detto...

Perfetto allora,sono d'accordo infatti il max() non aveva senso.
Infatti anche per me in questo caso va visto come indice globale di efficienza.

Posta un commento