Memoire PGA + fichier temp [Résolu]

[Jenesépa]

Bonjour,


Je vais reprendre un petit peu la démarche de korian.
J'ai une BDD oracle 9.0.2.4 en mode OLTP. J'ai constaté que le fichier temp n'arrêtait pas de grossir, je suis donc allé sur le forum et suis tombé sur le lien avec sort_area_size puis sur pga_agregate_target. Dans mon cas workarea_size_policy est en AUTO.

Code :

1
2
3
4
5
Oracle9i Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS FOR 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

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
SQL> SHOW PARAMETER pga_aggregate_target;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
pga_aggregate_target                 big integer 50331648
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     2097152
sort_area_size                       integer     1048576
workarea_size_policy                 string      AUTO
aggregate PGA target parameter	50331648	bytes
aggregate PGA auto target	4194304	bytes
global memory bound	2515968	bytes
total PGA inuse	73123840	bytes
total PGA allocated	91985920	bytes
maximum PGA allocated	104840192	bytes
total freeable PGA memory	0	bytes
PGA memory freed back TO OS	0	bytes
total PGA used FOR auto workareas	32768	bytes
maximum PGA used FOR auto workareas	3888128	bytes
total PGA used FOR manual workareas	0	bytes
maximum PGA used FOR manual workareas	0	bytes
over allocation count	6851	
bytes processed	2264163328	bytes
extra bytes READ/written	184237056	bytes
cache hit percentage	92,47	percent
 
 
PGA_TARGET_FOR_ESTIMATE,PGA_TARGET_FACTOR,ADVICE_STATUS,BYTES_PROCESSED,ESTD_EXTRA_BYTES_RW,ESTD_PGA_CACHE_HIT_PERCENTAGE,ESTD_OVERALLOC_COUNT
 
12582912	0,25	ON	2272029696	1089937408	68	147
25165824	0,5	ON	2272029696	1077878784	68	139
37748736	0,75	ON	2272029696	1050681344	68	131
50331648	1	ON	2272029696	177567744	93	123
60397568	1,2	ON	2272029696	177567744	93	117
70463488	1,4	ON	2272029696	177567744	93	101
80530432	1,6	ON	2272029696	174934016	93	30
90596352	1,8	ON	2272029696	162827264	93	0
100663296	2	ON	2272029696	162827264	93	0
150994944	3	ON	2272029696	141462528	94	0
201326592	4	ON	2272029696	141462528	94	0
301989888	6	ON	2272029696	141462528	94	0
402653184	8	ON	2272029696	33868800	99	0

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
  2   onepass_count, round(onepass_count*100/total, 2) onepass_perc,
  3   multipass_count, round(multipass_count*100/total, 2) multipass_perc
  4  FROM
  5   (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
  6  sum(OPTIMAL_EXECUTIONS) optimal_count,
  7   sum(ONEPASS_EXECUTIONS) onepass_count,
  8   sum(MULTIPASSES_EXECUTIONS) multipass_count
  9  FROM v$sql_workarea_histogram
 10    WHERE low_optimal_size > 64*1024);
 
OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT
------------- ------------ ------------- ------------ ---------------
MULTIPASS_PERC
--------------
         1282        98,24            23         1,76               0
             0

Code :

1
2
3
4
5
6
SQL> SHOW parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
lock_sga                             BOOLEAN     FALSE
pre_page_sga                         BOOLEAN     FALSE
sga_max_size                         big integer 210837176

En fait je souhaiterais savoir si ma con figuration est ok sachant que j'ai un serveur Windows 2003 32 bits avec 4Go de RAM dont 2.2 de disponible.
Et également comment faire pour réduire la taille de ce fichier temporaire d'Oracle ?
Je ne sais pas si ma démarche est dans la bonne direction si ce n'est le cas pourriez vous me guider

[root_nizar]

Bonjour

Je trouve on gros que la mémoire PGA est bien dimensionner puisque le paramétre :cache hit percentage est à 92,47% est se paramétre refléte la performance de la gestion automatique de la PGA .

Presque toutes les zones de travail ont été exécutées en mode optimal
Par contre tu parles de quel fichier temporaire oracle ???

[Jenesépa]

Je parle de ce fichier qd je fais la requête suivante :

Code :

1
2
3
4
 
 SELECT * FROM dba_tablespaces
TABLESPACE_NAME	BLOCK_SIZE	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENTS	MAX_EXTENTS	PCT_INCREASE	MIN_EXTLEN	STATUS	CONTENTS	LOGGING	FORCE_LOGGING	EXTENT_MANAGEMENT	ALLOCATION_TYPE	PLUGGED_IN	SEGMENT_SPACE_MANAGEMENT	DEF_TAB_COMPRESSION	
TEMP	 8 192   	 1 048 576   	 1 048 576   	 1   		0	1048576	ONLINE	TEMPORARY	NOLOGGING	NO	LOCAL	UNIFORM	NO	MANUAL	DISABLED

De ce que j'ai compris ce fichier se vide lorsque la base est arrêté, mais j'ai l'impression que la taille ne bouge pas et que le fichier est perpétuellement rempli ? Ma crainte est la taille physique du serveur qui est très limitée (reste 1 GB).

Aujourd'hui le fichier temp01 évolue en espace physique autour de 32 GB (la moitié de la taille de la bdd), cela me semble énorme.
Il y a un param SEGMENT_SPACE_MANAGEMENT qui indique manuelle qui ne semble pas être pris en compte (ou est déterminé cette taille maxi?)
Y a t'il un moyen de limiter sa taille et quelles en sont les impacts ?

[root_nizar]

Lorsque tu fait un shutdown de la base et la tablespace temp ne se libère pas ça veux dire que :
--> soit le processus SMON ne fait pas le nettoyage des tablespaces temp et qui est fixé tous les 2 heures .
--> si tu exécutes cette requête :

Code :

SELECT count(*) FROM db_extents WHERE segment_type = 'TEMPORARY';

Si ce nombre s'accroît constamment alors il faut faire un wake up du process SMON
et pour faire :
-->

Code :

1
2
SELECT pid FROM v$process
 WHERE addr = (SELECT paddr FROM v$bgprocess WHERE name = 'SMON');

Et ensuite

Code :

ORADEBUG WAKEUP 6

Cordialement.

[Jenesépa]

Merci la requête ci dessous me renvoie 0

Code :

1
2
 
SELECT count(*) FROM dba_extents WHERE segment_type = 'TEMPORARY';

Cependant si je recherche une type TEMPORARY je ne trouve rien est ce normal ?

[root_nizar]

La vue

Code :

V $ TEMP_SPACE_HEADER

t'enregistre l'espace libre pour les tablespaces temporaires.
Tu peux voir est ce qu'il requéte qui font le tri et qui staure la mémoire temporaire ,sinon rajoute un tempdbfile pour avoir plus d'espace .

sinon augmente le paramétre sort_area_size

Cdlt

[root_nizar]

Re-

Tu doit avoir une tablespace temp du type TEMPORARY, parce'que si la zone de tri en mémoire est insuffisante alors il utilisera le TEMPORARY TABLESPACE
sinon il va utilisé la tablespace SYSTEM