Serveurs utilisateur dédiés et partagés

[Ashleycole]

Bonjour,
je viens solliciter vos éclaircissements sur un sujet qui m'est inconnu, dont j'entends parler pourtant et lequel, semble-t-il pourrait jouer sur les performances d'une Base de Données.
Il s'agit des types de serveurs utilisateurs, DEDIE ou PARTAGE (dedicated/shared je crois).
De quoi s'agit-il réellement?
Un choix le plus souvent intervient en fonction du/des résultats que l'on souhaite avoir,c'est vrai;mais lequel des deux cas est par défaut choisi par Oracle et lequel pourrait faire chuter les performances d'une base de données au point que çà "rame"? C'est-à-dire (dans mon cas), pendant le travail, entre temps le curseur prend la forme de sablier et peut souvent interrompre le travail pendant 15mn, souvent même 30 à 45mn;et avec statpack j'ai détecté des requêtes qui mettent trop de temps dans l'exécution;je me résigne à faire des

Code :

startup force

pour clore les sessions et demander aux utilisateurs de se reconnecter;
Je note que cela peut m'arriver plus de 10 dans une journée de travail et curieusement, des fois, tout est fluide si bien que j'ai du mal à situer le problème.
Merci

[Ashleycole]

Je viens de consulter un rapport statpack dans un intervalle de temps où j'estime que le problème s'est posé.
Là-dedans j'ai lu

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
STATSPACK report FOR
 
DATABASE    DB Id    Instance     Inst Num Startup Time    Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3964322685 guf                 1 12-Jan-12 08:54 10.2.0.1.0  NO
 
Host  Name:   base.guf.bf      Num CPUs:    4        Phys Memory (MB):        3
~~~~
 
Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:        131 12-Jan-12 09:14:16       31      10.7
  End Snap:        133 12-Jan-12 10:14:23       35      10.6
   Elapsed:               60.12 (mins)
 
Cache Sizes                       Begin        End
~~~~~~~~~~~                  ---------- ----------
               Buffer Cache:       592M             Std Block Size:         8K
           Shared Pool Size:     1,104M                 Log Buffer:    14,958K

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
LOAD Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              1,044.41              6,912.27
              Logical reads:                190.85              1,263.14
              Block changes:                  3.89                 25.74
             Physical reads:                  0.54                  3.60
            Physical writes:                  0.54                  3.57
                 User calls:                 20.89                138.23
                     Parses:                  3.34                 22.11
                Hard parses:                  0.07                  0.44
                      Sorts:                  1.50                  9.94
                     Logons:                  0.05                  0.30
                   Executes:                 12.29                 81.34
               Transactions:                  0.15
 
  % Blocks changed per READ:    2.04    Recursive Call %:    57.24
 Rollback per transaction %:   14.13       Rows per Sort:    11.87

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.71    In-memory Sort %:  100.00
            Library Hit   %:   98.23        Soft Parse %:   98.02
         Execute TO Parse %:   72.81         Latch Hit %:  100.00
Parse CPU TO Parse Elapsd %:   53.57     % Non-Parse CPU:   95.95
 
 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:    7.48    8.31
    % SQL WITH executions>1:   65.08   80.93
  % Memory FOR SQL w/exec>1:   80.35   80.41
 
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
enq: TM - contention                             3,530      10,286   2914   99.9
CPU time                                                         7            .1
db file sequential READ                            791           1      2     .0
db file scattered READ                             210           1      5     .0
os thread startup                                   53           1     20     .0

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Host CPU  (CPUs: 4)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                       0.00    0.00      0.05    0.02   99.93

Instance CPU
~~~~~~~~~~~~
              % of total CPU for Instance:    0.10
              % of busy  CPU for Instance:  137.71
  %DB time waiting for CPU - Resource Mgr:

Memory Statistics                       Begin          End
~~~~~~~~~~~~~~~~~                ------------ ------------
                  Host Mem (MB):          2.9          2.7
                   SGA use (MB):      1,744.0      1,744.0
                   PGA use (MB):         67.8         73.0
    % Host Mem used for SGA+PGA:      63527.2      66103.5

