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
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 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 | -----------------------------------------------------------------------------------------------
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
Partager