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:
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.
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%
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).
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.
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.
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.
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.
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