les requêtes relatées ici semblent être celles qui consomment beaucoup et donc prennent beaucoup trop de temps

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
SQL ordered BY CPU  DB/Inst: GUF/guf  Snaps: 131-133
-> Resources reported FOR PL/SQL code includes the resources used BY ALL SQL
   statements called BY the code.
-> Total DB CPU (s):              10
-> Captured SQL accounts FOR   85.7% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU
 
    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      1.59            2       0.80   15.3       2.07          17,427  333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
 
      1.05           71       0.01   10.1       1.28         160,031  231290128
Module: frmweb.exe
SELECT ROWID,DOS_GUF_CODE,DOS_NUMERO,ETAP_ACTE_CODE,ACTEUR_CODE,
ETAP_TRAI_CODE,ETAP_TA_CODE,ETAP_NUM_ORDRE,LD_NUM_LOT,DATE_DEBUT
,DATE_FIN,ETAT_ETAPE,MOTIF_REJET,OBSERVATION,DATE_DEBUT_REEL,DAT
E_FIN_REEL FROM ETAPE_DOSSIER WHERE dos_guf_code=:1 AND dos_nume
ro=:2 AND etap_acte_code=:3 AND (DOS_GUF_CODE=:4) AND (DOS_NUMER
 
      0.91           42       0.02    8.8       1.08          15,792  976446690
Module: frmweb.exe
SELECT NVL(MAX (SUBSTR(LPAD(NUMERO , 20 , '0' )  , -20 , 10 ) )
, 0 )  +  1  FROM DOSSIER
 
      0.54           55       0.01    5.2       0.69          12,148 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;
 
      0.45           36       0.01    4.3       0.60          13,318 2727156606
Module: frmweb.exe
SELECT ROLE GRPNAME ,FLAG GRPFLG  FROM FRM50_ENABLED_ROLES  orde
r BY ROLE DESC
 
      0.40           74       0.01    3.9       0.53          15,075 3785553856
Module: frmweb.exe
SELECT ROWID,AP_ACTE_CODE,DOS_GUF_CODE,DOS_NUMERO,AP_PIE_CODE,FO
URNIE FROM PIECE_FOURNIE WHERE AP_acte_code=:1 AND dos_guf_code=
:2 AND dos_numero=:3 AND (AP_ACTE_CODE=:4) AND (DOS_GUF_CODE=:5)
 AND (DOS_NUMERO=:6)
 
      0.39           24       0.02    3.8       0.39          30,079 3873092949
Module: frmweb.exe
SELECT ALL DOSSIER.NUMERO, DOSSIER.GUF_CODE, DOSSIER.DATE_DEPOT,
 DOSSIER.ETAT_DOSSIER, DOSSIER.TYPE_PARCELLE FROM DOSSIER WHERE
DOSSIER.ETAT_DOSSIER = 'ATTENTE' AND DOSSIER.GUF_CODE = :1
 
      0.33        8,661       0.00    3.2       0.33          51,865 3787173813
Module: javaw.exe
SELECT LIBELLE FROM NOMBRE   WHERE CODE =  :1
 
      0.28        4,267       0.00    2.7       0.28          42,670   80611585
Module: javaw.exe
SELECT AG.NOM ||  ' '  ||  AG.PRENOM  FROM AGENT AG   WHERE UPPE
R(AG.CODE )  =  (SELECT UPPER(AGENT_CODE )  FROM SESSION_CAISSE
SQL ordered BY CPU  DB/Inst: GUF/guf  Snaps: 131-133
-> Resources reported FOR PL/SQL code includes the resources used BY ALL SQL
   statements called BY the code.
-> Total DB CPU (s):              10
-> Captured SQL accounts FOR   85.7% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU
 
    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
  WHERE CODE =  :1   )
 
      0.23           44       0.01    2.2       0.23          26,856  297487287
Module: frmweb.exe
SELECT CODE FROM PARCELLE   WHERE NUMERO =  :1  AND LOT_NUMERO =
  :1  AND SECTION_NUMERO =  :1
 
      0.23           16       0.01    2.2       0.23         103,446 1531467468
Module: javaw.exe
SELECT ALL PAIEMENT.NUM_QUITTANCE, PAIEMENT.DATE_PAI, PAIEMENT.M
ODE_PAI, PAIEMENT.BANQUE,  PAIEMENT.INFO_CHEQUE, PAIEMENT.NUM_RE
CU_CARTE, PAIEMENT.MONTANT_PAI, ACTE.LIBELLE ACTE,  DOSSIER.NUME
RO dos_num, SECTEUR_VILLAGE.NUMERO secteur_nuM, SECTION.NUMERO s
ection_num,  LOT.NUMERO lot_num, PARCELLE.NUMERO parcelle_numero
 
      0.22           17       0.01    2.1       0.22          21,549 3633342607
Module: frmweb.exe
SELECT ALL COMMISSION.CODE, COMMISSION.LIBELLE commission, COMMI
SSION.GUF_CODE FROM COMMISSION WHERE code IN (SELECT comis_code
FROM dossier) AND commission.guf_code=:1
 
      0.20           19       0.01    1.9       0.26          24,133  368804450
Module: frmweb.exe
SELECT DOSSIER.NUMERO num_dossier, ACTE.LIBELLE ACTE_lib, DOSSIE
R.GUF_CODE FROM DOSSIER, ACTE WHERE ( (DOSSIER.ACTE_CODE = ACTE.
CODE) ) AND DOSSIER.GUF_CODE=:1 AND TYPE_ACTE='CONSTAT' AND doss
ier.comis_code= :2
 
      0.19           20       0.01    1.9       0.26          24,879 3500326148
Module: frmweb.exe
SELECT COUNT (*) FROM DOSSIER   WHERE PAR_CODE =  :1  AND ETAT_D
OSSIER !=  'ACCORDE'
 
      0.16          249       0.00    1.6       0.18           6,618 1120568383
SELECT parent_owner,parent_name,parent_link_name,parent_type,par
ent_timestamp,property FROM ora_kglr7_dependencies WHERE owner=:
1 AND name=:2 AND type=:3 AND obj#=:4 order by order_number
 
      0.15        4,267       0.00    1.4       0.15          38,404 1755727275
Module: javaw.exe
SELECT 'Secteur ' ||  SECTEUR_VILLAGE.NUMERO  ||  ' Section  '
||  SECTION.NUMERO  ||  '  Lot  '  ||  LOT.NUMERO  ||  ' Parcell
e  '  ||  PARCELLE.NUMERO  FROM PARCELLE , LOT , SECTEUR_VILLAGE
 , SECTION   WHERE ( ( PARCELLE.LOT_NUMERO =  LOT.NUMERO )  AND
( PARCELLE.SECTION_NUMERO =  LOT.SECTION_NUMERO )  AND ( LOT.SEC
 
      0.12        4,267       0.00    1.1       0.12          12,801  808444546
Module: javaw.exe
SELECT DECODE(PERS_TYPE , 'P' , TITRE ||  ' '  ||  NOM  ||  DECO
DE(NOM_JF ,  NULL  , '' , ' Nee ' ||  NOM_JF  )   ||  ' '  ||  P
SQL ordered BY CPU  DB/Inst: GUF/guf  Snaps: 131-133
-> Resources reported FOR PL/SQL code includes the resources used BY ALL SQL
   statements called BY the code.
-> Total DB CPU (s):              10
-> Captured SQL accounts FOR   85.7% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU
 
Instance Activity Stats  DB/Inst: GUF/guf  Snaps: 131-133
 
Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used BY this session                         740            0.2          1.4
CPU used when call started                       499            0.1          0.9
CR blocks created                                234            0.1          0.4
Cached Commit SCN referenced                     272            0.1          0.5
Commit SCN cached                                  1            0.0          0.0
DB time                                    1,227,916          340.4      2,253.1
DBWR checkpoint buffers written                1,930            0.5          3.5
DBWR checkpoints                                   0            0.0          0.0
DBWR transaction TABLE writes                    120            0.0          0.2
DBWR undo block writes                           349            0.1          0.6
IMU CR rollbacks                                 216            0.1          0.4
IMU Flushes                                      159            0.0          0.3
IMU Redo allocation size                     332,288           92.1        609.7
IMU commits                                      373            0.1          0.7
IMU contention                                    31            0.0          0.1
IMU recursive-transaction FLUSH                    3            0.0          0.0
IMU undo allocation size                   1,284,472          356.1      2,356.8
PX LOCAL messages recv'd                           0            0.0          0.0
PX local messages sent                             0            0.0          0.0
SMON posted for undo segment shri                  1            0.0          0.0
SQL*Net roundtrips to/from client             48,359           13.4         88.7
active txn count during cleanout                 396            0.1          0.7
application wait time                      1,028,632          285.2      1,887.4
background timeouts                           11,542            3.2         21.2
buffer is not pinned count                   308,045           85.4        565.2
buffer is pinned count                     4,332,628        1,201.2      7,949.8
bytes received via SQL*Net from c          6,828,241        1,893.1     12,528.9
bytes sent via SQL*Net to client          13,655,874        3,785.9     25,056.7
calls to get snapshot scn: kcmgss             76,934           21.3        141.2
calls to kcmgas                                1,538            0.4          2.8
calls to kcmgcs                                   80            0.0          0.2
change write time                                 20            0.0          0.0
cleanout - number of ktugct calls                382            0.1          0.7
cleanouts and rollbacks - consist                226            0.1          0.4
cleanouts only - consistent read                   7            0.0          0.0
cluster key scan block gets                    6,449            1.8         11.8
cluster key scans                              1,771            0.5          3.3
commit batch/immediate performed                   6            0.0          0.0
commit batch/immediate requested                   6            0.0          0.0
commit cleanout failures: callbac                 24            0.0          0.0
commit cleanout failures: cannot                   2            0.0          0.0
commit cleanouts                               2,696            0.8          5.0
commit cleanouts successfully com              2,670            0.7          4.9
commit immediate performed                         6            0.0          0.0
commit immediate requested                         6            0.0          0.0
commit txn count during cleanout                  86            0.0          0.2
concurrency wait time                            113            0.0          0.2
consistent changes                               376            0.1          0.7
consistent gets                              673,503          186.7      1,235.8
consistent gets - examination                157,418           43.6        288.8
consistent gets from cache                   673,503          186.7      1,235.8
cursor authentications                           229            0.1          0.4
data blocks consistent reads - un                318            0.1          0.6
db block changes                              14,031            3.9         25.7
db block gets                                 14,909            4.1         27.4
db block gets direct                              18            0.0          0.0

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Instance Activity Stats  DB/Inst: GUF/guf  Snaps: 131-133

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
recursive cpu usage                              375            0.1          0.7
redo blocks read for recovery                      0            0.0          0.0
redo blocks written                            8,216            2.3         15.1
redo entries                                   6,787            1.9         12.5
redo ordering marks                              197            0.1          0.4
redo size                                  3,767,188        1,044.4      6,912.3
redo synch time                                   85            0.0          0.2
redo synch writes                              1,298            0.4          2.4
redo wastage                                 292,576           81.1        536.8
redo write time                                  115            0.0          0.2
redo writes                                    1,006            0.3          1.9
rollback changes - undo records a                 33            0.0          0.1
rollbacks only - consistent read                  10            0.0          0.0
rows fetched via callback                     35,707            9.9         65.5
session connect time                               0            0.0          0.0
session cursor cache hits                      7,327            2.0         13.4
session logical reads                        688,412          190.9      1,263.1
session pga memory                        60,139,180       16,672.9    110,347.1
session pga memory max                    89,630,380       24,849.0    164,459.4
session uga memory                   253,409,315,672   70,254,869.9 ############
session uga memory max                   148,736,272       41,235.5    272,910.6
shared hash latch upgrades - no w             16,563            4.6         30.4
sorts (memory)                                 5,415            1.5          9.9
sorts (rows)                                  64,261           17.8        117.9
sql area purged                                    0            0.0          0.0
switch current to new buffer                     112            0.0          0.2
table fetch by rowid                       2,280,124          632.1      4,183.7
table fetch continued row                      3,502            1.0          6.4
table scan blocks gotten                     204,503           56.7        375.2
table scan rows gotten                     9,747,726        2,702.5     17,885.7
table scans (short tables)                    15,018            4.2         27.6
total number of times SMON posted                  1            0.0          0.0
transaction rollbacks                              6            0.0          0.0
undo change vector size                    1,330,464          368.9      2,441.2
user I/O wait time                               232            0.1          0.4
user calls                                    75,333           20.9        138.2
user commits                                     468            0.1          0.9
user rollbacks                                    77            0.0          0.1
workarea executions - optimal                  2,559            0.7          4.7

j'avoue que là, je ne comprends plus bien

Code :

1
2
3
4
5
6
7
8
9
10
 
Instance Activity Stats  DB/Inst: GUF/guf  Snaps: 131-133
-> Statistics WITH absolute VALUES (should NOT be diffed)
 
Statistic                             Begin Value       End Value
--------------------------------- --------------- ---------------
logons current                                 31              35
opened cursors current                        332             372
session cursor cache count                    866           2,649
workarea memory allocated                     164             164

ma configuration système

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
OS Statistics  DB/Inst: GUF/guf  Snaps: 131-133
-> ordered BY statistic type (CPU USE, Virtual Memory, Hardware Config), Name
 
Statistic                                  Total
------------------------- ----------------------
BUSY_TIME                                  1,011
IDLE_TIME                              1,440,791
NICE_TIME                                     22
SYS_TIME                                     313
USER_TIME                                    676
NUM_CPUS                                       4

les stats sql

Code :

1
2
3
4
5
6
7
8
9
 
SQL Memory Statistics  DB/Inst: GUF/guf  Snaps: 131-133
 
                                   Begin            End         % Diff
                          -------------- -------------- --------------
   Avg Cursor Size (KB):           41.62          43.90           5.19
 Cursor TO Parent ratio:            1.03           1.06           2.47
          Total Cursors:             803            957          16.09
          Total Parents:             776            902          13.97

et mon fichier init

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
Parameter Name                Begin value                       (IF different)
----------------------------- --------------------------------- --------------
audit_file_dest               /u01/app/oracle/admin/guf/adump
background_dump_dest          /u01/app/oracle/admin/guf/bdump
compatible                    10.2.0.1.0
control_files                 /u01/app/oracle/oradata/guf/contr
core_dump_dest                /u01/app/oracle/admin/guf/cdump
db_block_size                 8192
db_domain
db_file_multiblock_read_count 16
db_name                       guf
db_recovery_file_dest         /u01/app/oracle/flash_recovery_ar
db_recovery_file_dest_size    10737418240
dispatchers                   (PROTOCOL=TCP) (SERVICE=gufXDB)
job_queue_processes           10
open_cursors                  300
pga_aggregate_target          536870912
processes                     250
remote_login_passwordfile     EXCLUSIVE
resource_limit                TRUE
sga_max_size                  1828716544
sga_target                    1828716544
undo_management               AUTO
undo_tablespace               UNDOTBS1
user_dump_dest                /u01/app/oracle/admin/guf/udump

Dans l'ensemble du rapport,je crois que ma Base roule assez bien (environ à 65%),hormis les quelques données relatées notamment concernant les pourcentages de la SGA et de la PGA que je ne comprends pas.
Alors, je m'excuse pour la longueur du poste avant tout, et j'aimerais qu'on m'éclaircisse sur l'état général de ma base et des modifications éventuelles à effectuer pour avoir de meilleures performances.
Merci

[remi4444]

si tu as constaté les mêmes lenteurs dans l'heure ou tu as fait les stats, c'est que le soucis ne semble pas venir de la base, en tout cas, pas des requêtes. Il y a pas mal d'octets envoyés aux clients, pas mal de fetch, un "application wait time " qui me parait grand. Donc peut etre que le soucis vient de l'applicatif qui a du mal à traiter les donnée

aussi, "le session uga memory" parait énorme, je ne suis pas expert dans ce domaine, il faudrait peut etre investiguer de ce coté là (je crois que c'est la mémoire pour les serveurs partagés)