Amis DBA bonjour,

Je sèche depuis ce matin sur la signification des colonnes BUFFERS et READS dans un plan d'exécution.
La doc Oracle ne mentionne rien, pouvez-vous me dire à quoi cela correspond précisément? Je précise "précisément" car la colonne BUFFERS augmente parfois de façon bizarre, surtout si je lis des données via un ou plusieurs "user rowid".

Test 1 : un rowid
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> select /*+ gather_plan_statistics */ * from zztest where rowid = 'AAAUKFAAMAAACRDAAA';
...
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID	1kwbmj9ubvzma, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from zztest where rowid =
'AAAUKFAAMAAACRDAAA'
 
Plan hash value: 833697196
 
-----------------------------------------------------------------------------------------------
| Id  | Operation		   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |	    |	   1 |	      |      1 |00:00:00.01 |	    1 |
|   1 |  TABLE ACCESS BY USER ROWID| ZZTEST |	   1 |	    1 |      1 |00:00:00.01 |	    1 |
-----------------------------------------------------------------------------------------------
Test 2 : dix rowid. Deux pour la colonne BUFFERS alors que les ROWID pointent vers le même bloc Oracle selon DBMS_ROWID.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
SQL> select /*+ gather_plan_statistics */ * from zztest where rowid IN ('AAAUKFAAMAAACRDAAA','AAAUKFAAMAAACRDAAB','AAAUKFAAMAAACRDAAC','AAAUKFAAMAAACRDAAD','AAAUKFAAMAAACRDAAE','AAAUKFAAMAAACRDAAF','AAAUKFAAMAAACRDAAG','AAAUKFAAMAAACRDAAH','AAAUKFAAMAAACRDAAI','AAAUKFAAMAAACRDAAJ');
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	f963u8zq3nbbh, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from zztest where rowid IN
('AAAUKFAAMAAACRDAAA','AAAUKFAAMAAACRDAAB','AAAUKFAAMAAACRDAAC','AAAUKFA
AMAAACRDAAD','AAAUKFAAMAAACRDAAE','AAAUKFAAMAAACRDAAF','AAAUKFAAMAAACRDA
AG','AAAUKFAAMAAACRDAAH','AAAUKFAAMAAACRDAAI','AAAUKFAAMAAACRDAAJ')
 
Plan hash value: 904234787
 
------------------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	    1 |        |     10 |00:00:00.01 |	     2 |
|   1 |  INLIST ITERATOR	    |	     |	    1 |        |     10 |00:00:00.01 |	     2 |
|   2 |   TABLE ACCESS BY USER ROWID| ZZTEST |	   10 |      1 |     10 |00:00:00.01 |	     2 |
------------------------------------------------------------------------------------------------
 
Note
-----
   - statistics feedback used for this statement

Par avance merci pour vos retours